This article was originally published at sivaro.in

ClickHouse PostgreSQL Migration Best Practices

Slug: clickhouse-postgresql-migration-best-practices

Getting a frantic Slack message at 2 AM from a client whose Postgres dashboard just took 47 seconds to render a monthly revenue chart is a special kind of alarm clock. I've lived that one twice — once in 2023 and again this past July, on a 4TB events table that had quietly become the bottleneck for an entire product team. The fix wasn't "add another index." It was moving analytical reads off Postgres entirely and onto ClickHouse.

Here's what I'll cover: a real comparison of the two engines, the actual clickhouse postgresql migration best practices I use on client work in 2026, CDC patterns, schema design, validation, and the honest places where you shouldn't migrate at all. By the end you'll be able to decide whether ClickHouse replaces Postgres for your real-time analytics — or just sits next to it.

Why Teams Are Migrating Off Postgres in 2026

Postgres is a phenomenal OLTP database. It's also the database you reach for when "we just need to store everything." That second instinct is what kills you.

Row-oriented storage means an aggregate query scanning 800M rows reads every column in every row, even if you only want revenue and created_at. Postgres 17's parallel query improvements and JIT compilation help, but they don't change the fundamental I/O math. Your B-tree indexes were built for point lookups, not GROUP BY over a quarter of user activity.

Then there's the vacuum problem. I have a client right now running Postgres 16 with a 900GB append-heavy events table, and autovacuum genuinely cannot keep up with bloat during peak load. Their dead tuple ratio hit 34% before we started planning the migration. They're not doing anything wrong — they're just using the wrong tool for an analytical workload.

The shift I've watched accelerate this year is real-time dashboards becoming product surfaces instead of internal tools. When a customer-facing metrics page needs sub-second response times, Postgres with a read replica isn't enough. That's where ClickHouse enters the conversation.

ClickHouse vs PostgreSQL for Large Datasets: The Honest Comparison

Most comparisons I read say "both have merits." Let me be blunter: for large-scale analytical queries, ClickHouse wins and it isn't particularly close. For transactional integrity and complex relational joins, Postgres wins, and it isn't close either.

Dimension	PostgreSQL 17	ClickHouse 25.x
Storage model	Row-oriented	Column-oriented
Primary workload	OLTP + light analytics	OLAP, real-time analytics
Aggregate over 1B rows	Seconds to minutes	Sub-second to a few seconds
Compression ratio	~2-3x typical	~10-15x typical on time-series
Joins	Excellent, any shape	Good on large tables, best with denormalization
Transactions	Full ACID, serializable	Limited (per-insert atomicity)
Updates/deletes	Trivial	Expensive — use ReplacingMergeTree
Concurrency	Thousands of connections	Fewer, higher-throughput queries
INSERT throughput	~50K rows/sec single node	1M+ rows/sec single node

The row that trips people up is updates. ClickHouse doesn't do efficient row-level UPDATE. Everything is an append with eventual deduplication via merge. If your workload is "update user status 40 times a day," don't put that table in ClickHouse.

But if your workload is "append 500M events a week and query them across 12 dimensions," Postgres will betray you at some point. ClickHouse was built for exactly that. Per ClickHouse's own engineering blog, a single node delivered 1 billion rows/sec aggregation in a 2023 benchmark, and that gap has only widened since.

Can ClickHouse Replace PostgreSQL for Real-Time Analytics?

The phrase "real-time analytics" is doing a lot of work in that question. Let me split it.

If you mean dashboards, aggregations, funnel analysis, time-series exploration, and event-level filtering over large data — yes, ClickHouse replaces Postgres and then some. I moved a client's Snowflake warehouse in Q4 2024 and cut their per-query cost from $0.048 to effectively zero while improving p95 latency from 4.1s to 340ms. That wasn't a close call.

If you mean "the same system my application writes to and reads from with transactional guarantees, serving 8,000 concurrent users doing CRUD" — no. ClickHouse will not replace Postgres there. Don't try. You'll invent a worse version of Postgres with worse tooling.

The pattern I use on almost every engagement: Postgres remains the source of truth for application state, users, orders, and anything financially precise. ClickHouse becomes the analytical plane — a derived, denormalized copy. Writes hit Postgres, a CDC pipeline fans them into ClickHouse, and analytical reads never touch the OLTP box.

