We had a billion rows of event data and a dashboard that had stopped being usable. The obvious move was to put the analytics somewhere purpose-built. The less obvious question was whether we needed to at all.
So we measured. Same hardware, same dataset, same twelve queries pulled from the dashboard people actually loaded.
The setup
One billion rows of event data: timestamp, user ID, event type, a handful of dimensions, and a numeric value. About 180 GB as raw CSV. Both engines on the same 16-core box with 64 GB of RAM and NVMe storage.
Postgres 16 with a BRIN index on the timestamp and btree on the dimensions
we filtered by. ClickHouse with a MergeTree partitioned by month and ordered
by (event_type, ts).
The first surprise was on disk. Postgres took 214 GB with its indexes. ClickHouse took 19 GB. Columnar storage plus per-column compression means the low-cardinality columns (event type, country, platform) compress to almost nothing, and you never read the columns a query doesn’t mention.
Where ClickHouse ran away with it
The dashboard’s bread-and-butter query, one aggregate over a wide time range, is exactly what a columnar engine is for:
SELECT toStartOfDay(ts) AS day,
event_type,
count() AS events,
sum(value) AS total
FROM events
WHERE ts >= now() - INTERVAL 90 DAY
GROUP BY day, event_type
ORDER BY day;
Postgres: 42 seconds. ClickHouse: 0.6 seconds.
That’s not tuning; that’s architecture. Postgres reads whole rows off the heap, so a query touching four columns still pays for all twenty-two. ClickHouse reads four columns, compressed, in parallel across cores. Across our twelve dashboard queries the pattern held: full-scan aggregates came in 20× to 70× faster.
Where Postgres quietly won
Then we ran the queries that weren’t dashboards.
Point lookups. WHERE user_id = ? returning one row: Postgres 0.8 ms,
ClickHouse 41 ms. A B-tree lookup is the thing Postgres is for. ClickHouse has
no equivalent; its sparse primary index narrows to a granule and then scans it.
Updates and deletes. A single-row correction: Postgres, trivial. ClickHouse,
an ALTER TABLE ... UPDATE mutation that rewrites data parts in the background
and is asynchronous by design. GDPR-style “delete this user’s rows” requests
went from a one-liner to a piece of operational planning.
Joins across many tables. Our five-table join on normalised reference data was 1.4× slower on ClickHouse. Its join support has improved a great deal, but Postgres has decades of planner work behind it and it shows.
Transactions. ClickHouse doesn’t offer the multi-statement, multi-table guarantees we relied on everywhere in the application. That’s not a gap to work around; it’s a statement about what the tool is.
These aren’t bugs on either side. Each engine is fast at the shape of question it was designed to answer, and slow at the other one.
What we actually shipped
Both. Postgres stayed the system of record: transactions, user data, anything that gets updated or read one row at a time. Events stream into ClickHouse for the dashboards. A small worker tails the Postgres WAL and batches inserts, which matters more than it sounds: ClickHouse wants large inserts, and row-at-a-time writes will bury it in tiny parts to merge.
The dashboard went from 42 seconds to under a second. The application code didn’t change at all.
How to decide without running the benchmark
If your queries scan a lot of rows and return a few aggregates, over data that’s append-only, you want a columnar engine. If your queries fetch a few rows by key, over data that gets updated, inside transactions, you want Postgres.
Most products have both kinds of question and try to answer them with one engine. That works fine until it doesn’t, and where it stops working is a size, not a design flaw. For us it was somewhere north of a hundred million rows.
The mistake isn’t picking the wrong database. It’s assuming there’s one right answer to two different questions.