IoT Telemetry

Design the storage schema for an IoT platform ingesting readings from 2 million devices, each reporting several metrics every 10 seconds. Dashboards show the last hour at full resolution and the last year as hourly rollups, and alerts fire when a reading crosses a threshold.

Clarifying Questions

Is the set of metrics fixed per device type, or arbitrary per device?
How long do we keep raw readings, and how long the rollups?
Can devices send late or out-of-order readings after being offline?
Are queries mostly per device, or across a fleet, such as every device at one site?
Is a time-series extension like TimescaleDB acceptable, or plain Postgres only?

Core Requirements

Register devices with type, site, and firmware version.
Ingest about 200,000 device reports per second, each with a device timestamp.
Query one device's metrics over the last hour at full resolution.
Query hourly min, max, and average per device for the past year.
Store alert rules per device type or device, and a history of fired alerts.
Drop raw data past retention cheaply.

Canonical schema (PostgreSQL)

CREATE TABLE device_types (
  id SMALLINT PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  metric_names TEXT[] NOT NULL
);

CREATE TABLE sites (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  timezone TEXT NOT NULL
);

CREATE TABLE devices (
  id BIGINT PRIMARY KEY,
  serial TEXT UNIQUE NOT NULL,
  device_type_id SMALLINT NOT NULL REFERENCES device_types(id),
  site_id INTEGER NOT NULL REFERENCES sites(id),
  firmware_version TEXT,
  last_seen_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- One row per device report. metric_values is ordered by device_types.metric_names.
CREATE TABLE readings (
  device_id BIGINT NOT NULL,
  ts TIMESTAMPTZ NOT NULL,
  received_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  metric_values REAL[] NOT NULL,
  PRIMARY KEY (device_id, ts)
) PARTITION BY RANGE (ts);

CREATE TABLE readings_hourly (
  device_id BIGINT NOT NULL,
  metric_index SMALLINT NOT NULL,
  hour TIMESTAMPTZ NOT NULL,
  min_value REAL NOT NULL,
  max_value REAL NOT NULL,
  sum_value DOUBLE PRECISION NOT NULL,
  sample_count INTEGER NOT NULL,
  PRIMARY KEY (device_id, metric_index, hour)
) PARTITION BY RANGE (hour);

CREATE TABLE alert_rules (
  id SERIAL PRIMARY KEY,
  device_type_id SMALLINT NOT NULL REFERENCES device_types(id),
  device_id BIGINT REFERENCES devices(id),
  metric_index SMALLINT NOT NULL,
  comparator TEXT NOT NULL CHECK (comparator IN ('>', '<')),
  threshold REAL NOT NULL,
  cooldown_seconds INTEGER NOT NULL DEFAULT 300,
  enabled BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE alerts (
  id BIGSERIAL PRIMARY KEY,
  rule_id INTEGER NOT NULL REFERENCES alert_rules(id),
  device_id BIGINT NOT NULL REFERENCES devices(id),
  fired_at TIMESTAMPTZ NOT NULL,
  value REAL NOT NULL,
  state TEXT NOT NULL CHECK (state IN ('firing', 'resolved')),
  resolved_at TIMESTAMPTZ
);

Key Indexes

PRIMARY KEY (device_id, ts) on each readings partition

The last hour for one device is a contiguous range inside one or two daily partitions, and the key rejects duplicate reports.

CREATE INDEX idx_readings_ts_brin ON readings USING BRIN (ts)

Fleet-wide time scans; rows arrive roughly in time order, so a BRIN index stays tiny and adds almost no write cost.

CREATE INDEX idx_devices_site_type ON devices(site_id, device_type_id)

Resolves a fleet query (every thermostat at one site) to device IDs before touching readings.

CREATE UNIQUE INDEX uq_alerts_firing ON alerts(rule_id, device_id) WHERE state = 'firing'

At most one open alert per rule and device, so a flapping sensor can't page the on-call engineer a hundred times.

CREATE INDEX idx_alert_rules_type ON alert_rules(device_type_id, metric_index) WHERE enabled

The evaluator loads the active rules for a device type in one lookup.

Key Decisions to Defend

readings is range partitioned by day; retention drops whole partitions, which is instant next to deleting billions of rows.
One row per report with values in a REAL array keeps rows narrow; a row per metric would multiply row overhead and index entries by the metric count.
Rollups store sum and count alongside min and max, so averages stay correct when hours roll up again into days.
Late readings land by device timestamp, and the rollup job reprocesses any hour that received data after it closed, found through received_at.
Plain Postgres handles this rate only with batched COPY inserts and very few indexes; beyond that, a time-series or columnar store (TimescaleDB, InfluxDB, or ClickHouse) takes the raw tier while devices and alerts stay relational.

Likely follow-ups

· Ingest doubles to 400,000 reports per second, what breaks first?
· A firmware bug sends readings timestamped in 2009, how do you protect the partitions?
· Add a fleet dashboard showing p95 temperature per site over 30 days.
· Compress raw data older than a week without dropping it.

Dive Deeper

Telemetry schemas test whether you design for write volume and retention first. Strong candidates estimate the ingest rate, partition by time, keep the raw table nearly index free, and pre-aggregate with sum and count so rollups compose. The senior signal is knowing where Postgres stops: past a few hundred thousand rows per second, the raw tier belongs in a time-series or columnar store while device metadata and alert state stay relational.

All Database Design Prompts