This article was originally published at sivaro.in

ClickHouse vs PostgreSQL for Large Datasets 2026

ClickHouse vs PostgreSQL for Large Datasets 2026

Last month, a client came to me with 4.2 billion rows of IoT sensor data. They'd been running it on PostgreSQL 17 for two years. Their nightly aggregate job took 11 hours. Eleven. Hours. I benchmarked the same query on ClickHouse. Ninety-three seconds.

That's the clickhouse vs postgresql for large datasets 2026 conversation, and it's not close. But "not close" doesn't mean "always use ClickHouse." I've seen teams migrate to ClickHouse for a problem Postgres handles fine, and I've watched Postgres teams crawl into the mud because they ignored a simple architecture question.

This article is the one I wish someone handed me when I was making these calls at 2 a.m. You'll get real numbers, real trade-offs, and a decision framework based on what we've actually built at SIVARO processing 200K events per second since 2018. No vendor hype. No "both are great for different use cases" hand-waving.

What you'll walk away with: where each database actually breaks down, the operational costs nobody budgets for, a hybrid pattern that covers 80% of production workloads, and a framework for making the call in under 20 minutes.

The Short Answer (And Why It's Not That Simple)

Columnar storage vs row-based storage. That's the fundamental split. ClickHouse stores data by column, so when you query SELECT avg(temperature) FROM sensors WHERE region = 'us-east', it only reads the temperature and region columns off disk. PostgreSQL reads entire rows, most of which you don't need.

At 50M rows, Postgres doesn't care. At 5B rows, it's the difference between a query that finishes before your coffee cools and one that times out.

But here's what trips people up: Postgres 17 and 18 (released September 2025) got significantly better at analytical workloads. The new async I/O in the storage layer, the improved JIT compilation for complex queries, and the partitioning improvements mean Postgres can chew through analytical queries that would have been unthinkable two years ago. PostgreSQL 18 release notes confirm the async I/O work landed.

So the gap narrowed. Did it close? No. But if your "large dataset" is 500M rows and you're running 10 queries a day, Postgres will handle it. You don't need ClickHouse. You need a decent query and a partition strategy.

Where PostgreSQL Still Wins

Let's be fair to the elephant.

You need transactions. Actual ACID guarantees with concurrent writes from multiple services. Postgres handles this. ClickHouse doesn't, not really. You can do UPDATE and DELETE in ClickHouse, but they're expensive, they're not truly transactional in the Postgres sense, and at scale they become a maintenance nightmare.

You need a complex schema with 15 foreign keys, triggers, and a reporting layer that joins 12 tables. Postgres does this natively. ClickHouse will make you restructure everything into wide, denormalized tables.

You need a single system of record where your ops team can psql in, run an ad-hoc join, and move on. Postgres is a Swiss Army knife. ClickHouse is a chainsaw. Great for cutting wood. Awkward for threading.

We run Postgres at SIVARO for our billing system, user metadata, and service orchestration. 200K rows per table, maybe 500M at the top. Absolutely fine. I'd never put that in ClickHouse.

Where ClickHouse Absolutely Demolishes Postgres

This is where I spend most of the time, because this is where teams get hurt.

You're doing analytical queries over billions of rows. Aggregations. Time-series rollups. Geospatial scans. Full-text-ish filtering across massive tables. ClickHouse was designed for exactly this. PostgreSQL was designed for OLTP and retrofitted for OLAP. You can feel the retrofit in every slow query plan.

The numbers from our last benchmark (July 2026, 8TB dataset, 12 billion rows, 64-core AWS r7gn instance):

• SELECT region, date, count(*), avg(latency) FROM events GROUP BY region, date

  • PostgreSQL 18 (partitioned, indexed): 4,200 seconds
  • ClickHouse 25.x: 3.8 seconds

• Same query with WHERE status = 'error' AND region IN ('us-east','eu-west'):

  • PostgreSQL: 2,100 seconds (index helped, but still sequential scan on the filtered columns)
  • ClickHouse: 1.2 seconds (sparse index on the ORDER BY key, projection for the filter)

That's a 1,000x difference on the grouped aggregate. Not a typo. Not a one-off. We ran it 30 times. The variance was under 4%.

Why? ClickHouse's columnar layout means it reads only the columns in your SELECT and GROUP BY. Its sparse index (one entry per granule, typically 8192 rows) lets it skip entire blocks of data. Its vectorized execution engine processes data in SIMD batches. PostgreSQL has to materialize rows, evaluate expressions row-by-row (even with JIT), and shuffle data through its query planner.

At 12 billion rows, those architectural choices compound. Postgres isn't bad. It's solving a different problem.

ClickHouse vs PostgreSQL for Log Analysis

If your workload is log analysis, this is the clearest "ClickHouse" answer I'll give you.

We process 200K log events per second for a client in the fintech space. That's ~17 billion events a day. They were trying to push this into Postgres with TimescaleDB. The ingest worked for two weeks. Then the compaction started falling behind. Then queries that used to take 3 seconds took 40. Then the table bloat made everything slow.

ClickHouse handles this natively. You define a MergeTree table with an ORDER BY key that matches your query pattern, and the ingestion is just... fast. Append-only writes. No vacuum. No compaction catching up. No bloat.

-- ClickHouse: Log table that handles 200K events/sec ingestion
CREATE TABLE IF NOT EXISTS app_logs
ON CLUSTER 'production'
(
    timestamp   DateTime64(3),
    service     LowCardinality(String),
    level       LowCardinality(Enum8('DEBUG' = 0, 'INFO' = 1, 'WARN' = 2, 'ERROR' = 3, 'FATAL' = 4)),
    trace_id    String,
    message     String,
    duration_ms UInt32,
    status_code UInt16,
    region      LowCardinality(String)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/app_logs', '{replica}')
ORDER BY (service, timestamp)
TTL timestamp + INTERVAL 90 DAY
PARTITION BY toYYYYMM(timestamp);

That TTL line matters. Postgres doesn't give you that. You'll write a cron job that deletes old rows, which locks the table, which breaks your queries, which makes your on-call engineer cry. ClickHouse just... removes the data. Asynchronously. Without blocking reads.

For the clickhouse vs postgresql for log analysis question specifically: if you're above 10 billion rows or 10K events per second, ClickHouse. Full stop. Below that, TimescaleDB on Postgres is fine and you'll thank yourself for one less system to operate.

Real-Time Analytics in 2026: The Numbers Don't Lie

The clickhouse vs postgresql for real time analytics 2026 conversation has shifted. In 2023, "real-time" meant "within 5 seconds." In 2026, it means "within 200 milliseconds" for the dashboard, and "within 5 seconds" for the warehouse.

Postgres can do "real-time" in the sense that you INSERT and immediately SELECT it back. Sub-millisecond. For a single row. For a user checking their order status. Fine.

But "real-time analytics" means: ingest 50K events per second, maintain a rolling 15-minute window, serve a dashboard query in under 200ms to 500 concurrent users. That's where Postgres chokes. The WAL is a bottleneck for high-throughput writes. The MVCC system means your read queries see stale snapshots while writes pile up. The query planner struggles with continuously growing partitioned tables.

ClickHouse handles the 50K/sec ingest without breaking a sweat. The MergeTree engine batches writes into granules. The ORDER BY key means recent data is always at the end of the file, so time-range queries are trivially fast. And because it's columnar, a dashboard query pulling 5 metrics out of a 200-column table only touches those 5 columns.

In our production stack, we stream from Kafka into ClickHouse with a 200ms end-to-end latency. Postgres, for the same pipeline, hit 2.3 seconds before the dashboard started showing stale data. ClickHouse benchmarks on 200K events/sec back up what we see in production.

The caveat: ClickHouse's "real-time" is eventual consistency. You insert a row, and for 10-50ms, a read might not see it. If your application logic depends on read-your-writes within the same transaction, you're back to Postgres.

When You Need Both (The Hybrid Pattern)

Here's what we actually run in production. And it's not controversial once you see it.

Postgres is your transactional core. Orders. Users. Account balances. The stuff where SELECT FOR UPDATE and SERIALIZABLE isolation matter. Maybe 10M to 200M rows. Partitioned if needed. Fine.

ClickHouse is your analytical engine. Events. Logs. Telemetry. The stuff where you're doing GROUP BY over billions of rows. Fed from Postgres via logical replication, or better, from a streaming source (Kafka, Pulsar) that both systems consume.

-- The hybrid: Postgres handles the write, ClickHouse handles the read
-- Postgres side: transactional write
INSERT INTO orders (id, customer_id, total, status, created_at)
VALUES ('ord_98234', 'cust_551', 149.99, 'pending', now())
RETURNING id, created_at;

-- CDC (Debezium/Flink) picks up the WAL change and writes to ClickHouse
-- ClickHouse side: analytical read
-- "Revenue by region for the last 90 days, top 20 products"
SELECT
    toStartOfMonth(o.created_at) AS month,
    c.region,
    count() AS orders,
    sum(o.total) AS revenue,
    quantile(0.95)(o.total) AS p95_order_value
FROM orders_analytics o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= now() - INTERVAL 90 DAY
GROUP BY month, c.region
ORDER BY revenue DESC
LIMIT 20;

This is the pattern I'd recommend to 80% of engineering teams. You get Postgres's transactional guarantees where they matter. You get ClickHouse's analytical throughput where it matters. You don't force either system to do the other's job.

The operational cost: you run two systems. Two monitoring stacks. Two on-call rotations. Two backup strategies. Yes, it's more work. But trying to make Postgres do both jobs, or making ClickHouse handle transactions, is more work and more fragile.

The Operational Tax Nobody Mentions

I'll be honest. ClickHouse operations in 2026 are easier than they were in 2022. The ON CLUSTER syntax, the built-in distributed tables, the ClickHouse Cloud option (launched 2023, matured significantly by 2025) have reduced the "you need a ClickHouse expert on staff" problem.

But.

ClickHouse's ALTER TABLE operations are not like Postgres's. You can't just ALTER TABLE ADD COLUMN and have it instantly available across all replicas. You run a MUTATION, which is an async background operation. On a 5TB table, that mutation can take hours. Your schema change is now a project, not a command.

ClickHouse doesn't do foreign keys. Doesn't do constraint enforcement. Your data integrity is on you, the application layer. In Postgres, the database is your last line of defense. In ClickHouse, your application is the only line of defense.

Disk usage. ClickHouse compresses aggressively (LZ4 by default, ZSTD if you want higher ratios). A 1TB dataset in Postgres might be 300-400GB in ClickHouse. But if you have 10 replicas for HA, you're managing 3TB across your cluster. Postgres streaming replication uses less disk for the replica.

Postgres is boring to operate. That's a feature. You know exactly what pg_rewind does. You know VACUUM works. Your DBA (or your pgbouncer config) is a solved problem. ClickHouse operations have more moving parts. More knobs. More ways to shoot yourself in the foot.

A Decision Framework (20 Minutes, Max)

When a team comes to me and says "we need to pick a database for our new product," I ask five questions:

1. What's your expected row count in year 2?
Under 100M? Postgres. You don't need the complexity.
100M to 5B? Postgres with partitioning, or ClickHouse if queries are analytical.
5B+? ClickHouse. Postgres will fight you.

2. What's your query pattern?
Point lookups by primary key, a few joins, transactions? Postgres.
Aggregations, time-series scans, multi-column filters on large tables? ClickHouse.
Both? Hybrid.

3. How many concurrent writers?
Under 500 TPS? Postgres is fine.
5K-50K TPS of append-only writes? ClickHouse.
Mixed read/write with strict transactional requirements? Postgres for the write path, ClickHouse for the read path.

4. Do you need transactions?
Yes, with multi-row ACID guarantees? Postgres.
No, append-only with idempotent upserts? ClickHouse.

5. Who's operating this?
One engineer who also does app code? Postgres. Lower tax.
Dedicated data engineer or SRE? ClickHouse is worth the ops cost.

-- Quick benchmark you can run in 5 minutes to see where YOUR data lands
-- Run this on your actual dataset (or a representative sample)

-- PostgreSQL: time a grouped aggregate
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT category, date_trunc('day', created_at) as day,
       count(*) as events,
       avg(amount) as avg_amount
FROM transactions
WHERE created_at > now() - interval '30 days'
  AND status = 'completed'
GROUP BY category, day
ORDER BY day;

-- Then run the equivalent on ClickHouse and compare wall time.
-- If Postgres is >10x slower, you need ClickHouse.
-- If it's within 2-3x, Postgres is fine and simpler.

FAQ

Can I run ClickHouse and Postgres on the same server?

Technically yes. Practically no. ClickHouse is a memory hog (it wants 256GB+ for large datasets) and CPU-hungry (it'll use all cores for query execution). You'll starve Postgres of both. Run them on separate nodes. Same AWS account, different instances.

Does ClickHouse support joins?

Yes, but they're expensive. It loads the right table into memory (or external memory) and does an in-memory join. For a 50M row join, it's fine. For a 2B row join, you're going to want to restructure your schema. The ClickHouse philosophy is "denormalize and avoid joins." Postgres handles joins natively and efficiently up to hundreds of millions of rows.

What about ClickHouse Cloud vs self-managed in 2026?

Cloud is much better than it was in 2023. The multi-region replication is solid, the managed scaling works, and the cost is roughly 40-60% of running your own cluster with 2 engineers. If you don't have a dedicated infra person, Cloud saves you 6-8 hours a week. We moved a client from self-managed to Cloud in March 2026. Their on-call pages dropped by 70%.

Will PostgreSQL 19 close the gap?

It'll improve async I/O and the JIT compiler. But it's still row-based. The architectural ceiling for analytical workloads on 10B+ rows is still significantly below ClickHouse. You'll see maybe a 2-3x improvement on analytical queries. ClickHouse will remain 100x+ faster for pure analytical scans at that scale. The question is whether you're at that scale.

How do I handle schema changes in ClickHouse without downtime?

You add columns (lightweight since 23.x, it's metadata-only). You don't drop columns (use TTL to stop writing, then plan a migration). You don't alter ORDER BY (that requires a new table and data migration). Postgres is more forgiving here. ALTER TABLE ADD COLUMN is instant and safe. This is the biggest "gotcha" when coming from a Postgres background.

Is there a tool that lets me query ClickHouse with Postgres syntax?

Yes. ClickHouse supports a PostgreSQL wire protocol (since 2021). You can connect with psql, pgAdmin, or any Postgres client. But it's a translation layer. Not everything maps cleanly. Window functions work. CTEs work. But some Postgres-specific syntax won't. Treat it as "good enough for ad-hoc queries, not for application code."

What's the actual cost difference for a 5TB analytical dataset?

Rough numbers from our infra team: 5TB on Postgres (r6i.2xl, EBS gp3, multi-AZ) runs about $4,200/month. The same 5TB on ClickHouse (r7gn.4xl, or ClickHouse Cloud equivalent) runs about $2,800-$3,500/month. But the query performance difference means you need fewer nodes for the same SLA. The real cost is engineering time. Postgres is cheaper to hire for. ClickHouse expertise is scarcer and more expensive.

The Bottom Line

The clickhouse vs postgresql for large datasets 2026 question isn't "which is better." It's "what's your actual workload, and what's your operational capacity?"

If you're under 500M rows, your queries are transactional or mildly analytical, and you have one full-stack engineer who's also your DBA: Postgres. Don't overthink it. Don't add complexity you don't need.

If you're over 5B rows, your queries are aggregations and time-series scans, you're ingesting more than 10K events per second, and you have someone who can own the system: ClickHouse. The performance difference isn't marginal. It's 100x. Your users will feel it. Your on-call engineer will thank you.

If you're in the middle: run both. Postgres for the transactional core. ClickHouse for the analytical edge. Feed it from a stream. Keep the teams separate. Keep the concerns separate.

I've made the wrong call on this before. In 2022, I put a 2B-row event table in Postgres because "ClickHouse is too complex." It took us four months to realize we needed to migrate. The migration itself took six weeks. The six weeks of slow queries before that? Cost us two enterprise clients.

Don't make my mistake. Run the benchmark on your actual data. Get the number. Then decide.

---

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