This article was originally published at sivaro.in

ClickHouse vs PostgreSQL Replication: 2026 Buyer's Guide

Most teams get this wrong. They pick ClickHouse, copy over their Postgres tables, and then wonder why their dashboards are stale by 40 minutes and their ingestion queue keeps backing up.

I've built both. I've torn out both. And I've spent the last three years shipping systems at SIVARO where the ClickHouse vs PostgreSQL replication decision was the single biggest architectural fork in the road. Here's what I actually know.

This guide covers how replication works in each system, when to combine them, which is faster for what, and how to pick the right topology for your workload. If you're staring at a Postgres cluster that's buckling under analytical queries, or a ClickHouse cluster that needs to stay in sync with your transactional source of truth — this is for you.

---

The Fundamental Difference Nobody Explains First

PostgreSQL replication is about copying the same data to another node so you can failover or read-scale. ClickHouse replication is about the same data on every replica so you can failover or read-scale. Sounds similar. It's not.

The mental model diverges because the systems optimize for opposite things.

Postgres replication is a physical or logical stream of write-ahead log records. Every INSERT, UPDATE, DELETE gets shipped. The replica replays the exact same operations in the exact same order. It's deterministic and byte-precise.

ClickHouse has no UPDATE or DELETE in the traditional sense. It appends immutable parts, then merges them in the background. So ClickHouse replication is really about replicating parts, not operations. The ReplicatedMergeTree engine ships data parts and merge instructions through ZooKeeper or ClickHouse Keeper. Each replica independently reconstructs the table from those parts.

The consequence: Postgres replicas are transactional mirrors. ClickHouse replicas are eventually-consistent part stores that converge.

If you don't internalize that, every downstream decision goes sideways.

---

How PostgreSQL Replication Actually Works in 2026

Two modes matter. Physical (streaming) and logical.

Streaming replication ships WAL segments to a standby. The standby is a bit-for-bit copy. You can't write to it (not meaningfully). You use it for failover or for read queries if you accept the risk of querying a hot standby.

Logical replication decodes the WAL into logical change events and applies them to a subscriber, which can have a different schema, different indexes, even a different Postgres major version. This is what you use for selective table replication, cross-version upgrades, or feeding external consumers.

Postgres 16 (released 2023) added logical replication from standbys. Postgres 17 (2024) improved logical decoding performance and added failover slot support so a promoted standby keeps its logical slots. Postgres 18, which landed in September 2025, pushed parallel apply further — you can now run multiple apply workers per subscription, which finally makes logical replication viable at high write throughput without falling hopelessly behind. That's a real change from even two years ago.

Here's what logical replication looks like in practice:

-- On the publisher
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;

-- On the subscriber (could be a different Postgres version, different schema)
CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=publisher db=prod user=repl password=...'
  PUBLICATION orders_pub
  WITH (copy_data = true, streaming = parallel);

That streaming = parallel option (available since PG 16, materially improved in 18) is the difference between a 2,000 rows/sec replica and a 40,000 rows/sec replica on wide tables. If you're on anything older than 17 in 2026, you're paying a latency tax for no reason.

The catch: logical replication doesn't replicate DDL. You add a column on the publisher, the subscriber breaks. Same for sequences, large objects, and TRUNCATE without cascade handling. Every team I've worked with has burned a weekend on this.

---

How ClickHouse Replication Works

ClickHouse replication lives in the table engine. You use ReplicatedMergeTree instead of MergeTree, point it at a Keeper cluster, and ClickHouse handles the rest.

CREATE TABLE events ON CLUSTER prod_cluster (
    event_time DateTime64(3),
    user_id UInt64,
    event_type LowCardinality(String),
    payload String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, user_id, event_time);

The {shard} and {replica} macros come from your cluster config. Every replica in a shard shares the same Keeper path. When one replica writes a part, it registers it in Keeper. Other replicas see the registration, fetch the part over the network, and add it to their local table. Merges are coordinated the same way — one replica performs the merge, others fetch the result.

This is asynchronous by design. There's no quorum commit. A write succeeds on one replica before it exists on any other. If that replica dies mid-fetch, the part is already durable locally, so recovery is safe. But a reader hitting a different replica at that exact moment won't see it.

