ClickHouse vs PostgreSQL for GROUP BY Queries

Cut Infra Costs 64%

Free ClickHouse Audit

Two years ago, a fintech client paged me at 2 AM. Their Postgres dashboard query — a simple GROUP BY merchant_id over 400 million transaction rows — had gone from 8 seconds to 6 minutes after a data migration. That query powered a compliance report. It couldn't just be slow.

We rebuilt that aggregation in ClickHouse in an afternoon. Same data. Same grouping. 340 milliseconds.

But here's the part nobody tells you: we kept Postgres running the transactional workload right alongside it. Because ClickHouse would've been the wrong tool for half the queries hitting that same table.

That's the whole game with clickhouse vs postgresql for group by queries. It's not a winner-takes-all fight. It's a question of which query shape you're running, and how much you're willing to pay for it in 2026.

I've shipped both in production. I've watched Postgres column-store extensions change the math. I've watched ClickHouse pricing shift under new licensing. This is what I'd tell a peer who's about to make this call.

Quick definition for the uninitiated: GROUP BY is the SQL clause that collapses rows into buckets — sum revenue by region, count events by user, average latency by endpoint. Everything else in this article is about how fast each database does that collapse, and what it costs you.

Here's what you'll learn: where each engine actually wins on aggregation, a real cost comparison for 2026, and how to decide without regretting it in eighteen months.

Why GROUP BY Is the Query That Exposes Everything

Most benchmarks lie to you. They run SELECT * on a single row, or they measure inserts, and you walk away thinking you know which database is faster.

Aggregation is the honest test.

A GROUP BY query forces the engine to do four things at once: read a huge number of columns, filter, build hash tables or sort groups, and merge partial results across threads. Every architectural decision a database makes — storage layout, execution model, memory management — shows up in that one query.

Postgres stores data row by row. To sum one column, it drags the entire row off disk. That's not a bug. It's the price of MVCC, transactions, and the ability to update a single cell without rewriting a chunk of the table.

ClickHouse stores data column by column. To sum one column, it reads only that column. A thousand-row-wide table becomes a ten-column scan. For aggregation, this is a cheat code.

So when someone asks me "which is faster for GROUP BY," the honest answer is: ClickHouse, by a lot, for analytical aggregations. And then I ask them what else they're running, because that's where the decision actually lives.

The Architecture Gap That Drives Everything

Let me be blunt about why the performance numbers look the way they do.

Postgres uses a Volcano-style execution model. Rows flow one at a time through operators. It's flexible, it handles complex joins and transactions beautifully, and it's been hardened by thirty years of production use. It was never designed to scan a billion rows and aggregate them in 300ms.

ClickHouse uses vectorized execution. It processes data in batches of thousands of values at once, using SIMD instructions on the CPU. Combine that with MergeTree storage, sparse primary indexes, and aggressive parallelism, and you get an engine that scans columns like it's reading from RAM even when it's on disk.

There's a second, subtler difference: ClickHouse has no row-level locking, no MVCC in the Postgres sense. It appends. That's why it can't do UPDATE ... WHERE id = 5 efficiently (it rewrites whole parts asynchronously), and it's also precisely why it can aggregate so fast. No version chains to walk. No dead tuples to filter.

Postgres, meanwhile, has to check visibility on every row. That's the transaction tax, and it's real.

If you want the deep version of this, ClickHouse's own docs on MergeTree are worth an hour of your time.