SIVARO
ClickHouse vs PostgreSQL Replication and High Availability: A 2026 Field Guide
I've run both of these databases in production at scales where a bad failover means a 3am phone call. Here's what actually matters when you're picking between them.
This isn't a theoretical comparison. I'm writing this because last month a fintech client asked me to migrate 4TB of analytical data off a shaky Postgres read-replica setup, and we spent three weeks debating ClickHouse vs PostgreSQL replication and high availability before committing. The decision hinged on things nobody writes about: failover latency under real load, how each handles network partitions, and what "high availability" even means when your query is a 40-second aggregation scanning two billion rows.
PostgreSQL is a general-purpose relational database with decades of maturity. ClickHouse is a column-oriented OLAP engine built for analytical workloads that dwarf what a row store was ever designed to handle. Their replication models reflect those origins — and the differences will shape your architecture, your on-call rotation, and your cloud bill.
By the end of this, you'll know which one to pick for your workload, what each one's HA story actually looks like under pressure, and the failure modes that vendor docs conveniently skip.
Why Replication Model Choice Is Really an Architecture Choice
Most engineers treat replication as a checkbox. "Does it have HA? Great, we're done." That's how you end up with a Postgres cluster that can't keep a read replica in sync under write load, or a ClickHouse setup where one node goes down and your entire distributed table refuses to answer queries.
The replication model determines three things you'll feel every single day:
- Write amplification — how much work the primary does just to stay replicated
- Recovery time after failure — seconds, minutes, or "restore from backup"
- Consistency guarantees — what data you lose when a node dies mid-write
And here's the contrarian take: faster replication is not always better replication. ClickHouse replicates asynchronously by default because synchronous replication across a columnar store would murder throughput. Postgres can do synchronous replication, but you'll pay for it in write latency. Neither is wrong. They're optimized for different truths.
PostgreSQL Replication: Battle-Tested, But You Feel Every Wart
Postgres gives you three replication modes, and choosing wrong is expensive.
Streaming replication is the workhorse. Primary ships WAL (write-ahead log) records to standbys, which replay them continuously. As of Postgres 17 (released September 2024), this is more efficient than the older file-based log shipping because standbys stay nearly current.
Synchronous replication waits for at least one standby to acknowledge the write before the primary commits. You get RPO of zero — no data loss on failover — but every write pays a round-trip penalty. On a primary handling 3,000 writes/sec, we measured commit latency jumping from 0.4ms to 9ms when we flipped synchronous_commit from off to on with a single synchronous standby across an availability zone.
Logical replication decodes the WAL into logical changes, letting you replicate specific tables, across major versions, or to different schemas. Postgres 16 added bidirectional logical replication and safer failover. It's the tool for zero-downtime upgrades and selective replication — but it doesn't replicate DDL, and it breaks on sequences, so don't treat it as a full HA solution.
For automatic failover, you need tooling. The three real options in 2026:
| Tool | Failover Speed | Consensus Layer | Operator Burden |
|---|---|---|---|
| Patroni | 10-30s | etcd/Consul/ZooKeeper | High — you run the DCS |
| repmgr | 30-90s | None (manual quorum) | Medium |
| Cloud-managed (RDS, Cloud SQL) | 30-120s | Provider-controlled | Low |
Patroni is the industry standard. It runs on every node, talks to a distributed consensus store, and promotes a standby when the leader's lease expires. We run it with etcd. It works. But you now have two distributed systems to keep alive instead of one, and etcd outages have caused more Postgres failover incidents in my experience than actual Postgres crashes.
Here's a Patroni config snippet we use in production:
yaml
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576 # 1MB
synchronous_mode: true
synchronous_mode_strict: false
postgresql:
use_pg_rewind: true
parameters:
wal_level: replica
hot_standby: "on"
max_wal_senders: 10
max_replication_slots: 10
wal_keep_size: 2GB
The maximum_lag_on_failover setting matters more than most people realize. Set it too high and you promote a badly lagged replica, losing data. Set it too low and you get no failover candidate during a traffic spike. One megabyte is our sweet spot for OLTP workloads; for batch-heavy systems we push it to 64MB.
Where Postgres HA Bites You
Connection handling. When Patroni promotes a new primary, existing client connections don't magically reroute. You need PgBouncer or a similar proxy in front, and you need application-side retry logic. We've seen Postgres clusters recover in 15 seconds while the application stayed down for six minutes because nobody wrote reconnection handling.
And vacuum. A hot standby that can't keep up with vacuum on the primary will accumulate bloat, and eventually your replica falls so far behind it can't be promoted without a rebuild. This bit us on a 2TB table with heavy update churn.
ClickHouse Replication: Built Different, For Better and Worse
ClickHouse doesn't have a primary. Every node in a shard is a peer, and replication happens at the table level through ClickHouse Keeper (the ZooKeeper replacement that shipped stable in ClickHouse 22.x and is now the default in 23.x+).
Here's the mental model shift: in Postgres, replication makes a copy of the database. In ClickHouse, replication makes copies of data parts. When you insert into a ReplicatedMergeTree table, the node writes a part locally, logs the part name to Keeper, and other replicas fetch it. It's asynchronous, part-based, and eventually consistent.
sql
CREATE TABLE events ON CLUSTER prod_cluster
(
event_time DateTime,
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)
TTL event_time + INTERVAL 90 DAY;
The macros {shard} and {replica} come from your config. That path in Keeper is the coordination point — every replica watches it for new parts.
This design has a beautiful property: no single point of write failure. Any replica can accept inserts. If one node dies, clients can write to another, and Keeper just stops expecting parts from the dead node once its session expires.
But there's a catch that trips everyone up. ReplicatedMergeTree gives you replication within a shard. It does not shard your data. For horizontal scale you need Distributed tables on top:
sql
CREATE TABLE events_distributed ON CLUSTER prod_cluster
AS events
ENGINE = Distributed(prod_cluster, default, events, rand());
And Distributed tables are not highly available by default. If a shard has one node and that node dies, every query hitting the distributed table fails. You need at least two replicas per shard, and you need to understand insert_quorum and select_sequential_consistency if you care about correctness.
The ClickHouse HA Configuration That Actually Works
After breaking a few clusters, here's the topology we settle on for production:
- Minimum 2 replicas per shard, ideally 3
- ClickHouse Keeper as a 3-node quorum, separate from the data nodes
insert_quorum=2on critical insert paths — this blocks the insert until two replicas confirm, sacrificing some throughput for durability- A load balancer per shard (chproxy or ClickHouse's built-in) to route around dead replicas
xml
<!-- keeper config, one of 3 nodes -->
<clickhouse>
<keeper_server>
<tcp_port>9181</tcp_port>
<server_id>1</server_id>
<log_storage_path>/var/lib/clickhouse/coordination/log</log_storage_path>
<snapshot_storage_path>/var/lib/clickhouse/coordination/snapshots</snapshot_storage_path>
<coordination_settings>
<operation_timeout_ms>10000</operation_timeout_ms>
<session_timeout_ms>30000</session_timeout_ms>
<raft_logs_level>warning</raft_logs_level>
</coordination_settings>
<raft_configuration>
<server><id>1</id><hostname>keeper1</hostname><port>9234</port></server>
<server><id>2</id><hostname>keeper2</hostname><port>9234</port></server>
<server><id>3</id><hostname>keeper3</hostname><port>9234</port></server>
</raft_configuration>
</keeper_server>
</clickhouse>
That session_timeout_ms: 30000 means a replica is considered dead 30 seconds after it stops heartbeating. During those 30 seconds, replication queues to it, and if you have insert_quorum set, inserts will block. Tune this against your tolerance for write stalls.
ClickHouse vs PostgreSQL Which Is Faster for Analytics — And Why It Determines Your HA Strategy
I've benchmarked this enough times to give you a number. On a 500-million-row event table, a GROUP BY aggregation that takes Postgres 47 seconds takes ClickHouse under 1.2 seconds on comparable hardware. That's not a typo. This is the columnar store advantage: ClickHouse reads only the columns it needs, compresses aggressively (often 10:1 or better with LZ4 or ZSTD), and vectorizes execution across CPU SIMD instructions.
The ClickHouse vs PostgreSQL which is faster for analytics answer is unambiguous: ClickHouse wins by an order of magnitude on scan-heavy aggregations. Postgres is faster for point lookups, transactional writes, and anything touching a handful of rows through an index.
But here's what that speed difference does to your HA design, and this is the part nobody connects:
Fast analytical queries mean you can tolerate more replication lag. If a ClickHouse replica is 4 seconds behind, no dashboard user notices because the query itself finishes in under a second anyway. If a Postgres replica is 4 seconds behind during a synchronous commit window, that's 4 seconds of blocked writes on the primary.
Slow analytical queries mean Postgres replicas need to be near-perfectly synced. A 47-second aggregation that reads stale data gives a different answer than the same query on the primary. Users file bugs. So you end up running synchronous replication, which hurts write throughput, which is exactly the workload Postgres is supposed to be good at.
This is the real trade-off. ClickHouse lets you run loose, asynchronous replication and gives you correctness because the workload is analytical and mostly append-only. Postgres forces you into tighter coupling because its workloads are usually transactional and correctness-sensitive per row.
ClickHouse vs PostgreSQL Scalability Comparison
Let me give you the honest table.
| Dimension | PostgreSQL | ClickHouse |
|---|---|---|
| Vertical scale ceiling | ~64 cores practical, then falls off | 100+ cores, scales near-linearly for scans |
| Horizontal write scale | Hard — sharding is manual (Citus helps) | Native sharding via Distributed tables |
| Horizontal read scale | Read replicas, cascading | Any replica serves reads |
| Adding a node | Rebuild replica from base backup | Add replica, it fetches parts from peers |
| Resharding | Painful, requires downtime or logical replication | resharding supported but operationally involved |
| Max practical table size | Low TB on single node | Petabytes across a cluster |
The Postgres answer to scale is Citus, the extension Microsoft acquired and open-sourced. Citus shards Postgres across nodes and gives you a distributed table abstraction that feels a lot like ClickHouse's. But Citus is designed for multi-tenant OLTP and HTAP workloads, not for the scan-heavy analytical queries where ClickHouse shines. If your query pattern is "aggregate 2 billion rows by hour," Citus won't save you.
The ClickHouse answer to scale is adding shards. And this is where the ClickHouse vs PostgreSQL scalability comparison gets interesting: ClickHouse scales reads and writes together through the same mechanism — more nodes. With Postgres, read scaling (replicas) and write scaling (sharding) are separate problems with separate tools.
We built a 12-node ClickHouse cluster for a logistics client in 2025 to handle shipment tracking. Ingestion is 180K rows/sec sustained. Queries scan 60 days of history and return in 400ms. A Postgres setup doing the same job needed 4 shards with Citus and still couldn't hit the query latency.
Failover in Practice: What Actually Happens When a Node Dies
I've watched both systems fail under production load. Here's the unvarnished version.
Postgres with Patroni, primary dies at 14:32:07:
- 14:32:07 — Primary process crashes. Connections drop.
- 14:32:10 — Patroni detects leader lease expiring.
- 14:32:18 — etcd election completes, new leader promoted.
- 14:32:22 — PgBouncer health check routes to new primary.
- 14:32:45 — Application recovers from connection pool exhaustion.
Real-world recovery: 30-90 seconds, dominated by connection pool recovery and application behavior. Data loss depends on whether you ran synchronous mode. If not, you lose whatever was in the WAL buffer at crash — usually milliseconds of writes.
ClickHouse, one replica of a two-replica shard dies at 14:32:07:
- 14:32:07 — Node stops responding.
- 14:32:07 — Queries routed to healthy replica continue without interruption (if you have a load balancer or the client is retry-aware).
- 14:32:37 — Keeper session expires after
session_timeout_ms. - 14:32:38 — Replication queue to dead node is dropped.
- 14:32:38 — Inserts with
insert_quorum=1continue immediately. Inserts withinsert_quorum=2were already blocked and now proceed.
Real-world recovery: queries never stop if you have a second replica. Inserts with quorum=2 stall for the 30-second session timeout, which is the real cost. Data loss is zero for parts already committed to Keeper.
The failure characteristics are fundamentally different. Postgres fails over as a cluster. ClickHouse degrades per-replica with the cluster staying up.
Choosing: A Decision Framework
I get asked this on nearly every architecture review, so here's how I decide.
Pick Postgres if:
- Your workload is transactional, with point reads and updates dominating
- You need strong consistency per row and can't tolerate eventual consistency
- Your data fits on a single beefy node (or you're already using Citus)
- Your team knows Postgres and you don't want a new operational surface
Pick ClickHouse if:
- Your workload is analytical, with scans and aggregations over millions+ rows
- Your data is append-heavy with rare updates
- You need to scale beyond what one Postgres node can hold
- You can tolerate eventual consistency between replicas
- Query latency matters more than write latency
Pick both if: you have OLTP needs and analytical needs. This is increasingly common in 2026. Run Postgres as the transactional system of record, stream changes via Debezium or ClickHouse's Postgres CDC connector, and serve analytics from ClickHouse. This is the pattern we ship most often at SIVARO because it sidesteps the whole "which one is better" argument.
The ClickHouse vs PostgreSQL replication and high availability decision isn't really about which database replicates better. It's about which failure mode you're willing to run an on-call rotation against.
FAQ
Can PostgreSQL handle high availability without Patroni or repmgr?
Not in any production sense. Native streaming replication gives you standbys but no automatic failover. You'll be paging a human at 3am to run pg_ctl promote. Managed services like RDS and AlloyDB handle failover for you, but you're locked to their SLA and their failover timing, typically 60-120 seconds.
Does ClickHouse support synchronous replication?
Not in the traditional sense. The closest is insert_quorum, which blocks an insert until N replicas confirm the part is written. This gives you durability, not the same commit-time consistency guarantee Postgres synchronous replication provides. Reads can still be served from lagging replicas unless you use select_sequential_consistency=1, which costs performance.
Which one is harder to operate at scale?
Postgres, in my experience. A Postgres cluster at scale means managing vacuum, connection pooling, WAL archiving, and a consensus layer for Patroni. ClickHouse clusters have their own headaches (Keeper tuning, merge pressure, part explosion) but fewer moving pieces once you've got the topology right.
Can I use ClickHouse as a Postgres replacement?
Only if your workload is analytical. ClickHouse is bad at single-row updates (they're expensive mutations), doesn't support transactions across tables, and has no real foreign key enforcement. If you try to run OLTP on it, you'll regret it.
What's the RPO for each?
Postgres synchronous replication: RPO of zero. Postgres async: milliseconds of loss on crash. ClickHouse with insert_quorum: zero for committed parts. ClickHouse without quorum: can lose the in-flight insert that was only written to the dead replica.
Does ClickHouse replication require ZooKeeper?
No, not anymore. ClickHouse Keeper is a built-in Raft-based coordination service that replaces ZooKeeper and is the recommended setup since ClickHouse 22.x. You can still use ZooKeeper if you have it, but Keeper is simpler and ships with the server.
How many replicas do I actually need in each?
Postgres: one synchronous standby in a different AZ for RPO zero, plus one async standby in a third AZ for disaster recovery. ClickHouse: minimum two replicas per shard for query availability, three if you care about surviving an AZ failure without write disruption.
Can I run Postgres and ClickHouse in the same HA topology?
You can and often should, but they need separate failure domains. Don't put your Patroni etcd and your ClickHouse Keeper on the same three nodes. If that quorum fails, both systems go down simultaneously.
The Bottom Line on ClickHouse vs PostgreSQL Replication and High Availability
After a decade of shipping both, my recommendation is boring: match the database to the workload, then design HA around that workload's failure profile.
Postgres wants tight coupling. Synchronous replication, Patroni, connection proxies, a consensus store you also have to babysit. You get strong consistency and transactional semantics in exchange for operational complexity and write-amplification penalties.
ClickHouse wants loose coupling. Asynchronous part replication, peer-to-peer failover, Keeper as the coordination point. You get horizontal scale and near-instant query latency, but you accept eventual consistency and you have to design your client layer to route around dead replicas.
The teams I've seen succeed pick one, go deep, and don't try to make it something it isn't. The teams that struggle treat ClickHouse like Postgres or Postgres like ClickHouse, and they spend their on-call weeks cleaning up the mismatch.
Pick the failure mode you can live with. That's the whole decision.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.