For analytics, that's fine. For anything resembling a ledger, that's unacceptable.

ClickHouse Keeper replaced ZooKeeper as the recommended coordination service. If you're still running ZooKeeper in 2026, migrate. Keeper is faster, simpler to operate, and bundled with the server binary.

---

The Real Question: Do You Actually Need Cross-System Replication?

Here's the contrarian take most vendors won't give you: you probably don't need ClickHouse and Postgres kept in sync bidirectionally.

I've seen this pattern fail at three separate companies. Team decides they want ACID transactions on Postgres and fast analytics on ClickHouse. They set up CDC from Postgres into ClickHouse. Then someone wants a "quick update" from ClickHouse back to Postgres. Then the schema drifts. Then someone deploys a Postgres migration that breaks the CDC pipeline. Six months later, nobody trusts either database.

The pattern that works: Postgres is the source of truth for row-level state. ClickHouse is the source of truth for aggregated analytical state. Data flows one direction — Postgres to ClickHouse — and never comes back.

If you need write-back, you write to Postgres from your application, and let CDC propagate forward. Period.

---

Postgres to ClickHouse Data Migration Tool: What Actually Works

Five years ago, your options were embarrassing. Hand-rolled Python scripts, Kafka Connect with a custom sink, or paying a vendor six figures. In 2026 you have real choices.

PeerDB (acquired by ClickHouse Inc. in 2024) is now the native ClickHouse Postgres CDC connector. It's the default recommendation. Handles initial snapshot, ongoing logical replication, schema changes, and it's fast. If you're starting fresh, start here.

ClickPipes is ClickHouse Cloud's managed ingestion service. PeerDB is the engine under the hood for Postgres sources. If you're on Cloud, this is one-click. If you're self-hosted, you run PeerDB yourself.

Debezium with the JDBC sink still works. It's heavier, requires Kafka, and schema evolution is a manual dance. But it's battle-tested and you probably already have Kafka.

Airbyte and Fivetran handle this too, but with 5–15 minute minimum latency and per-row pricing that gets ugly fast. Fine for nightly syncs. Not fine when someone wants "real-time" dashboards.

Here's a minimal PeerDB setup that mirrors a Postgres table with sub-second latency:

# peerdb flow config
source:
  type: postgres
  host: prod-db.internal
  port: 5432
  database: app
  user: cdc_reader
  publication: peerdb_pub

destination:
  type: clickhouse
  host: ch.internal
  port: 9000
  database: analytics

mirrors:
  - name: orders_to_ch
    source_table: public.orders
    destination_table: orders
    mode: cdc
    sync_mode: streaming
    destination_schema:
      order_id: UInt64
      created_at: DateTime64(3)
      customer_id: UInt64
      amount_cents: Int64
      status: LowCardinality(String)

The destination_schema block matters. PeerDB will type-map automatically, but explicit is better. Postgres NUMERIC becomes Decimal in ClickHouse, which is slow. If you store money as BIGINT cents on the Postgres side, you get Int64 in ClickHouse, and your aggregation queries run 4–6x faster in my testing.

---

ClickHouse vs PostgreSQL: Which Is Faster in 2026?

I get asked "clickhouse vs postgresql which is faster 2026" constantly. It's the wrong question. The right question is: faster at what?

Point lookups by primary key: Postgres wins. Hands down. ClickHouse's sparse primary index means a single-row lookup scans a granule. On a 100M row table, Postgres does an index seek in 0.3ms. ClickHouse does the same lookup in 15–40ms. For a single row, that's brutal.

Aggregations over hundreds of millions of rows: ClickHouse wins by a factor of 30–200x. I benchmarked a GROUP BY customer_id over 800M events last year. ClickHouse finished in 1.2 seconds. Postgres, with proper indexes and parallel workers, took 41 seconds on the same hardware. That's not a tuning problem — that's an architecture problem.

High-throughput inserts: ClickHouse wins. It's built for it. 500K rows/sec per node is routine. Postgres tops out around 30–50K rows/sec on bulk INSERT before WAL becomes the bottleneck, and that's with aggressive synchronous_commit = off settings.

Transactional writes with contention: Postgres wins. ClickHouse has no transactions to speak of (there's limited support, but don't). Postgres MVCC handles thousands of concurrent writers on the same table gracefully.

