Apache Cassandra
Wide Rows & Time-Series Data Modeling
Learn techniques for modeling time-series data in Cassandra using wide partitions and time-bucketing to avoid unbounded partition growth.
Cassandra is the usual choice for sensor readings, metrics and event logs, and the reason is the shape of a partition.
Instead of stuffing every year's worth of a person's mail into one infinitely growing mailbox, you switch to a new labeled mailbox each month — retrieving one month's mail is still a single simple lookup, and old mailboxes can be discarded wholesale once they're no longer needed.
Key Concepts
1
All readings for one device sit in one partition, sorted by time, together on disk. Asking for the last
hour is one seek and a sequential read.
2
The obvious model breaks slowly. Start here:
3
PRIMARY KEY ((device_id), reading_time)4
One partition per device, growing forever. Work out how fast:
5
1 reading every 10 seconds = 8,640 a day
x 200 bytes = 1.7 MB a day
x 365 = 630 MB after one year6
Cassandra is unhappy above roughly 100 MB per partition and in real trouble past a few hundred. This model
is fine in testing, fine for months, and then degrades — reads slow down, compaction on that partition gets
expensive, and repair streams enormous partitions.
7
The trap is that nothing fails at a threshold. It gets gradually worse, long after the schema was decided.
8
Bucket by time to bound it. Put a time component in the partition key:
9
PRIMARY KEY ((device_id, day), reading_time)10
Now each partition holds one device-day: 1.7 MB, forever. The partition is bounded by construction rather
than by hoping the data stops.
11
Choose the bucket from the arithmetic, not by habit. The right window depends on the rate:
12
10 readings/sec -> hourly buckets (~7 MB each)
1 reading/10 sec -> daily buckets (~1.7 MB each)
1 reading/hour -> monthly buckets (~150 KB each)13
Aim for tens of megabytes. Too large brings back the original problem; too small means a query for a week
has to hit hundreds of partitions.
14
Queries spanning buckets are still fine. A week of daily buckets is seven known partition keys. Issue
seven queries in parallel and merge — you are not searching, because you can compute every key you need.
15
That is the part to say aloud in an interview: bucketing does not cost you the range query, it costs you a
loop in the client.
16
Pair it with TWCS and a TTL. Time-bucketed data is written once, never updated, and eventually expires.
TimeWindowCompactionStrategy keeps each time window in its own SSTables, so when a window's TTL passes the
whole file is dropped — no merging, no tombstone scanning.
17
Under the default STCS, old and new data get mixed into the same files, and expiring the old data means
rewriting files full of live data. That mismatch is the most common cause of a time-series cluster that
slowly fills its disks.
18
What the interviewer is probing.1. "How do you model time-series data?" Probing: the pattern. Stalls: "One row per reading."
Moves up: partition by entity and a time bucket, clustered by timestamp descending, so a query is
one contiguous read.
19
2. "Why bucket the time?" Probing: the reason. Stalls: "Tidiness." Moves up: without it a
partition grows forever past the safe size; bucketing by day or month bounds it.
20
3. "How do you get the newest readings first?" Probing: the technique. Stalls: "ORDER BY in
the query." Moves up: declare the clustering order descending in the table, so it is the physical
order and needs no sort.
21
4. "Which compaction strategy fits?" Probing: the pairing. Stalls: "STCS." Moves up:
time-windowed, so expired windows are dropped whole.