Skip to content

Databases for IoT

1 Topics 1 Posts

TimescaleDB, PostgreSQL basics, SQL queries, time-series data concepts

This category can be followed from the open social web via the handle databases-for-iot@forum.grapesiot.com

  • šŸ—„ļø Where IoT data actually goes

    database sql timescaledb
    1
    0 Votes
    1 Posts
    82 Views
    A
    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_trunc shape, 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 ts and group by (device_id, date_trunc): Hypertables auto-create time chunks behind a normal-looking table (create_hypertable on ts), 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.