The Two Parts of a Primary Key
This lesson is the most important one in the course. Cassandra's fast lookups and its strict WHERE rules both come from how primary keys work.
A Cassandra primary key has two parts:
PRIMARY KEY (partition_key, clustering_columns...)
~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~
where the how rows are sorted
row is stored inside the partition
The partition key: where a row is stored
The partition key is the first column of the primary key. You met it in the Introduction: Cassandra hashes its value, and the hash decides which node stores the row. All rows with the same partition key value form one partition. A partition is stored in one place, with its rows side by side.
Clustering columns: the order inside a partition
All other columns of the primary key are clustering columns. They decide the order of the rows inside each partition. The rows are stored on disk in this order.
Here is the orders table from the aggregates lesson:
CREATE TABLE orders (
customer text,
order_date date,
amount decimal,
PRIMARY KEY (customer, order_date)
);
customer is the partition key and order_date is a clustering column. So the data is stored like this:
partition 'alice' partition 'bob'
+---------------------+ +---------------------+
| 2024-01-15 | 120.50 | | 2024-01-20 | 300.00 |
| 2024-02-10 | 20.25 | | 2024-02-25 | 45.99 |
| 2024-03-02 | 75.00 | +---------------------+
| 2024-04-18 | 250.00 |
+---------------------+
sorted by order_date (the clustering column)
You can see this when you read the whole table:
SELECT * FROM orders;
Output:
customer | order_date | amount
----------+------------+--------
bob | 2024-01-20 | 300.00
bob | 2024-02-25 | 45.99
carol | 2024-03-11 | 89.50
alice | 2024-01-15 | 120.50
alice | 2024-02-10 | 20.25
alice | 2024-03-02 | 75.00
alice | 2024-04-18 | 250.00
(7 rows)
The partitions come back in hash order: bob, carol, then alice. But inside each partition, the rows are sorted by order_date.
The two parts together still identify each row, like any primary key. Alice can have many orders, but only one order for each order_date.
Why this design is fast
For WHERE customer = 'alice', Cassandra goes straight to one partition on one node. The rows there are already sorted by date. There is nothing to search and nothing to sort when you run the query. The work was done when the data was written. This is how Cassandra answers in milliseconds, even when a table has billions of rows.
It also explains the rules you have seen so far:
- Rows from different partitions have no useful order between them. They come back in hash order.
- A filter on a column that is not a key has to visit every partition. That is why Cassandra asks for
ALLOW FILTERING.
You will sometimes see a key with double parentheses, like PRIMARY KEY ((sensor_id, day), hour). That is a partition key made of two columns. It comes up in a few lessons.
Answer a short question about partition keys and clustering columns below.