Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 30 ยท Cassandra Data Model

Partition keys and clustering keys

Understand the anatomy of a Cassandra primary key: the partition key that picks the node and the clustering columns that sort rows inside a partition.

If you remember only one thing about Cassandra data modeling, remember this: a primary key has two jobs. The first part, the partition key, decides which nodes store the data. The second part, the clustering columns, decides how rows are sorted inside that place. Every Cassandra table design, good or bad, comes down to how these two parts are chosen.

In this lesson you will learn the syntax of composite primary keys, what a partition looks like, why a query must name its partition, how clustering order lets you read the latest rows first, and why oversized "wide" partitions are dangerous. Real Cassandra cannot run on this site, so the CQL is read-only. The JavaScript stand-ins build a small partitioned table with sorted rows so you can watch the layout and the rules in action. They model the idea, not the real storage format.

Anatomy of a primary key

SQL
CREATE TABLE readings_by_sensor (
  sensor_id text,
  ts        timestamp,
  temp      double,
  PRIMARY KEY ((sensor_id), ts)
) WITH CLUSTERING ORDER BY (ts DESC);

Read the PRIMARY KEY clause as two layers. The inner parentheses (sensor_id) are the partition key. Everything after them (ts) is a clustering column. So all readings of one sensor form one partition, and inside it they are stored sorted by time, newest first because of CLUSTERING ORDER BY (ts DESC).

The partition key can contain several columns. The extra parentheses are what mark it as composite:

SQL
PRIMARY KEY ((sensor_id, day), ts)

Here a partition is one sensor on one day. If you write PRIMARY KEY (sensor_id, ts) with no inner parentheses, Cassandra treats the first column as the partition key and the rest as clustering columns, which is the same as the first example. Being explicit with double parentheses avoids confusion.

flowchart LR
    K["Primary key<br/>((sensor_id), ts)"] --> PK["Partition key<br/>sensor_id"]
    K --> CC["Clustering column<br/>ts"]
    PK --> H["Hash to a token"]
    H --> N["Chooses the replica nodes"]
    CC --> S["Sorts rows inside the partition"]

The partition key goes through the hash and picks the nodes; clustering columns only order the rows once you are there.

What a partition is

A partition is a group of rows that share the same partition key. All the rows of a partition are stored together, on the same replicas, in one sorted structure. That has two big consequences.

Reads inside a partition are cheap. Cassandra finds the partition by hash and then reads a contiguous, already-sorted slice of rows. "Give me the last 10 readings of sensor 7" is a fast query: one partition, the first 10 rows.

Reads across partitions are expensive. Rows in different partitions may live on different machines. There is no global ordering between partitions, so "all readings above 30 degrees from any sensor" would have to ask every node.

This is why Cassandra requires you to name the partition. A SELECT must restrict every partition key column with = (or IN, which fans out to several partitions and should be used with care). Without that, Cassandra does not know where to look.

Clustering columns: sorting and slicing

Clustering columns control the order of rows inside a partition, and they also define what slices you can read. The rules, in plain terms:

  • You can restrict clustering columns in the order they are declared: first the first one, then the second, and so on.
  • You may use equality on the leading ones and a range (<, >=, BETWEEN-style pairs) on the last one you restrict.
  • The default order is ascending. CLUSTERING ORDER BY (ts DESC) stores descending, so "newest first" needs no work at read time.
SQL
SELECT * FROM readings_by_sensor
WHERE sensor_id = 's1'
LIMIT 2;                       -- the two newest readings

SELECT * FROM readings_by_sensor
WHERE sensor_id = 's1' AND ts >= '2026-03-01' AND ts < '2026-03-02';

The primary key as a whole is also what makes a row unique. Writing a row with the same partition key and the same clustering values overwrites the existing one, which is the upsert behaviour from CQL: keyspaces, tables and basic queries.

Stand-in 1: a partitioned, sorted table

The WideTable class below stores rows in a Map of partitions. Each partition keeps its rows sorted by the clustering columns on every insert. select refuses to run unless the partition key is given, and supports a range on the first clustering column plus limit. Run it and compare the storage layout to the descriptions above.

JS Partitions with sorted rows

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The rows arrived in a jumbled order, yet each partition lists them from newest to oldest. The re-inserted (s1, 30) did not add a row; it replaced the old one. And a query that names only ts was rejected, because the table cannot find a partition from that.

Wide rows and wide partitions

Because one partition holds many rows, Cassandra people sometimes talk about "wide rows": a single partition key that has thousands or millions of clustering rows. That is a feature when used on purpose: a sensor's time series, a user's inbox, an order's line items.

It becomes a problem when a partition grows without limit. A partition is stored on its replicas as a unit, and it is read, repaired and compacted as a unit. Oversized partitions make reads slow, use a lot of memory and cause uneven load, because the one node holding the giant partition does all the work for it. A typical rule of thumb is to keep a partition well under about 100 MB, though the exact limit depends on your hardware and usage.

The cure is to put something time-like or bucket-like into the partition key, so that each partition stays bounded. For example, (sensor_id, day) makes a new partition every day. You will see this pattern in depth in Time-series and bucketing patterns.

Stand-in 2: bounding partition size

The program below simulates one reading every ten minutes from one sensor for 30 days (4,320 rows). It stores them under two designs: a partition per sensor, and a partition per sensor per day. It prints how many partitions there are, the largest partition and how the rows spread over four nodes, using the toy hash from Cassandra architecture in one picture.

JS One huge partition or many small ones

Press Run (or Ctrl + Enter) to see the console output here.

No output.

With a single partition per sensor, all 4,320 rows landed on one node and the other three held nothing. With a partition per day there are 30 partitions of 144 rows each, spread over all four nodes. This is the effect, and the numbers come from this toy hash; real clusters distribute the same way in spirit.

The cost of the second design is that a query for a month now has to name each day: the application asks for 30 partitions. Every modeling decision here trades a simple query for a safe partition size.

Choosing the partition key

A good partition key has these properties:

  1. It appears in your query's WHERE as an equality. If you cannot supply it at read time, the table does not serve that query.
  2. It has many distinct values, so data spreads across the cluster. A key with only a handful of values (such as country) creates a few giant partitions and hot nodes.
  3. It keeps partitions bounded. Add a bucket such as the day or month if the data keeps growing.

A good clustering column is something you want sorted or sliced inside the partition: time, a sequence number, an alphabetical name.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In PRIMARY KEY ((sensor_id, day), ts), what is the partition key?

    The inner parentheses mark the partition key: the pair (sensor_id, day). ts is the clustering column.
  2. 2. What does a clustering column do?

    Clustering columns sort rows inside one partition. Which node stores the partition is decided by the partition key alone.
  3. 3. Why must a SELECT normally restrict the whole partition key with equality?

    The partition key is hashed to a token to locate the replicas. Without it Cassandra would have to look on every node.
  4. 4. What is the main danger of a partition that grows forever?

    A partition is handled as a unit, so oversized partitions slow reads and strain the nodes that hold them. Bucketing the partition key keeps them bounded.

Summary

  • The partition key picks the nodes; clustering columns sort rows inside the partition.
  • A partition is a group of rows sharing a partition key, stored together and read as a sorted slice.
  • Queries must restrict the whole partition key; clustering columns are restricted in declaration order, with a range allowed on the last one used.
  • CLUSTERING ORDER BY stores rows in the order you will read them, such as newest first.
  • Keep partitions bounded by adding a bucket to the partition key when data keeps growing.

Next: Query-first data modeling.