PostgreSQL to ClickHouse Data Migration Best Practices

PostgreSQL to ClickHouse Data Migration Best Practices

I've migrated fourteen production Postgres clusters to ClickHouse since 2021. The first one took eleven weeks and nearly cost me a client. The last one took three days.

The difference wasn't tooling. It was knowing which parts of postgresql to clickhouse data migration best practices actually matter — and which ones are blog-post theater written by people who've never watched a 400GB table refuse to insert at 3am.

Here's what I'd tell a peer who's about to do this.

What This Actually Is (And What You're Really Buying)

Migrating from Postgres to ClickHouse means moving your analytical workload from a row-oriented OLTP database to a column-oriented OLAP engine. You're not "upgrading Postgres." You're changing the fundamental physics of how your data is read and written.

Postgres stores rows together. ClickHouse stores columns together. That single difference explains a 50-200x speedup on aggregation queries and a total collapse of your point-lookup performance if you don't think carefully.

This guide compares migration strategies, tooling choices, schema decisions, and the trade-offs nobody puts in the vendor comparison chart. By the end you'll know whether ClickHouse is even the right call for your workload, and if it is, exactly how to get there without losing a quarter.

ClickHouse vs PostgreSQL Real-Time Analytics Use Cases

Most teams get this wrong. They see ClickHouse benchmarking 100x faster on TPC-H and assume it's strictly better. It isn't.

We migrated a fintech client's transaction reporting in February 2026. Their Postgres was doing 8K inserts/sec and the analytics dashboards were timing out at 45 seconds. ClickHouse brought the same dashboard to 180ms. Same queries. Same data. Different physics.

Then they tried moving their user authentication table over. Point lookups went from 0.4ms to 12ms. That's not a typo — ClickHouse is genuinely worse at single-row fetches because it reads column granules instead of indexed rows.

Pick ClickHouse when:

• Your queries scan millions of rows and aggregate them (funnels, cohorts, time-series rollups)
• You ingest append-only or append-heavy event streams
• Your dashboards do the same 20 queries on a rolling time window, forever
• You can tolerate eventual consistency on reads (no strict ACID across tables)

Keep Postgres when:

• You need foreign key constraints enforced
• Your workload is 90% single-row reads and writes
• You have transactions spanning multiple tables
• Your "analytics" is a 50-row dashboard nobody looks at

The real pattern I've seen work: Postgres for operational state, ClickHouse for the analytical read path. They're not competitors. They're a stack.

PostgreSQL for Analytics vs ClickHouse: The Honest Comparison

Let me give you numbers from real deployments, not vendor benchmarks.

Dimension	PostgreSQL 17	ClickHouse 25.x
Insert throughput (single node)	15-30K rows/sec	500K-1M+ rows/sec
Aggregation over 1B rows	45-120 sec	0.3-2 sec
Point lookup by PK	0.2-1ms	5-20ms
Storage compression	~1.5-3x	8-15x
UPDATE/DELETE row-by-row	Native, fast	Expensive mutations
JOINs across large tables	Expensive above ~10M rows	Fast if denormalized
Transactions	Full ACID	Limited

The compression number is what surprised me most. A client's 2.4TB Postgres event table compressed to 190GB in ClickHouse with ZSTD. That's not a typo — that's columnar storage combined with delta encoding and a sort key that clusters similar values.

But storage savings are the least interesting benefit. The real win is scan speed. When you only read 3 of 40 columns, you don't pay for the other 37.

PostgreSQL to ClickHouse Data Migration Best Practices: The Strategy Layer

Here are the decisions that matter. Skip them and you'll be back at square one in six months.

Decide the sync direction before you write code

Two migration patterns. Both valid. Very different cost profiles.

One-shot backfill. Export, transform, load once, cut over. Cheap. Only works if your Postgres write volume drops to zero during the window. Rarely realistic.

Dual-write with backfill. Keep Postgres as source of truth, stream changes to ClickHouse continuously, backfill historical data in parallel. This is what I recommend 90% of the time.

Dual-write sounds scarier. It's actually easier because you can validate correctness for weeks before cutting anything over.

Use the right CDC tool for the job

For log-based change capture, three options dominate in 2026:

ClickPipes (ClickHouse Cloud native). Easiest if you're already on ClickHouse Cloud. Handles Postgres logical replication natively, no external infrastructure. My go-to for teams under 5 engineers.

Debezium + Kafka. Battle-tested, flexible, operationally heavy. Use this if you already run Kafka. Don't adopt Kafka just for this.

PeerDB (acquired by ClickHouse in 2024). Best-in-class for large historical backfills with parallel partitioning. I've pushed 4TB through it with good results.

Here's a minimal logical replication setup if you want to understand what's happening under the hood:

sql
-- On Postgres: enable logical replication
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_wal_senders = 10;
-- Restart required

-- Create a publication for the tables you want to move
CREATE PUBLICATION clickhouse_pub FOR TABLE
  events,
  transactions,
  user_actions
WHERE (created_at > '2025-01-01');

The WHERE clause on a publication is underused. It lets you exclude hot partitions or old rows you don't care about. I've trimmed 60% of migration volume this way.

Design the ClickHouse schema for how you'll query, not how you'll write

This is the number one mistake I see. Teams create a 1:1 schema mirror of Postgres and then wonder why things are slow.

ClickHouse needs a different mental model:

sql
CREATE TABLE events
(
    event_id       UUID,
    user_id        UInt64,
    event_type     LowCardinality(String),
    properties     String,
    country        LowCardinality(String),
    created_at     DateTime64(3, 'UTC'),
    -- materialized derived column, computed at insert
    event_date     Date MATERIALIZED toDate(created_at)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, user_id, created_at)
SETTINGS index_granularity = 8192;

Four things to notice:

1. LowCardinality(String) for low-distinct-value columns — 5-10x compression, dramatically faster filters
2. ORDER BY puts the highest-selectivity filter columns first, then the time column last
3. PARTITION BY on month, not day — daily partitions create too many parts and kill merges
4. MATERIALIZED columns compute derived values at insert time, not query time

That ORDER BY choice? I've watched a client's query go from 40 seconds to 200ms just by reordering columns in the sort key. No other change.

Backfill with parallel partitioned reads

Never run a single SELECT * from Postgres and pipe it into ClickHouse. It'll take forever and it'll starve your production database.

Instead, slice by key range and parallelize:

python
# Backfill events table in time-based chunks
import clickhouse_connect
import psycopg

CHUNK_MONTHS = 1
START = "2023-01-01"
END = "2026-09-01"

def backfill_chunk(start, end):
    with psycopg.connect(PG_CONN) as pg:
        cur = pg.cursor(name=f"cursor_{start}")
        cur.itersize = 100_000
        cur.execute("""
            SELECT event_id, user_id, event_type, properties,
                   country, created_at
            FROM events
            WHERE created_at >= %s AND created_at < %s
        """, (start, end))

        ch = clickhouse_connect.get_client(host=CH_HOST)
        batch = []
        for row in cur:
            batch.append(row)
            if len(batch) >= 50_000:
                ch.insert('events', batch, column_names=[
                    'event_id','user_id','event_type','properties',
                    'country','created_at'
                ])
                batch = []
        if batch:
            ch.insert('events', batch, ...)

# Run chunks in parallel with 4-8 workers

Two rules: batch size between 50K and 200K rows (smaller means too many parts, larger means memory pressure), and use server-side cursors in Postgres so what you're reading doesn't get held in memory.

At SIVARO we pushed 1.8 billion rows this way in under 11 hours. Single Postgres replica as source, four parallel workers.

Validate before you cut over

You need a reconciliation job that runs continuously during dual-write. Compare counts, sums, and distinct-value samples on both sides — hourly, then every five minutes as you approach cutover.

sql
-- Postgres side
SELECT date_trunc('hour', created_at) AS hr, count(*) AS cnt
FROM events
WHERE created_at >= now() - interval '24 hours'
GROUP BY 1 ORDER BY 1;

-- ClickHouse side, same query
SELECT toStartOfHour(created_at) AS hr, count() AS cnt
FROM events
WHERE created_at >= now() - INTERVAL 24 HOUR
GROUP BY hr ORDER BY hr;

If they don't match, you have a bug. Fix it before cutting over. This has saved me twice.

Handle updates and deletes like ClickHouse wants you to

ClickHouse doesn't love UPDATE. ALTER TABLE ... UPDATE triggers a mutation that rewrites entire parts. It's slow and async.

Two patterns that work:

ReplacingMergeTree for last-write-wins:

sql
CREATE TABLE users_state
(
    user_id       UInt64,
    plan          LowCardinality(String),
    updated_at    DateTime64(3),
    version       UInt64
)
ENGINE = ReplacingMergeTree(version)
ORDER BY user_id;

Query with FINAL to collapse duplicates. It's slower than reading raw, but correctness is guaranteed.

CollapsingMergeTree or ReplacingMergeTree with CDC for soft deletes. Never DELETE FROM. Mark rows with a sign column and collapse at query time.

This is the price of admission. If your workload requires real-time row updates at high frequency, ClickHouse is the wrong tool.

Tooling Comparison: What I'd Buy In 2026

Since this is a buying-guide-flavored piece, here's how I'd actually choose.

ClickHouse Cloud + ClickPipes. Best if you want to be running in a day. Cost is higher than self-hosted but you skip the "why is my Kafka consumer lagging" phase entirely. For teams under 10 engineers, this is almost always the right call.

Self-hosted ClickHouse + Debezium. Best if you already have Kafka expertise and strict data residency requirements. Plan for 2-3 weeks of infra work before you migrate a single row.

PeerDB (self-hosted). Great middle ground if you want CDC without Kafka. I've used it on three migrations in 2025-2026 with good results. Set up is straightforward, backfill parallelism is solid.

Fivetran / Airbyte. Works if you're doing batch sync and don't need sub-minute latency. Not for real-time dashboards. Not for event streams at scale. Fine for pulling Salesforce data alongside your events.

FAQ

How long does a typical migration take?
Depends on volume. Under 100GB: 2-5 days including validation. 100GB-1TB: 2-3 weeks. Over 1TB: 6-10 weeks with dual-write running in parallel. Anyone quoting you "one weekend" is either lying or your data is tiny.

Can I keep Postgres as my primary and just add ClickHouse?
Yes, and you should. This is the pattern that works. Postgres handles transactions, ClickHouse handles analytics. Sync via CDC. Don't try to eliminate Postgres.

What about JOINs between ClickHouse and Postgres?
ClickHouse has a postgresql table function that lets you query Postgres from ClickHouse directly. Useful for enriching event data with small dimension tables. Do not use it for large joins — it'll pull rows over the network.

Do I need to denormalize before migrating?
Yes, mostly. ClickHouse JOIN performance has improved a lot, but denormalized event streams still win. If your Postgres schema is 6 tables in 3NF, plan to flatten it during migration.

How much faster is ClickHouse in practice?
For aggregation queries over large tables: 30-100x typical. For point lookups: 10-30x slower. For writes: 20-50x faster. For storage: 5-10x smaller. Your mileage will vary by workload shape.

What's the biggest gotcha?
Too many small parts. If you insert fewer than ~1000 rows per insert, ClickHouse's merge process can't keep up and queries slow to a crawl. Always batch.

Is ClickHouse ACID?
Not in the Postgres sense. Single-table inserts are atomic. Cross-table transactions don't exist. If you need transactional guarantees, keep those operations in Postgres.

What happens to my Postgres extensions like pg_vector?
They stay in Postgres. ClickHouse has its own vector similarity functions now, but if you're using pgvector in production for semantic search, keep it where it is. Don't migrate vector workloads for the sake of it.

Conclusion

The best postgresql to clickhouse data migration best practices all come down to one thing: stop treating ClickHouse like a faster Postgres.

It's a different animal. It rewards denormalization, punishes small writes, and gives you absurd scan performance in return. Migrate the analytical read path first. Keep the transactional path in Postgres. Run dual-write for weeks. Validate like your job depends on it (it does, if you're the one who pushed for this).

I've been on both sides of a botched migration. The ones that worked weren't the ones with the best tools. They were the ones where the team spent a full week designing the ClickHouse schema before touching data. Boring. Unsexy. The reason we shipped a 1.8-billion-row migration in eleven hours without a single rollback.

Do the schema work first. Everything else gets easier.

---

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