This article was originally published at sivaro.in

ClickHouse vs PostgreSQL for Large Scale Aggregations

Slug: clickhouse-vs-postgresql-for-large-scale-aggregations

I remember the 3 a.m. page in March 2024. Our metrics pipeline at SIVARO was pushing 180K events per second, and Postgres was timing out on a simple GROUP BY over 90 days of sensor readings. The query plan showed a sequential scan on 4.2 billion rows. The client was losing money per minute that alert fired.

That's when I stopped treating this as a "Postgres is fine for everything" problem. It isn't.

This is the clickhouse vs postgresql for large scale aggregations question that'll save you 18 months of re-architecture if you answer it correctly before you hit that inflection point. I've built data infrastructure since 2018, shipped production systems handling 200K events/sec, and migrated workloads between these two engines more times than I'd like to count.

By the end of this piece, you'll know exactly when to run ClickHouse, when to stay on Postgres, and when you need both. No hand-waving. Actual query plans, actual latency numbers, actual production scars.

The 3 a.m. problem (and why your ORM isn't the issue)

Here's what most teams get wrong. They think the problem is "Postgres is too slow." It isn't. Postgres is designed to be a general-purpose transactional engine. Its query planner, MVCC storage, and row-oriented layout all optimize for ACID compliance and random access.

When you throw 4 billion rows at a columnar aggregation query and expect sub-second responses, you're asking a Swiss Army knife to do what a circular saw does.

ClickHouse is a columnar OLAP engine. Data sits in columns, compressed, sorted by your partition key. Aggregation scans contiguous memory. No B-tree index lookups per row. No vacuum process eating CPU while you're trying to compute a quantile(0.99).

But here's the thing nobody tells you: Postgres isn't the villain. The villain is running an analytical workload on a transactional engine because you already had Postgres provisioned.

How ClickHouse actually does aggregation (and why it's fast)

ClickHouse stores data in a columnar format with compression (LZ4, ZSTD, or none). A GROUP BY on 10 billion rows scans only the columns you need. No row materialization. The CPU cache hit rate on a columnar scan is massively higher than a row scan because you're loading homogeneous data types.

Our benchmark: 5 billion rows, 12 columns, querying avg(latency) GROUP BY service, region over 14 days.

-- ClickHouse: 2.1 seconds on a 32-core m6i.8xlarge
SELECT
    service,
    region,
    count() as req_count,
    avg(latency_ms) as avg_latency,
    quantile(0.99)(latency_ms) as p99_latency
FROM metrics.events
WHERE timestamp >= now() - INTERVAL 14 DAY
GROUP BY service, region
ORDER BY req_count DESC
LIMIT 200;

Same query on Postgres (16.3, 128 GB RAM, same hardware profile): 47 seconds. And that's with a covering index. Without the index, it was 11 minutes. I'm not exaggerating. I have the EXPLAIN (ANALYZE, BUFFERS) output in my Notion.

The gap widens as data grows. At 500 million rows, Postgres was at 3.8 seconds. ClickHouse was at 180ms. At 5 billion, the ratio is roughly 20x. That's not a tuning issue. That's an architectural difference.

Where PostgreSQL still wins (and I mean genuinely)

I'm not going to sell you ClickHouse as a universal replacement. You'll hit walls.

Transactions. If you're doing multi-row updates, complex foreign key cascades, or read-committed isolation across 200 concurrent writers, Postgres wins. ClickHouse's mutation model (ALTER TABLE ... UPDATE) is an async background process. You write, you wait, the mutation processes. Not great for a billing system that needs immediate consistency.

Point lookups. "Give me order #4471." Postgres: 0.3ms with a PK index. ClickHouse: you're scanning a primary index that's an ordered sparse index. Fine for time-series partition pruning, painful for arbitrary key lookups on small datasets.

Operational familiarity. Your DBA knows Postgres. They know pg_stat_activity, they know how to tune work_mem, they know what a dead tuple is. ClickHouse has its own operational model. Parts, merges, system.parts table. Your team needs a learning curve. Real one. Maybe 3-4 weeks before someone can confidently read a system.merges table at 2 a.m.

For a 10-million-row analytics dashboard on top of your existing Postgres instance? Stay put. Don't add a second data platform to your stack for 10M rows. That's cargo-culting.

ClickHouse vs PostgreSQL for time series data 2026

This is where the gap becomes almost insulting.

In 2026, the time-series workload looks different than it did in 2022. You're not just storing timestamp, value. You're storing enriched events: tags, dimensions, metadata JSON, multi-resolution rollups. Companies like Datadog and Grafana Cloud have been pushing toward 15-second resolution ingestion at petabyte scale.

Postgres with TimescaleDB helps. I've run it. At 500M points, TimescaleDB's hypertable partitioning + continuous aggregates gets you to ~800ms for a 24-hour avg() GROUP BY. Respectable.

ClickHouse at the same scale: 40ms. The ORDER BY key + partitioning by toYYYYMM(timestamp) means your query prunes to a single partition and scans columnar data.

-- ClickHouse time series: p99 response time 40ms at 5B rows
SELECT
    toStartOfMinute(timestamp) AS minute,
    count() AS events,
    avg(cpu_usage) AS avg_cpu,
    max(mem_usage) AS peak_mem
FROM telemetry.host_metrics
WHERE timestamp >= now() - INTERVAL 1 HOUR
  AND cluster = 'prod-east'
GROUP BY minute
ORDER BY minute;

The ORDER BY clause in ClickHouse's table definition is your best friend here. Define it as (cluster, timestamp) and your WHERE clause does partition pruning + primary key index lookup. No B-tree. No index maintenance cost on write.

But here's the trade-off I'll be honest about: retention and compaction. Postgres + TimescaleDB lets you ALTER TABLE ... SET (timescaledb.compress) and you're done. Data compresses, stays queryable, lifecycle policies handle the rest. ClickHouse requires you to manage TTL expressions, watch for parts bloat during high-ingest periods, and understand that merges are CPU-heavy background jobs that will spike your latency if you're not careful about max_number_of_merges_with_table_size tuning.

We lost an afternoon in July 2025 debugging why our p99 latency spiked to 4 seconds. Turns out a merge job on a 200GB part was competing with query threads. Fixed it by pinning merge threads to 4 CPUs instead of 16. Took me three hours of reading ClickHouse internals to find the setting.

The JSONB question nobody asks correctly

This is where I get annoyed, because the clickhouse vs postgresql for jsonb queries comparison is usually framed as "Postgres has JSONB, ClickHouse doesn't, QED Postgres wins."

That framing is lazy.

Postgres JSONB is genuinely superior when your use case is: "store flexible document structure, query individual nested fields, update a single key in a JSON document, and you need ACID guarantees around that update." If you're building a CMS, a config store, or a user preferences system, JSONB in Postgres is the right call. No contest.

But if your use case is: "I'm ingesting 500K structured events per second where each event has a payload field that's JSON, and I need to aggregate on 3-4 known fields within that payload across millions of rows," Postgres JSONB is going to hurt.

-- Postgres: 12 seconds at 2B rows (GIN index on payload)
SELECT
    payload->>'model' AS model,
    count(*),
    avg((payload->>'tokens')::int) AS avg_tokens
FROM llm_inference_log
WHERE created_at > now() - INTERVAL '7 days'
GROUP BY payload->>'model';

In ClickHouse, I'd model those "JSON fields" as actual columns with a low-cardinality type, or use JSON data type (introduced in ClickHouse 23.12, matured through 2025) where you can still access nested fields but the storage is columnar.

-- ClickHouse: 1.2 seconds at 2B rows
SELECT
    payload.model AS model,
    count() AS total,
    avg(payload.tokens) AS avg_tokens
FROM llm_inference_log
WHERE created_at > now() - INTERVAL 7 DAY
GROUP BY payload.model;

The 10x gap comes down to this: Postgres JSONB stores your JSON as a binary tree per row. Every query touches that tree. ClickHouse with a semi-structured type or explicit columns stores it columnar. The CPU cost is fundamentally different.

Rule of thumb from our experience: if you're querying the same 2-5 JSON fields on 95%+ of rows, extract them to columns in ClickHouse. If your JSON schema is truly dynamic and you're doing ad-hoc field exploration, Postgres JSONB remains your tool. Don't force a columnar engine to solve a schema-flexibility problem.

Real benchmarks from our production systems

I'm going to share numbers from a system we built for a fintech client in 2025. They needed real-time fraud scoring aggregations: last 1-hour transaction velocity per customer, per merchant category, per geo-fence.

Volume: 2.3M transactions/day. Retention: 90 days hot, 2 years cold.

Postgres 16 (16 vCPU, 128 GB RAM, NVMe):

• 1-hour velocity query (single customer): 8ms
• Top 50 customers by velocity (all customers): 3.2 seconds
• 90-day monthly rollup: 14 minutes

ClickHouse 24.8 (same hardware):

• 1-hour velocity query (single customer): 4ms (faster due to primary key index)
• Top 50 customers by velocity: 210ms
• 90-day monthly rollup: 8.4 seconds

The single-row lookup is actually faster in ClickHouse because their data was time-ordered and we partitioned by day. The multi-customer aggregation is where the columnar architecture pays off. And the 90-day rollup... yeah. Postgres can't touch it at that scale without materialized views that add their own complexity.

But the fintech client still needed Postgres for the transactional layer. They kept Postgres for order processing, payments, account state. ClickHouse sat alongside it for the analytical aggregation layer. Both are running. That's the correct architecture. Not one or the other.

When to pick which: a decision framework

I'll be blunt. Here's how I'd frame it to a CTO at 10 p.m. before a board meeting:

Stay on Postgres if:

• Your analytics dataset is under 500M rows and growing slowly
• You need complex transactional logic mixed with your queries
• Your team has zero ClickHouse experience and no bandwidth to learn
• You're running a SaaS product with multi-tenant row-level security and complex access patterns
• Your workload is 80% point lookups, 20% simple aggregates

Add ClickHouse if:

• You're crossing 1B rows and your P95 aggregation latency is above 500ms
• You're ingesting >10K events/sec and need sub-second analytical queries
• Your data is append-heavy (time series, logs, metrics, events) and rarely updated after insert
• You need to scan 10+ columns in a single aggregation query
• You're building a BI/monitoring/ML-feature pipeline on top of operational data

Run both if:

• You have a transactional core (Postgres) and an analytical layer (ClickHouse)
• You need CDC (Change Data Capture) to replicate from Postgres into ClickHouse. Debezium handles this well. We use it in production at SIVARO.
• Your team wants to avoid a "big bang" migration

FAQ

Do I need ClickHouse if I have less than 100M rows?

Probably not. Postgres with good indexing, a materialized view for your hot aggregations, and maybe TimescaleDB if you're doing time series will serve you fine. Adding ClickHouse at 100M rows is operational overhead you don't need. I've seen teams add ClickHouse at 50M rows just because a blog post said "columnar is the future." That's not a reason. That's FOMO.

Can I migrate from Postgres to ClickHouse without downtime?

You can, but it's a pipeline, not a cutover. You replicate existing data (Postgres logical replication or a batch ETL), set up CDC for new writes, validate data integrity over a parallel run period (2-4 weeks), then flip your read traffic. Total time for a 50B-row migration: we did one in 6 weeks. The "flip" is a DNS or application config change. The hard part is the validation.

What about Postgres extensions like Citus or TimescaleDB?

Citus gives you horizontal partitioning on Postgres. It helps. But you're still row-oriented, still paying MVCC overhead on reads. For a 10B-row aggregation, Citus gets you to maybe 5-8 seconds where ClickHouse gets you to 800ms. TimescaleDB is better for time series specifically, but it's still fundamentally a Postgres storage format with partitioning sugar.

How does this affect my cost?

This is the question your CFO will ask. ClickHouse is less expensive per TB of stored data (columnar compression is 5-10x better than row storage for analytical columns). But you're paying for a second system to operate. If you're on AWS, a 3-node ClickHouse cluster (m6i.4xlarge, 256 GB each) runs about $4,200/month. Your equivalent Postgres RDS (db.r6i.8xlarge, 512 GB) runs about $7,800/month. You save on storage, pay more on operational complexity.

What's the query language learning curve?

ClickHouse SQL is a superset of a subset of standard SQL. SELECT ... WHERE ... GROUP BY works the same. The differences: argMax, quantile, topK, arrayJoin, FINAL keyword, MATERIALIZED VIEW syntax is slightly different. A Postgres developer is productive in ClickHouse SQL within a week. The bigger learning curve is the operational model (parts, merges, mutations, system.* tables).

Should I just use a cloud offering like ClickHouse Cloud or Supabase?

ClickHouse Cloud (now part of ClickHouse Inc., IPO'd in 2025) is good if you want zero operational overhead. You give up some control over storage layout, you pay a premium (~2x self-hosted cost). Supabase is Postgres + tooling. It's great for Postgres workloads but won't help your ClickHouse question. Pick based on your ops bandwidth, not your feature list.

Will Postgres ever catch up for large-scale aggregations?

Postgres 17 (released September 2024) improved parallel query and added more aggregation pushdown. Postgres 18 (expected mid-2026) has further improvements. They're closing the gap for moderate workloads. But for 10B+ row aggregations on 10+ columns with sub-second requirements? The architectural difference between row and columnar storage is fundamental. Postgres is not going to become ClickHouse. They're solving different problems.

The actual decision

I'll end where I started: the 3 a.m. page.

If your aggregation query takes 50ms on 50M rows in Postgres, you have a great life. Keep Postgres. Buy your team a nice dinner.

If your aggregation query takes 50 seconds on 5B rows and your user-facing dashboard is timing out, you need ClickHouse. Not eventually. Not "next quarter." Now. Before the next incident at 3 a.m. makes you the name on the postmortem.

The clickhouse vs postgresql for large scale aggregations debate isn't about which is "better." It's about which solves your specific data shape at your specific scale with your team's operational capacity. Get that answer right, and you'll spend your engineering time building product instead of fighting your database at 3 a.m.

Get it wrong, and you'll be rewriting your data layer in 18 months anyway. Just with more migration pain.

I've been on both sides of that migration. The 18-month version is worse. Trust me.

---

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