How would you design the health check that a database proxy uses to decide which backend node is the writable primary, and what makes a naive check dangerous?

## Why the naive check fails The default health check in most load balancers is a TCP connect. It answers "is something listening", which is nearly useless here. Every one of the following passes it: a demoted node that is now a read-only standby; a node still in crash recovery; a node whose disk is full so every write errors; a node at `max_connections` so no client can actually get in. Routing writes to any of them produces errors or, if the node is wrongly writable, divergent data. ## What a good check asks 1. **Can I connect and authenticate?** Use a dedicated low-privilege account against the actual application database, not the default one — a full disk or a broken database shows up here, and a check that connects as a superuser hides authorisation problems. 2. **Is the node writable and is it the primary?** This is the decisive question. Query the engine's role indicator, or ask a cluster manager (Patroni, Orchestrator, an operator) whose REST endpoint already reflects the authoritative decision. Deriving role from a cluster manager is preferable when one exists, because two independent opinions about who is primary is precisely the situation to avoid. 3. **Is it usable, not merely alive?** Optionally check that the query returned within a latency bound and, for replicas, that lag is under a threshold. For read backends the check is different — a standby should be *rejected* from the write pool for exactly the reason it is *accepted* into the read pool, so proxies normally run two checks with different predicates against the same node. ## The dangerous details **Saturation feedback loop.** When a node hits its connection limit, the health check cannot connect and the node is ejected. Its traffic moves to the remaining nodes, which saturate too. A load spike becomes a full outage. Defences: reserve connection slots for the check user (most engines support reserved superuser or per-user connection allowances), give the check its own low-cost path, and treat connection saturation as degradation rather than death. **Flapping.** With a single failed check as the ejection criterion, one GC pause or IO stall reshuffles routing, breaking every connection on that backend. Require N consecutive failures to eject and M consecutive successes to restore, with the check interval and timeout chosen so total detection time still meets the availability target. Asymmetry is deliberate: quick to eject on repeated failure, slow to trust again. **Timeouts.** The timeout must be shorter than the interval, or slow checks overlap and queue. A check that hangs forever on a wedged node is worse than no check, because the proxy keeps the node in rotation while it waits. **Cost.** Checks run per proxy instance per backend per interval. Twenty proxies checking five backends every second is a hundred connections a second; if the check opens a new connection each time rather than reusing one, that alone can be significant load. **Fail closed for writes.** If the check cannot determine role, the correct behaviour is to withhold write traffic, not to guess. Reads may degrade gracefully; writes must not be sent to a node that might be a standby or a stale former primary. **Routing is not fencing.** A proxy that stops sending traffic to the old primary has not stopped anything else from reaching it — other clients, cron jobs, or a second proxy with a different opinion. Ensuring only one node can accept writes is a separate mechanism; the health check merely reflects it. ## Observability Export check results as metrics — per backend state, transition timestamps, check latency, failure reason. The most useful post-incident question is "when did the proxy think each node changed role, and why", and that is only answerable if every transition was recorded with its cause.