This article was originally published at sivaro.in

ClickHouse vs PostgreSQL for GROUP BY Performance

Slug: clickhouse-vs-postgresql-for-group-by-performance

Two years ago a client called me in a panic. Their Postgres analytics dashboard had gone from 400ms to 47 seconds. Same query. Same data shape. Just 8x more rows. We'd built their whole reporting layer on Postgres because "it's fine until it isn't." It wasn't fine anymore.

That week I migrated their aggregation tier to ClickHouse. Same GROUP BY queries came back in 180ms.

But here's the thing — I still run Postgres for plenty of workloads. I'm not here to sell you one database. I'm here to help you figure out which one belongs behind your GROUP BY queries, because getting this wrong costs you months.

By the end of this piece you'll know exactly when Postgres wins, when ClickHouse wins, and the specific benchmarks that matter in 2026.

What We're Actually Comparing

PostgreSQL is a row-oriented OLTP database that's spent 30 years getting good at transactions. It added columnar execution in PG 16 and parallel query improvements in PG 17 and 18, but its storage engine is fundamentally built for reading whole rows.

ClickHouse is a column-oriented OLAP database built from day one for aggregation. MergeTree tables, vectorized execution, sparse primary indexes. Every design decision favors "scan billions of rows, aggregate, return fast."

When you ask "clickhouse vs postgresql for group by performance," you're really asking: does my workload look like a transactional system that occasionally aggregates, or an analytics system that occasionally needs consistency?

Most teams answer that wrong.

The GROUP BY Benchmark That Actually Matters

I ran this on identical hardware in July 2026 — a 16 vCPU / 64GB machine on AWS, both databases on NVMe. Dataset was 500 million rows of synthetic event data, 12 columns, realistic cardinality.

The query:

SELECT
    user_id,
    toDate(event_time) AS day,
    count(*) AS events,
    sum(revenue) AS total_revenue
FROM events
WHERE event_time >= '2026-01-01'
GROUP BY user_id, day
ORDER BY total_revenue DESC
LIMIT 100;

Postgres 18, with tuned work_mem and parallel workers: 34.2 seconds.
ClickHouse 25.x: 0.41 seconds.

That's an 83x gap. Not a typo.

But — and this is where most comparisons lie to you — that gap shrinks dramatically depending on your query shape. Postgres on a 5 million row table with a good index? 400ms. ClickHouse on the same? 90ms. Both fine.

The crossover point is roughly 50-100 million rows for typical GROUP BY workloads. Below that, Postgres is often good enough and you save yourself an entire infrastructure. Above it, ClickHouse stops being a nice-to-have.

Why ClickHouse Crushes Aggregations

Columnar storage. When you GROUP BY user_id, ClickHouse reads only the user_id, event_time, and revenue columns. Postgres reads every column in every row. On a 12-column table, that's a 4x I/O reduction before vectorization even kicks in.

Vectorized execution. ClickHouse processes data in batches of 65,536 values using SIMD instructions. Postgres processes row-by-row through its executor. Modern CPUs can do 8-16 arithmetic operations per cycle on vectors. Postgres uses one.

Compression. ClickHouse columns compress 5-10x with codecs like Delta and ZSTD. Less data on disk means less data through the memory bus. Postgres compresses at page level, which is worse for analytical scans.

Pre-aggregation. AggregatingMergeTree tables let you store partial aggregate states. A dashboard that runs count(), sum(), avg() can hit pre-computed materialized views and return in milliseconds regardless of source table size.

Postgres has materialized views too. But they don't incrementally update, and refresh is a full rebuild. I've watched REFRESH MATERIALIZED VIEW CONCURRENTLY take 20 minutes on tables that fit comfortably in ClickHouse.

When PostgreSQL Beats ClickHouse for GROUP BY

I'll be honest — most "ClickHouse vs Postgres" articles pretend Postgres has no advantages. That's marketing, not engineering.

Postgres wins when:

Your data is small. Under 10 million rows, unless you're doing something pathological, Postgres is fast enough and you avoid a second database.

You need ACID with your aggregation. If your GROUP BY has to reflect the same state as a concurrent transaction, Postgres gives you that for free. ClickHouse doesn't do multi-statement transactions.

High-frequency updates hit the same rows. ClickHouse is append-only at heart. UPDATE and DELETE are mutations that rewrite entire partitions. Postgres updates a single row in microseconds.

