Bounding a Runaway Telemetry Partition

The partition key is the unit of distribution. Every row with the same vehicle_id lives entirely on the same three replicas and can never be split across nodes, so a bus writing every 200 ms produces 432,000 rows per day that accumulate into one ever-growing partition. At 21 million cells the partition is far past the size where it fits comfortably in the partition index and the page cache.

The LIMIT is not the cost model. To serve 200 rows in descending clustering order, the coordinator's replica must:

1. Locate the partition in every SSTable that contains any row for that key - the partition index is per partition per SSTable, so all of them are opened and their row indexes read.
2. Merge clustering rows across those SSTables (tablestats shows a high SSTables-per-read average because compaction cannot keep up with a partition this large).
3. Skip tombstones and overwritten cells from earlier writes before it can count live rows.

So the read is closer to O(SSTables x partition index size) than O(200). Compaction of a single huge partition is also effectively single-threaded, which is why the backlog persists, and streaming that partition is why repair takes 30+ hours. The fix is to bound the partition, not to tune the LIMIT.

Add a time bucket to the partition key:

CREATE TABLE telemetry (
  vehicle_id uuid, bucket text, event_time timestamp,
  lat double, lon double, speed int,
  PRIMARY KEY ((vehicle_id, bucket), event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

Sizing: use an hour bucket computed by the application (for example 2024-05-01T13). The 200 ms buses then produce 18,000 rows per hour, and normal vehicles 120 rows per hour - all partitions land in the low megabytes, with a bounded number of SSTables and fast repair streams. A day bucket would be 432,000 rows for the buses and is too coarse.

Design consequences you must handle:

• Every query supplies the bucket. The last-N query reads the current bucket; if fewer than N rows come back, read the previous bucket too, concurrently, and merge client-side. Do not use IN on the partition key for this - it defeats token-aware routing and puts the fan-out on one coordinator.
• Uniform bucket granularity across the fleet keeps client code simple; per-vehicle resolution needs a lookup table of vehicle to newest bucket, which adds a read per query and a write per hour.
• Retention gets cheap. Apply a TTL and expired buckets disappear a whole partition at a time; the bucket column also acts as a guard when one emitter misbehaves and starts pinging every 10 ms.

Neither can be served from the existing table, and filtering on lat/lon is hopeless: ALLOW FILTERING scans every partition on every node and merges results at the coordinator, which is the 40 s failure the analyst saw.

Depot screen - add a second, denormalized table written alongside telemetry:

CREATE TABLE vehicle_latest_by_depot (
  depot_id text, vehicle_id uuid, last_seen timestamp,
  lat double, lon double,
  PRIMARY KEY (depot_id, vehicle_id)
);

Each ping upserts one row; the row is overwritten rather than appended, so a depot partition stays at ~1,500 rows and the screen is 40 single-partition reads. Write it asynchronously next to the telemetry insert. Do not wrap the two inserts in a multi-partition BATCH - it adds coordinator work, is not atomic across partitions, and gives no benefit for this pattern. Accept that the tables can diverge for milliseconds and reconcile from telemetry.

lat/lon range - Cassandra has no ad-hoc range predicate on non-key columns. Two realistic options: (a) a pre-computed geohash-prefix table PRIMARY KEY ((geohash_prefix), event_time, vehicle_id) where the query fans out over the 20-40 prefix cells covering the analyst's box; or (b) stream changes to a search or analytics engine and query there. Choose (b) if ad-hoc geographic predicates are a product requirement; choose (a) if they are rare and the boxes are known in advance.