Direct answer

Choose IoT time-series storage from the queries, retention, correction behavior, and recovery obligations—not from peak ingest claims. Separate current device state, immutable raw history, query-ready measurements, aggregates, and business events when their access patterns differ. One engine may serve several layers at modest scale, but the architecture should not assume that one representation is optimal for all of them.

Benchmark with your real timestamp disorder, tenant filters, tag cardinality, backfills, and concurrent queries. A database that accepts the target write rate can still fail the product if dashboards are slow, late corrections are expensive, or a tenant-level export disrupts ingestion.

Scope and non-scope

This guide covers storage for timestamped device measurements, derived metrics, and operational queries. It addresses schema shape, cardinality, retention, tiering, late data, and evaluation.

It does not select a specific commercial service, prescribe a data lake format, or replace legal retention and privacy review. It also does not treat commands, billing transactions, or audit events as ordinary telemetry; those records may require transactional semantics and retention controls that deserve separate stores.

Start with data products and queries

Inventory what the product must answer. Common workloads include a latest-value dashboard, a 24-hour chart for one device, a fleet aggregate by site, anomaly detection over a rolling window, a maintenance investigation across raw signals, and a tenant export. For each query, record time range, dimensions, expected freshness, concurrency, result size, and latency objective.

Then classify stored data:

Data class Typical access Useful treatment
Current state Point lookup by device Upserted state table or cache with durable source
Normalized measurements Time range plus device or site filters Time partitioning, compression, selective indexes
Raw payloads Replay, audit, parser recovery Immutable object storage with lifecycle policy
Aggregates Dashboards and fleet trends Precomputed time buckets with refresh rules
Business events Workflow and audit by identity Transactional/event store with explicit schema

Keeping raw payloads can make parser fixes and backfills possible, but “forever” is not a strategy. Define why the raw data exists, who can access it, how it is encrypted, when it expires, and how deletion propagates. If raw replay is not a requirement, storing a normalized record with provenance may be sufficient.

Model schema, cardinality, and time explicitly

Use a stable device identifier, tenant or ownership scope, event time, ingestion time, measurement name, value, unit, quality, and schema or decoder version where appropriate. Event time says when the device observed the value; ingestion time says when the platform received it. Both are essential for diagnosing delayed devices and applying deterministic correction windows.

Distinguish indexed dimensions from measured values. Device ID, site, model, and controlled labels may be query dimensions. Unbounded values such as request IDs, raw error text, or arbitrary user tags can create extreme series or index cardinality. Do not turn every payload field into an indexed tag.

Wide rows work well when a fixed set of signals arrives together and is queried together. A narrow measurement/value representation is flexible but can multiply rows and complicate typed queries. JSON preserves changing payloads but weakens validation and can make common filters expensive. Choose from representative queries and schema evolution, not aesthetics.

Time partitioning should align with retention, query windows, ingest volume, and maintenance cost. Partitions that are too small create metadata overhead; partitions that are too large make retention and index maintenance expensive. Compression results depend on ordering and data shape, so test actual cohorts rather than extrapolating from synthetic monotonic values.

A practical selection process

  1. Define freshness, query latency, availability, retention, deletion, replay, and recovery objectives.
  2. Capture a representative dataset with real payload sizes, timestamps, missing values, duplicates, labels, and tenant distribution.
  3. Write the ten to twenty queries that drive the product, including dashboards, investigations, exports, aggregates, and deletion.
  4. Design at least two plausible schemas and document how each handles current state, late data, units, provenance, and tenancy.
  5. Load sustained data while running concurrent production-shaped queries. Include retention jobs, compression, aggregate refresh, backups, and replica activity.
  6. Inject late and corrected data, then measure the cost and correctness of refreshing affected aggregates.
  7. Test a backfill while live ingestion continues. Throttle it so historical work cannot consume the entire write and I/O budget.
  8. Restore from backup into a clean environment and verify row counts, time ranges, ownership boundaries, and application queries.
  9. Estimate cost from stored bytes after replication and compression, write and query compute, backups, data transfer, and operational labor.

Evaluate operational fit as seriously as benchmark speed. Schema migrations, online index changes, observability, backup verification, tenant deletion, and on-call familiarity affect the long-term cost of the store.

Late data, duplicates, and corrections

Networks reorder and retry messages, and devices can reconnect with a backlog. Give events a stable identity when deduplication matters. If no natural event ID exists, define a key from trusted device identity, timestamp or sequence, measurement, and schema version, while accounting for legitimate repeated samples.

Set an explicit lateness policy. Recent aggregates may be refreshable for seven days, while older corrections enter a controlled backfill. Keep raw and normalized provenance so an operator can explain which decoder, unit conversion, or calibration produced a value. Do not silently overwrite history when auditability matters; record correction lineage or versioned facts.

Clock quality must be visible. Preserve device timestamps, platform receipt time, and a quality indicator for missing or implausible clocks. Sorting purely by arrival time hides the physical sequence; trusting every device timestamp can place data years outside useful partitions.

Failure modes to test

Cardinality growth can exhaust memory or indexes long before disk capacity. A tenant export can scan years of data and starve fresh dashboards. Continuous aggregates can look correct until late data falls outside their refresh window. Retention jobs can block writes if partitioning does not match deletion boundaries.

Backfills may duplicate rows, bypass current validation, or publish events that trigger live alerts. Use an isolated ingestion path, idempotency, rate limits, and a clear rule for downstream side effects. Schema evolution can also mix units or decoder versions in one chart; store unit and provenance rather than relying on current device configuration.

Finally, replication is not backup. Test point-in-time recovery and application-level correctness after restore. A successful database restore that loses tenant mappings, object-store raw payloads, or aggregate definitions is not a successful system recovery.

Implementation checklist

  • Product queries and latency objectives are written before engine selection.
  • Current state, raw history, normalized measurements, aggregates, and events have explicit roles.
  • Event time, ingestion time, unit, quality, and schema provenance are preserved.
  • Tag and index cardinality are bounded and observable.
  • Retention, deletion, backup, and restore requirements are tested.
  • Benchmarks include late data, duplicates, backfills, tenant filters, and concurrent reads.
  • Aggregates have a documented correction and refresh window.
  • Historical work is throttled so live ingestion keeps its service objective.
  • Cost estimates include replicas, backups, compute, transfer, and operations.

Primary sources

Use the selected database’s official behavior as the source of truth. Timescale documentation covers hypertables, chunking, compression, retention, and continuous aggregates for its PostgreSQL extension. The PostgreSQL documentation on table partitioning explains native partition behavior and maintenance trade-offs. If object storage is part of the design, verify lifecycle, consistency, encryption, and restore semantics in the chosen provider’s official documentation rather than assuming all services behave alike.