Two databases for a plant historian, and why neither one wins

Sooner or later somebody suggests replacing the plant historian, and the conversation turns into an argument between two databases that are both, genuinely, good. I ran the comparison on ten million readings to get past the sales material.

The short version: neither wins outright, which one suits you depends entirely on what your plant asks it most often, and there is a third finding that matters more than the contest does.

The two are ClickHouse and TimescaleDB. If you have not met either, the only thing you need to know for now is that TimescaleDB is built on top of Postgres, which is the ordinary database a great deal of other software already knows how to talk to. ClickHouse is a specialist built for enormous piles of readings.

How the test was set up#

Both got the same machine, and both were filled from the same file. That matters more than it sounds. If I had written two separate loading scripts I would have been comparing my own code rather than the databases.

The readings themselves behave like a plant: temperatures that drift and hold, flows that sit at zero and then jump when a pump starts, counters that only climb. Using random numbers instead would have made both databases look far more expensive to run than they are, which is a mistake worth its own article.

Filling it up#

LOADING THE SAME FILE INTO BOTH

ClickHouse

3.75s

TimescaleDB

41.2s

0s15s30s45s

This one is not close. The same file went into ClickHouse in under four seconds and took the other one forty-one.

If your problem is getting a decade of history out of an old system and into a new one over a shutdown weekend, that gap is the whole argument and you can stop reading here.

Disk, and the setting nobody mentions#

The raw readings take up nearly seven hundred megabytes. TimescaleDB squeezes them into sixteen. ClickHouse, with the settings it arrives with, uses forty-two, which is worse. That surprised me, because ClickHouse has a reputation for being extremely good at exactly this.

It turns out the default is the problem. Out of the box it is set up to be fast rather than small. Readings from a plant want different handling: store the difference between one timestamp and the next rather than the whole thing every time, and use a method built for sensor values that barely change.

Switching those on was one line in the table definition and about two seconds of work.

ONE SETTING NOBODY CHANGES

42 MB

as it arrives

6 MB

one setting changed

Same readings. Same query speed. Seven times the disk.

Six and a half times less disk, and the queries ran at the same speed as before. That is not a trade-off to weigh up. It is a default that is simply wrong for this job, and nothing in the product tells you.

If you run ClickHouse as a historian and nobody has changed that setting, you are storing roughly seven times more than you need to.

Asking it questions#

This is where it stops being one-sided.

WHO WINS WHICH QUESTION

“What is everything reading right now?”ClickHousea tenth of a second, against eleven seconds

“Show me this one instrument.”TimescaleDBthe quicker of the two, by a little

“Add up the whole site.”ClickHouseseveral times faster

Which of those three your plant asks most often is the whole decision.

"What is everything reading right now." The most ordinary question in any control room, and the worst case for the Postgres-based one: eleven seconds against a tenth of a second. Turning on its squeezing brings it back to about half a second, which is fine for a wall display, but ClickHouse is still five times quicker. In fairness, TimescaleDB has purpose-built tools for exactly this question and a real installation would use them. This is what the obvious version of the question does.

"Show me this instrument for the last ten minutes." What an operator does dozens of times a shift, and here TimescaleDB is the faster of the two. Same for averaging one tag over time. If your historian mostly serves single trends, the one everybody assumes is the underdog is not.

"Add up the whole site." ClickHouse by a distance. And here its rival got slower after squeezing, because a question that touches everything has to unpack everything on the way past. Squeezing pays off on narrow questions and charges you on wide ones.

So which one#

ClickHouse if you are pouring data in hard, keeping many years of it, or regularly asking questions that sweep across every tag you own.

TimescaleDB if the daily reality is opening one instrument's trend, or if you already run Postgres, or if anything downstream expects to talk to an ordinary database. That last point deserves more weight than it usually gets. Your reporting tool, the dashboards, the application somebody wrote in 2019 and the colleague who already knows Postgres are all real costs on one side of the ledger and real savings on the other.

There is also a fair criticism of this whole exercise. Ten million readings is about three hours of a thousand-tag plant. Both databases are comfortable at that size, and the honest reading is that at small scale you should choose on what fits your operation rather than on speed. The gap widens with volume, and that is when the filling and disk numbers start to decide it.

The one thing I would change on Monday regardless of which you run is that compression setting. Six megabytes against forty-two, for the same readings and the same speed, is the cheapest win in this entire comparison.

The full setup, every query, the fairness checks and a bug I found in my own test data are in the lab write-up.