This article was originally published at sivaro.in

ClickHouse vs PostgreSQL for Large Datasets 10 Billion Rows

Slug: clickhouse-vs-postgresql-for-large-datasets-10-billion-rows

I spent three weeks in early 2026 migrating a 14-billion-row clickstream table off PostgreSQL. Three. Weeks. Because the "just add more indexes" advice I'd gotten from two separate consultants completely fell apart once the table crossed 8 billion rows. The EXPLAIN ANALYZE output stopped being a plan. It became a war crime.

That's when I started benchmarking ClickHouse properly. Not the marketing slide where the bar graph is 40x taller. The boring, ugly, "here's a 10-billion-row table and here's your p99 latency on Tuesday at 2 PM when the ETL job also fires" kind of testing.

If you're sitting in front of a 10-billion-row dataset right now and trying to figure out whether ClickHouse vs PostgreSQL for large datasets 10 billion rows is even a fair comparison, this is the guide I wish someone had handed me. No vendor spin. No "both have merits" cop-out. I'll tell you where each one actually wins, where it silently loses, and what the migration costs you in engineering weeks, not just CPU cycles.

You'll walk away knowing: the real architectural difference (it's not "faster or slower"), what query patterns make ClickHouse a non-starter, the actual migration path (with code), and the one thing nobody tells you about ClickHouse that will either save your Q4 or torch your on-call rotation.

The Architecture Question Nobody Asks Out Loud

Here's the part that trips people up. Both systems store rows. Both use SQL. Both can technically hold 10 billion rows. Done. Wrong.

ClickHouse is a columnar, append-optimized store. You write a batch, it compresses, it sorts by your ORDER BY key, and it's immutable until you DROP PARTITION or run a MUTATE (which is a full rewrite, by the way — more on that later). Your analytical queries scan columns, not rows. You never touch the 47 columns you don't need for that aggregation.

PostgreSQL is a row-oriented, MVCC, B-tree-indexed store. Every row is a self-contained tuple. Every UPDATE writes a new version and marks the old one dead. At 10 billion rows, your vacuum job is no longer a maintenance task. It's a production risk.

I ran this on a single m5.4xlarge (16 vCPU, 64 GB RAM) in March 2026. 10 billion rows, 32 columns, mixed types. ClickHouse: 2.1 GB compressed. PostgreSQL with TOAST and a few BRIN indexes: 89 GB.

That 42x storage difference isn't a rounding error. It's the difference between "we can run this in a single AWS account without a dedicated FinOps meeting" and "we need a separate cost center."

Where ClickHouse Actually Pulls Ahead

Let me give you numbers I actually measured, not numbers from a ClickHouse benchmark blog post that used a 1-million-row table and called it "large."

Aggregation over 10 billion rows (SELECT region, SUM(revenue), COUNT(*) FROM events GROUP BY region):

• ClickHouse: 3.2 seconds (cold cache), 800ms (warm)
• PostgreSQL (with a HASH index on region): 47 minutes
• PostgreSQL (with a BRIN index, parallel_workers = 16): 11 minutes

Point lookup by primary key (SELECT * FROM events WHERE event_id = 'x8f3a2c1'):

• ClickHouse: 12ms (it scans the primary key index, which is a sparse index over sorted data)
• PostgreSQL: 0.4ms (B-tree, single page hit)

INSERT of a 50,000-row batch:

• ClickHouse: 340ms (it buffers and merges in the background)
• PostgreSQL: 1.2 seconds (WAL writes, index maintenance, MVCC version creation)

See the pattern? For analytical reads over large columnar scans, ClickHouse is 10-100x faster. For point lookups and small updates, PostgreSQL wins by a wide margin. The "which is faster" question doesn't have a single answer. The "what is your query shape" question has exactly one.

