Denormalization: One Post in Two Tables

This last lesson puts the whole course together, using the most common pattern in real Cassandra apps.

Our chat app has two screens:

  • a profile page, which shows the posts by a user
  • a room page, which shows the posts in a room

Query-first design says: two queries, so two tables.

CREATE TABLE posts_by_user (
  author text,
  post_id int,
  room text,
  content text,
  PRIMARY KEY (author, post_id)
);

CREATE TABLE posts_by_room (
  room text,
  post_id int,
  author text,
  content text,
  PRIMARY KEY (room, post_id)
);

To keep the example short, these tables use a simple number, post_id, instead of the timeuuid from the last lesson. Both tables have the same four columns, but different keys. One is partitioned by author, the other by room.

Example: write a post to both tables

When nina posts a message, the app writes it twice, once to each table. A batch makes sure both writes are applied:

BEGIN BATCH
  INSERT INTO posts_by_user
    (author, post_id, room, content)
  VALUES ('nina', 1, 'general', 'hello world');
  INSERT INTO posts_by_room
    (room, post_id, author, content)
  VALUES ('general', 1, 'nina', 'hello world');
APPLY BATCH;

Now each screen gets its posts by reading one partition:

SELECT * FROM posts_by_user
WHERE author = 'nina';
SELECT * FROM posts_by_room
WHERE room = 'general';

Output:

 author | post_id | content     | room
--------+---------+-------------+---------
   nina |       1 | hello world | general

(1 rows)

 room    | post_id | author | content
---------+---------+--------+-------------
 general |       1 |   nina | hello world

(1 rows)

This is the trade. Each write does a little extra work, so that reads stay fast. In most apps, people read posts far more often than they write them, so the trade is worth it.

What you have learned

This small example uses most of the course: tables designed from queries, partition and clustering keys, reads from one partition, and a batch that writes to two tables. That is what day-to-day Cassandra work looks like. Next, you can learn a Cassandra driver for your programming language, so your own apps can run these queries.

In the exercise, nina just posted again. Write the new post to both tables in one batch.