Ten million plant readings on one small box, and the mistake that cost 5.6×

Before anyone buys storage for a historian, somebody usually runs a load test. I wanted to know what a good one looks like, so I built the generator, then tried to break my own numbers.

Two things came out of it that I did not expect, and one of them would have cost real money.

What the generator produces#

Six kinds of tag, mixed so that slow analogue values dominate as they do on a real site: temperature, flow, pressure, vibration, a production counter and a discrete state. Ten thousand tags, one-second sampling.

The models are deliberately not noise:

Kind	Behaviour
temperature	slow drift, a daily cycle, a step at shift change
flow	flat at zero, then flat at rate, when a pump starts
pressure	follows flow, with its own small noise
vibration	quiet, occasional spikes, a slow creep on ~1 asset in 23
counter	monotonic, never decreases
state	discrete, holds for hours

WHAT PLANT DATA ACTUALLY LOOKS LIKE

temperature

drifts, then holds

flow

flat, then the pump starts

pressure

follows the flow

vibration

quiet, with a fault creeping in

counter

only ever goes up

state

sits still for hours

Every value is a pure function of (seed, tagIndex, sampleIndex), so a run reproduces exactly and nothing has to be stored to repeat it. That matters more than it sounds: it means the ClickHouse comparison later can be fed byte-identical data without shipping a dataset around.

Where the time goes#

One million readings through each stage, single process, each row adding the next real cost:

Stage	Points/sec
work out the values	3,164,557
+ serialise to JSON	1,158,749
+ publish over MQTT	211,193
+ batched multi-row INSERT	166,722
+ COPY, a row at a time	54,888
+ COPY, in 2,000-row chunks	309,502

ONE MILLION READINGS, ONE CORE

3,164,557

work out the values

1,158,749

turn them into JSON

211,193

publish over MQTT

166,722

batched INSERT

54,888

COPY, a row at a time

THE SAME COPY, WRITTEN IN CHUNKS — 309,502/sec

Buffering the rows and writing two thousand at a time made it 5.6 times faster and moved it from slowest to fastest. Nothing about the database changed.

Producing the readings is nearly free. A single core makes over three million a second, and JSON costs about two thirds of that (62 MB of text per million readings). MQTT publishing dropped it to 211,000 and, worth noting for anyone load-testing a broker, resident memory climbed to 706 MB as the client buffered ahead of the socket.

The mistake, and the wrong guess before it#

COPY is supposed to be the fast path into Postgres. Mine came out three times slower than ordinary batched inserts, which is the wrong way round and therefore worth chasing rather than reporting.

First guess, reasonable, wrong. Every tag in a sample shares one timestamp, and the code was rebuilding the ISO string once per reading rather than once per sample. Hoisting it out of the inner loop took 49,945 → 54,888. Real, about ten per cent, and not the answer.

The actual cost was one stream.write() per reading. Each write carries the same fixed overhead whether it holds forty bytes or forty thousand, and I was paying it ten million times over, plus a backpressure check each time. Buffering rows and writing in chunks:

Chunk size	Points/sec
a row at a time	54,888
500	295,508
2,000	309,502
20,000	280,112

5.6× faster, and it moved COPY from the slowest option in the table to the fastest. Chunk size barely matters past a few hundred, so there is no tuning exercise here — there is just one thing you must not do.

The general lesson is the one I keep relearning: when a well-known fast path measures slow, the tool is usually fine and the loop around it is not.

More cores barely helped#

Two million readings, varying worker processes. Node runs one thread, so parallelism means separate processes, each owning a disjoint slice of tags.

Workers	Points/sec
1	304,414
2	287,936
4	352,734
6	397,772

ADDING CORES TO A DATABASE PROBLEM

Six cores bought 1.31×. Two workers were briefly worse than one. The generator can produce 3.1 million readings a second on one core while six parallel COPY streams land 398,000, which puts the ceiling somewhere it is easy to misattribute.

THE CONSTRICTION

If a load test is running too slowly, a bigger box for the generator will do almost nothing. The database is the constriction.

The full run#

Points	10,000,000
Workers	6
Time	26.1 s
Rate	382,555 points/sec

On a shared box with several other containers running. Simulating a plant's worth of history is not a hardware problem.

The finding that costs money#

Two identical tables. Same schema, same 10,000,000 rows, same database, same compression settings. One holds plant-shaped data; the other holds uniform random numbers, which is what a quickly-written generator emits.

Arm	Raw	Compressed	Ratio	Bytes per reading
shaped like a plant	695 MB	16.2 MB	43.0×	1.69
uniform random	696 MB	81.7 MB	8.5×	8.57

10,000,000 READINGS, COMPRESSED

shaped like a plant43.0× · 1.69 bytes per reading

695 MB raw16.2 MB

uniform random numbers8.5× · 8.57 bytes per reading

695 MB raw81.7 MB

Compression works by finding repetition. Plant data is full of it — states that hold, counters that step by a constant, analogues that wander between neighbouring values. Random numbers contain none, so there is nothing to remove.

A load test built on noise understates compression by five times. It is not an obviously broken test. It runs, it produces a number, and the number is confidently wrong in the direction that makes you buy hardware.

THE NUMBERS WORTH REMEMBERING

26.1 s

TO MAKE 10 MILLION READINGS

1.69

BYTES PER READING, STORED

53 MB

ONE TAG, EVERY SECOND, FOR A YEAR

5×

THE ERROR IF YOU TEST WITH NOISE

At 1.69 bytes per stored reading, one tag sampled every second for a year is about 53 MB. A thousand of them, roughly 53 GB a year.

What I would tell someone repeating this#

Model the shape, not the statistics. You do not need a physics model; you need values that hold still, values that step, and counters that only climb. That is an afternoon of work and it is the difference between a storage estimate you can sign and one that is out by five times.

Write in chunks. Check your loading loop before you blame the engine.

And do not buy a bigger machine for the generator. It was idle.

What this feeds#

The ten-million-row table is the input for the ClickHouse against TimescaleDB comparison, so both engines get measured on identical, realistically shaped data rather than on noise.

The argument, without the measurements, is in the article.