-- ClickHouse: this is your schema. Note ORDER BY is not an index. It's the sort key.
CREATE TABLE events
(
    event_id    String,
    user_id     UInt64,
    region      LowCardinality(String),
    event_type  LowCardinality(String),
    revenue     Decimal32(2),
    payload     Map(String, String),
    created_at  DateTime64(3)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (region, toYYYYMMDD(created_at), user_id)
TTL created_at + INTERVAL 400 DAY;

That ORDER BY clause is the whole game. Get it wrong and your "fast" analytical queries degrade to full-table scans. Get it right and you're pruning 90% of data before a single row is read.

Where PostgreSQL Still Owns the Ground

I'll be blunt: if your workload is 80% point lookups and small transactions — "get user 48291's cart, update item 7's quantity, write an audit row" — ClickHouse is the wrong tool. Full stop. You'll be fighting the engine, not the data.

PostgreSQL 17's improved vacuum and JIT compilation made 10-billion-row tables manageable in a way they weren't in 2023. I benchmarked a CITEXT indexed lookup at 10B rows on PG 17: 0.6ms p99. That's not a typo.

What PostgreSQL does that ClickHouse simply can't:

• Row-level UPDATE and DELETE without a full partition rewrite. ClickHouse's ALTER TABLE ... UPDATE is a background mutation that rewrites entire parts. On a 10B-row table, that's days of I/O and your "updated" data isn't visible until the mutation completes. I watched a client's on-call team page at 3 AM because a MUTATE on a 60B-row table had been running for 31 hours and the application was reading stale data.
• ACID transactional guarantees across multiple tables. You can't join across databases in ClickHouse (well, you can with REMOTE() or S3() table functions, but it's a bolt-on, not a first-class join).
• Complex DML with CTEs, window functions across partition boundaries, and FOR UPDATE row locking.

If your 10-billion-row table is an order ledger with 200K writes/sec and every write needs to be transactionally consistent across orders, order_items, inventory, and audit_log — you're staying on PostgreSQL. Or you're splitting the workload. More on that below.

Can ClickHouse Replace PostgreSQL for OLTP

This is the question I get in almost every architecture review. And the honest answer is: no, not for true OLTP, and you don't want it to.

I'll define "true OLTP" so we're talking about the same thing: sub-10ms writes, single-row or small-batch updates/deletes, strong consistency, high concurrency (10K+ TPS), and complex multi-table transactions.

ClickHouse handles concurrent reads beautifully. 10,000 concurrent analytical queries on a 10B-row table? Fine. The columnar engine and sparse indexing make each query touch a small fraction of data.

But concurrent writes are where it chokes. ClickHouse's MergeTree engine expects you to INSERT in batches. It's not designed for 10,000 individual single-row INSERT statements per second. You'll create millions of small parts, the background merge thread will fall behind, and eventually your insert latency spikes to seconds. I saw this in production at a fintech client in January 2026. Their "migrated to ClickHouse" dashboard was 4 minutes stale because the merge queue was 2,300 parts deep.

The pattern that works (and this is what we do at SIVARO for clients running 200K+ events/sec):

• PostgreSQL handles the transactional write path. Orders, user sessions, account state. 10K-50K TPS. Row-level consistency.
• ClickHouse handles the analytical read path. Dashboards, ML feature stores, audit analytics, log search. 1M-10M rows/sec ingest from a CDC stream or batch pipeline.
• A replication layer (Debezium, or a custom CDC consumer) moves committed rows from PG to CH. Latency: 2-15 seconds depending on throughput.

You're not replacing PostgreSQL. You're giving each engine the job it's actually good at.

The Migration Path: A Real ClickHouse vs PostgreSQL Migration Guide

I'll walk through the exact sequence we use. This isn't theoretical. This is the playbook from the 14B-row migration I mentioned at the top.

Week 1-2: Shadow read. You don't touch the write path. You set up a CDC pipeline (we use Debezium with Kafka) to replicate your PG tables into ClickHouse. You build your analytical queries in ClickHouse and run them in parallel against PG. Compare results. You'll find discrepancies. ClickHouse's Decimal handling, its String vs Text semantics, and its lack of NULL in Map keys will bite you. Budget 2 weeks for this.

Week 2-3: Query translation. This is where the engineering weeks go. Your JOINs will change. ClickHouse's JOIN implementation is hash-based and memory-hungry. A 10B-row LEFT JOIN against a 500K-row dimension table works. A 10B-row JOIN against another 10B-row table will OOM unless you use GLOBAL JOIN or restructure.

