+1 (740) 926-6856
RapidMindLabs

Time-Series Data in Cassandra: TTL, Bucketing, and Getting TWCS Right

Time-series is the workload Cassandra handles best: high sustained write rates, append-mostly data, reads scoped to a recent window, and old data that eventually ages out. It is also the workload where we most often find a cluster whose disks are full of data everyone believes expired six months ago.

Three decisions determine whether a time-series table stays healthy for years: how you bucket the partition key, how you expire data, and how compaction groups SSTables by time. They interact. Getting two of the three right is not enough.

1. Bucket the partition key, always

The naive model puts one series in one partition:

CREATE TABLE readings (
  sensor_id uuid,
  ts        timestamp,
  value     double,
  PRIMARY KEY (sensor_id, ts)
) WITH CLUSTERING ORDER BY (ts DESC);

This works in staging and fails in production, because the partition is unbounded: it grows for as long as the sensor exists. A sensor reporting every 10 seconds produces roughly 8,640 rows per day — about 3.15 million rows per year in a single partition. Cassandra will accept that, but compaction, repair, and reads all degrade as partitions grow, and a single partition is never split across nodes.

Our working target is partitions under roughly 100 MB and under a few million cells; we start getting uncomfortable well before that. The fix is a time bucket in the partition key:

CREATE TABLE readings (
  sensor_id  uuid,
  bucket     text,      -- e.g. '2025-06' or '2025-06-14'
  ts         timestamp,
  value      double,
  PRIMARY KEY ((sensor_id, bucket), ts)
) WITH CLUSTERING ORDER BY (ts DESC);

Size the bucket from the arithmetic, not from habit. Take writes per series per second, multiply by bucket seconds, multiply by the bytes per row (cell overhead included — assume more than the raw column widths suggest), and aim an order of magnitude under your partition ceiling so a hot series does not blow the budget.

A rough guide from clusters we have worked on:

  • One row per minute per series: monthly buckets are comfortable (~43k rows).
  • One row per second: daily buckets (~86k rows).
  • Bursty, multi-hundred-Hz series: hourly buckets, and consider whether you should be aggregating before you store.

The cost of bucketing is on the read side: a query spanning a month of daily buckets is 30 partition reads. Issue them as concurrent async single-partition queries and merge client-side. Do not replace them with an IN clause over 30 buckets — that funnels the whole fan-out through one coordinator and turns a parallel read into a serial one with a single point of timeout.

If series have wildly different rates, do not force one bucket width on all of them. Store the bucket width per series in a small metadata table and let the client compute bucket keys. It is more code, and it is far cheaper than re-modeling later.

2. TTL: set it on the write path, and understand what it creates

Expiring time-series data with DELETE is how teams end up with tombstone storms. Use TTL instead — either a table default or a per-write value:

ALTER TABLE readings WITH default_time_to_live = 7776000;  -- 90 days

Two things to be clear-eyed about.

An expired cell is still a tombstone. TTL expiry produces the same kind of shadowing marker a delete does; it just produces it on a schedule you chose, uniformly across the data, instead of in a clump. That is why TTL is friendlier than bulk deletes, not why it is free. Reads that scan an expired range still pay for the markers until they are purged.

Purging waits for gc_grace_seconds. Data is not gone from disk at TTL expiry. It is gone after expiry plus gc_grace_seconds plus the compaction that actually rewrites (or drops) the SSTable. Plan disk capacity for retention + gc_grace, not retention.

That last point is where the temptation appears: set gc_grace_seconds = 0 on a TTL-only table so expired data drops immediately. It is defensible only if the table is genuinely append-only with no explicit deletes and no overwrites — because with grace at zero you also give up repair's role in propagating any deletion marker, which is how deleted data resurrects. If anything ever issues a DELETE or rewrites a row on that table, you have signed up for a data-correctness bug. Most teams should instead use a small grace (a few hours to a day) on strictly append-only TTL tables and keep the repair cycle inside it. Document the decision on the table; the person who later adds a delete path needs to find it.

Keep TTLs uniform within a table. Mixed TTLs in the same partition defeat the whole-SSTable drop optimization described next.

3. TWCS, and the read pattern that defeats it

Time Window Compaction Strategy exists for exactly this shape of data. It groups SSTables by the time window in which their data was written, compacts within a window, and then leaves that window alone. The payoff: when every cell in an SSTable is expired, compaction can drop the entire file without rewriting it. Reclaiming 90 days of telemetry becomes a file deletion instead of a full rewrite of your dataset.

ALTER TABLE readings WITH compaction = {
  'class': 'TimeWindowCompactionStrategy',
  'compaction_window_unit': 'DAYS',
  'compaction_window_size': 1
};

Size the window so that retention spans roughly 20 to 40 windows. Ninety days of retention with one-day windows gives 90 windows — acceptable but toward the high end of SSTable count. Too many windows means too many files and slower reads; too few means each dropped window frees a large, lumpy chunk of disk and reads inside the current window touch a big file. If you find yourself wanting fewer than about 10 or more than about 60 windows over your retention period, adjust the unit rather than pushing the size to an extreme.

TWCS depends on one assumption, and this is the part that breaks in the field: data arrives roughly in time order and is never updated. Three things violate it.

  • Late-arriving data. Devices that buffer offline for a day and then flush write old timestamps into the current window. The SSTable now spans two windows and cannot be dropped until the oldest cell in it expires. Some late data is survivable; a fleet that routinely backfills a week is not, and you should either partition by ingestion time or accept full rewrites.
  • Read repair and repair overwrites. Anti-entropy repair streams old data into new SSTables, mixing windows. This is the most common reason a TWCS table quietly stops dropping files. Run repairs, but subrange-repair on a predictable cycle and watch SSTables per read and max-timestamp spread rather than assuming the strategy is doing its job.
  • Updates to old rows. If your model rewrites historical rows, TWCS is the wrong strategy. Say so out loud before you enable it.

On Cassandra 5, Unified Compaction Strategy can be configured for time-series shapes too, and for mixed workloads it is often the better default. For a strict append-plus-TTL table, TWCS remains the simplest thing that gets whole-file drops, and we still reach for it first. Do not switch a busy production table between the two without a plan for the rewrite storm that follows.

Verifying it works

A time-series table is healthy when you can show these four things:

  1. Bounded partitions. nodetool tablehistograms <ks>.<table> — check the max partition size and cell count against your budget, not the mean. The p99 is what pages you.
  2. Few SSTables per read. Single digits for point and recent-range reads. A creeping number means compaction is losing or windows are mixing.
  3. Droppable tombstone ratio and max timestamp per SSTable. sstablemetadata on the oldest files tells you whether whole-file drops are actually happening. Files older than retention + gc_grace still on disk are the symptom.
  4. Disk reclaimed on schedule. Plot table size on disk over time. A healthy TTL'd table is a sawtooth that returns to roughly the same floor. A staircase means expired data is not leaving.

Most time-series clusters we are asked to fix fail test 1 or test 3: unbounded partitions written years ago, or a TWCS table that has not dropped a file since repairs were re-enabled. Both are fixable, but the first requires a re-model and a dual-write backfill, which is a project. The bucketing math takes an afternoon before launch.

If you are designing a telemetry or event store now, or looking at a cluster whose disk keeps climbing past what retention should allow, get in touch — a cluster and data-model review will tell you which of the four checks is failing and what the remediation costs.