When ClickHouse Is the Right Database, and When Postgres Is Enough

The database choice usually gets made backwards. Someone reads that ClickHouse aggregates a billion rows in the time Postgres takes to think about it, and the decision is made before anyone has written down what the product actually needs to do. Then the work becomes fitting the workload to the database, instead of the other way around.

It’s worth slowing that down, because this isn’t a popularity contest. ClickHouse is genuinely excellent at what it does. So is Postgres. The useful question isn’t which one is better, it’s which one your workload has actually earned, and most workloads earn Postgres for a lot longer than people expect.

There are only two query shapes that matter here

Almost every argument about analytical databases comes down to two shapes of query.

The first is transactional. Fetch one order, update its status, insert a row. Small, keyed, frequent, and latency-sensitive because a person or a request is usually waiting on it.

-- transactional: one row, by key
select * from orders where id = 91823;

update orders set status = 'shipped' where id = 91823;

The second is analytical. Scan a lot of rows, group them, and reduce them to a number or a trend.

-- analytical: aggregate margin across every store, by month
select
	store_id,
	date_trunc('month', sold_at) as month,
	sum(revenue - cost) as margin
from sales
where sold_at >= now() - interval '12 months'
group by store_id, month
order by month;

Postgres is built for the first shape and is very good at the second one too, right up until the second shape gets big enough to change the physics. That threshold is the whole conversation, and knowing which shape dominates your product is step one. If you don’t already know, that’s a signal to measure your real query patterns before you pick anything.

Postgres goes further than people expect

The mistake is jumping straight from “our dashboards are slow” to “we need ClickHouse,” skipping everything in between. There’s a lot in between.

The first moves are ordinary. The right indexes, partitioning large tables by time so a query only touches the range it needs, and materialised views that compute an expensive aggregate once instead of on every page load.

create materialized view monthly_store_margin as
select
	store_id,
	date_trunc('month', sold_at) as month,
	sum(revenue - cost) as margin
from sales
group by store_id, month;

-- refresh on a schedule that matches how fresh the numbers need to be
refresh materialized view concurrently monthly_store_margin;

The bigger move, and the one people skip, is separating the workloads without leaving the Postgres family. You don’t have to run analytics on the same server that takes your writes. Ingest on the primary, and run the heavy aggregations against a read replica or a separate reporting database, so a slow report can’t ever contend with a customer’s checkout. That’s OLTP and OLAP separated, on infrastructure your team already knows.

From there the family has specialists. TimescaleDB turns a large time-series table into a hypertable and keeps rollups fresh with continuous aggregates, which is exactly the shape most retail-style analytics takes:

-- TimescaleDB
select create_hypertable('sales', 'sold_at');

create materialized view monthly_store_margin
with (timescaledb.continuous) as
select
	store_id,
	time_bucket('1 month', sold_at) as month,
	sum(revenue - cost) as margin
from sales
group by store_id, month;

And Citus distributes Postgres across nodes when a single machine runs out of room to scan, giving you scale-out aggregation while still speaking Postgres. Read replicas, partitioning, materialised views, Timescale, Citus: that’s a long runway, and all of it keeps your query language, your tooling, and your team’s existing knowledge. Every step you take here is a second system you didn’t have to adopt.

When ClickHouse earns it

There’s a real point where all of that strains, and it’s worth naming so people can tell whether they’re actually there.

You’ve reached it when the analytical shape dominates and the volume is genuinely large: aggregations scanning hundreds of millions to billions of rows, ingest that’s high and continuous rather than occasional, and a need for sub-second responses across all of it. When you’re recomputing wide aggregates over enormous history often enough that even a tuned Timescale or Citus setup can’t keep up, you’ve hit the shape ClickHouse was built for. It’s columnar, so it only reads the columns a query touches. It’s heavily compressed, so scanning huge ranges stays cheap. On that workload it isn’t a little faster than Postgres, it’s a different category of fast, and it deserves the praise it gets.

The signal isn’t “our reports feel slow.” It’s “we’ve separated the workload, tuned the analytical side, and the numbers still don’t work.” That’s a real threshold, not a vibe.

Writing to ClickHouse isn’t like writing to Postgres

There’s a second half to “workload” that’s easy to miss when you’re staring at read speed: how the data gets in, and how it changes once it’s there. This is where Postgres quietly wins for a lot of products.

Postgres treats a single row as a first-class thing. You insert it, you update it in place, and an upsert is one statement that’s immediately consistent:

-- Postgres: upsert one row, immediately consistent
insert into stock (store_id, product_id, quantity, as_of)
values ('421', 'CB-1KG', 37, now())
on conflict (store_id, product_id)
do update set quantity = excluded.quantity,
              as_of    = excluded.as_of;

