Query-first modeling

Key-value stores and wide-column stores scale out by making every query name its key. The key tells the system which machine holds the data. You get fast, predictable reads and writes, and you pay with a fixed model. You design one table per query, and copy data where two queries need different keys.

The big idea

Think of a filing cabinet where each drawer is labeled with a key. You find a file by its drawer. If you later need files by a different label, you copy them into a second cabinet. Copies are cheap to read, but you must keep them in sync.

The model breaks when a query arrives that does not name the key. Then the system must read every drawer, which is slow.

Walking through it

DynamoDB. Every item has a partition key, CUST#7, and a sort key that starts with ORDER# and a timestamp. Items in one partition sit in sorted order.

Query:  PK = "CUST#7" AND begins_with(SK, "ORDER#")
        ScanIndexForward = false, Limit = 20
Result: the 20 newest orders for customer 7, in one partition read

The timestamp in the sort key gives newest-first order for free. That is the DynamoDB way of replacing a join: related items share a partition key, so one query returns them together.

Cassandra. Messages in a chat channel use a partition key of (channel_id, bucket). The bucket is a time window, so no partition grows forever.

CREATE TABLE messages (
  channel_id bigint,
  bucket     int,        -- days since epoch / 10
  message_id bigint,     -- time-sortable ID
  author_id  bigint,
  content    text,
  PRIMARY KEY ((channel_id, bucket), message_id)
) WITH CLUSTERING ORDER BY (message_id DESC);

Output: the table is created. "The latest 50 messages in a channel" is one partition read, already sorted.

This table cannot answer "all messages by one author across channels". That needs a second table keyed by author_id, written alongside the first.

DynamoDB partitions also have hard limits. Items are at most 400 KB. Each physical partition serves about 1,000 writes per second. A single hot partition key therefore tops out near 1,000 small writes per second, however much capacity you buy for the table.

Common mistakes

• Designing tables before listing the queries. Without the list, you cannot pick keys.
• Unbounded partitions. A key like user_id on an activity log fails when one user produces tens of millions of events. Add a time bucket.
• Expecting joins. Pre-join at write time by putting related items under one key.

Check yourself

1. Why does adding more capacity not raise the write rate of one hot key past about 1,000 per second? One physical partition serves about 1,000 writes per second, and a single key lives in one partition.
2. Why does the Cassandra table include bucket? It keeps each partition bounded, so a busy channel does not grow one partition forever.

Key takeaways

• Key-value and wide-column stores require the partition key in every query.
• Design one table per query, and copy data when two queries need different keys.
• Sort keys give ordered reads inside a partition.
• Time buckets keep partitions bounded. Hot keys cap throughput.

Further learning

Rick Houlihan's re:Invent talk shows how access patterns become keys in DynamoDB.

The DynamoDB Book (Alex DeBrie)The most comprehensive book on data modeling in DynamoDB.dynamodbbook.com

Next, compare these ideas against a relational model in an order system, two ways.