Apache Cassandra
Partition Keys & Clustering Columns
Learn how partition keys determine data placement and clustering columns control sort order within a partition.
A Cassandra primary key does two separate jobs. Splitting them apart is the single most useful thing to understand about the database.
The partition key is like the file cabinet a folder is stored in, and the clustering columns are like the alphabetical order of the documents within that specific folder.
Key Concepts
1
PRIMARY KEY ((device_id), reading_time)
^^^^^^^^^ ^^^^^^^^^^^^
partition clustering
WHERE it ORDER it
lives is stored in2
The partition key decides which machines hold the row. It is hashed into a token, and the token picks
the nodes. Nothing else in the row affects placement.
3
Clustering columns decide the order within that partition. All rows with the same partition key sit
together on the same replicas, contiguous on disk, sorted by the clustering columns.
4
device_id=7 | 10:00 22.1
| 10:01 22.3 <- one partition, sorted by time,
| 10:02 22.4 stored together on disk
device_id=9 | 10:00 19.8 <- different partition, different nodes5
Reading device 7's last hour is one seek and a sequential read on one machine. That is the fastest thing
Cassandra does, and the goal of every data model is to make your queries look like this.
6
Why a relational instinct fails here. In Postgres, physical layout is the database's business. In
Cassandra it is your schema, and picking the partition key wrongly cannot be fixed later with an index.
7
Two ways to get it wrong.Too coarse, and one node melts. Partitioning by country puts every Indian row on the same three
replicas. Those machines take all the traffic while the rest idle — a hot partition. Partitions also have
a practical ceiling around 100 MB, and a growing one eventually breaks the cluster.
country
8
Too fine, and every read is a scatter. Partitioning sensor readings by (device_id, reading_time) as
two partition key parts gives one row per partition. Now "the last hour for device 7" touches sixty
partitions across the whole cluster instead of one.
(device_id, reading_time)
9
The usual fix is a bucket. Keep the partition bounded by adding time to the key:
10
PRIMARY KEY ((device_id, day), reading_time)11
One partition per device per day. It stays a sensible size forever, and a query for one day is still a
single-partition read. Asking for a week means seven, which is fine — you know the seven keys without
searching.
12
Clustering order is fixed at creation. WITH CLUSTERING ORDER BY (reading_time DESC) stores newest
first, so "the latest 20 readings" reads the first 20 rows and stops. Choose it to match your common
query, because reversing it later means rewriting the table.
13
What the interviewer is probing.1. "What is the difference between the two?" Probing: the central concept. Stalls: "They are
the same." Moves up: the partition key decides which node stores the data; clustering columns
order rows within that partition.
14
2. "What makes a bad partition key?" Probing: the failure mode. Stalls: "A long value."
Moves up: low cardinality or uneven access — everything lands on one node, creating a hot
partition while the rest idle.
15
3. "How large can a partition safely get?" Probing: the practical limit. Stalls:
"Unlimited." Moves up: the guidance is under 100MB and ideally under 100,000 rows; beyond that
reads and compaction degrade badly.
16
4. "How do you query by something other than the partition key?" Probing: the modelling
answer. Stalls: "A secondary index." Moves up: a second table modelled for that query —
denormalisation is the intended approach, not indexing.