This article was originally published at sivaro.in

ClickHouse vs PostgreSQL: A Migration Guide for 10B+ Rows

Slug: clickhouse-vs-postgresql-migration-guide-for-10b-rows

I got paged at 2:47 AM in March 2025. Our analytics dashboard for a fintech client in Singapore was taking 94 seconds to render a simple cohort retention query. The table in question? 11.3 billion rows in Postgres 16. We'd partitioned it. We'd added covering indexes. We'd even split it across three shards with Citus. Still 94 seconds. The CTO was on a call with their board the next morning.

That night kicked off a six-week migration that I've now replicated in different flavors for four other clients. If you're googling a clickhouse vs postgresql migration guide at 1 AM because your p99 latency just crossed 30 seconds, this is the one I wish existed when I was in that server room.

Here's what you'll get: the actual performance numbers I've measured, the schema mapping pitfalls that'll waste you a week if you miss them, the OLTP question nobody answers honestly, and a concrete playbook for moving data without taking your platform offline.

The Moment Postgres Stopped Being Enough

Postgres is the best relational database I've used in 15 years. Full stop. For transactional workloads, for data integrity, for that "I can't believe it works" developer experience. It's still the default I reach for.

But Postgres hits a wall. Not a soft one. A hard one. Around 8-15 billion rows in a single table, even with aggressive partitioning, the planner starts making suboptimal decisions. Index scans degrade. The WAL gets bloated. Vacuum can't keep up. I've seen this exact pattern at a logistics company in 2024 — their shipment tracking table crossed 12 billion rows and their nightly ETL job went from 40 minutes to 6 hours. They didn't notice for three weeks because nobody was watching the ETL duration.

ClickHouse ClickHouse Docs was built for this exact problem. Columnar storage. SIMD-vectorized execution. No index tree to traverse. You scan compressed column data in parallel across cores and you get answers in milliseconds where Postgres is still negotiating with the buffer cache.

The question isn't "is ClickHouse better?" It's "is your workload the kind where ClickHouse's architectural bet pays off?" And for analytical queries over large datasets, it absolutely does.

What Actually Happens at 10 Billion Rows

When people search for "clickhouse vs postgresql for large datasets 10 billion rows," they usually want a benchmark table. Fair. Here's what I measured on identical hardware (r6i.4xlarge, 16 vCPU, 128GB RAM, NVMe EBS) running a synthetic events table with 11.2 billion rows:

-- The query that took 94s in Postgres, 800ms in ClickHouse
SELECT 
    toYYYYMM(event_time) AS month,
    customer_id,
    count() AS event_count,
    avg(revenue) AS avg_revenue
FROM events
WHERE event_time >= '2024-01-01'
  AND customer_id IN (SELECT id FROM vip_customers)
GROUP BY month, customer_id
ORDER BY month DESC, event_count DESC
LIMIT 1000;

Postgres 16 (with a composite index on (event_time, customer_id)): 94,200 ms.
ClickHouse 24.8 (same query, trivially rewritten): 812 ms.

That's a 116x difference. I ran it five times each. Standard deviation was under 40ms on the ClickHouse side. The Postgres number bounced between 88s and 101s depending on cache warmth.

But here's what nobody tells you: the first 500 million rows don't change much. You won't feel the pain until you're past 5-8 billion. If your table is at 2 billion and growing 50K rows per day, you've got about 14 months before this becomes your problem. Don't migrate yet.

Can ClickHouse Replace Postgres for OLTP?

I'll save you the hedging. No. Don't do this.

I asked the same question in early 2024 when a startup wanted to "consolidate to one database" and cut infrastructure costs. I said no. They did it anyway.

Their product had a checkout flow: insert order, update inventory, decrement balance, write audit log. Four writes per transaction, ACID required, sub-50ms p99 latency target. In ClickHouse, their transaction throughput dropped from 12K TPS to 800 TPS. Not even close.

The reason is architectural. ClickHouse is an append-optimized, columnar, batch-oriented engine. It mutates data through a "mutation" process that rewrites entire granules. There's no row-level locking. There's no MVCC in the way you'd expect from Postgres. You can do INSERT and ALTER TABLE ... UPDATE but the UPDATE is an async background rewrite, not an atomic in-place modification.

For read-heavy analytical workloads where your "transactions" are really just batch inserts followed by aggregation queries? ClickHouse handles that beautifully. For a checkout flow, a banking ledger, a social graph with real-time writes? Postgres. Or MySQL. Or CockroachDB if you need distributed transactions.

The honest answer to "can clickhouse replace postgresql for oltp" is: for 95% of workloads people ask this about, no. For the 5% that are actually "insert a batch of 10K events every 5 minutes and then query aggregates" — sure, and you'll be thrilled.

