← Journal Software · · 6 min

Storing IoT time-series data without overbuilding

How to store IoT time-series data in Postgres for a growing fleet: sizing sums, partitioning, schema choices, duplicates and retention.

Milo Perović
Founder & lead engineer

  • Cloud
  • Dashboards
  • Fleet
  • Storage
Storing IoT time-series data without overbuilding

Most connected products start sending readings long before anyone decides where those readings should live. Storing IoT time-series data usually begins as a single table someone created in an afternoon, and it works until the fleet grows, the dashboard slows down and nobody can say how much history the product actually keeps.

This post is for founders and engineers planning the backend for a device fleet in the hundreds to low tens of thousands. That range does not need a specialised data platform, but it does need a few deliberate decisions made early, while changing them is still cheap.

Do the sizing sums before choosing a database

The first useful number is rows per day, and it is easy to work out. Multiply devices by readings per hour by metrics per reading by 24. A thousand devices reporting ten values once a minute produce about 14.4 million values a day.

How that turns into disk depends on how you store it. In PostgreSQL, a narrow row (device, timestamp, metric, value) carries roughly 23 bytes of row header plus alignment, so with its index it typically costs somewhere between 60 and 120 bytes. At 14.4 million rows that is around one to two gigabytes a day before compression. The same data as one wide row per reading is 1.44 million rows, and usually a fraction of the space.

Do this sum for the fleet you expect in two years, not the pilot. It tells you whether you have a storage problem at all, and most small fleets find they have a retention problem instead.

Postgres first, with time partitioning

For fleets in this range we start with PostgreSQL, which is usually already in the stack for users, devices and permissions. Keeping telemetry in the same database means one backup, one access model and joins between readings and device metadata without a sync job. We wrote about that broader shape in one IoT backend for the device, dashboard and mobile app.

What makes Postgres hold up is partitioning by time. Native declarative partitioning splits the readings table into daily, weekly or monthly child tables. Queries over the last day only touch recent partitions, and deleting old data becomes dropping a partition rather than a slow DELETE that bloats the table.

The TimescaleDB extension automates the same idea with hypertables, and adds native compression and continuous aggregates. It is worth considering once the sums say you will hold hundreds of millions of rows, but check how your hosting provider supports it before you depend on it.

Indexes that match the queries

Almost every telemetry query asks for one device, or a group of devices, over a time range. A composite B-tree index on (device_id, ts) serves that directly. For very large, append-only partitions, a BRIN index on the timestamp is tiny compared with a B-tree and works well because rows arrive roughly in time order.

Narrow or wide tables for IoT time-series data

This is the schema decision that is hardest to undo, so it deserves a deliberate choice rather than a default.

LayoutGood atCosts you
Narrow (one row per metric)New metrics without migrations, mixed sensor sets across hardware revisionsMore rows, more index space, pivoting for charts
Wide (one row per reading)Compact storage, simple queries, fast chartsA migration for every new metric
Wide plus JSONB columnFixed core metrics as columns, rare extras as JSONDiscipline about what earns a real column

We usually land on the third option. Metrics that every device reports and every chart uses become typed columns. Diagnostic extras that vary by firmware version go into a JSONB column until they prove they are worth promoting.

Timestamps, duplicates and late data

Devices lie about time more often than people expect. A real-time clock that lost power, an NTP sync that has not happened yet after boot, or a device buffering readings while offline can all produce timestamps that are wrong or arrive hours late.

  • Store two times. Keep the device timestamp and the server receive time. When they disagree badly, you have a diagnostic signal rather than a corrupted chart.
  • Reject the impossible. Readings dated before the product existed, or more than a few minutes in the future, should go to a quarantine table, not the main one.
  • Expect duplicates. MQTT QoS 1 delivers at least once, and devices that retry after a lost acknowledgement will resend. A unique constraint on (device_id, ts) with ON CONFLICT DO NOTHING makes ingestion idempotent.
  • Allow backfill. A device that comes back online with a day of buffered readings should be able to write into older partitions without breaking aggregates.

Topic and payload structure on the device side shapes all of this. If you are still designing that layer, our notes on MQTT topic design for a growing device fleet cover the parts that affect ingestion.

Retention and downsampling as product decisions

Nobody needs per-minute temperature from three years ago, but someone will want the daily minimum and maximum. Decide this with the product owner, not the database administrator, because it changes what customers can see.

A typical policy keeps raw data for 30 to 90 days, hourly aggregates for one to two years, and daily aggregates for the life of the product. The right values depend on warranty periods, any regulatory record-keeping your sector imposes and what support engineers actually look at when a device misbehaves.

Build the aggregates as scheduled jobs or continuous aggregates that write to their own tables, and point dashboards at them for any range longer than a day or two. That single change is often what takes a slow dashboard from seconds to milliseconds.

When to move beyond Postgres

Postgres is not the answer forever. It starts to strain when ingest reaches tens of thousands of rows per second sustained, when analysts run heavy ad-hoc queries across the whole fleet history, or when storage costs for raw data dominate the hosting bill.

At that point the usual moves are columnar compression through TimescaleDB, exporting older partitions to object storage in a columnar format such as Parquet, or adding a dedicated analytics database alongside Postgres. Because the schema is already partitioned by time and keyed by device, each of those is a migration of old data, not a rewrite of the application.

What we would do

For a new connected product heading towards a few thousand devices, this is the order we would work in:

  1. Do the rows-per-day and bytes-per-row sums for the two-year fleet size.
  2. Use the Postgres you already have, with a readings table partitioned by time.
  3. Choose wide columns for core metrics and a JSONB column for extras.
  4. Store device and server timestamps, and make ingestion idempotent.
  5. Agree raw, hourly and daily retention with the product owner and write it down.
  6. Point every long-range chart at aggregates, never at raw rows.
  7. Revisit the storage engine only when measured ingest or query load says so.

None of this is exotic. It is a handful of decisions that are cheap in the first month and expensive in the second year, which is exactly why they are worth making now.

Working on something similar?Free scoping call →