CSE Roadmap

Specialist tier · about 2 hours · 5 parts

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.