Database High Availability Explained

What High Availability Actually Means

High availability describes how well a system keeps operating when a component fails. For a database, that usually comes down to two numbers: recovery time objective (RTO), how long clients are unable to read or write during a failure, and recovery point objective (RPO), how much recently committed data can be lost in that process. A database advertised as highly available is one where RTO is seconds to low minutes instead of hours, and RPO is zero or close to it.

HA is distinct from disaster recovery and from backups. A nightly backup protects against data loss over a long horizon but can take hours to restore. HA protects against short-term outages by keeping a second, live copy of the data ready to take over immediately.

How Replication Keeps a Standby in Sync

Most HA setups are built on replication: a primary node accepts writes, records each change in a write-ahead log (WAL), and ships that log to one or more standby nodes, which replay it to reproduce the same changes in the same order.

• Asynchronous replication confirms a transaction to the client as soon as the primary writes it, before any standby has the data. It is fast, but a primary crash can lose the last few transactions that never reached a standby.
• Synchronous replication makes the primary wait for acknowledgment from at least one standby before confirming the transaction to the client. This removes data loss on failover but adds write latency, especially if the standby is far away.

PostgreSQL implements this through streaming replication and lets the two modes coexist: a nearby synchronous standby for safety, plus additional asynchronous standbys for read scaling or geographic redundancy.

Automatic Failover and Who Makes the Call

Replication by itself does not produce high availability; something still has to notice the primary is unreachable and promote a standby in its place. PostgreSQL does not include this logic in the database engine, so production clusters typically add an external manager. Patroni relies on a distributed consensus store, such as etcd, Consul, or ZooKeeper, to agree on cluster state and avoid two nodes believing they are both the primary. repmgr takes a different approach: it tracks cluster state in its own metadata schema and can use an optional witness server, without requiring a separate consensus store.

Distributed databases built for horizontal scale, such as CockroachDB and MongoDB, fold consensus-based leader election and failure detection directly into the engine instead of relying on an external tool. The trade-off is less operational flexibility in exchange for one less moving part to manage.

A Minimal Streaming Replication Setup

A basic PostgreSQL HA pair needs a primary configured to send WAL and a standby built from a base backup of that primary.

# On the primary (postgresql.conf)
wal_level = replica
max_wal_senders = 5
synchronous_standby_names = 'standby1'

# Build the standby from a live base backup
pg_basebackup -h primary.example.com -D /var/lib/postgresql/data \
  -U replicator -P -R

# -R writes the connection info into postgresql.auto.conf
# and marks the data directory as a standby:
primary_conninfo = 'host=primary.example.com port=5432 user=replicator'

Once started, the standby connects to the primary, streams and replays WAL, and stays current. Promoting it to primary (manually, or through a tool like Patroni) is what completes the failover.

Trade-offs: Consistency, Latency, and Cost

Every HA design trades one thing for another.

• Zero data loss versus write latency. Synchronous replication guarantees the standby has every committed row, but every write now waits on a network round trip to that standby.
• Automatic failover versus split-brain risk. Faster, more aggressive failover reduces downtime but increases the chance of promoting a standby while the old primary is still technically reachable by some clients.
• More standbys versus more cost and complexity. Each additional node adds infrastructure, network traffic, and another thing that can fail or fall behind.

There is no universally correct setting. A financial ledger usually needs synchronous replication and accepts the latency cost; an analytics read replica usually does not.

Common Mistakes That Undermine HA

• Treating replication as a backup. A standby replays the primary's changes, including accidental deletes or corrupted writes. It is not a substitute for point-in-time backups.
• No fencing of the old primary. If a failed primary comes back online and keeps accepting writes after a standby has been promoted, both nodes now think they are authoritative. This is split-brain, and it causes silent, hard-to-reconcile data divergence.
• Ignoring replication lag. An asynchronous standby that falls far behind offers much weaker protection than its presence on a diagram suggests. Lag should be monitored, not assumed to be near zero.
• Never rehearsing a failover. A failover procedure that has only been read, not run, tends to fail in exactly the way nobody expected during a real incident.