Data Modeling: Queries First

This lesson turns what you have learned into one rule for designing Cassandra tables.

Relational databases start with the data

In MySQL or PostgreSQL, you would design a chat app like this: a users table, a rooms table, and a posts table that points to both. Each piece of data is stored once. Later, any new question ("posts by user", "posts by room") is answered with a JOIN that combines the tables. You design the data first, and the queries come later.

Cassandra starts with the queries

Cassandra has no joins. And as you saw, WHERE needs the partition key. So a table can answer one kind of question well: "give me this partition, in clustering order". That is why the design steps are turned around:

  1. List the queries your app needs first. For example: "show a user's posts, newest first" and "show a room's posts, newest first".
  2. Make one table for each query. The partition key is what you look up by. The clustering columns are the order you want.
CREATE TABLE posts_by_user (
  author text,
  posted_at timeuuid,
  room text,
  content text,
  PRIMARY KEY (author, posted_at)
) WITH CLUSTERING ORDER BY (posted_at DESC);

CREATE TABLE posts_by_room (
  room text,
  posted_at timeuuid,
  author text,
  content text,
  PRIMARY KEY (room, posted_at)
) WITH CLUSTERING ORDER BY (posted_at DESC);
  1. Write every post to both tables, in a batch.

Yes, each post is now stored twice. Storing the same data in more than one table is called denormalization. In relational databases, people try to avoid it. In Cassandra, it is the normal way to work. Disk space is cheap, and so are writes. What is slow is a read that has to collect pieces from many nodes. Here, each query reads just one partition.

This is also why Cassandra tables are often named something_by_lookup, like posts_by_user.

A checklist for your tables

  • One table for each query.
  • The partition key is the column in your WHERE. The clustering columns set the sort order.
  • Keep partitions from growing forever. For example, add the month to the partition key, so each partition holds one month of posts.
  • Your app keeps the copies in sync, using batches.
  • Need a new query later? Make a new table and fill it from the data you already have.

In the exercise, pick the right way to model posts by user and posts by room. In the next lesson, you will write a post into both tables yourself.