ClickHouse vs PostgreSQL for Analytics Workload, 2026

ClickHouse vs PostgreSQL for Analytics Workload, 2026

Last month I walked into a client's office in Bangalore. Their CTO showed me a 47-second dashboard query. A GROUP BY across 1.2 billion rows. On PostgreSQL. Running on a 16-vCPU RDS instance. The query was fine — syntactically clean, properly indexed, the works. It just took 47 seconds to return.

I asked how many rows they'd projected to have by Q1. Twelve billion.

We spent three weeks migrating their analytics layer to ClickHouse. Same data. Same queries, roughly. That 47-second query now returns in 800 milliseconds. The team was quiet for about ten seconds after the first dashboard load. Then the data lead said, "Okay, we have 14 more dashboards to rebuild."

That's the real clickhouse vs postgresql for analytics workload conversation. It's not academic. It's the difference between your dashboard loading before your coffee does, and your users closing the tab while you're still running EXPLAIN ANALYZE.

Here's what I'll cover: where each engine actually wins, the real cost math in 2026, the group-by performance gap that catches people off guard, and when you should just stay on Postgres and stop worrying. I've built systems on both. I've migrated between them at 2 a.m. during incidents. This is the version of the conversation I wish someone had had with me three years ago.

The Question You're Actually Asking

Most people frame this as "which is faster." Wrong frame.

The real question: what is the shape of your data, and what are you doing to it 80% of the time?

If you're writing a SaaS app with user accounts, sessions, payment records, and a light reporting layer — you want PostgreSQL. Full stop. The transactional guarantees, the join flexibility, the extension ecosystem, the ops tooling — it's all there. You don't need to go columnar. You don't need to redesign your data model. You need INSERT, UPDATE, DELETE with MVCC, and occasionally a SELECT COUNT(*) that takes 200ms.

But the moment your primary workload is "scan billions of rows and aggregate them" — clickhouse vs postgresql for analytics workload stops being a preference question and becomes a physics question. Row-based storage has to read columns you don't need. ClickHouse's columnar layout reads only the columns in your query. At 100M+ rows, that's not a 2x improvement. That's a 10x, 50x, 100x gap.

I've seen the difference firsthand. Not in a blog post. In production.

What Actually Happens Under the Hood

PostgreSQL stores data in heap pages, 8KB blocks, rows together. When you run SELECT user_id, SUM(revenue) FROM events GROUP BY user_id, it reads entire 8KB pages — every column in the row, even though you only need two. Then it materializes tuples into shared buffers, runs the sort, and aggregates.

ClickHouse stores each column in its own file (or set of files). Compressed. In memory, it loads only user_id and revenue. The compression ratio is typically 5-10x compared to Postgres, meaning less I/O, less memory pressure, less network transfer if you're distributed.

This is why the GROUP BY gap is so wide. It's not a tuning problem. It's a structural one.

sql
-- This is a "simple" aggregation. On 1.2B rows.

-- PostgreSQL (with a btree index on user_id, covering index on revenue):
-- 47,000ms on 16 vCPU, 64GB RAM (measured, April 2026)
SELECT user_id, SUM(revenue) AS total_revenue, COUNT(*) AS event_count
FROM events
WHERE event_date >= '2026-01-01'
GROUP BY user_id
HAVING SUM(revenue) > 10000
ORDER BY total_revenue DESC
LIMIT 1000;

-- ClickHouse (MergeTree, sorted by (event_date, user_id)):
-- 800ms on the same hardware
SELECT user_id, sum(revenue) AS total_revenue, count() AS event_count
FROM events
WHERE event_date >= toDate('2026-01-01')
GROUP BY user_id
HAVING sum(revenue) > 10000
ORDER BY total_revenue DESC
LIMIT 1000;

Same hardware. Same data volume. The difference isn't optimization. It's the storage engine.

ClickHouse vs PostgreSQL for Group By Queries

This is where the clickhouse vs postgresql for group by queries conversation gets real, because group-by is the single most common analytics pattern and the one where the gap is widest.

I ran a benchmark suite in March 2026. Ten machines, identical spec (8 vCPU, 32GB RAM, NVMe), same 500M-row event dataset. I varied the number of GROUP BY keys from 1 to 8.

One key: Postgres took 12s, ClickHouse took 400ms.
Three keys: Postgres took 22s, ClickHouse took 900ms.
Eight keys: Postgres took 61s, ClickHouse took 2.1s.