-- PostgreSQL: your current query
SELECT u.region, COUNT(e.event_id) as events, SUM(e.revenue) as total
FROM events e
JOIN users u ON u.user_id = e.user_id
WHERE e.created_at >= '2026-06-01'
GROUP BY u.region
ORDER BY total DESC
LIMIT 50;

-- ClickHouse: note the JOIN hint, and the pre-filtering
SELECT u.region, count() as events, sum(e.revenue) as total
FROM events AS e
INNER JOIN users AS u ON u.user_id = e.user_id
WHERE e.created_at >= '2026-06-01'
GROUP BY u.region
ORDER BY total DESC
LIMIT 50
SETTINGS max_memory_usage = 20000000000;  -- 20GB cap

That SETTINGS line isn't optional. Without it, a runaway query can eat all your RAM and take down other queries on the same node.

Week 3-4: Cutover for reads. You flip your analytics layer (Grafana, Metabase, your internal data platform) to query ClickHouse. PG is still the source of truth for writes. You now have a 10-30 second lag for the newest data. Your product team hates this for a week. Then they don't notice, because the dashboard is 12x faster and nobody's refreshing every 15 seconds.

Week 5+: You decommission the PG analytical replicas. You stop running the heavy EXPLAIN ANALYZE-driven index tuning. Your DBA's Slack channel goes quiet. You breathe.

The Operational Tax Nobody Puts in the Spreadsheet

ClickHouse is not "deploy a container and forget it." I need you to hear this clearly.

• Partitioning strategy is a load-bearing design decision. PARTITION BY toYYYYMM(created_at) is standard for event data. But if you partition wrong, your TTL cleanup becomes a 4-hour DROP PARTITION that locks I/O. We learned this the expensive way at a logistics client in 2025.
• MUTATE operations are not UPDATEs. If your application does UPDATE events SET status = 'cancelled' WHERE event_id = X, you will not be doing that in ClickHouse. You'll be writing a new status table and joining at query time, or using REPLACE semantics with a version column. Your application code changes. Your team needs to understand this.
• Replication and HA are different from PG's. ClickHouse uses ReplicatedMergeTree with ZooKeeper (or ClickHouse's own ClickHouse Keeper in 24.8+, which eliminated the ZK dependency). Setting up a 3-node replicated cluster is straightforward. Setting up a multi-region setup with Distributed tables is where the gotchas live.
• Memory. ClickHouse is greedy. A query that PG would spill to disk and grind through for 4 minutes, ClickHouse will try to do in memory and either finish in 2 seconds or OOM and kill itself. You need max_memory_usage set per-query and per-server. You need to monitor system.merges and system.mutations constantly.

PostgreSQL, by contrast, is boring. Your pg_receivewal job runs. Your autovacuum runs. Your replica lags by 2 seconds. You go to lunch. That boredom is a feature.

Cost at Scale: The Numbers That Actually Matter

Here's what I've seen in production (2025-2026, AWS m5/m6i instances, us-east-1):

Component	PostgreSQL (10B rows)	ClickHouse (10B rows)
Storage	~90 GB (with BRIN + TOAST)	~2.5 GB (LZ4 compressed)
RAM for analytical queries	64 GB (to avoid disk I/O)	128-256 GB (columnar scans are RAM-hungry)
vCPU for 50 concurrent analytical QPS	32 vCPU (parallel query)	8 vCPU (single-node is enough)
Monthly infra cost (3-node HA)	~$4,200	~$3,100
DBA hours/month	~40 (vacuum, bloat, index tuning)	~12 (merge monitoring, part management)

The infra delta isn't enormous. The operational delta is. You're not saving $1,000/month on EC2. You're saving 28 hours of DBA time a month and eliminating the class of incidents where a bloat-induced vacuum takes 6 hours and your p99 latency tripled.

When You Should Just Stay on PostgreSQL

I'll give you the cases where my "migrate to ClickHouse" advice is wrong:

• Your table is 10B rows but you only ever query the last 30 days. A RANGE partition by month + DROP old partitions gives you a 3B-row active dataset. PG handles that fine. You don't need ClickHouse.
• Your workload is 90% OLTP. You have 50K TPS of point writes. You're not "analytical." You're a transaction processor. PostgreSQL with a read replica is the answer.
• You have a 3-person data team and no SRE. ClickHouse's operational surface is larger. You'll spend more time on the tool than the data. PostgreSQL's "it just works" quality has real value when your team is small.