That flow, write a row, correct it later, read it back and trust it, is the bread and butter of operational data, and Postgres is excellent at it.

ClickHouse doesn’t work that way, and pretending it does is how people get hurt. It’s append-optimised and columnar. It wants large batched inserts, not a row at a time, and it has no cheap in-place update. The equivalent model is to append and let a table engine collapse duplicates during background merges:

-- ClickHouse: append, and let the engine keep the latest version
create table stock (
	store_id   String,
	product_id String,
	quantity   UInt32,
	as_of      DateTime
) engine = ReplacingMergeTree(as_of)
order by (store_id, product_id);

-- inserts are appends, batched where possible, never one row at a time in a loop
insert into stock values ('421', 'CB-1KG', 37, now());

The catch is in the word “eventually.” Those duplicates don’t collapse the moment you write. They collapse when a background merge runs, which might be seconds away or much longer. Until then a plain read sees every version, so you have to ask for the collapsed one explicitly, and pay for it:

-- duplicates linger until a merge runs; FINAL forces the collapsed view now, at a cost
select store_id, product_id, quantity
from stock final
where store_id = '421';

None of this is a flaw. It’s the trade that makes the reads so fast. But it means the write side is real design work, not a detail you sort out later. If your data is update-heavy, with lots of records changing in place, corrections, and per-row upserts, then Postgres’s model is genuinely the better fit and ClickHouse is fighting you the whole way. If your data is append-mostly, events that arrive, get batched, and rarely change after the fact, then ClickHouse is in its element. The shape of your writes is as much a part of the decision as the shape of your reads.

The real cost is a second system

The reason to be slow about this is that ClickHouse isn’t a faster Postgres you swap in. It’s a second database you run alongside the first, and that carries a standing cost that has nothing to do with query speed.

You now need a pipeline to move data from your transactional world into ClickHouse, and you accept whatever freshness lag that introduces. You take on a different query dialect, a different operational model, different failure modes, and the expertise to run all of it. None of that is a reason to avoid ClickHouse. It’s the bar the win has to clear. If separating the workload inside Postgres gets you fast enough, that bar isn’t worth paying. When ClickHouse is genuinely the right call, the speed is so far ahead that the second system pays for itself, and you’ll know because you did the cheaper things first and they weren’t enough.

Your platform and API shape the answer too

The workload isn’t the only input. How you’ve built the product tilts the decision as much as the query shapes do, and the thing that tilts it is whether there’s a seam to put a new datastore behind.

Here’s the version with no seam. The analytical query is welded straight into the endpoint, against the same Postgres that serves transactional traffic:

// no seam: the endpoint talks to the transactional database directly
app.get("/stores/:id/margin", async (req, res) => {
	const rows = await pg.query(
		`select date_trunc('month', sold_at) as month,
		        sum(revenue - cost) as margin
		 from sales
		 where store_id = $1
		 group by month
		 order by month`,
		[req.params.id],
	);
	res.json(rows);
});

To move that to ClickHouse you rewrite this handler, and every other one shaped like it. The database choice is smeared across the whole product, so changing it touches the whole product.

Now the version with a seam. The product depends on an interface, not a database:

// a seam: the product depends on an interface, not a database
interface Analytics {
	storeMargin(storeId: string): Promise<MarginPoint[]>;
}

app.get("/stores/:id/margin", async (req, res) => {
	res.json(await analytics.storeMargin(req.params.id));
});

The implementation lives behind that interface, and it’s the only thing that knows which database actually answers the question:

// today it's Postgres. swapping to ClickHouse changes this file and nothing else.
class PostgresAnalytics implements Analytics {
	storeMargin(storeId: string): Promise<MarginPoint[]> {
		return this.db.query(/* the aggregation lives in one place */);
	}
}

Same query, but now the datastore is a detail behind a boundary. Moving analytics to ClickHouse becomes a new implementation of one interface, not a rewrite of every endpoint. The architecture you already have decides how expensive the move is, sometimes more than the data volume does. A product built with that seam can defer the decision cheaply and make it cheaply when the time comes.

Separate the workload, not the database

The lesson underneath all of this is that most teams reaching for ClickHouse don’t actually have a ClickHouse problem. They have a “we’re running analytical queries against our transactional database” problem, and that one is solved by separating the workload, not by adopting a new engine.

So do the separation first. Split OLTP from OLAP, push it to a replica, reach for Timescale or Citus when the shape calls for it, and keep it all in the family your team already runs. ClickHouse is a superb tool, and when your workload genuinely arrives at its doorstep, reach for it without hesitation. Just make sure you’re reaching for it because the numbers took you there, and not because it was the exciting option on the shelf.