Part 4 of 5 · 3 min read
Compound keys and efficient range scans
A compound key splits into a partition part, deciding which node holds a row, and a clustering part, deciding sort order within that partition.
Compound keys and efficient range scans
Think of a library where each author gets one assigned shelf, the partition. Within that one shelf, the author's own books are kept in publication-date order, the clustering, so browsing one author's timeline in order needs no searching across other shelves.
In plain words: a column-family store commonly uses a compound key made of two parts. The first part, the partition key, decides which node stores the row, exactly like the key-value partitioning covered in the previous module. The second part, the clustering column, decides the sort order of rows sharing the same partition key. A query that specifies the partition key, and then asks for a range of the clustering column, can scan that range efficiently, since the matching rows are already stored together, in order, on one node.
A worked example. The order-events row is keyed by (order_id, event_timestamp). The partition key, order_id, decides which node holds that order's events. The clustering column, event_timestamp, keeps all of one order's events stored together, in time order, so fetching "every event for order 9001 after 2026-01-03" is an efficient, single-node range scan.
The key lines.
-- Conceptual compound key: partition by order_id, cluster by event_timestamp
PRIMARY KEY (order_id, event_timestamp)
-- An efficient range scan within one partition
SELECT * FROM order_events
WHERE order_id = 9001 AND event_timestamp > '2026-01-03';
Compound key (partition key, clustering column)
A compound key combines a partition key, which decides which node stores a row, with one or more clustering columns, which decide the sort order of rows sharing the same partition key. A query naming the partition key and a range on the clustering column can scan that range efficiently within one partition.
Example: a compound key of (order_id, event_timestamp), letting every event for one order be fetched in time order from a single node, is a compound key enabling an efficient range scan.
Remember this
A column-family store pairs a wide, sparse column layout with the LSM-tree write path from Level 5, making it a strong fit for write-heavy workloads. A compound key gives it efficient range scans within one partition, at the cost of the same missing joins and secondary indexes the key-value model gave up. The next page covers choosing among all of this level's models for a real workload.
Quick notes
- A wide-column layout lets each row hold a different, sparse set of columns, grouped into named column families, with no fixed schema declaring every column in advance.
- Column-family stores are commonly built on the LSM-tree write path from Level 5, which absorbs a high rate of writes through sequential appends.
- A compound key splits into a partition key, deciding which node stores a row, and a clustering column, deciding sort order within that partition.
- A query naming the partition key and a clustering-column range can scan efficiently within one partition.
- Column-family stores give up the same joins and native secondary indexes the key-value model gives up, in exchange for write throughput and flexible, sparse rows.