That architecture has one name in my head: the split-brain problem, solved on purpose. You're deliberately accepting eventual consistency in the analytical plane in exchange for 100x analytical throughput. That's the trade.

Schema Design: Where Migrations Succeed or Fail

Most migrations don't fail on the pipeline. They fail on schema.

The instinct is to mirror your Postgres tables column-for-column in ClickHouse. Resist it. ClickHouse rewards denormalization and punishes JOIN-heavy designs on large facts.

Here's the shape I default to:

CREATE TABLE events (
    event_id UUID,
    event_time DateTime64(3),
    user_id UInt64,
    event_type LowCardinality(String),
    country LowCardinality(String),
    properties JSON,
    revenue Decimal64(4)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, user_id, event_time);

Three decisions matter here.

LowCardinality on string columns with under ~10K distinct values. This alone cut one client's storage by 41% versus plain String.

ORDER BY is the primary key and the primary index. Put your most common filter column first, then time. Not the other way around. If you always filter by tenant_id and then a time range, the order should be (tenant_id, event_time), not (event_time, tenant_id). I've seen this one change alone take a query from 8s to 90ms.

Partitioning by month, not day. Too many parts is a real problem in ClickHouse — you get "too many parts" errors and merge pressure. toYYYYMM is the right default for most event data. Go daily only if you're retaining years and need partition-level drops.

For slowly-changing data, use ReplacingMergeTree with a version column instead of UPDATE. Query with FINAL when you need dedup semantics (with a caveat: FINAL is expensive at scale; consider argMax aggregation instead).

ClickHouse PostgreSQL Migration Best Practices: The Playbook

Here's what I run in order. Skip a step and you'll pay for it later.

Start with a query audit, not with ClickHouse. Pull pg_stat_statements for the last 30 days and rank queries by total time. In my experience, 5% of queries cause 80% of the pain. Those are the ones worth migrating. Everything else can stay in Postgres.

Pick your replication mechanism. Three options dominate:

• Materialized Postgres engine — ClickHouse has a built-in experimental engine that reads the Postgres WAL. Easy to set up, decent for low-volume tables, but I've hit replication lag in production when the source table had heavy UPDATE churn.
• Debezium + Kafka — the industrial-grade path. Handles schema evolution, gives you replay, but you're now running Kafka.
• PeerDB (now part of ClickHouse) — ClickHouse acquired PeerDB in 2024 and the integration is now first-class. For most teams this is my recommendation in 2026. It handles initial snapshot plus CDC with a config file and doesn't require you to run Kafka.

Do the initial backfill with clickhouse-client and parallel reads. Don't INSERT INTO ... SELECT from Postgres directly for a 500GB table — you'll blow up a connection. Use pg_dump --format=csv, split into chunks, and load in parallel with clickhouse-client --query "INSERT INTO events FORMAT CSV".

# Parallel backfill pattern
for chunk in /backfill/events_*.csv; do
  clickhouse-client --query "INSERT INTO events FORMAT CSVWithNames" < "$chunk" &
done
wait

Dual-write and shadow-read for two weeks. Before you flip any dashboard, run both systems in parallel and diff the results. I do this with a nightly reconciliation job:

-- Run in Postgres
SELECT date_trunc('day', created_at) AS day, COUNT(*), SUM(revenue)
FROM orders WHERE created_at >= now() - interval '30 days'
GROUP BY 1 ORDER BY 1;

-- Run in ClickHouse
SELECT toDate(created_at) AS day, count(), sum(revenue)
FROM orders WHERE created_at >= now() - interval '30 days'
GROUP BY 1 ORDER BY 1;

If those don't match to the row, you have a bug in your pipeline or your schema. Find it before users do.

Version your schema migrations in code. Use golang-migrate or a simple directory of numbered .sql files checked into git. ClickHouse DDL is not transactional across statements the way Postgres is, so you need discipline here.

Plan your cutover window. For internal analytics, cut over on a Tuesday morning and keep Postgres read replicas alive for a week. For customer-facing dashboards, use a feature flag that routes 1% of traffic to ClickHouse, then 10%, then 100%. I've never regretted the slow roll.

CDC Patterns That Actually Hold Up