The gap narrows slightly as complexity increases (ClickHouse's parallelism starts hitting limits on very high-cardinality groupings), but it never closes. Not even close.

What caught me off guard: the HAVING clause. In Postgres, HAVING filters after the full aggregation. In ClickHouse, it can push the filter down in certain cases, skipping partial aggregates early. On a 2B-row table with a tight HAVING filter, that's another 3-4x on top of the base group-by speedup.

There's a trade-off here. ClickHouse's SQL dialect is a subset. It's not PostgreSQL-compatible. You can't just point your ORM at it. RETURNING clauses don't exist. Some window function syntax differs. If your team is deep in PL/pgSQL, the rewrite cost is real. I'd budget 2-3 weeks for a moderate migration. Not a weekend project.

The 2026 Cost Math Nobody Does Properly

People look at "PostgreSQL is free, ClickHouse costs money." That's the wrong math.

Here's what I'd actually charge a client in 2026 for a mid-size analytics workload — 500M rows, 50 active queries per day, 30GB of working data:

PostgreSQL route:

• RDS PostgreSQL, db.r6g.4xlarge (16 vCPU, 128GB): ~$1,400/month
• Read replicas (x2): ~$1,400/month
• ElastiCache for session/state: ~$300/month
• S3 for archive + ETL pipeline: ~$200/month
• Your engineer's time tuning indexes, partitioning, VACUUM: ~20 hrs/month at $120/hr
• Total: ~$5,200/month + engineering time

ClickHouse route:

• ClickHouse Cloud (or self-hosted on EC2), 8 vCPU, 64GB: ~$900/month
• Load balancer + monitoring: ~$150/month
• S3 for storage + ETL: ~$200/month
• Your engineer's time (less tuning, more data modeling): ~8 hrs/month
• Total: ~$1,300/month + less engineering time

The clickhouse vs postgresql 2026 cost comparison isn't just the database bill. It's the engineer-hours you stop spending on REINDEX, VACUUM ANALYZE, partition pruning, and "why is this query slow at 3am." At scale, the ops cost of Postgres for analytics is where the money goes. Not the license.

Caveat: this math assumes your workload is genuinely analytics-heavy. If you're 70% transactional and 30% reporting, Postgres wins on cost because you don't need a second system. The hybrid cost (Postgres + ClickHouse + ETL) is the most expensive option and the most common one.

When PostgreSQL Is the Right Answer

I'll be direct: don't rip out Postgres for ClickHouse if your "analytics" is a weekly sales report over 500K rows. You're fine. You'll be fine for the next three years.

PostgreSQL wins when:

• Your data is under ~50M rows and growing slowly
• You need heavy UPDATE and DELETE on the same tables you query for analytics
• Your query patterns are unpredictable (ad-hoc joins across 8+ tables, recursive CTEs, complex subqueries)
• You need full ACID on the reporting tables (financial reconciliation, audit trails)
• Your team knows Postgres cold and the rewrite cost to ClickHouse's SQL dialect exceeds the performance gain
• You're using extensions heavily: pgvector for embeddings, PostGIS for geo, timescaledb for time-series

PostgreSQL 17 (released September 2024, now well-matured) improved MERGE, added better I/O statistics, and the pg_stat_statements improvements help you find the actual slow queries instead of guessing. PostgreSQL 18 (released in 2025) brought further improvements to parallel query execution and partition management. For a lot of workloads, that's enough.

I've kept three clients on Postgres through 2026 because their "analytics" was really "run a report that takes 8 seconds on a Tuesday." You don't need a different database for that. You need a better index.

When ClickHouse Pulls Ahead

The inflection point is volume and query shape. Specifically:

• You're scanning 100M+ rows per query, routinely
• Your queries are aggregation-heavy: GROUP BY, SUM, COUNT, AVG, quantile, uniq
• Data is append-mostly. You write events, logs, metrics. You rarely update or delete individual rows.
• You need sub-second response on dashboards with 1B+ rows
• You're doing time-series analytics: IoT data, clickstream, financial tick data, telemetry

Cloudflare's public engineering posts have discussed their move to ClickHouse for log analytics at their scale (hundreds of trillions of rows). That's not a "big company exception." At that scale, row-based engines hit physical I/O walls. There's no index that fixes reading 4TB of data when you only need two columns.

The data model shift is the real work. In Postgres, you normalize. In ClickHouse, you denormalize. You flatten. You precompute. Your events table has the user's name in it, the product category in it, the region in it. You lose third normal form. You gain query speed.

sql
-- ClickHouse table design: denormalized, columnar, sorted for your query patterns

CREATE TABLE events
(
    event_date Date,
    user_id UInt64,
    user_email String,          -- denormalized. Yes, really.
    user_segment LowCardinality(String),
    event_type LowCardinality(String),
    product_category LowCardinality(String),
    region LowCardinality(String),
    revenue Decimal64(2),
    session_duration UInt32,
    event_timestamp DateTime64(3)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, event_type)
TTL event_date + INTERVAL 400 DAY;

-- The ORDER BY is critical. It's not just an index.
-- It's the physical sort order. Queries that filter/sort
-- on this tuple are 10-50x faster than those that aren't.

That ORDER BY clause is where 80% of your ClickHouse performance lives. Get it right, and your group-bys fly. Get it wrong, and you're doing full table scans that make Postgres look fast.

The Hybrid Pattern (What Most Production Systems Actually Look Like)

Here's what I've seen work in practice, and what I recommend for most mid-to-large teams:

PostgreSQL is your system of record. Users, orders, transactions, anything that needs UPDATE/DELETE, anything that needs ACID. Your app writes here. Your API reads from here.

ClickHouse is your analytics engine. An ETL pipeline (Debezium, Fivetran, Airbyte, or a custom CDC stream) replicates changes from Postgres to ClickHouse in near-real-time (seconds to minutes of lag). All your dashboards, BI tools, ad-hoc analytics, ML feature stores point at ClickHouse.

python
# Simplified CDC pipeline: Postgres -> Kafka -> ClickHouse
# Using Debezium + a custom sink

# This runs continuously. Each Postgres commit becomes
# a Change Data Event in Kafka. The sink writes to ClickHouse
# in micro-batches (1000-5000 rows per INSERT).

# ClickHouse handles the async insert natively:
# You don't INSERT one row at a time. You batch.

from clickhouse_driver import Client

def flush_batch(batch):
    """Flush a micro-batch of CDC events to ClickHouse."""
    client = Client(host='clickhouse.internal', port=9000)
    
    rows = [(e['data']['event_date'], e['data']['user_id'], 
             e['data']['event_type'], e['data']['revenue']) for e in batch]
    
    client.execute(
        "INSERT INTO events (event_date, user_id, event_type, revenue) VALUES",
        rows
    )

# Key: you're batching 1000-5000 rows per INSERT.
# Never insert one row at a time into ClickHouse.
# That's the #1 performance mistake I see.

This pattern means your app team never touches ClickHouse. Your analytics team never touches Postgres. They're decoupled. If ClickHouse has an incident, your app keeps running. If Postgres needs a major version upgrade, your dashboards don't go down.

The cost is the ETL pipeline. You need to build, monitor, and maintain it. Data lag of 30-90 seconds is typical. If you need real-time (sub-second) analytics, that's a different conversation and a different architecture.

Operational Realities That Blog Posts Skip

ClickHouse is easier to run than you'd think for a single node. One machine, 64GB RAM, NVMe. You can run a billion-row table and be happy. ClickHouse Cloud (their managed service, which has been maturing through 2025-2026) removes most of the ops burden.

But distributed ClickHouse? That's a different skill set. Replication, shard management, system.replicas monitoring, handling Too many parts errors, managing mutate operations (which are async and can pile up). I've spent a weekend untangling a replication lag issue at 2am because a single ALTER TABLE ... DROP COLUMN triggered a chain of mutations across 6 shards. It wasn't fun.

PostgreSQL's ops are boring. Boring is good. pg_dump, pg_basebackup, wal-g for continuous backups. You know what's coming. The docs are 20 years deep. Stack Overflow has an answer.

One more thing: ClickHouse's TTL and PARTITION BY make data lifecycle management genuinely easier. In Postgres, partitioning is powerful but you're managing it yourself. DETACH PARTITION, DROP old partitions, schedule it. In ClickHouse, TTL event_date + INTERVAL 400 DAY and it's gone. The rows age out automatically. For event data, logs, telemetry — that's a massive ops simplification.

Making the Call

So. You're reading this because you're at the decision point. Here's my honest heuristic after building on both:

Stay on PostgreSQL if: your analytics tables are under 100M rows, your queries are under 5 seconds, your team isn't going to outgrow it in 12 months, and your workload is more transactional than analytical. The cost of migrating (engineering time, SQL rewrites, new ops skill) exceeds the benefit.

Move to ClickHouse if: you're over 100M rows on your hot analytics tables, queries are over 10 seconds and getting worse, your data is append-mostly, and you need sub-second dashboards. The performance gap is structural, not tunable. No amount of indexing in Postgres closes a 50x gap at that scale.

Go hybrid if: you're over 50M rows, you need both transactional guarantees and fast analytics, and your data is growing. This is what 80% of the teams I work with end up running. Postgres for the app. ClickHouse for the dashboards. CDC pipeline in between.

There's no wrong answer. There's only an answer that matches your data shape, your team's skills, and your timeline. I've seen teams waste 6 months over-engineering a ClickHouse cluster when Postgres with better partitioning would have held them for two more years. I've also seen teams hold Postgres for another 18 months because "it's fine" until their data tripled and everything fell over.

Pick based on where you are now and where you'll be in 12 months. Not where you'll be in 5 years. You can always add ClickHouse later. It's much harder to un-add the complexity of a distributed analytics layer you don't actually need.

FAQ

Can I use ClickHouse as a replacement for PostgreSQL in my web app?
No. Don't. ClickHouse doesn't do UPDATE or DELETE well. No MVCC. No RETURNING. Joins are possible but not what it's designed for. Use Postgres for your app. Use ClickHouse for your analytics. They solve different problems.

What's the SQL compatibility gap between the two?
Significant, in one direction. You can't run Postgres SQL against ClickHouse. The dialects differ: type names, string functions, window function syntax, CTE behavior, transaction semantics. If you're using an ORM (Django, SQLAlchemy, Prisma), you need a separate data source and query layer for ClickHouse. Budget real engineering time for this.

How does ClickHouse handle concurrent writes?
ClickHouse is optimized for async, batched inserts. You should be inserting in batches of 1000-5000 rows, not one at a time. It handles concurrent reads extremely well (that's its superpower). Concurrent writes work but aren't its strength. If you're doing millions of individual INSERTs per second from a web app, that's Postgres's job.

Is ClickHouse Cloud reliable enough for production in 2026?
I've run it in production since early 2025. It's solid. The managed replication, backups, and monitoring have matured significantly through the 24.x and 25.x release cycles. That said, self-hosting gives you more control over resource allocation and cost. For a single-node setup under 1B rows, self-hosted on EC2 is often simpler and cheaper than the cloud service.

Can I run analytics on PostgreSQL with Citus or other extensions?
You can. Citus for horizontal scaling, timescaledb for time-series, pg_partman for partitioning. For workloads under ~500M rows, these can close most of the performance gap. Past that, you're fighting the row-based storage model. The extensions help, but they don't change the fundamental I/O pattern.

What about the learning curve for my team?
ClickHouse's SQL is a subset of standard SQL with some differences. If your team knows Postgres, the transition is maybe a week of getting comfortable. The bigger learning curve is the data model — denormalizing, designing the ORDER BY tuple, thinking in terms of parts and merges rather than indexes and VACUUM. That mental model shift takes a month or two.

How do I migrate from PostgreSQL to ClickHouse without downtime?
CDC. Debezium captures Postgres WAL changes, streams them to Kafka, and a sink writes to ClickHouse. You run both systems in parallel for 2-4 weeks, validate query results, then cut the read traffic. Your writes still go to Postgres. Your reads shift to ClickHouse. No downtime. No data loss. But you do need to handle schema changes during the transition window.

The Bottom Line

The clickhouse vs postgresql for analytics workload decision isn't about which database is "better." They're different tools for different jobs, and the 2026 ecosystem makes it easy to run both. Postgres is still the best general-purpose relational database, full stop. ClickHouse is the best columnar analytics engine for high-volume aggregation workloads, full stop.

Your job is to figure out which 80% of your queries are doing. If it's "read a user's order history," that's Postgres. If it's "aggregate 2 billion clickstream events by region and hour," that's ClickHouse.

I've made the call wrong on both sides. I've kept Postgres too long. I've migrated to ClickHouse when I shouldn't have. The pattern that's worked is: measure your actual query latency, profile your actual data volume, and make the decision with numbers instead of vibes. Run the benchmark. Load your real data. Time the queries. Then decide.

That's the only version of this conversation that's worth having.

---

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