ClickHouse or TimescaleDB for a plant historian? It depends which question your plant asks

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

The short version is that neither wins outright, the winner depends entirely on which question your plant asks most often, and there is a third finding that matters more than the contest.

How the test was set up#

Both engines got the same box, and both were loaded from the same generated file — not from two separate scripts, because then I would be measuring my own loaders rather than the databases.

The data matters as much as the engines. It is ten million readings from a thousand simulated tags: temperatures that drift and hold, flows that sit at zero and then step when a pump starts, counters that only ever climb, states that stay put for hours. Testing with random numbers instead would have understated both engines' compression by about five times, which is a mistake worth its own write-up.

Loading#

This one is not close. The identical file went into ClickHouse in under four seconds and took TimescaleDB forty-one. If your problem is getting a decade of tag history out of an old system and into a new one over a weekend, that gap is the whole argument.

Storage, and the setting nobody mentions#

The raw readings occupy nearly seven hundred megabytes. Compressed TimescaleDB holds them in sixteen. ClickHouse with its default settings uses forty-two — worse than Timescale, which surprised me, because ClickHouse has a reputation for compression.

It turns out the default is the problem. ClickHouse ships with LZ4, which is tuned to be fast rather than small. Time-series data wants different encodings: store the difference between one timestamp and the next rather than the whole timestamp, and use a float encoding built for slowly-changing sensor values. Switching to those took one table definition and about two seconds of rewriting.

Six point six times less disk, and the queries ran within three milliseconds of where they were. That is not a trade-off, it is a default that is simply wrong for this job. If you run ClickHouse as a historian and have never set codecs on the table, you are storing roughly seven times more than you need to, and nothing in the product will tell you.

At 0.67 bytes per stored reading, a thousand tags sampled every second for a year comes to about twenty gigabytes.

The queries#

This is where it stops being one-sided.

Read that by row rather than by column, because the row that matters is whichever question your plant asks most.

"What is everything reading right now." The most routine question in any control room, and the worst case for a plain Postgres table: 11.2 seconds against 104 milliseconds. Turning on compression drags Timescale back to 508 ms, which is fine for a wall display, but ClickHouse is still five times quicker. In fairness to TimescaleDB, it has purpose-built tools for exactly this query, and a real deployment would use them. This is what the obvious query does.

"Show me this instrument for the last ten minutes." The thing an operator does dozens of times a shift, and here compressed TimescaleDB is faster than ClickHouse — 57 ms against 77. Same for bucketing one tag into averages, 59 ms against 76. If your historian mostly serves single trends, the row-store is not the underdog anybody assumes.

"Roll up the whole estate." ClickHouse by a distance, 167 ms against 1.2 seconds. And here compression actually made Timescale worse, 1,202 ms to 1,955 ms, because a query that touches everything has to decompress everything on the way past. Compression pays on selective reads and charges you on full scans.

So which one#

ClickHouse if you are ingesting hard, keeping years of history, or regularly asking questions that sweep across every tag you own. It loads eleven times faster and, once its codecs are set, stores two and a half times smaller than compressed TimescaleDB.

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 a normal SQL database. That last point deserves more weight than it usually gets in benchmark arguments. Grafana, your reporting tool, 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 engines are comfortable at that size, and the honest reading of these numbers is that at small scale you should choose on operational fit rather than performance. The gap widens with volume, and that is when the load and storage figures start to decide it.

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

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