Storing time-series data

NoSQL and choosing a database Mid-level Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Predict the output

How many points a day does this fleet produce?

Python 3.13
devices, every_s = 50_000, 10
per_day = devices * (86_400 // every_s)
print(f"{per_day:,}")

Options
Hint 1 of 1

86,400 seconds in a day.

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A 432,000,000

Correct. 8,640 points per device per day × 50,000 devices.

A day has 86,400 seconds, so a reading every 10 s is 8,640 points per device. Times 50,000 devices that is 432 million rows a day, which is why time-series stores partition and downsample.

Why not the others?
  • B 4,320,000. That's for 500 devices.
  • C 5,000. That's per second.
  • D 43,200,000. Off by ten: one point every 10 s is 8,640 a day.

30-second answer

Time-series data is append-mostly and queried by time range and series. Partition by time (daily or hourly tables, or a time-series database like TimescaleDB or InfluxDB) so recent writes are local and old data can be dropped cheaply. Store raw points briefly and roll them up into minute and hour aggregates kept longer. BRIN or time-first indexes stay small on time-ordered data.

Practise saying it · 30 s. No recording, just a timer

How do you store time series?

Say your answer out loud, then check which of these points you covered:

  • Partition by time
  • Rollups
  • Retention by drop
  • BRIN / time indexes
  • Watch cardinality

Deep dive

50,000 devices × one point every 10 s is 5,000 points per second, about 432 million a day.

Retention by dropping whole partitions is instant; DELETE of old rows is slow and bloats.

Downsampling: keep 10 s data for 7 days, 1 min for 90 days, 1 h for years.

High cardinality (many unique label combinations) is the usual scaling problem.

Common mistakes

  • One unpartitioned table with DELETE for retention.
  • Keeping raw data forever.
  • Labels with unbounded values (user ids) in a metrics system.

Senior take & follow-ups

Time partitioning, rollups, retention by drop, and cardinality.

Follow-up questions

Think of your answer first, then open the hint.

  1. Why drop partitions instead of DELETE?

    Show hint

    Dropping is a metadata operation; DELETE creates dead rows to vacuum.

  2. Why does cardinality matter?

    Show hint

    Each unique series costs index and memory.

Related questions

Suggest a fix if something here is wrong or out of date.