šļø Where IoT data actually goes
-
One device sending telemetry every 5 seconds produces about 17,000 rows a day. A hundred devices produce 1.7 million rows a day ā all timestamped, all needing fast answers for dashboards and alerts: average temperature per hour, last-seen per device, events inside a time window. This is where most IoT projects quietly succeed or fail, long after the firmware works.
Start with Postgres, think in time
Postgres handles millions to low billions of rows on ordinary hardware when the schema is narrow, indexed on
(device_id, ts), and old data is dropped by time range. The discipline that makes it scale ā and yes, this is a data-modelling problem first, which is why it helps to have the fundamentals straight:
ā Database fundamentals: modelling and normalisation- One time column to rule queries. Every analytical query bounds
ts. Queries that filter on payload alone (WHERE temperature_c > 30, no time bound) are full-table scans at any scale ā always add the time bound first. - Narrow rows, typed columns. Keep hot fields relational. Unmodelled JSON accumulates five spellings of temperature and breaks every downstream query ā and columnar compression works best on typed columns, not opaque JSON.
- Retention is a partition operation. Dropping whole time partitions by age runs in milliseconds; deleting a year row-by-row can stall a cluster for days. Design deletes by time range from day one.
ā Native partitioning, compression thinking and retention - Precompute the dashboards. An hourly rollup table turns a million-row dashboard scan into a 24-row lookup. Same
GROUP BY date_truncshape, precomputed ā keep the bucket expression identical in raw and rollup form.
ā Joins, aggregation and subqueries
The Timescale upgrade path (concepts, no new queries)
When the fleet grows, TimescaleDB automates exactly these patterns with zero query rewrites ā because your queries already bound
tsand group by(device_id, date_trunc):- Hypertables auto-create time chunks behind a normal-looking table (
create_hypertableonts), with per-chunk indexing and pruning that behaves like your manual partitions. - Compression on chunks older than N days (roughly 10x on narrow numeric telemetry) for cheap long retention.
- Continuous aggregates replace hand-maintained hourly tables with background materialisation.
- Retention policies (
add_retention_policy, e.g. raw 90 days, rollups 2 years) replace the detach-and-drop routine.
The rule from the field: earn the extension with growth numbers. A Timescale dependency for a million rows loses to a clean native design with pruning, two composite indexes, and a documented upgrade path.
Finish with the telemetry schema capstone ā a 500-sensor fleet designed and measured end to end. Post your schema, the slow query with its EXPLAIN, and the time range involved. Tag with
database. - One time column to rule queries. Every analytical query bounds
Hello! It looks like you're interested in this conversation, but you don't have an account yet.
Getting fed up of having to scroll through the same posts each visit? When you register for an account, you'll always come back to exactly where you were before, and choose to be notified of new replies (either via email, or push notification). You'll also be able to save bookmarks and upvote posts to show your appreciation to other community members.
With your input, this post could be even better š
Register Login