12 — Replication and High Availability

"Replication just mirrors stuff" was my high-availability model, and it fused two mechanisms with different jobs. The framing that untangled it: there are two replication mechanisms with different units of transfer — streaming replication ships WAL bytes for a byte-identical standby, logical replication ships row-level changes for selective, cross-version copying — and high availability is a separate concern, solved by a failover controller that watches the primary and promotes a replica when it dies [1][2][3]. Once I separated the replication mechanism from the HA controller, the tooling map became legible.

Two replication mechanisms

Streaming replication is physical [1]. The primary ships WAL segments as they're generated; the standby applies them, producing a byte-identical copy of the entire cluster. It's the mechanism behind hot standbys (read-only replicas that serve queries) and the foundation of most HA setups. Constraints: same major version on both ends, entire cluster (can't pick one table), and the standby is read-only (unless promoted to primary).

Logical replication is a different unit of transfer — it ships row-level changes (INSERT/UPDATE/DELETE) [2]. The source defines a publication (a set of tables and change types); the target subscribes. Because it's row-level, it crosses version boundaries (great for major-version upgrades with near-zero downtime), selects specific tables, and the subscriber can accept its own writes (enabling multi-master-ish patterns). The cost: more overhead per change, and it doesn't replicate schema — the target schema must already exist.

High availability: the failover problem

Replication gives me a copy; high availability is the problem of detecting primary failure and promoting a replica without operator intervention [3]. The challenge is split-brain — two nodes both thinking they're primary — which is why HA needs a consensus layer.

The de facto solution is Patroni [4]. Each Postgres instance runs a Patroni sidecar; the sidecars use a distributed key-value store (etcd, Consul, or ZooKeeper) to elect a leader and store cluster state. When the primary fails, the consensus store notices, and Patroni promotes the healthiest replica to primary automatically, then reconfigures clients. The roadmap's alternatives — Stolon, repmgr, PAF — solve the same problem with different architectures; Patroni is the most widely deployed.

Connection pooling: PgBouncer and friends

A separate but adjacent concern is connection pooling [5]. Because Postgres uses a process per connection, thousands of client connections exhaust resources. PgBouncer sits between clients and Postgres, maintaining a small pool of real backend connections and multiplexing many client connections through them. The pooling modes trade off correctness for density:

• Transaction pooling — a backend is assigned only for the duration of a transaction. Highest density, but breaks session-level state (prepared statements, temporary tables, SET).
• Session pooling — a backend is held for the whole client session. Safe, but less efficient.
• Statement pooling — per-statement. Only works if transactions are never used across statements.

Alternatives like Pgpool-II add load balancing and query routing on top of pooling, and Odyssey is a multithreaded option for very high scale [6]. For most setups, PgBouncer in transaction-pooling mode in front of a primary-plus-replicas cluster is the standard.

Load balancing reads

With a primary and read-only replicas, the next question is routing writes to the primary and reads to replicas [7]. HAProxy can health-check each Postgres instance and route connections, sending writes to the primary and distributing reads. Consul and Keepalived provide service discovery and virtual-IP failover respectively, so clients always reach a healthy node [8][9]. The full stack for a self-hosted HA cluster is often: Patroni (failover) + etcd (consensus) + PgBouncer (pooling) + HAProxy (routing) — each layer with one job.

How I use this

For anything production, the default is: a primary plus at least one streaming-replica standby, Patroni + etcd automating failover, and PgBouncer in front to protect the backend process count. Reads that tolerate slight staleness go to the standby via HAProxy. Logical replication I reserve for specific jobs — major-version upgrades with minimal downtime, or feeding a subset of tables into a separate analytics database. And I treat HA testing as mandatory: a planned failover exercise (kill the primary, watch Patroni promote) is the only proof the setup actually works. Replication gives you a copy; the failover controller is what makes "available" true.

References

[1] PostgreSQL Wiki, "Streaming Replication," 2024. [Online]. Available: https://wiki.postgresql.org/wiki/Streaming_Replication

[2] PostgreSQL Global Development Group, "Logical Replication," 2024. [Online]. Available: https://www.postgresql.org/docs/current/logical-replication.html

[3] Kinsta, "PostgreSQL replication," 2024. [Online]. Available: https://kinsta.com/blog/postgresql-replication/

[4] Zalando, "Patroni," GitHub, 2024. [Online]. Available: https://github.com/zalando/patroni

[5] PgBouncer, "PgBouncer," 2024. [Online]. Available: https://www.pgbouncer.org/

[6] Yandex, "Odyssey," GitHub, 2024. [Online]. Available: https://github.com/yandex/odyssey

[7] HAProxy, "HAProxy," 2024. [Online]. Available: https://www.haproxy.org/

[8] HashiCorp, "Consul," 2024. [Online]. Available: https://www.consul.io/

[9] Keepalived, "Keepalived," 2024. [Online]. Available: https://www.keepalived.org/