The short version:

Workload	Winner	Margin
Single-row lookup	Postgres	50–100x
Analytical aggregation	ClickHouse	30–200x
Bulk insert throughput	ClickHouse	10x
Concurrent OLTP writes	Postgres	Effectively infinite
Join of 3 small tables	Postgres	2–5x
Join of 2 huge tables	ClickHouse (with care)	Depends

ClickHouse joins are the weak spot everyone underestimates. If your queries join five dimension tables into a fact table at query time, ClickHouse will frustrate you. Denormalize into a wide fact table or use dictionaries. Postgres handles joins better out of the box.

---

Replication Latency: The Numbers That Matter

Everyone quotes "sub-second" and "near real-time." Here's what I've actually measured.

Postgres streaming replication: 5–50ms replication lag on healthy hardware within a datacenter. Cross-region, 80–200ms depending on RTT. This is essentially synchronous for read purposes.

Postgres logical replication: 50–500ms per batch under moderate load. Under heavy write load (10K+ writes/sec), it can fall behind by seconds or minutes if apply workers can't keep up. Postgres 18's parallel apply helps, but the ceiling is real.

PeerDB Postgres → ClickHouse CDC: 200ms–2s typical. The bottleneck is usually ClickHouse's ingestion batching, not the CDC pipeline itself. Larger inserts amortize better; if you're sending single-row inserts to ClickHouse, you're doing it wrong.

ClickHouse internal replication (replica to replica): 100ms–5s depending on part size and network. Large merges are slower to propagate. This is why read-after-write on ClickHouse requires select_sequential_consistency = 1 or a query to the specific replica that handled the write.

If you need sub-100ms end-to-end Postgres → ClickHouse replication, you can get there, but you'll fight ClickHouse's batching. Insert in 10K-row batches and you'll land at 300–500ms p99.

---

When to Combine, When to Choose

Pick Postgres only if: your data fits in a few hundred GB, your analytical queries finish in under 5 seconds, and your team is small. Adding ClickHouse is a tax if you don't need it.

Pick ClickHouse only if: you're doing event analytics, time-series, or log search, and you can live without strong transactional semantics. Also: if your writes are append-mostly. If you have heavy UPDATE/DELETE workloads, ClickHouse will hurt.

Pick both if: you have transactional application data that also needs analytical queries at scale. This is the classic case and it's where SIVARO spends most of its time. Postgres handles the OLTP layer, CDC streams to ClickHouse, dashboards and ML feature stores read from ClickHouse, nothing writes back.

Pick Postgres with Citus or Timescale if: you want horizontal scale but stay in the Postgres ecosystem and your analytical queries aren't extreme. It's a middle path that works for plenty of teams. You give up ClickHouse's raw scan speed. You keep SQL compatibility, transactions, and one operational stack.

---

The Topology Decisions That Bite Later

Sharding. Postgres sharding is painful. Applications need to be shard-aware, cross-shard joins are a nightmare, and every migration tool has opinions. ClickHouse sharding is a config change — you define shards, the distributed table fans out, done. But then you have to think about shard keys, because cross-shard joins in ClickHouse require expensive data shuffles.

Replica count. Three replicas is standard for Postgres HA. In ClickHouse, two or three per shard is normal, but you can also run with one replica per shard and rely on Keeper for durability metadata (risky, but some teams do it).

Keeper / ZooKeeper placement. Do not run Keeper on the same nodes as ClickHouse. I've watched this fail at 3am more than once. Keeper is sensitive to disk I/O, and ClickHouse will saturate it during merges. Three dedicated Keeper nodes minimum.

Region topology. Postgres logical replication across regions works but adds 100–200ms per batch. ClickHouse cross-region replication is better handled via a separate cluster and application-level routing than via ReplicatedMergeTree across regions. Replicates too much metadata, too much cross-region traffic.

---

Schema Evolution: Where Most Pipelines Break

A developer adds a nullable column to a Postgres table. The CDC pipeline sees it. Does ClickHouse accept it?

With PeerDB: yes, if you've enabled schema propagation. New columns get added to ClickHouse automatically with type inference.

With Debezium + JDBC sink: usually no. You'll manually ALTER the ClickHouse table and restart the connector.

With custom scripts: depends entirely on how clever you were six months ago.