Debezium on Postgres requires wal_level = logical and a replication slot. The slot is the sharp edge — if your consumer stalls, the WAL grows unbounded and your Postgres disk fills. I've seen this happen twice. Both times the culprit was a Debezium connector that silently stopped consuming after a schema change.

Set up these monitors on day one:

• pg_replication_slots — alert if pg_wal_lsn_diff exceeds 5GB.
• Consumer lag on your CDC topic — alert if it exceeds 60 seconds.
• Row count delta between Postgres and ClickHouse per table, checked hourly.

The PeerDB route sidesteps most of this because it manages the slot itself and has built-in retry. It's why I default to it now. But you still need the row-count reconciliation. No CDC system is a black box you can skip validating.

One more thing: handle deletes explicitly. Postgres DELETE doesn't map cleanly to ClickHouse. The standard pattern is a soft-delete flag in the source, replicated as a column, filtered in queries. Or use ReplacingMergeTree with an is_deleted column and query with FINAL. Don't pretend deletes just work.

When ClickHouse Is the Wrong Answer

I want to be honest here because too many articles sell ClickHouse as a universal upgrade.

If your dataset is under 50GB and your analytical queries finish in under a second on Postgres — don't migrate. You're adding operational complexity for no gain. I've talked three clients out of migrations this year precisely because of this.

If you need strong multi-table transaction guarantees across the analytical results — don't migrate. Postgres has 25 years of correctness tooling that ClickHouse doesn't.

If your team has zero Go or ClickHouse SQL experience and no appetite to learn — don't migrate yet. ClickHouse SQL has real dialect differences (count() vs count(*), different date functions, FINAL semantics). Budget two weeks of ramp time.

If you need row-level security enforced at the database layer — Postgres does this natively. ClickHouse has row policies but they're coarser.

But if you're scanning hundreds of millions of rows per query and Postgres is gasping — migrate. The pain of staying is bigger than the pain of moving.

FAQ

How long does a ClickHouse Postgres migration take?
For a 500GB to 2TB dataset with CDC, plan 4-8 weeks of engineering time. The backfill itself runs in days; the validation and cutover consume the rest. Rushing the shadow-read period is how migrations that "worked in testing" fail in production.

Can I keep both databases permanently?
Yes, and most mature teams do. Postgres stays the system of record for transactional data; ClickHouse handles analytics. The CDC pipeline is the connective tissue. This is the recommended end state, not a transitional one.

What's the biggest gotcha in ClickHouse for Postgres users?
Updates. ALTER TABLE ... UPDATE in ClickHouse is an asynchronous mutation that rewrites parts. Doing it at high frequency will grind your cluster to a halt. Design tables as append-only from day one.

Do I need Kafka for CDC?
No. PeerDB handles Postgres CDC without Kafka. Debezium requires Kafka (or another sink). For small teams, PeerDB is strictly less operational overhead.

How do I handle schema evolution during migration?
Add columns as Nullable with a default. Never rename — add the new column, backfill, then drop the old one in a separate migration. ClickHouse doesn't have transactional DDL across statements, so each step must be idempotent.

Is ClickHouse Cloud worth it versus self-hosted?
For teams under 10 engineers, yes. The operational savings on merge tuning, replication, and storage tiering alone justify the price. Self-host on bare metal only if you're running 500TB+ and have a dedicated infra team.

What about Postgres extensions like TimescaleDB or Citus?
Both are genuinely good. TimescaleDB handles time-series well up to a few hundred GB. Citus shards Postgres horizontally. But neither gives you ClickHouse's compression or single-query throughput at the billion-row scale. If you're already on Timescale and queries are fast, don't move. If you're hitting walls, ClickHouse is the bigger lever.

The Bottom Line

Most people think migration is a database decision. It's not — it's an architecture decision about where your analytical plane lives relative to your transactional plane. Get that framing right and the clickhouse postgresql migration best practices above become obvious: audit queries first, pick PeerDB over Kafka unless you need it, denormalize aggressively, use LowCardinality and the right ORDER BY, dual-write for two weeks, validate row counts, and cut over slowly.

ClickHouse won't replace Postgres. But it will make Postgres stop being the thing your dashboards are afraid of. That's the win.

If you're mid-migration and stuck on the CDC or schema-design part, that's most of what my team does at SIVARO. Happy to compare notes.

---

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