Cassandra Admin: Meter readings: an unbounded partition that keeps growing for 24 months

Meter readings: an unbounded partition that keeps growing for 24 months

An energy utility stores smart-meter readings. 12,000 meters report every 30 seconds - roughly 34 million writes per day, retained for 24 months. The table is readings (meter_id uuid, reading_time timestamp, watts int, PRIMARY KEY (meter_id, reading_time)). Each meter therefore produces about 2,900 rows per day, or 2.1 million rows across the retention window, all in one partition. Three access paths exist: a dashboard showing one meter's last 24 hours at 30-second resolution; a nightly billing job scanning one meter across one calendar month; and a new requirement to list every meter on a feeder (200 meters per feeder, 60 feeders) with its most recent reading. Read p99 is already 900 ms and heap occupancy climbs during the nightly job. Compaction never catches up because reads touch rows spread over many SSTables. Compression is LZ4, gc_grace_seconds is 864000, and there are no TTLs.

1. Explain why read p99 is 900 ms today and why it will degrade further. Propose a primary key change and state precisely what it costs the application.

Read the worked answer

The latency is not a disk-bandwidth problem, it is partition size. A 2.1 million-row partition means a partition index measured in megabytes, the partition is spread across many SSTables, and every dashboard read is a multi-SSTable merge inside a very large partition. At 30-second resolution, 24 hours is only 2,880 rows, so the cost is index seeking plus heap churn from partition index sampling - not scanning rows. Growth is linear, so p99 keeps climbing until a single partition no longer fits comfortably in memory.

Change the key to PRIMARY KEY ((meter_id, bucket), reading_time) with bucket as text such as 2026-03 (or an int like 202603). That caps a partition at roughly 87,000 rows.

Costs to the application:

Every read must now supply the bucket. The 24-hour dashboard query spans two buckets at month boundaries and needs two parallel queries merged client-side.
Ordering is only guaranteed inside a bucket; a cross-bucket merge is the client's job.
The billing job becomes a single-partition scan, which is the large win and justifies the change on its own.
Do not use IN on partition keys for the dashboard path - it becomes coordinator-side fan-out with one read per partition plus a merge, and it hides the real cost instead of removing it.

2. Design the schema for all three access paths. Say explicitly whether the original table survives, and how you keep the two write paths consistent.

Read the worked answer

Three access paths, three tables. In Cassandra each query must map to one partition, so denormalisation is the design, not a compromise.

readings_by_meter with PRIMARY KEY ((meter_id, month), reading_time) serves the 24-hour dashboard (two buckets at most) and the monthly billing scan (one partition).
meter_latest_by_feeder with PRIMARY KEY (feeder_id, meter_id) and regular columns last_reading_time, watts. Clustering by meter_id makes a feeder a single-partition scan of 200 rows, and the partition is naturally bounded.
The original readings table is retired. Keeping it 'just in case' means paying write and compaction cost for data nothing reads.

Consistency between the two writes:

Do not wrap them in a batch. They are different partitions, so it becomes a multi-partition logged batch: coordinator overhead plus a batchlog write, for no atomicity benefit you can actually rely on.
Write readings_by_meter on every sample (34 million/day) and meter_latest_by_feeder on a slower cadence - every 5 minutes per meter is 12,000 updates per interval, which is cheap and still gives a dashboard-fresh 'latest' view.
Accept that the latest-reading table can lag by minutes and document it. If it must be exact, derive it from readings_by_meter in the read path instead of maintaining a second write.

3. An engineer proposes `CREATE INDEX ON readings (feeder_id)` for the new feeder query and says ad-hoc thresholds on `watts` can use `ALLOW FILTERING`. Evaluate both and explain what you would do instead.

Read the worked answer

Both proposals fail for the same underlying reason: neither maps to a single partition.

Secondary index: index entries are written per row, so at 34 million inserts per day the index becomes larger than the base data within weeks. A query for one feeder's index entries is scatter-gathered - the index is local to each node, so the coordinator asks every node and merges server-side. It also returns every reading ever written for those meters in arbitrary order, with no clustering order available, so the 'most recent reading' requirement forces a client-side sort over millions of rows. Low selectivity (60 distinct values) is only acceptable when the rows per value are small; here they are effectively unbounded.

ALLOW FILTERING on watts: the predicate is not a primary key component, so the query degrades to a full scan of every partition in the table. On 34 million rows per day it will time out at the default read request timeout and it puts the load on whatever coordinator receives it. It is acceptable only for a one-off operational query that you run token-range by token-range, never in a request path.

What to do instead: the denormalised meter_latest_by_feeder table, written as readings arrive or on a five-minute cadence. That is one partition read of 200 rows, no scatter-gather, ordered by clustering column. For genuine ad-hoc analytics on watts, export to Spark or run token-range scans offline; do not solve it with a secondary index.