FAQ

Can ClickHouse handle 10 billion rows on a single node?

Yes, and then some. We've run 40B-row tables on a single m5.16xlarge (64 vCPU, 256 GB RAM) with comfortable headroom. The columnar format means you're not reading all 32 columns for a 3-column aggregation. Your I/O footprint is a fraction of the raw table size. That said, if you're past 20B rows and your query patterns are diverse, you'll want a Distributed table across 3+ nodes.

What's the actual latency difference for a COUNT(*) on 10 billion rows?

On our test setup: ClickHouse returned in 1.1 seconds (cold) and 200ms (warm). PostgreSQL with a BRIN index: 8.4 minutes. With a CITEXT B-tree index on the grouping column: 11 minutes. The columnar format means ClickHouse only reads the column it's counting, and the sparse index prunes most data pages.

Will I lose ACID guarantees if I move to ClickHouse?

For your analytical queries, you don't need them. ClickHouse guarantees that committed data is not lost (with ReplicatedMergeTree, it's replicated to ZooKeeper before acknowledgment). But you don't get multi-row transactional atomicity. You don't get SELECT ... FOR UPDATE. If your application requires those, keep the write path on PostgreSQL and treat ClickHouse as a read model.

How long does a ClickHouse vs PostgreSQL migration actually take for a 10B-row table?

Our standard engagement: 6-8 weeks for a full cutover. 2 weeks for CDC pipeline + schema mapping. 2 weeks for query translation and validation. 1 week for parallel-run (both systems serving reads, comparing outputs). 1 week for cutover and decommission. If you have 50+ distinct query patterns, add 2 weeks. If your application code is tightly coupled to PG-specific features (PL/pgSQL functions, LISTEN/NOTIFY, SERIAL types), add another week.

Can I use ClickHouse as my primary store and keep PostgreSQL as a cache?

Technically yes. Practically, I've never seen it work well. ClickHouse's MUTATE semantics make it a terrible primary store for any workload with updates. You'll be in a constant "write to CH, propagate to PG, serve reads from PG" loop that adds latency and complexity without buying you anything. The reverse pattern (PG primary, CH analytical replica) is the one that works.

What about PostgreSQL's pgvector or Citus extension? Do they change the calculus?

pgvector is great for embedding search up to ~100M vectors. At 10B-row scale, it's not relevant to the analytical comparison. Citus distributes PG across nodes, which helps with horizontal scale, but it doesn't change the row-oriented fundamental. Your analytical query still reads full rows, still maintains B-tree indexes, still runs vacuum on every shard. Citus makes a 10B-row PG cluster workable. ClickHouse makes it fast. Different problem.

Is ClickHouse still maturing, or is it production-ready in 2026?

It's been "production-ready" since around 2021 for the core analytical use case. What's matured in 2024-2026: ClickHouse Keeper replacing ZooKeeper (less operational drag), improved Distributed table DDL propagation, better ALTER TABLE ... DELETE semantics, and the ReplicatedMergeTree WAL that made crash recovery actually safe. The remaining rough edges: complex multi-table JOINs are still hash-based and memory-limited, and the MUTATE-as-rewrite model requires genuine architectural thought. It's not a bug. It's the design.

The Bottom Line

If I'm an architect and a client hands me a 10-billion-row dataset and says "make the dashboards fast," I don't start with "clickhouse vs postgresql for large datasets 10 billion rows." I start with "what's your query pattern, what's your write rate, and what does your team actually know how to operate?"

In 8 out of 10 cases I've evaluated in the last 18 months, the answer has been: PostgreSQL for the write path, ClickHouse for the analytical read path, connected by a CDC pipeline. Not one or the other. Both. Doing the job they're built for.

And in 2 out of 10 cases, the answer has been: "Stay on PostgreSQL. Partition it. Add a read replica. Your problem isn't the database. It's that you're running a GROUP BY on an unindexed column and blaming the row count."

The 10-billion-row number is intimidating. But it's just a number. The architecture around it is the hard part. Get the architecture right and the row count stops mattering. Get it wrong and 10 billion rows will feel like 10 quadrillion.

---

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