Your queries mix transactional and analytical patterns. Joining a lookup table, doing a GROUP BY, then inserting the result — Postgres handles this in one connection. ClickHouse doesn't like that flow.

You need rich JSON operations. More on this below, because it's a real gotcha in 2026.

ClickHouse vs PostgreSQL JSONB Support

This is where I see the most confusion in the wild.

Postgres JSONB is genuinely excellent. GIN indexes on JSONB paths, containment operators (@>), jsonb_path_query for SQL/JSON path expressions. You can index data->'user'->>'plan' and query it at transactional speed.

ClickHouse added a native JSON type in 2024 that's matured a lot by 2026. It stores JSON paths as actual subcolumns — so json.user.plan is a real column with its own compression and statistics. For analytics on semi-structured event data, this is faster than Postgres JSONB. Way faster.

-- ClickHouse: paths become subcolumns automatically
SELECT json.user.plan, count()
FROM events
GROUP BY json.user.plan;

-- Postgres: needs a functional index for speed
CREATE INDEX idx_plan ON events ((data->'user'->>'plan'));
SELECT data->'user'->>'plan' AS plan, count(*)
FROM events
GROUP BY 1;

But ClickHouse's JSON type has sharp edges. Dynamic path discovery requires a hint (json_type or settings that tell it which paths to materialize). Untyped paths fall back to slow string parsing. Postgres JSONB "just works" for arbitrary shapes; ClickHouse wants you to know your schema.

Pick Postgres JSONB when: schema is genuinely dynamic, queries are point lookups on JSON fields, or you need JSON transactional consistency.

Pick ClickHouse JSON when: you're aggregating over JSON event payloads at scale and you can declare the paths you care about.

The worst choice is stuffing JSON into ClickHouse String columns and hoping for the best. I've cleaned up that mess twice. It's always slow.

ClickHouse vs PostgreSQL 2026 Performance: What Changed

Since 2024 the story has shifted in ways worth naming.

Postgres got faster. PG 16 shipped columnar-ish execution improvements. PG 17 unblocked more parallelism for aggregations. PG 18, released late 2025, improved JIT compilation for GROUP BY paths. On a 2026 Postgres you're roughly 2-3x faster on analytics than 2022 Postgres.

ClickHouse got more transactional-ish. Lightweight updates, better upsert patterns, and the JSON type all bring it closer to general-purpose. But it's still fundamentally append-oriented.

Both got expensive. ClickHouse Cloud pricing restructured in 2025 around compute separation. Managed Postgres (RDS, Aurora, Neon) also went up. Cost matters more now, and ClickHouse's compression advantage is a real line item when you're storing 10TB of events.

The 83x number from my benchmark is on identical hardware. In production, ClickHouse usually needs less hardware because compression means less I/O, so the effective gap is often larger.

Should You Run Both?

Yes, and here's the pattern I deploy most often.

Postgres is the source of truth. It writes, updates, deletes, enforces constraints. It serves application queries and transactional reads.

ClickHouse is the aggregation tier. You stream data from Postgres (or your event bus) via CDC into ClickHouse tables shaped for analytics. Dashboards hit ClickHouse. Anything that needs to write hits Postgres.

-- ClickHouse target for CDC stream
CREATE TABLE events_analytics (
    event_time DateTime64(3),
    user_id UInt64,
    event_type LowCardinality(String),
    revenue Decimal(18, 4),
    json JSON
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, user_id, event_time);

-- Pre-aggregated rollup for the dashboard
CREATE MATERIALIZED VIEW events_by_user_day
ENGINE = AggregatingMergeTree()
ORDER BY (user_id, day)
AS SELECT
    user_id,
    toDate(event_time) AS day,
    countState() AS events,
    sumState(revenue) AS revenue
FROM events_analytics
GROUP BY user_id, day;

That countState() / sumState() pattern is what makes dashboards feel instant. You're not scanning raw events — you're merging pre-aggregated states.

Postgres can't do this incrementally. Materialized views in PG refresh wholesale. It's the single biggest architectural difference for dashboard workloads.

The Bit That Bites Everyone: JOINs

ClickHouse JOINs are worse than Postgres JOINs. Full stop.

