RapidMindLabs

Partition Key Design: The Decision You Can't Refactor Later

Almost every Cassandra engagement we run eventually arrives at the same table: the one whose partition key was chosen in week one, before anyone knew what the data would do, and which now cannot be changed without a full rewrite and backfill. Partition keys are the least refactorable decision in a Cassandra schema — columns can be added, tables can be created alongside, but the partition key is baked into how every SSTable on disk is organized. It deserves more design time than it usually gets.

What the partition key actually controls

Three things, all fundamental:

  1. Data placement. The partition key hashes to a token; the token decides which replicas own the row. Every row with the same partition key lives together, on the same nodes.
  2. Query reach. Efficient queries address one partition (or a small, known set). If a query can't name its partition keys, it's a scatter-gather across the cluster — the thing ALLOW FILTERING warns you about.
  3. The unit of load. Reads and writes concentrate on the replicas that own the partition. A popular partition is a popular set of three nodes, no matter how big the cluster is.

Design, then, is a balancing act: partitions must be specific enough to stay bounded and spread load, and general enough that your queries can name them.

Start from the queries

Write down every query the application needs before designing any table. Not abstractly — literally, with the WHERE clauses: "fetch the last 100 readings for a device", "fetch a conversation's messages newest-first", "fetch a user's cart". Each query names the data it can supply at call time; the partition key must be derivable from exactly that data. If a query can't supply a value, that value can't be in the partition key for the table serving that query — full stop. This is why Cassandra modeling produces one table per query pattern rather than one table per entity.

The sizing math

Keep partitions under roughly 100 MB and under a few hundred thousand rows; performance degrades well before Cassandra's hard limits. The math is simple and worth doing explicitly:

  • rows per partition per day × row size × retention days = partition size
  • A device writing one 200-byte reading per second: ~17 MB/day. With 90-day retention, that's 1.5 GB in one partition — far too big.

When the math overflows, add a time bucket to the partition key: (device_id, day) instead of device_id. Now each partition holds one device-day (~17 MB — fine), and the query "last 100 readings" reads today's bucket, spilling into yesterday's only when needed. Choose the bucket size from the math, not from habit — a low-rate sensor might bucket by month; a chat-messages table might bucket by week per conversation.

Hot partitions: the other failure mode

Bounded size isn't enough; load has to spread too. Classic hot-partition designs we still see in reviews:

  • Partitioning events by event_type when 80% of events share one type
  • Partitioning by country for a product with most users in one country
  • A status partition where every worker polls status = 'pending'

The cluster looks idle while three nodes burn. The fix is adding a distributing component — a hash bucket ((event_type, bucket) with bucket = hash(source) mod N) — at the cost of fanning reads across N sub-partitions. That trade is fine when N is small and known.

Anti-patterns that fill our review reports

  • Unbounded growth by design: user_id as the sole partition key for an append-forever event stream. It works in the demo and fails in year two.
  • Secondary indexes as an escape hatch: adding an index because the partition key can't serve a query. On any real cluster, that's a scatter-gather per lookup. Build a second, query-shaped table instead.
  • UUID-only partitions for list queries: partitioning by message_id makes "all messages in a conversation" unanswerable. The partition key should be the grouping (conversation_id), with the unique id as a clustering column.
  • Low-cardinality keys: anything with fewer distinct values than you have nodes guarantees imbalance.

A worked checklist

For each table, answer in writing:

  1. Which query does this table serve, and what values can that query supply?
  2. What is the projected partition size at your retention horizon? Show the arithmetic.
  3. What is the access-skew story — will some partitions be orders of magnitude hotter?
  4. What happens in year three? Growth is the thing week-one designs forget.

If a table can't pass all four, redesign now. It is a whiteboard exercise today and a dual-write migration later — we run those migrations for a living, and we would rather you never need one for this reason.