A regular SQL table breaks under sensor data because relational databases are optimized for updating and looking up individual rows, while connected products do the opposite: they append enormous volumes of readings that are never edited and are almost always queried as time ranges. One device reporting once per second generates over eighty thousand rows per day. Multiply by a thousand units in the field and you are inserting millions of nearly identical rows daily — timestamp, device ID, value. A time-series database (TSDB) is built for exactly that access pattern, and the honest recommendation is to choose one during architecture, not after your dashboards start timing out.

Why the normal table stops working

The failure is rarely dramatic. It shows up as a set of familiar symptoms:

  • A chart that took 200 milliseconds in the pilot now takes eight seconds with a year of history.
  • Indexes that grow larger than the data they index, because a B-tree index on a timestamp column of billions of rows is enormous.
  • Backups that stretch from minutes into hours, and restores nobody has actually tested.
  • Nightly "cleanup" jobs that delete old rows to free disk, which in most row stores leaves bloat behind rather than reclaiming space.
  • Per-row overhead that exceeds the payload — a four-byte temperature reading wrapped in tens of bytes of row metadata.

At that point teams start hand-writing rollup scripts and summary tables. That is engineering effort spent rebuilding, badly, something off-the-shelf products already solved.

What a time-series database does differently

  • Columnar compression. Sequential readings from the same sensor are highly similar, so column-oriented storage with delta and dictionary encoding routinely compresses ten to twenty times better than row storage.
  • Append-only write path. The engine assumes new data lands at the end of the timeline, which lets it sustain very high ingest rates without index churn.
  • Retention policies. You declare that raw samples expire after 30 days while hourly aggregates live for years, and the database enforces it by dropping whole time partitions — cheap, instant, no bloat.
  • Continuous aggregates. Minute and hour averages are computed as data arrives, so a six-month chart reads a few thousand pre-computed rows instead of scanning half a billion raw ones.
  • Native time functions. Time bucketing, moving windows, gap filling, last-value carry-forward, and resampling are built in rather than reinvented in application code.

Choosing an engine

PostgreSQL with a time-series extension

If your team already knows SQL and you have relational data alongside telemetry — accounts, devices, firmware versions — extending PostgreSQL is usually the lowest-friction path. You keep joins, existing tooling, and one database to operate.

Purpose-built TSDBs

Dedicated engines push ingest throughput and compression further and ship richer downsampling and retention tooling out of the box. The trade-off is a second datastore to run and a query language your team must learn.

Managed cloud services

The major cloud platforms offer serverless time-series and analytics stores that eliminate operations work and bill by ingest and query volume. They are excellent early and can get expensive at scale, so model the cost curve before committing. Our comparison of AWS IoT versus Azure IoT covers how the surrounding platform choice usually decides this one.

Decisions to make before writing the schema

Sampling rate versus transmitted rate. A device can sample at 100 Hz and transmit one-second aggregates. Deciding what actually needs to leave the device is the single biggest lever on storage and bandwidth cost, and it belongs in the firmware spec.

Cardinality. This is the trap that bites hardest. Cardinality is the number of unique series — roughly devices multiplied by metrics multiplied by every tag you attach. Adding a high-variability tag such as a session ID or a firmware build string can multiply series count by orders of magnitude and destroy performance in engines that index every tag combination. Tags should be low-cardinality attributes you filter on; identifiers belong in fields or a relational table.

Retention tiers. Decide up front how long you need raw resolution. Most products need seconds-level data for days, minutes for months, and hours for years. Write it down — it is a product decision, sometimes a regulatory one, not an infrastructure detail.

Late and duplicate data. Devices buffer while offline and re-send. Your ingest path needs idempotent writes keyed on device plus timestamp, or your averages will quietly drift.

What it costs

Storage itself is rarely the problem; compressed telemetry is cheap. Costs concentrate in ingest processing, query compute, and egress. In practice, moving from an unoptimized relational table to a properly configured time-series store with retention and rollups tends to cut monthly cloud spend substantially while making dashboards feel instant — the same data, a fraction of the bytes scanned. Our breakdown of IoT cloud infrastructure cost puts the database line item in context with the rest of the bill.

How it fits the rest of the system

The database is one link in a chain: firmware batches readings, a lightweight transport moves them — see MQTT versus HTTP for IoT — an ingest service validates and writes them, and the TSDB serves dashboards and alerts. Downstream, the same store is what makes predictive maintenance from product data and a digital twin feasible, and it is the backbone of IoT fleet management once you have thousands of units deployed. Getting the storage layer right early is what keeps those features from requiring a rewrite. For the wider architecture picture, start with our software development guide.

Talk to us about your data architecture

Projects House designs connected products end to end — firmware, transport, cloud, and the dashboards your customers actually see. If you are planning a connected product or your current telemetry stack is starting to strain, send us a message through the contact form with what your devices measure and how many you expect in the field, and we will tell you what the data layer should look like.