Databases · Senior · Question 42 of 75

How would you model time-series data, like metrics every second from 10,000 devices, in PostgreSQL?

1One table per device
✓Correct answer: Partition by time (or use TimescaleDB), index by (device_id, time), use BRIN for time ranges, and roll up old data into aggregates
3A single unpartitioned table with an index on every column

Explanation

Time partitions make retention a cheap DROP. Continuous aggregates or materialized rollups keep dashboards fast.

Further reading

Test yourself

Take the Databases · Senior quiz: questions and options are shuffled, and you get a review of your mistakes at the end.