The Migration: A Playbook That Actually Works

This is the clickhouse vs postgresql migration guide section you came for. Here's the sequence I follow, refined across four projects:

Week 1-2: Schema mapping and dual-write setup.

You don't stop writing to Postgres. You start writing to both. Use a CDC tool — Debezium, or if you're in a cloud environment, AWS DMS or Fivetran. The dual-write period is where you catch schema mismatches before they become outages.

-- ClickHouse table definition for the events table
CREATE TABLE events (
    event_id        UInt64,
    customer_id     UInt64,
    event_type      LowCardinality(String),
    event_time      DateTime64(3, 'UTC'),
    revenue         Decimal(12, 4),
    metadata        Map(String, String),
    created_at      DateTime64(3, 'UTC') DEFAULT now64(3)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (customer_id, event_time)
TTL event_time + INTERVAL 24 MONTH;

Notice the ORDER BY clause. This is your "index." Get this wrong and every query is a full table scan. I've seen teams put event_id first "because it's unique" and then wonder why their latency is 40x worse than benchmarks.

Week 3-4: Backfill and validation.

For an 11-billion-row table, the backfill takes 18-36 hours depending on your network and compression settings. Use clickhouse-copier or a custom Python script with COPY FROM for the initial dump, then stream CDC events for the delta.

import psycopg2
import clickhouse_connect
from datetime import datetime
import time

pg = psycopg2.connect("dbname=analytics host=pg-primary user=repl_password=...")
ch = clickhouse_connect.get_client(host="ch-cluster:8443", secure=True)

BATCH_SIZE = 500_000
OFFSET = 0

while True:
    cur = pg.cursor(name="backfill_cursor")
    cur.itersize = BATCH_SIZE
    cur.execute("""
        SELECT event_id, customer_id, event_type, event_time, revenue, metadata
        FROM events
        ORDER BY event_id
        OFFSET %s LIMIT %s
    """, (OFFSET, BATCH_SIZE))

    rows = cur.fetchall()
    if not rows:
        break

    ch.insert(
        "events",
        rows,
        column_names=["event_id", "customer_id", "event_type", 
                      "event_time", "revenue", "metadata"]
    )

    OFFSET += BATCH_SIZE
    print(f"Backfilled {OFFSET} rows. Last event_time: {rows[-1][3]}")
    time.sleep(0.5)  # be gentle on the source

print("Backfill complete. Now reconciling row counts.")

Validate with row counts per partition, checksum samples (pick 100K random rows, hash them, compare), and spot-check your top 50 queries for result equivalence.

Week 5: Cutover.

Flip the read traffic. Not all at once. Canarize 5% of dashboard traffic to ClickHouse for 48 hours. Watch error rates. Then 25%. Then 100%. Keep Postgres in write-only mode for two weeks as a rollback window.

Week 6: Decommission and cost optimization.

Delete the Postgres shards. Right-size your ClickHouse cluster. This is where you save the actual money. That 6-node Postgres cluster at $14K/month becomes a 3-node ClickHouse at $6K/month because columnar compression on analytical data is 5-10x more efficient than row storage.

Schema Mapping: Where People Lose a Week

The data types don't map 1:1. I cannot stress this enough.

Postgres TIMESTAMPTZ → ClickHouse DateTime64(3, 'UTC'). Not DateTime. The precision matters if you have sub-second events.

Postgres JSONB → ClickHouse JSON type (since v23.9) or Map(String, String) if your structure is flat. The JSON type is newer and still maturing. For a production migration in 2026, I'd use Map unless you have deeply nested structures and are comfortable with ClickHouse 25.x JSON semantics.

Postgres ARRAY → ClickHouse Array(T). Works fine. But if your arrays are variable-length and you're aggregating over them, expect 3-5x slower performance than a flattened columnar layout.

Postgres partial indexes, expression indexes, GIN indexes on JSONB? They don't exist in ClickHouse. You replace them with ORDER BY keys, PROJECTION definitions, or secondary indexes (minmax, bloom_filter). The mental model is fundamentally different. You're not building a B-tree. You're telling ClickHouse the columnar layout that makes your query patterns fast.

Hybrid Architecture: When You Keep Both

At SIVARO, we don't rip-and-replace. We've moved three clients to a hybrid where Postgres handles transactions (orders, accounts, auth) and ClickHouse handles analytics (events, logs, metrics, feature stores for ML pipelines).

The integration layer is a simple pub/sub. Postgres emits CDC events to Kafka. A lightweight consumer writes them to ClickHouse within 200-500ms. Your transactional data is in Postgres. Your analytical queries hit ClickHouse. Both stay in sync.

This is the architecture I'd recommend for 80% of companies asking this question. You get Postgres reliability for your core data. You get ClickHouse speed for your dashboards and ML training data. You're not betting the company on a single engine.

The Performance Numbers I've Actually Seen

Across four production migrations (fintech, logistics, SaaS, ad-tech) between mid-2025 and now:

Workload	Postgres (p95)	ClickHouse (p95)	Speedup
10B row aggregation, 7-day window	12.4s	420ms	29x
10B row aggregation, 90-day window	47s	1.8s	26x
Point lookup by unique ID	3ms	8ms	0.37x (Postgres wins)
Multi-tenant filter + sort, 1B rows	3.2s	310ms	10x
Batch insert 1M rows	8.5s	1.1s	7.7x

Point lookup is where ClickHouse loses. If your primary workload is "give me the row with id=447291," Postgres with a primary key is still faster. ClickHouse's strength is range scans, aggregations, and filters over billions of rows.

FAQ

Do I need to rewrite all my SQL?

Mostly no. ClickHouse supports a large subset of SQL. Your SELECT ... WHERE ... GROUP BY ... ORDER BY queries will translate directly. What breaks: JOINs over large tables (they work but are expensive; prefer GLOBAL JOIN or restructure), UPDATE/DELETE as synchronous operations, and Postgres-specific extensions like pg_trgm or PostGIS. If you use PostGIS, you're stuck on Postgres for geospatial.

What's the minimum cluster size for 10 billion rows?

For an events table at ~200 bytes per row uncompressed, you're looking at ~120GB raw. With LZ4 compression (default in ClickHouse), expect 25-35GB on disk. A 3-node cluster with 64GB RAM and 500GB NVMe per node handles this comfortably. I've run 11B rows on a single 32GB node, but you want replication for production.

Can I migrate incrementally table by table?

Yes. And I recommend it. Move your highest-query-volume analytical table first. Get the team comfortable with ClickHouse query syntax and ops tooling. Then move the next table. Don't do a big-bang cutover of 40 tables.

What happens to my existing monitoring and alerting?

ClickHouse has its own metrics endpoint (/metrics), integrates with Prometheus natively, and exposes system.metrics and system.events tables for internal introspection. Your PagerDuty runbooks will change. "Check Postgres slow query log" becomes "check ClickHouse system.query_log." Budget a week for ops retraining.

Is ClickHouse stable enough for production in 2026?

I've been running it in production since 2022. The 24.x and 25.x releases have been solid. The team ships features fast (sometimes too fast), but the core MergeTree engine hasn't changed its fundamental behavior in years. If you pin a minor version and test upgrades in staging, you're fine. Cloud-native ClickHouse (ClickHouse Cloud, managed by the company) has been GA since 2023 and handles the ops burden if you don't want to manage the cluster yourself.

What about Postgres 18 and its improvements?

Postgres 18 shipped in early 2026 with better parallel query execution, improved JIT compilation for complex expressions, and the new pg_vector integration. It's a great release. But it doesn't change the fundamental row-oriented architecture. At 10 billion rows, you're still fighting the buffer cache, the index tree, and the vacuum cycle. Postgres 18 makes 2 billion rows feel like 1.5 billion rows. ClickHouse makes 10 billion rows feel like 10 million. Different problem domains.

Will this migration affect my compliance posture?

If you're in a regulated industry (fintech, healthcare), check your data residency and retention requirements. ClickHouse's TTL feature handles retention well. For audit trails, keep the immutable ledger in Postgres. The hybrid approach I described earlier keeps you compliant: transactional records stay in a system of record with full ACID guarantees, while the analytical copy in ClickHouse is derived and reconstructable.

The Decision Framework

After all of this, here's how I actually decide. I print this out and tape it to my monitor during architecture reviews.

If your primary query pattern is aggregation over large datasets (GROUP BY, SUM, COUNT, percentiles over billions of rows) and your write pattern is append-heavy batch inserts, move to ClickHouse. You'll be fast. You'll be happy.

If your primary query pattern is point lookups, short transactions, complex joins between small tables, stay on Postgres. ClickHouse will frustrate you daily.

If you need both — and most companies in 2026 do, because they run transactions and they run analytics and they run ML pipelines and they run real-time dashboards — run both. Postgres for OLTP. ClickHouse for OLAP. A CDC pipeline in between. It's not one database. It's a data platform. And that's fine. That's what production systems actually look like.

The migration isn't about picking a winner. It's about giving each query the engine it was designed to run on.

The clickhouse vs postgresql migration guide that actually matters isn't a feature comparison table. It's a decision about what your data does, how often it changes, and how fast you need answers. Get that right and the rest is mechanical.

---

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