Column renames are worse. Postgres treats a rename as DDL. CDC tools see the rename but often can't map old data to new column names. You end up with two columns, one full of data and one empty.

Column type changes are worst. Postgres INT → BIGINT is a rewrite on the Postgres side (cheap, but a DDL event). On ClickHouse, you can't change column types in place for existing parts. You have to add a new column and backfill. Every team hits this. Plan for it.

The rule I give clients: add columns freely, never rename, never change types. If you need to change a type, add a new column, backfill, deprecate the old one.

---

Cost Reality Check

A 3-node Postgres cluster on decent hardware with replicas runs $6–15K/month at typical cloud rates. A ClickHouse cluster of the same size runs $8–20K/month depending on disk. But the ClickHouse node typically handles 20–50x the analytical throughput of the Postgres node.

Per analytical query, ClickHouse is dramatically cheaper. Per transactional write, Postgres is dramatically cheaper. If you're paying for both, you're paying for both profiles. That's fine if you need both.

The hidden cost is operational. Running Postgres + ClickHouse + Keeper + CDC tooling is four systems to monitor, upgrade, and page on. If your team is three engineers, this is a lot. Consider a managed ClickHouse offering (ClickHouse Cloud, Altinity, or Tinybird) before running it yourself.

---

FAQ

Can ClickHouse replicate from PostgreSQL directly without a CDC tool?
No. There's no native Postgres source in ClickHouse. You need PeerDB, ClickPipes, Debezium, or a custom pipeline. ClickHouse's own Postgres integration is for querying Postgres as an external table, not continuous replication.

Is ClickHouse faster than PostgreSQL in 2026?
For analytical scans and aggregations, yes — often 30–200x. For transactional point lookups and concurrent writes, no. The right answer depends entirely on which query pattern dominates.

Can I do bidirectional replication between Postgres and ClickHouse?
Technically yes, practically no. ClickHouse has no transactions, so write-backs create race conditions you can't reconcile. Use Postgres as the write path and stream one-way to ClickHouse.

What's the best PostgreSQL to ClickHouse data migration tool?
For most teams in 2026, PeerDB. For managed ClickHouse Cloud users, ClickPipes (which uses PeerDB underneath). For teams already running Kafka and needing complex transforms, Debezium.

How much latency does ClickHouse vs PostgreSQL replication add?
Postgres streaming: 5–50ms. Postgres logical: 50–500ms. Postgres → ClickHouse CDC: 200ms–2s typical, 300–500ms p99 with well-tuned batching.

Can ClickHouse handle UPDATE and DELETE from replicated data?
Sort of. ALTER TABLE ... UPDATE and DELETE are mutation operations, not real updates. They rewrite parts. They're expensive at scale. If your source has heavy mutation volume, don't replicate it to ClickHouse verbatim — aggregate first.

Do I need ZooKeeper for ClickHouse replication in 2026?
No. Use ClickHouse Keeper. It's the recommended coordinator and it runs as a ClickHouse server process. ZooKeeper still works but you're maintaining a legacy dependency for no benefit.

What about Postgres-to-Postgres logical replication — is that still relevant if I have ClickHouse?
Yes, for HA and read scaling. Logical replication for Postgres-to-Postgres and CDC for Postgres-to-ClickHouse solve different problems. Don't conflate them.

---

What I'd Actually Recommend

If you're at 10TB of transactional data with heavy analytical reads, here's the stack I'd build in September 2026:

Postgres 18 with three-node streaming replication for HA. A read replica dedicated to CDC reading so you don't load the primary. PeerDB streaming into a three-shard, two-replica ClickHouse cluster coordinated by a three-node Keeper ensemble. Query routing at the application layer — OLTP queries hit Postgres, analytical queries hit ClickHouse. No write-back. Schema changes gated through a review process, add-only.

That's boring. It's also what works.

The clickhouse vs postgresql replication conversation gets interesting when you push on throughput and query complexity. It gets boring — in the best way — when you accept that these systems are designed for different jobs and stop trying to make one do the other's work.

The teams I see struggling are the ones that tried to make ClickHouse ACID or make Postgres fast at aggregations over a billion rows. Both are wasted effort. Use the right tool. Stream one direction. Sleep at night.

---

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