During a planned primary switchover behind a database proxy, what has to happen to the connections that clients already hold, and what must the application do so users do not see errors?
answer
- Open transactions cannot migrate — they abort
- Drain: stop new, grace period, then kill
- Proxy pause/queue → latency instead of errors
- Pool must evict; keepalives + socket timeout vs black-holed sockets
- Retry only safe classes; commit-with-no-reply is indeterminate
basics
~20 sIn-flight transactions cannot be moved; they are rolled back. The proxy stops issuing the old primary, lets open transactions finish for a bounded time, then closes them, optionally queueing new connections. Clients must discard pooled connections and retry — with backoff, jitter, and only where the operation is safe to repeat.
solid answer
~60 sNo mechanism migrates an open transaction between nodes, so a switchover always ends some work. The controlled sequence is: mark the old primary as draining so no new connections are routed to it; allow in-flight transactions a bounded grace period to commit; then terminate the rest; promote; and route new connections to the new primary. A proxy can pause and queue incoming connections across that gap, so clients experience a latency spike rather than errors. On the client side the pool is the problem. Its connections point at the old primary and are now dead or read-only, and TCP may not notice for minutes if the node vanished. So: enable keepalives and socket timeouts, validate connections on borrow, and evict the pool on the errors that signal a topology change. Retries must be classified, not blanket. Failure to connect, or failure before the statement was sent, is safe to retry. A commit that returned no answer is *indeterminate* — retrying may duplicate. Add exponential backoff with jitter so a fleet does not stampede the new primary.
go deeper
Know that in-flight transactions are lost, connections must be re-established, and the application needs retry logic.
Describe draining with a grace period, pool eviction, and the difference between retryable connection errors and everything else.
Add proxy pause/queue behaviour, keepalives and timeouts against black-holed sockets, error-class-based retry, idempotency keys for indeterminate commits, and backoff with jitter.
Set the switchover budget end to end, insist that user-visible error duration is the measured metric, make idempotency a platform-level contract, and require regular drills under load.
## The unavoidable fact A session's state — its open transaction, temporary tables, locks, prepared statements — lives on one node. Nothing transfers it. Every switchover therefore aborts in-flight transactions. The goal is not to avoid that but to make it small, brief, and invisible to users. ## Draining, step by step 1. **Stop issuing the old primary.** The routing layer marks it draining: existing connections continue, no new ones are created. 2. **Grace period.** Give open transactions a few seconds to finish. Long-running work will not finish and should not be waited for indefinitely — a switchover blocked behind an hour-long batch is a worse outcome than aborting it. Set a deadline. 3. **Terminate the rest.** Kill remaining backends so the old primary has no writers before promotion; this is also what makes the demotion safe. 4. **Make the old node read-only** as part of demotion, so any straggler write fails loudly rather than diverging. 5. **Promote and re-point.** The proxy's health check sees the new role, or the cluster manager tells it. 6. **Release queued connections** to the new primary. ## Why queueing matters Between steps 3 and 5 there is a gap of a second or several. A proxy that rejects connections during that gap converts a brief unavailability into a wave of application errors. A proxy that *holds* new connections — PgBouncer's pause/resume, similar mechanisms in ProxySQL — turns it into added latency instead. This is the single biggest practical argument for a proxy over a floating IP or DNS. The queue needs a bound: past a few seconds, failing fast is better than making every thread in the fleet block. ## The client side The application's pool is holding connections to the old primary. Three failure shapes appear: - **Reset** — clean and immediate; the pool sees an error and can discard. - **Black hole** — the node disappeared without closing sockets, so reads hang until the OS timeout, which can be minutes. Fix with TCP keepalives and an application-level socket/statement timeout. Without these, a failover that the database handled in five seconds looks to users like a multi-minute outage. - **Read-only errors** — the connection still works but the node is now a standby, so writes fail with a "read-only transaction" error. The pool must treat that as a topology signal and recycle, not as an application bug to surface. Practical settings: validate on borrow (or `keepaliveTime`-style background validation), a maximum connection lifetime so the pool naturally rotates onto the new primary, and an explicit "evict the whole pool" reaction to connection-class errors. ## Retry classification Blanket retry is a data-integrity risk. Classify by what the database can have done: - **Safe**: connection refused, failed during connect, failed before the statement was transmitted, explicit read-only or serialization errors (SQLSTATE class `08`, `25006`, `40001`). The write certainly did not commit, or is explicitly retryable. - **Indeterminate**: the connection dropped after sending a `COMMIT` with no reply. The transaction may or may not have committed. Only safe to retry if the operation is idempotent, guarded by a unique key or an idempotency token, or verifiable by reading back. The design answer is to make writes idempotent — a client-supplied unique request key that the insert conflicts on — so an indeterminate commit becomes safe to repeat. Failing that, the operation must be surfaced to the user rather than silently retried. ## Backoff and the stampede When the new primary opens, thousands of pooled connections reconnect simultaneously, plus the queued requests, plus retries. A cold cache on the new primary meets a connection storm. Use exponential backoff with jitter, cap pool growth, and rely on the proxy to hold the fan-in — a proxy multiplexing a fleet down to a bounded number of backend connections is what keeps the new primary from being knocked over the moment it is promoted. ## Verifying it All of this is untestable by inspection. Practise switchovers regularly under load and measure user-visible errors, not just database-side timings; the client's timeout and pool configuration is where the seconds actually go.
- A write fails with 'connection closed' after the client sent COMMIT. Is it safe to retry?Not in general — the commit may have completed on the server before the connection dropped, so retrying can duplicate the effect. The transaction is in an indeterminate state and must be resolved, not assumed. The robust patterns are to make the write idempotent with a client-supplied unique key so a repeat is a no-op, or to read back a definitive marker before deciding. Blind retry of indeterminate commits is how duplicate orders and double charges happen.
- The database reports the switchover completed in four seconds, but users saw errors for two minutes. Where did the time go?Almost always on the client. Sockets to the old primary that were never cleanly closed hang until an OS-level timeout, so pooled connections neither fail nor work; without TCP keepalives and a socket or statement timeout the pool keeps handing out dead connections. Add aggressive keepalives, connection validation on borrow, a bounded maximum connection lifetime, and pool eviction on connection-class errors — then re-run the drill and measure client-side error duration.
- Why bound the proxy's connection queue during a switchover instead of holding indefinitely?Every queued connection is an application thread or request blocked upstream, so an unbounded queue propagates the stall into the application tier and can exhaust its own thread or connection limits, turning a database blip into a whole-service outage. A bound of a few seconds covers the normal promotion gap and fails fast beyond it, letting upstream shed load or serve degraded responses. The bound should be shorter than the callers' own request timeouts.
saying these in an interview costs you the question
- Believing an open transaction can be migrated to the new primary
- Retrying every failed statement regardless of whether it may have committed
- Ignoring pooled connections and assuming clients reconnect automatically and promptly
- No socket or statement timeout, so black-holed connections stall for minutes
- Retrying immediately with no backoff or jitter, stampeding the freshly promoted primary