Composite Partition Keys: PRIMARY KEY ((a, b), c)
Sometimes one column is not a good partition key. Think of a table of temperature readings, with one row per sensor per hour.
If the partition key were only sensor_id, every reading of a sensor would go into the same partition. After a few years, that one partition would hold a huge number of rows. A partition that keeps growing without end is called an unbounded partition. It gets slower to read and harder for the cluster to manage. A common guideline is to keep each partition under about 100 MB.
The fix is to make the partition key from two columns: the sensor and the day. Note the double parentheses:
CREATE TABLE sensor_readings (
sensor_id text,
day date,
hour int,
temperature decimal,
PRIMARY KEY ((sensor_id, day), hour)
);
(sensor_id, day) together are a composite partition key. hour is still a clustering column. Now one partition holds the readings of one sensor on one day, so it can never have more than 24 rows. Time can go on forever, and no partition grows too big:
('temp-1', 2024-05-01) ('temp-1', 2024-05-02)
+---------------------+ +---------------------+
| hour 0 | 18.4 | | hour 0 | 17.9 |
| hour 6 | 21.0 | | hour 12 | 25.5 |
| hour 12 | 26.3 | +---------------------+
| hour 18 | 22.1 |
+---------------------+
two partitions of the same sensor
Splitting data into partitions by a time window (one day, one month) is called bucketing.
Give the whole partition key in WHERE
Cassandra needs both columns to find the partition, so your WHERE must have both:
SELECT * FROM sensor_readings
WHERE sensor_id = 'temp-1'
AND day = '2024-05-02';
Output:
sensor_id | day | hour | temperature
-----------+------------+------+-------------
temp-1 | 2024-05-02 | 0 | 17.9
temp-1 | 2024-05-02 | 12 | 25.5
(2 rows)
If you give only sensor_id, Cassandra refuses. It hashes the two values together, so one value alone does not point to any partition:
SELECT * FROM sensor_readings
WHERE sensor_id = 'temp-1';
Output:
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"
This is the same error you got for department in the employees table. Without the full partition key, Cassandra would have to look through every partition.
How to read a key
| Key | Partition key | Clustering columns |
|---|---|---|
PRIMARY KEY (a) | a | none |
PRIMARY KEY (a, b) | a | b |
PRIMARY KEY ((a, b)) | a and b | none |
PRIMARY KEY ((a, b), c) | a and b | c |
The inner parentheses always mark the partition key.
In the exercise, get the readings of one sensor on one day.