Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 30 ยท Cassandra Data Model

Time-series and bucketing patterns

Model sensor and event data in Cassandra: time buckets that cap partition size, clustering order DESC, TTL expiry and how to read across buckets.

Time-series data is where Cassandra shines: a steady stream of timestamped facts (sensor readings, logins, clicks, payments), written constantly, and read mostly as "the latest N" or "everything between two times". But there is a trap. The obvious design, one partition per sensor with a row per reading, makes every partition grow forever. This lesson shows why that is a problem and how time buckets fix it, then adds clustering order and TTL.

The CQL appears in read-only blocks, because Cassandra cannot run in a browser. The Python stand-ins, which run in your browser, mimic the design with plain lists and dictionaries so you can see partition sizes and queries across buckets. Numbers printed by the stand-ins describe the toy data, not real Cassandra measurements. You should already know partition keys and clustering keys and query-first modeling.

The naive design and why it breaks

Suppose a thermometer reports every 15 seconds. The first design that comes to mind:

SQL
CREATE TABLE iot.readings_naive (
  sensor_id   text,
  ts          timestamp,
  temperature double,
  PRIMARY KEY ((sensor_id), ts)
);

All readings of one sensor live in one partition, and a partition always lives together on a few replica nodes. After a year that is about two million rows for a single sensor, and the number never stops growing. Large partitions cause real trouble: they are slow to read and compact, they put pressure on memory, repairs have to stream them in one piece, and the nodes that own them become hot spots. Cassandra has a hard limit of about two billion cells per partition, but problems start much earlier. A common rule of thumb is to keep a partition under roughly 100 MB and well below a few hundred thousand rows.

The fix is to put a bucket in the partition key, so a partition covers one sensor for one slice of time, such as a day.

Bucketed design

SQL
CREATE TABLE iot.readings_by_sensor_day (
  sensor_id    text,
  day          date,
  ts           timestamp,
  temperature  double,
  PRIMARY KEY ((sensor_id, day), ts)
) WITH CLUSTERING ORDER BY (ts DESC)
  AND default_time_to_live = 7776000;   -- 90 days, in seconds

-- Write: the application derives day from the reading's timestamp
INSERT INTO iot.readings_by_sensor_day (sensor_id, day, ts, temperature)
VALUES ('s1', '2026-03-01', '2026-03-01 00:15:00+0000', 21.5);

-- Read: the latest readings of today's bucket
SELECT ts, temperature FROM iot.readings_by_sensor_day
WHERE sensor_id = 's1' AND day = '2026-03-03' LIMIT 20;

Three ideas are combined here:

  1. A composite partition key (sensor_id, day). The double parentheses mean both columns together choose the partition. A partition now holds one sensor-day, with a size you can compute.
  2. CLUSTERING ORDER BY (ts DESC). Rows inside a partition are stored sorted, and here the newest comes first. "Latest 20 readings" then reads the first 20 rows of the partition instead of scanning to the end. Without DESC, you can still ask for reverse order with ORDER BY ts DESC, but declaring the order you usually read is the cheaper habit.
  3. default_time_to_live. Every row expires 90 days after it was written. You do not run cleanup jobs; old data disappears by itself (the mechanics are in Deletes, TTL and tombstones).
flowchart LR
    S["Sensor s1<br/>one reading per 15 min"] --> B1["Partition (s1, 2026-03-01)<br/>96 rows"]
    S --> B2["Partition (s1, 2026-03-02)<br/>96 rows"]
    S --> B3["Partition (s1, 2026-03-03)<br/>growing, newest first"]

Each day is its own bounded partition, and partitions of different days may live on different nodes.

Choosing the bucket size

The bucket should be small enough that a partition stays comfortably bounded, and big enough that typical queries touch only one or two buckets. A sensor with one reading per second produces 86,400 rows a day, so a day bucket is borderline and an hour bucket may be safer. A device that reports once an hour could use a month. Estimate it: rows per bucket = readings per second x seconds in the bucket, then multiply by bytes per row.

Do not make the bucket so small that "the last hour" always spans many partitions, because each extra partition is another read request.

Stand-in 1: bucketing readings and reading across buckets

This program creates three days of readings (one every 15 minutes), groups them into day partitions sorted newest first, and then answers "latest N readings" by walking buckets backwards. It also applies a TTL.

Python Day buckets, newest first, and a TTL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The latest 5 or 96 readings come from the single newest partition, while 100 readings cross into the previous day. That cross-bucket loop is something your application code does; Cassandra answers one partition per query. The TTL line shows rows disappearing as they age, with no delete statements.

Time-window compaction fits time series

When all rows in a table carry a TTL and arrive in time order, a compaction strategy called TWCS (time-window compaction) groups SSTables by the time window in which data was written. When every row in an old window has expired, the whole file can be dropped at once. You will meet it in The read path, bloom filters and compaction. The practical consequence for design is simple: write time-series data in time order, give it a TTL, and avoid updating or deleting old rows.

Other patterns worth knowing

  • Query by time range. Because rows inside the partition are sorted by ts, a clause such as AND ts >= '2026-03-03 06:00:00+0000' AND ts < '2026-03-03 12:00:00+0000' is a cheap slice of one partition.
  • Spanning buckets. A report over a week issues one query per day, ideally in parallel, and merges the results in the application.
  • One table per question. If you also need "all sensors in a building for today", create a second table partitioned by (building_id, day). Duplicating data is normal; see query-first modeling.
  • Hot buckets. If every device writes to a partition that includes only the date, all writes for the day go to one partition. Always include something that spreads load, like the sensor id, in the partition key.
  • Late data. A reading that arrives late is simply written to the bucket its timestamp belongs to.

Stand-in 2: estimating partition size

Instead of guessing, compute it. The next program tries several bucket sizes for a sensor reporting every 5 seconds and warns when a partition gets too big, using an illustrative limit of 100,000 rows or 100 MB. The 150 bytes per row is an assumption for the toy; real size depends on the columns and Cassandra's storage format.

Python How big is one bucket?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

For this sensor a day bucket is fine, a week is not, and an unbucketed partition grows past six million rows in a year. Run it with a different interval to see how the decision changes.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is PRIMARY KEY ((sensor_id), ts) risky for a sensor that reports forever?

    All readings of one sensor share one partition, which keeps growing. Large partitions hurt reads, compaction, repair and balance.
  2. 2. What do the double parentheses in PRIMARY KEY ((sensor_id, day), ts) mean?

    The inner parentheses group the partition key columns. Everything after them is a clustering column, here ts.
  3. 3. What is the benefit of CLUSTERING ORDER BY (ts DESC) for a 'latest readings' query?

    Rows are sorted on disk by the clustering order. Declaring DESC makes the most common read a cheap read of the start of the partition.
  4. 4. A query needs the latest 100 readings, but a day bucket holds 96. What happens?

    A CQL query reads one partition (or a list of partitions you name). Walking back across buckets is the application's job.

Summary

  • Never let a time-series partition grow forever: put a time bucket (hour, day, week) in the partition key, together with the entity id.
  • Choose the bucket size by arithmetic: rows per bucket and bytes per row, kept well under about 100 MB.
  • Use CLUSTERING ORDER BY (ts DESC) when you mostly read the newest rows, and use TTL so data expires by itself.
  • Reading across buckets is done by the application, one partition per query.
  • The Python stand-ins show the idea on toy data; the CQL is shown read-only and was not run here.

Next: The write path: commit log, memtable, SSTable.