High Availability with Patroni and pg_auto_failover

Streaming replication gives you a standby ready to take over, but it does not decide when to fail over, promote the replica, or redirect clients — those are manual steps a human would otherwise perform at 3 AM. High availability means automating that response so the system recovers from a primary failure on its own, within seconds, without human intervention and without ever ending up with two primaries.

Automatic failover is harder than it sounds because of split-brain: if the old primary is merely unreachable rather than truly dead, promoting the standby could leave two servers both accepting writes, diverging irreparably. Safe HA therefore needs reliable failure detection, a way to agree on a single leader, and a guarantee the old primary cannot keep writing.

Two tools dominate PostgreSQL HA. Patroni uses a distributed consensus store (etcd, Consul, or similar) to elect exactly one leader and orchestrate failover, and is the de facto standard for serious deployments. pg_auto_failover offers a simpler monitor-based model. Both turn raw streaming replication into a self-healing cluster.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.