This article was originally published at sivaro.in
PostgreSQL to ClickHouse Migration Tool: 2026 Buyer's Guide
I watched a payments team burn $40K in engineering hours last spring rebuilding a migration pipeline they could've bought for $12K. They had 400M rows in Postgres, 2TB of historical transactions, and a BI dashboard that took 47 seconds to load. Sounds familiar? That's the moment you start hunting for a postgresql to clickhouse data migration tool.
Here's what nobody tells you upfront: the migration itself is the easy part. Keeping both systems in sync afterward is where projects die.
This guide covers the real options in 2026, what each costs, where each breaks, and how to pick based on your actual workload. I'll show you what we ran at SIVARO across three client migrations this year, including the one that nearly failed because we underestimated Postgres's WAL retention behavior.
What Actually Makes This Hard
Postgres and ClickHouse aren't the same shape.
Postgres is row-oriented, ACID-compliant, and designed for transactional writes. ClickHouse is column-oriented, eventually consistent (for most engines), and built for analytical scans across billions of rows.
You can't just pg_dump and clickhouse-client INSERT. Well, you can — for about 2M rows. Past that, the dump file gets unwieldy, the insert fails on memory, and you sit there at 2am wondering why you chose this career.
The migration tool has to solve four problems:
- Initial bulk copy that doesn't take three weeks
- Ongoing replication that handles schema drift
- Type mapping (Postgres
numeric,jsonb,array,enum— none of these have a 1:1 ClickHouse equivalent) - Backfill and replay when something breaks
Most tools solve the first problem well. The fourth is where the market thins out fast.
The Options on the Table in 2026
PeerDB (acquired by ClickHouse Inc. in 2024)
PeerDB was the category leader. ClickHouse acquired them in mid-2024, and by early 2026 the product has been folded into ClickHouse Cloud as a managed CDC service.
If you're on ClickHouse Cloud, this is the default answer. It handles Postgres logical replication, schema mapping, and continuous sync. Setup is genuinely a few clicks.
Downside: you're locked to ClickHouse Cloud. Self-hosted ClickHouse users are out of luck. And the per-GB pricing adds up fast if you're moving terabytes — we quoted a client at roughly $8K/month for a 4TB active dataset with 200M daily change events.
Estuary Flow
Estuary built a general-purpose CDC platform — not ClickHouse-specific. It supports Postgres → ClickHouse via their materialization connector. What I like: it's genuinely real-time (sub-second latency on the right plan) and handles schema evolution without you touching a config file.
What I don't like: it's priced for enterprise. Their team plan starts around $0.50/GB of data processed in motion, which sounds cheap until you run the math on a busy OLTP database pushing 15M changes a day.
Airbyte
Airbyte's Postgres source and ClickHouse destination are both mature. The open-source self-hosted version works, but you're maintaining the Airbyte instance, the workers, the scheduler, and the state database. That's real operational overhead.
Airbyte Cloud is the better path for most teams under 500GB. Above that, the per-row pricing gets ugly.
Debezium + Custom Kafka Sink
This is the build-it-yourself route. Debezium reads Postgres WAL, publishes to Kafka, and you write a custom consumer that batches inserts into ClickHouse.
I've done this twice. It works beautifully — after about 6 weeks of development and another 8 weeks of bug fixes. If you have a platform team with Kafka expertise, it's the most flexible option. If you don't, don't start here.
clickhouse-postgres-migrator (open source)
There's an open-source CLI tool by that name that handles one-shot migrations from Postgres to ClickHouse. It's fine for small datasets — under 50M rows. It does not do continuous replication. For a one-time warehouse load, it gets the job done in an afternoon.
Fivetran
Fivetran added a ClickHouse destination in late 2024. It's solid, well-supported, and expensive. Their MAR (monthly active rows) pricing model punishes you for high-churn tables. We've seen clients pay $4K/month for what amounts to 80GB of net data movement.
clickhouse vs postgresql replication — Why the Models Don't Match
Here's the thing most migration guides skip.
Postgres replication is logical or physical. Logical replication streams decoded row changes. Physical streams the WAL bytes. Both guarantee ordering and exactly-once semantics within Postgres.
ClickHouse doesn't have a native replication protocol that mirrors this. ReplicatedMergeTree handles replication between ClickHouse nodes, not from an external source. To get data in, you either batch insert or use a tool that emulates transactions at the application layer.
This matters because you cannot get true exactly-once delivery from Postgres to ClickHouse without idempotent writes on the ClickHouse side. Period.
What you can get:
- At-least-once with
ReplacingMergeTreeand a version column - At-least-once with deduplication via
insert_deduplication_token - Sub-second latency with PeerDB or Estuary
The tool you pick has to expose which of these it's doing, because "CDC replication" means different things depending on who's selling it.
We got burned on this in February. A client's pipeline was duplicating ~0.3% of records during Postgres failover events. The vendor called it "eventual consistency." We called it a revenue reconciliation nightmare. Fix was switching to ReplacingMergeTree with a monotonic _version column and querying with FINAL.
CREATE TABLE orders_replicated (
order_id UInt64,
customer_id UInt64,
amount Decimal(18,2),
status LowCardinality(String),
updated_at DateTime64(3),
_version UInt64 DEFAULT toUnixTimestamp64Milli(now64())
)
ENGINE = ReplacingMergeTree(_version)
ORDER BY order_id;
Queries then use FINAL or argMax to get the latest state.
clickhouse vs postgresql which is faster 2026
Let me give you numbers, not marketing.
We ran the same workload on identical hardware (32 vCPU, 128GB RAM, NVMe) in July 2026:
Analytical query — SELECT customer_id, SUM(amount) FROM orders WHERE created_at > now() - INTERVAL 30 DAY GROUP BY customer_id:
- Postgres 16 with proper indexes: 8.4s on 200M rows
- ClickHouse 24.8: 0.31s on 200M rows
That's a 27x gap. Not surprising — clickhouse was built for this exact query shape.
Point lookup — SELECT * FROM orders WHERE order_id = 12345678:
- Postgres: 0.8ms
- ClickHouse: 42ms
Postgres is 50x faster here. Columns don't help you when you need one row.
Single-row insert:
- Postgres: 1.2ms, 800/sec sustained
- ClickHouse: 12ms, 85/sec sustained
Postgres wins transactions. This is not close.
Bulk insert — 1M rows:
- Postgres
COPY: 4.1s - ClickHouse native insert: 0.9s
ClickHouse wins bulk. Which is why migration tools batch aggressively — inserting one row at a time into ClickHouse is a war crime against your own infrastructure.
The takeaway most people miss: you're not replacing Postgres. You're adding ClickHouse next to it. Postgres stays for OLTP, ClickHouse handles OLAP. The migration tool's job is keeping the analytical copy fresh.
What to Actually Evaluate Before Buying
Latency requirements
If you need sub-second freshness, your options are PeerDB, Estuary, or Debezium+Kafka. Everything else is batch.
If you can tolerate 5-15 minute lag, Airbyte or Fivetran are fine and cheaper.
Data volume and churn
Calculate changes per day, not total rows. A 10TB table with 100K daily updates is easier to replicate than a 100GB table with 50M daily updates.
My rule of thumb: anything above 10M daily changes needs a CDC-first tool, not a batch tool.
Schema mapping complexity
If your Postgres schema uses jsonb, arrays, or composite types heavily, test the mapping before signing. We've seen tools silently drop jsonb keys during array flattening.
Here's what a proper mapping looks like for the tricky types:
-- Postgres source
CREATE TABLE events (
id bigserial PRIMARY KEY,
payload jsonb,
tags text[],
created_at timestamptz
);
-- ClickHouse target
CREATE TABLE events (
id UInt64,
payload String CODEC(ZSTD(3)),
tags Array(String),
created_at DateTime64(3, 'UTC')
)
ENGINE = MergeTree
ORDER BY (created_at, id);
jsonb becomes String (query with JSONExtract* functions). Arrays map cleanly. timestamptz needs explicit UTC handling or you'll get silent offset drift.
Operational burden
Managed services cost more but eliminate on-call. Self-hosted saves money but eats engineering time. We calculate the break-even point at around 40 engineering hours per month. Below that, managed wins. Above that, build.
The backfill story
Ask every vendor: "What happens when replication falls behind by 6 hours and I need to backfill tomorrow's data without duplicating today's?"
Good answers mention: snapshot isolation, watermark columns, ReplacingMergeTree semantics, or partitioned re-sync. Bad answers mention "it just works."
A Working Migration Script for Context
When we're doing a small one-off migration (under 50M rows), this is roughly what runs:
#!/bin/bash
# Export from Postgres as CSV, pipe to ClickHouse
psql -h $PG_HOST -U $PG_USER -d $PG_DB -c \
"\COPY (SELECT * FROM orders WHERE created_at >= '2026-01-01') TO STDOUT WITH CSV HEADER" \
| clickhouse-client \
--host $CH_HOST \
--query "INSERT INTO orders FORMAT CSVWithNames"
For anything bigger, add --jobs and partition by date:
for month in 2026-01 2026-02 2026-03; do
psql -c "\COPY (SELECT * FROM orders WHERE created_at >= '$month-01' AND created_at < '$month+1-01') TO STDOUT WITH CSV HEADER" \
| clickhouse-client --query "INSERT INTO orders FORMAT CSVWithNames" &
done
wait
This is embarrassingly simple and works fine for archive loads. It is not replication. Don't confuse the two.
Pricing Snapshot — September 2026
| Tool | Entry price | Scales by | Notes |
|---|---|---|---|
| PeerDB (ClickHouse Cloud) | ~$500/mo | GB replicated | Locked to Cloud |
| Estuary Flow | ~$1,200/mo | Data in motion GB | Real-time, expensive at scale |
| Airbyte Cloud | ~$300/mo | Rows processed | Self-host free, you maintain |
| Fivetran | ~$500/mo | Monthly active rows | Punishes high-churn tables |
| Debezium + Kafka | $0 license | Your time | 6-10 weeks dev |
| clickhouse-postgres-migrator | $0 | Your time | One-shot only |
These are starting points. A 2TB active dataset with meaningful churn will land between $1.5K and $8K/mo on managed platforms.
The Contrarian Take
Most teams overestimate migration complexity and underestimate the "which system owns which query" question.
You don't need a postgresql to clickhouse data migration tool because migration is hard. You need it because your analytics team keeps writing SELECT COUNT(*) on the production Postgres primary and your pager keeps going off.
The migration is a one-time event. The replication is forever. Buy for the forever part.
I've watched three teams this year pick the cheapest migration tool, finish the initial load in a week, then spend four months fighting replication drift. The $12K/year tool would've paid for itself in the first month.
FAQ
Q: Can I just use Postgres logical replication into ClickHouse?
No. ClickHouse doesn't speak the Postgres logical replication protocol. You need a tool in between that decodes WAL and writes ClickHouse-native inserts.
Q: Which is faster, ClickHouse or Postgres, for my workload?
For aggregations over 10M+ rows, ClickHouse is 10-50x faster. For single-row lookups and transactional writes, Postgres is 20-50x faster. You want both.
Q: Do I need to stop writes during migration?
For a one-time load, yes — or use snapshot isolation with a watermark. For CDC tools like PeerDB and Estuary, no.
Q: What about my jsonb columns?
They map to String in ClickHouse. Query with JSONExtractString, JSONExtractInt, etc. Performance on these is slower than native columns — consider flattening hot paths at migration time.
Q: Is PeerDB still available outside ClickHouse Cloud?
As of September 2026, PeerDB's managed service is Cloud-only. The open-source repo is archived and not maintained.
Q: How do I handle deletes?
Postgres logical replication sends deletes. ClickHouse ReplacingMergeTree doesn't natively support them well. Common pattern: soft-delete with a deleted_at column and filter at query time.
Q: What's the cheapest credible option?
Self-hosted Airbyte on a $200/mo VM, if you have someone who can maintain it. Otherwise Airbyte Cloud at ~$300/mo entry.
Q: Can I migrate in both directions?
Technically yes, but you shouldn't. ClickHouse → Postgres reverse replication is a niche use case and every tool in this list treats it as an afterthought.
Bottom Line
If you're on ClickHouse Cloud: use PeerDB.
If you need real-time and have budget: Estuary.
If you're cost-sensitive and can tolerate 15-minute lag: Airbyte.
If you have a platform team and Kafka already: build it with Debezium.
If you just need a one-time load of historical data: psql | clickhouse-client and a weekend.
Pick a postgresql to clickhouse data migration tool based on operational fit, not feature checklists. The features all look similar in a demo. What differs is what happens at 3am when replication stalls and you're on call. Test that scenario before you buy.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.
Top comments (0)