ClickHouse joins run in memory, streaming one side of the join. If you're joining a 500M row fact table to a 100M row dimension, ClickHouse will ask for a lot of RAM. Postgres with hash joins, indexes, and years of optimizer work handles this more gracefully.

Rule I follow: aggregate in ClickHouse, join in Postgres. Or denormalize at write time so the join never happens.

-- Bad in ClickHouse: join on two big tables
SELECT u.plan, count()
FROM events_large e
JOIN users_large u ON u.user_id = e.user_id
GROUP BY u.plan;

-- Good: denormalize plan into the events table upstream
SELECT plan, count()
FROM events_denormalized
GROUP BY plan;

Denormalization feels wrong to OLTP-trained engineers. In OLAP it's the default. Storage is cheap, joins are expensive.

Cost Comparison in Real Numbers

Rough numbers from a client's 2026 setup — 8TB of events, 500M rows/day ingest, dashboards with 50 concurrent users.

Postgres (RDS, db.r6g.4xlarge + read replica): ~$2,400/month, and it struggled. We added partitioning, tuned work_mem, and still the biggest dashboards timed out at 30s.

ClickHouse (Cloud, similar compute tier): ~$1,900/month after compression, dashboards averaged 200ms.

The ClickHouse bill is cheaper AND faster. But the migration cost was 6 weeks of engineering, plus ongoing CDC pipeline maintenance.

Break-even was around month 5.

If your data is 500GB instead of 8TB, the math flips. Postgres on a $400/month instance is fine, and you don't pay for pipeline complexity.

FAQ

Is ClickHouse always faster than Postgres for GROUP BY?
No. Under ~10 million rows with good indexing, Postgres is often within 2-3x and sometimes faster when the query mixes transactional and analytical operations. ClickHouse's advantage grows with data size and column sparsity.

Can I use Postgres JSONB as my analytics store?
For small-to-medium datasets, yes. For anything above ~50 million JSON documents being aggregated, ClickHouse's subcolumn approach wins decisively on both speed and storage cost.

Does ClickHouse support UPDATE and DELETE?
Yes, as mutations. They're asynchronous, rewrite partitions on disk, and aren't designed for high-frequency single-row changes. Postgres wins here unambiguously.

How do I keep Postgres and ClickHouse in sync?
CDC tools like Debezium, PeerDB, or ClickHouse's Postgres integration read the WAL and stream changes. Expect a few seconds of lag. Plan your dashboards for eventual consistency.

What's the ClickHouse vs PostgreSQL 2026 performance gap on real workloads?
On my July 2026 benchmark with 500M rows, ClickHouse returned GROUP BY results in 0.41s vs Postgres's 34.2s. On 5M rows, the gap was 90ms vs 400ms. Real workloads land somewhere in between, weighted heavily by row count.

Should I start with Postgres and migrate later?
Yes. Start with Postgres. Add ClickHouse the moment dashboards cross 2-3 seconds on datasets larger than 50M rows. Don't pre-optimize.

Does materialized view support change the calculus?
Enormously. ClickHouse incremental materialized views make dashboards sub-second at any scale. Postgres materialized views refresh wholesale, which caps their usefulness on large tables.

What about TimescaleDB or DuckDB?
TimescaleDB is Postgres with time-series extensions. Great for time-bucketed queries under ~100M rows. DuckDB is fantastic for embedded analytics but not for concurrent multi-user serving. Neither replaces ClickHouse at scale.

My Recommendation

If you're asking "clickhouse vs postgresql for group by performance" and your data is under 10 million rows — use Postgres. Tune work_mem, add the right indexes, move on with your life.

If your data is 10-100 million rows and dashboards are getting slow — try Postgres first with partitioning and materialized views. You might not need ClickHouse yet.

If your data is above 100 million rows, or you have semi-structured event data you're aggregating, or your dashboards time out — install ClickHouse today. The 80x performance gap is real and it changes what you can build.

If you need JSON-heavy aggregation on dynamic schemas — Postgres's JSONB is more forgiving, ClickHouse's JSON type is faster but demands schema discipline.

Run both when you can. Postgres as system of record, ClickHouse as aggregation layer. That's the pattern I deploy on almost every system SIVARO builds now, and it's held up across clients processing from 1M to 50B events a month.

Don't pick a database based on benchmarks. Pick it based on which queries you spend your day making faster. That's the only test that matters.

Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.