Storing a reading a minute from 100,000 devices

One reading per device per minute sounds modest. It is the kind of requirement that gets written into a spec without anyone doing the multiplication, and then it quietly decides the architecture of everything downstream. I have spent a good part of the last few years around fleets in the tens of thousands, and the problems at this scale tend to come down to arithmetic, timing and bookkeeping, done carefully.

Start with the arithmetic

A hundred thousand devices reporting every minute is about 1,667 messages per second on average. That is 144 million rows a day and a little over 52 billion rows a year. None of those numbers is frightening for a modern database on its own, but they compound. If a single reading carries a device id, a timestamp and four or five small sensor values, a row in Postgres with its header and a primary key index lands somewhere around a hundred bytes. Multiply that out and you get roughly 14 GB a day, or around 5 TB for a year of raw history, before replicas and backups.

The average is also the least interesting number here. What matters for sizing is the peak, and the peak is decided by something much less obvious than the fleet size.

The top of the minute

Devices love round numbers. If the firmware wakes up on a timer aligned to the wall clock, a large share of your fleet will try to connect and publish in the same second, every minute, forever. Your average of 1,667 messages per second becomes a burst of tens of thousands followed by fifty-odd seconds of near silence. Brokers, load balancers and database connection pools all get sized for the burst, and you pay for that capacity during the silence too.

The fix costs almost nothing if you do it early. Give each device a fixed offset inside the minute, derived from something stable like a hash of its serial number, and keep sampling on the minute while spreading the publish across the whole interval. The reading still carries the timestamp of when it was measured, so nothing downstream changes, and the load curve flattens out into something you can plan around. Doing this after deployment means an OTA rollout to every device, which is exactly the kind of change you would rather not make under pressure.

Getting messages off the broker

Most fleets like this talk MQTT, whether that is a managed service such as AWS IoT Core or a broker you run yourself like EMQX or HiveMQ. The broker is very good at holding a hundred thousand long-lived connections and fanning messages out. It is a poor place to do anything slow, so the first job of the backend is to get messages out of the broker and into something durable as quickly as possible.

I like putting a log in between, usually Kafka or Kinesis depending on where the rest of the system lives. The consumers that write to the database can then fall behind, restart, or be redeployed without the broker noticing, and you gain the ability to replay a day of traffic when you inevitably find a bug in how a field was parsed. With MQTT shared subscriptions you can run several bridge instances that split the incoming stream between them, which keeps that first hop horizontally scalable and boring.

Partition the log by device id. It keeps each device's readings in order within a partition, which makes later deduplication and any per-device logic much simpler to reason about.

Batch everything you write

The single biggest performance mistake I see is writing one row per message. At 1,667 inserts a second you will spend most of your database's time on transaction overhead and very little on actually storing data. Collect a few thousand rows in the consumer and write them in one statement, or use the database's bulk path such as COPY in Postgres. ClickHouse is even fussier about this and really wants batches of thousands to tens of thousands of rows at a time. If your writers cannot batch on their side, its asynchronous inserts will do the buffering on the server for you.

A batch every second or two from each consumer is plenty. The latency you add is invisible to anyone looking at a dashboard, and the database will thank you.

Duplicates and stragglers

MQTT with QoS 1 guarantees a message arrives at least once, which in practice means sometimes twice. Devices that lose connectivity will also buffer readings and send them later, sometimes hours later, sometimes after a reboot that reset their sense of which messages were already acknowledged. If your storage treats every arrival as a new fact, your averages will drift and your billing reports, if you have them, will be wrong in ways that are very hard to spot.

The cleanest answer is to make the write idempotent. Use the device id and the measurement timestamp as the key and let the database ignore a row it has already seen. It costs a little on insert and saves you from ever having to write a deduplication job.

Where the data should live

For this shape of data I would reach first for TimescaleDB or ClickHouse. Both are good choices, and the decision usually comes down to the rest of your stack and what your team already knows. TimescaleDB is Postgres, so you keep joins against your device registry, transactions and the whole ecosystem of tooling. ClickHouse is faster for large analytical scans and very efficient on disk, at the cost of being a separate system with its own operational habits.

A schema that works well in TimescaleDB for this workload looks something like this.

CREATE TABLE readings (
  device_id  integer     NOT NULL,
  ts         timestamptz NOT NULL,
  temp       real,
  humidity   real,
  battery_mv smallint,
  rssi       smallint,
  PRIMARY KEY (device_id, ts)
);

SELECT create_hypertable('readings', by_range('ts', INTERVAL '1 day'));

ALTER TABLE readings SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id',
  timescaledb.compress_orderby   = 'ts DESC'
);

SELECT add_compression_policy('readings', INTERVAL '2 days');

Daily chunks keep each partition to around 144 million rows, which is easy to compress and cheap to drop. Segmenting the compressed data by device and ordering it by time puts each device's readings next to each other, and sensor values that change slowly compress extremely well that way. Savings of 90 percent or more are normal here, and sensor telemetry is about as friendly as data gets for compression. On the kind of data described above it is realistic to bring that 5 TB year down to a few hundred gigabytes.

Writes then become a batched insert that quietly skips anything it has already stored.

INSERT INTO readings (device_id, ts, temp, humidity, battery_mv, rssi)
VALUES ($1, $2, $3, $4, $5, $6), ...
ON CONFLICT (device_id, ts) DO NOTHING;

Nobody reads minute data from last March

Once the data is flowing, the question that keeps costs down is how long anyone actually needs each level of detail. In my experience the answer is usually short for raw readings and long for summaries. Engineers debugging a device want per-minute data from the last few days or weeks. Product dashboards and yearly reports want hourly or daily aggregates and would be slower and more expensive if they had to scan raw rows.

Continuous aggregates in TimescaleDB, or materialized views in ClickHouse, let you keep those summaries up to date as data arrives.

CREATE MATERIALIZED VIEW readings_hourly
WITH (timescaledb.continuous) AS
SELECT device_id,
       time_bucket(INTERVAL '1 hour', ts) AS bucket,
       avg(temp)       AS temp_avg,
       min(temp)       AS temp_min,
       max(temp)       AS temp_max,
       min(battery_mv) AS battery_min
FROM readings
GROUP BY device_id, bucket;

SELECT add_continuous_aggregate_policy('readings_hourly',
  start_offset      => INTERVAL '3 hours',
  end_offset        => INTERVAL '1 hour',
  schedule_interval => INTERVAL '30 minutes');

SELECT add_retention_policy('readings', INTERVAL '90 days');

With that in place the raw table only ever holds three months, the hourly view holds as much history as you like at a fraction of the size, and the start offset of a few hours gives late readings a chance to land before their hour is finalised.

If anyone might one day need the raw history, for a warranty dispute or to train a model, export each day to Parquet on object storage before the retention policy drops it. Storage there is cheap, and tools like Athena or DuckDB can query it directly when the rare request comes in.

What it adds up to

Put together, the system is not complicated. Devices spread their publishes across the minute, a broker holds the connections, a log absorbs the stream, a small group of consumers writes batches idempotently into a time series database that compresses aggressively and rolls data up, and anything old enough to be boring goes to object storage. Every piece is well understood.

The part that takes real effort is deciding those numbers deliberately, before the fleet exists, while changes are still cheap. If you are planning something at this scale and want a second pair of eyes on it, I am always happy to talk.