PostgreSQL in Practice #8 Replication and High Availability: Streaming Replication, Read Scaling, Failover

Infrastructure PostgreSQL Database
4 min read

Everything so far has lived inside one server. But one server has two limits: if it dies the service stops (availability), and when read load piles up there is nowhere left to grow (scaling). The standard means for both is replication — multiple servers holding the same data. Understand the mechanics and the managed-service options (the choices covered in RDS Multi-AZ vs read replicas) become legible as “products selling exactly which layer.”

The mechanism: shipping WAL #

PostgreSQL writes every change to the WAL (Write-Ahead Log), a sequential log, before committing (the basis of crash recovery — and the raw material for the next chapter’s PITR). Streaming replication sends this WAL over the network to a standby server in real time, and the standby replays it. The result is a byte-level copy, which is why it’s also called physical replication. The standby can serve read-only queries (hot standby), making it both a warm spare and a read-scaling vehicle at once.

Asynchronous is the default — and replication lag #

The default behavior is asynchronous. The primary completes commits regardless of whether they’ve reached the standby, so write performance is unburdened — at two costs.

  • A loss window on failure: at the instant the primary dies, commits whose WAL hasn’t shipped yet can vanish.
  • Replication lag: the standby is always slightly in the past. Milliseconds normally, but seconds under load.

Lag’s practical symptom is the read-your-writes problem: a user posts (on the primary), refreshes the list (on the standby), and their own post isn’t there. It’s a problem the application inherits the moment read scaling is introduced; the usual remedy is a routing rule — “reads immediately following a write go to the primary.” Observe lag on the primary via the pg_stat_replication view.

Check replication lag 
SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

For domains that cannot tolerate the loss window (payments and such), there is synchronous replication (synchronous_commit). Commits complete only after standby confirmation, eliminating loss — at the price of a round trip on every commit, and standby trouble spilling over into write trouble. You don’t have to go all-in: it can be chosen per transaction, and paying the price only where needed is the sound approach.

Failover: promotion is easy — the judgment is hard #

When the primary dies, you promote a standby into the new primary. Promotion itself is one command. What’s hard is everything around it: deciding it’s actually dead (network blip or real failure?), switching application connections, and preventing the worst accident, split-brain (the old primary is still alive and both accept writes, forking the data). That’s why automated failover is entrusted not to a script but to a consensus-based orchestrator. The de facto standard for self-managed setups is Patroni (leader management over a distributed consensus store), and a managed service’s Multi-AZ automatic failover is exactly this layer sold whole — a big reason that line item weighs so much in the RDS vs self-managed calculus.

Logical replication: shipping selected tables #

If physical replication is “a byte copy of the whole cluster,” logical replication publishes changes (row INSERT/UPDATE/DELETE) per table via publications and subscriptions. Its uses are different: sending only some tables to an analytics database, replicating to a server on a different major version (the raw material of zero-downtime upgrades — next chapter), consolidating data from several databases. The mnemonic: if the goal is a complete standby, physical; if the goal is moving a subset of data into a differently shaped system, logical.

Summary #

  • Replication’s mechanism is WAL streaming. It produces a byte copy (physical), and the standby can serve reads.
  • The default is asynchronous; the costs are a loss window and replication lag. Read scaling comes bundled with read-your-writes routing.
  • Pay the synchronous-replication price only for transactions that can’t tolerate loss. All-synchronous is overkill.
  • Failover’s substance isn’t promotion but judgment, switching, and split-brain prevention. Self-managed answers with Patroni; managed answers with Multi-AZ.
  • Per-table and cross-version needs belong to logical replication. Next chapter closes the series: backups, PITR, and upgrades built on WAL.
X