Here's the short version. We store hundreds of millions of order book snapshots. Every one has a timestamp down to the millisecond. People run queries like "show me every price change on this market between 2pm and 2:05pm on a specific day." We needed a database built for exactly that job, not one that could sort of do it.
What the actual difference is
TimescaleDB is Postgres with extra tricks for time series data. Under the hood it is still a row based database. That means when it saves a snapshot, it saves the whole row together, timestamp, bid price, ask price, depth, all of it, as one chunk.
ClickHouse works the opposite way. It is column based. It saves all the timestamps together, all the bid prices together, all the ask prices together, and so on. Sounds like a small difference. It is not.
Why that matters for us specifically
Picture what a typical query on our data actually looks like. Someone wants every ask price for one market over a two hour window. They do not care about ninety percent of the other columns in that row.
With a row based system, the database still has to read the whole row to get that one column, then throw away everything else. With a column based system like ClickHouse, it only touches the column you actually asked for. When you are scanning millions of rows for one or two fields, that difference in how much data gets read turns into a massive difference in how fast the answer comes back.
The other reason: how fast we write data in
We are not just storing a little bit of data slowly. We are capturing full order book depth at millisecond resolution, across a hundred plus markets, all the time. That is a constant, heavy stream of writes.
Postgres, and TimescaleDB by extension, was built assuming you write data one transaction at a time with strict guarantees around it. That is great for things like bank transactions. It is not built for a firehose of market ticks arriving nonstop. ClickHouse was built from the ground up for exactly that kind of high volume ingestion, without the overhead Postgres carries for transactional safety we do not actually need here.
What we gave up
Nothing is free. ClickHouse is not great at the kind of thing Postgres is great at, updating individual rows, complex joins across many tables, strict transactional guarantees. If your use case is smaller data with lots of updates and complex relationships, TimescaleDB or plain Postgres is probably the better call. Ours is not that. Ours is enormous, mostly append only, and read in bulk. That is ClickHouse's exact use case.
The real test
We run ClickHouse Cloud so we are not managing the infrastructure ourselves on top of everything else. For a dataset our size, growing by millions of snapshots a day, this was the tool built for the job instead of the tool we happened to already know.
Full docs at resolvedmarkets.com/docs/mcp if you want to see the kind of queries this setup actually handles.
Top comments (0)