Skip to content
All articles

Cassandra Data Modeling: Partition Keys, Sizing, and Splitting Hot Partitions

Dean Jain

Dean Jain

Senior Staff Software Engineer · Enterprise AI, Data & Cloud Architect

· 5 min read

CassandraData ModelingNoSQLPerformance
---
config:
  theme: neutral
  fontSize: 16
---
flowchart LR
    Q["Start with the queries"]:::gov --> T["Design tables around them"]:::server
    T --> PK["Partition key<br/>which node (distribution)"]:::gate
    T --> CK["Clustering key<br/>sort within partition"]:::obs
    classDef gov fill:#FFFFFF,stroke:#333333,stroke-width:1px,color:#111111
    classDef server fill:#F0F0F0,stroke:#333333,stroke-width:1px,color:#111111
    classDef gate fill:#DAE8FC,stroke:#333333,stroke-width:1px,color:#111111
    classDef obs fill:#DCDCDC,stroke:#333333,stroke-width:1px,color:#111111

Figure 1: Cassandra modeling start from the queries, then let the partition key (distribution) and clustering key (sort) serve them.

If you bring relational instincts to Cassandra, you will build a slow, painful database. There are no joins, no referential integrity, and normalization the thing you were taught to prize actively hurts you. Cassandra modeling inverts the whole process: you start with the queries and let the data organize around them, and the single most important object is the partition. Get the partition design right and Cassandra is blisteringly fast at scale; get it wrong and you’ll fight hot partitions and slow queries forever.

Summary

  • Query-first design. Don’t model the data and then write queries model the queries, then build tables to serve them.
  • Denormalize, don’t join. No joins exist; duplicate data into purpose-built tables instead. Duplication is the design, not a smell.
  • Partition key vs clustering key. The partition key decides which node stores the data (distribution); clustering columns decide how it’s sorted within the partition.
  • Aim for one partition per query. A query that hits a single partition is the fast path; minimize partitions touched.
  • Watch partition size, split hot ones. Keep partitions under ~100K cells; split an over-wide one by adding a column to the partition key.

1. Query-first, and denormalize

Relational design teaches you to normalize model entities cleanly, then reassemble them with joins. Cassandra punishes that, because at its scale joins across distributed nodes are infeasible, so the engine simply doesn’t offer them. The consequences cascade into a different discipline:

  • No joins if you need join-like results, you either do the work client-side (rare) or, far better, create a denormalized table that already contains the joined result.
  • No referential integrity no cascading deletes, no cross-table constraints. Consistency between tables is your application’s job.
  • Denormalization is the goal Cassandra performs best when data is denormalized and duplicated across tables, each shaped for a specific query.
  • Query-first you start with the query model, not the data model. List the most common query paths your app needs, then create exactly the tables that serve them.

The mindset flip: in relational design, one normalized model serves many queries via joins. In Cassandra, each query gets its own table, even if that means storing the same data several ways. Storage is cheap; the join you can’t do at read time is expensive. Embrace the duplication it’s the whole strategy, not a compromise.

2. The primary key is two jobs

Cassandra’s primary key does two distinct things, and conflating them is the root of most modeling mistakes:

---
config:
  theme: neutral
  fontSize: 16
---
flowchart TD
    PK["PRIMARY KEY ((a, b), c, d)"]:::gov
    PK --> P["Partition key (a, b)<br/>which node stores it"]:::gate
    PK --> C["Clustering key (c, d)<br/>sort order within the partition"]:::obs
    classDef gov fill:#FFFFFF,stroke:#333333,stroke-width:1px,color:#111111
    classDef gate fill:#F0F0F0,stroke:#333333,stroke-width:1px,color:#111111
    classDef obs fill:#DAE8FC,stroke:#333333,stroke-width:1px,color:#111111

Figure 2: A composite primary key the partition key controls distribution across nodes; the clustering key controls sort order inside each partition.

  • The partition key determines which node(s) a row lives on it’s responsible for data distribution across the cluster. It can be a single column or a composite of several (wrapped in extra parentheses).
  • The clustering columns control how data is sorted for storage within a partition and therefore the order your range queries return.

The syntax encodes this precisely:

  • PRIMARY KEY (a) → partition key a.
  • PRIMARY KEY (a, b) → partition key a, clustering key b.
  • PRIMARY KEY ((a, b), c) → composite partition key (a, b), clustering key c.

This drives a hard query rule: you must supply the entire partition key in a WHERE clause (it’s the minimum specifier), then optionally add clustering keys in their defined order. That’s not a limitation to work around it’s the contract. Because sorting is a design decision fixed by your clustering columns (ORDER BY can only follow that order), you decide your access and sort patterns at table-creation time. The goal that ties it together: minimize the number of partitions a query must touch. A query satisfied by a single partition which doesn’t get split across nodes is the best-performing query you can write.

3. Size your partitions and split the hot ones

Query-first design can produce partitions that grow too wide, and wide partitions are the #1 Cassandra performance killer. Partition size is measured in cells (values). The hard limit is 2 billion cells per partition, but you’ll hit performance trouble long before that the recommended ceiling is ~100,000 cells. The formula:

Nv = Nr(Nc − Npk − Ns) + Ns

The number of values Nv = static columns Ns plus rows Nr times the per-row values (columns Nc minus primary-key columns Npk minus static Ns). Run this during design, not after an incident it tells you whether a “model all of a user’s events under one partition key” idea will quietly create a billion-cell monster.

When a partition is (or will be) too wide, split it by adding a column to the partition key:

---
config:
  theme: neutral
  fontSize: 16
---
flowchart LR
    HOT["Hot/wide partition<br/>PK = (sensor_id)"]:::danger --> SPLIT["Add a column to the PK<br/>PK = (sensor_id, day)"]:::good
    SPLIT --> R["Many smaller, balanced partitions"]:::good
    classDef danger fill:#FFFFFF,stroke:#333333,stroke-width:1px,color:#111111
    classDef good fill:#F0F0F0,stroke:#333333,stroke-width:1px,color:#111111

Figure 3: Splitting a hot partition add a column (e.g. a time bucket) to the partition key so data spreads across many balanced partitions.

Usually you move an existing column into the partition key (e.g. add a day or month bucket to a time-series table); occasionally you introduce a dedicated sharding-key column, which needs a little extra application logic.

Indexes denormalize first. When you need to query by a non-key column, you have options, but they’re not equal. A secondary index works, but each node maintains its own index over the partitions it owns, so queries fan out to many nodes and get significantly more expensive. For read performance, denormalized tables or materialized views are preferred a materialized view lets Cassandra keep a query-shaped copy in sync with the base table for you, instead of your app juggling multiple denormalized tables by hand. Reach for secondary indexes (or SASI, which adds inequality and LIKE searches) only for queries you didn’t anticipate, not as the primary access path.

Why it matters: Cassandra rewards a completely different instinct than relational databases model the query, denormalize freely, and treat the partition as sacred. Design each query to hit one partition, size your partitions before they bite, and split the hot ones by extending the partition key. Do that and Cassandra delivers the linear-scale, low-latency performance it’s famous for; ignore it and you’ll have a distributed database that’s somehow slower than the single box you left behind.

References