Test your historian with random numbers and it will lie to you about disk

You are about to size the storage for a historian. Somebody suggests a load test, which is the right instinct, so a script gets written that pumps a few million readings in and everyone looks at the disk usage afterwards.

If that script makes up its numbers at random, the answer will be wrong. Not slightly wrong. In my test it was wrong by a factor of five, and it was wrong in the expensive direction.

Why the shape of the data matters#

A historian does not store your readings one by one. It looks for repetition and squeezes it out. A value that has not changed since the last sample costs almost nothing to keep. A value that climbs by the same amount every second costs almost nothing either, because only the pattern needs storing.

Plant data is full of that kind of repetition, and it is worth looking at what it really looks like rather than imagining it.

Every one of those is easy to squeeze. The state tag sits on one value for hours. The counter climbs in a straight line. The flow is flat at zero, then flat at fifty. Even the temperature, which never stops moving, only wanders slowly between nearby values.

Random numbers have none of this. Each one is unrelated to the one before it, so there is nothing to find and nothing to remove. That is the whole problem: a test built on random numbers is measuring a kind of data your plant will never produce.

The test#

I wrote a generator that produces six kinds of tag — temperature, flow, pressure, vibration, a production counter and a discrete state — mixed so that slow analogue values dominate, the way they do on a real site. Ten thousand tags, sampled every second. Every value is worked out from the tag number and the sample number, so a run can be repeated exactly without storing anything.

Then I made the same volume twice: ten million readings shaped like a plant, and ten million uniform random numbers. Same table layout, same database, same compression settings. The only difference was the shape of the numbers.

Six hundred and ninety-five megabytes of raw readings became 16.2 MB when the data behaved like a plant, and 81.7 MB when it was noise. That is 43× against 8.5×.

Had I sized a historian off the random-number run, I would have bought five times the disk I actually needed, and been confident about it, because the test was real and the numbers were honest. They were just measuring the wrong thing.

The figure worth carrying around is 1.69 bytes per stored reading. One tag sampled every second for a year is about 31.5 million readings, so roughly 53 MB kept forever. A thousand such tags is around 53 GB a year. That is a number you can take into a budget meeting, and it is nothing like what the noise test would have told you.

What it took to make ten million readings#

This is the part I expected to be hard, and it was not. Making the readings is almost free.

One core works out over three million readings a second. Turning them into the JSON a broker would carry costs about two thirds of that. Publishing them over MQTT costs most of the rest. And writing them into the database is where the money goes.

There is a lesson buried in that bottom bar. COPY is meant to be the fastest way to bulk-load Postgres, and mine came out three times slower than ordinary batched inserts, which should not happen.

My first guess was timestamp formatting. Every tag in a sample shares the same timestamp, and my code was rebuilding that text once per reading instead of once per sample. Fixing it helped by about ten per cent, so the guess was reasonable and also not the answer.

The real cost was writing one row at a time. Each tiny write carries the same overhead whether it holds forty bytes or forty thousand, and I was paying it ten million times. Buffering the rows and writing two thousand at a time made it 5.6 times faster and moved COPY from the slowest option to the fastest. The database was never the problem; my loop was.

Adding cores does not fix a database problem#

Once the loading was sensible I tried spreading the work over more processes, expecting it to scale.

It did not. Six processes gave 1.31 times the throughput of one. The generator can make 3.1 million readings a second on a single core, but six of them writing at once only landed 398,000.

Which tells you where to spend. If your load test is too slow, a bigger machine for the generator will do almost nothing, because the generator was never working hard. The database is absorbing everything, and that is the thing to tune or to accept.

The whole ten million landed in 26.1 seconds, on one small box, alongside several other containers. Simulating a plant's worth of history is not a hardware problem.

If you take three things#

Make your test data behave like your plant: values that hold, values that step, counters that only climb. It does not have to be a sophisticated model, but it must not be noise, or every storage number that follows will be wrong in the expensive direction.

Write to the database in chunks, not row by row, and check the loading code before blaming the engine. Mine was off by 5.6 times and looked perfectly reasonable.

And do the test at all. Ten million readings took under half a minute to produce. That is a cheap way to find out what a year of history actually costs before somebody signs for the disks.

The generator, the signal models, every measurement, and the wrong guess I made along the way are in the lab write-up.