Rolling PgBouncer without dropped queries

PgBouncer rollouts kept producing short bursts of PG::ConnectionBad and 5xx responses. The errors stopped once the rollout finished. We knew open connections were getting cut off, but knowing why hadn't stopped it from happening on the next deploy.

I wanted to understand why clients still had connections open when Kubernetes stopped the old PgBouncer pod, and how to drain them before the shutdown window ran out.

What PgBouncer is, and why anyone runs one

Postgres uses a process per client connection. More connections consume memory and add scheduling overhead, even before every client starts running expensive queries. How many are comfortable depends on the instance and workload. The application fleet can grow its connection count much faster than its query rate.

Rails ships with a connection pool inside ActiveRecord, scoped per process. That limits each process separately. With one worker per pod and ten open connections per worker, a hundred pods can still hold a thousand database connections. More workers per pod multiply the total again.

PgBouncer sits between the application processes and Postgres and multiplexes. Application processes connect to PgBouncer with their usual Rails pool. PgBouncer holds a smaller pool of Postgres connections and assigns them per transaction or per session. Its limits apply to each proxy instance and configured pool, so the database budget still has to account for the whole proxy fleet.

A few alternatives exist. AWS RDS Proxy manages the proxy for you, with a service charge and its own session-pinning rules. PgCat adds sharding and multithreaded proxying. A small fleet may fit comfortably within the database's connection budget without an external proxy. This post is about the client-lifetime problem in a PgBouncer deployment.

The pooling modes you can pick from

PgBouncer offers three pooling modes¹, each with a different contract with the application.

Session pooling is the most permissive. PgBouncer assigns a backend to a client session and keeps it for the entire lifetime of the client connection. Same as if PgBouncer wasn't there, just with the bookkeeping handled. You get full SQL semantics, including session-scoped state like temp tables, session GUCs, and advisory locks. The cost is zero pooling benefit if your clients hold connections for a long time. Effectively a bouncer that never sends anyone home.

Transaction pooling assigns a backend at the start of a transaction and releases it when the transaction commits or rolls back. Many clients can share a smaller backend pool when their transactions do not overlap. Session-scoped state needs care: session GUCs and session advisory locks cannot be assumed to follow the client across transactions. Protocol-level prepared statements need a compatible PgBouncer version and configuration. Transaction-scoped advisory locks stay within the transaction's assigned backend.

Statement pooling is the most aggressive. A backend is assigned per statement and released immediately. Multi-statement transactions don't work at all in this mode, so it's rarely useful for Rails.

For this workload, transaction pooling provided the useful reduction in backend connections. Session-dependent paths needed a separate route or a change in how they used database state.

Three layers, not one

If you want to think about rollouts cleanly, you have to think about three connection layers, not one.

The first layer is the ActiveRecord pool, scoped to the application worker process. A Ruby object holding a list of connection wrappers, with a checkout/check-in dance for thread-safety. Each entry in this pool corresponds to a real TCP connection.

The second layer is the TCP socket itself. From the kernel's point of view this is just an open file descriptor talking to a remote endpoint. The kernel doesn't know it's a Postgres connection. It doesn't even know the endpoint is PgBouncer rather than Postgres. It only knows there's a socket, and that the socket either has bytes to read, or doesn't, or has been closed by the remote side.

The third layer is the backend connection from PgBouncer to Postgres, which PgBouncer manages independently. PgBouncer holds its own pool of these. The mapping between "client connection from a Rails worker" and "backend connection to Postgres" is dynamic. In transaction pooling mode, a single Rails connection might use ten different Postgres backends over its lifetime.

The reason the three-layer view matters is that a PgBouncer rollout breaks layer two from PgBouncer's side. The Rails worker still believes its layer-one entry is healthy. The kernel might or might not have noticed the close yet, depending on TCP timeout settings. The next time Rails checks out that connection and tries to issue a query, the socket fails and you get PG::ConnectionBad. That isn't a connection pool bug. It's a layer-mismatch.

Why a rollout is the awkward case

PgBouncer is a stateful proxy, and rolling it without losing in-flight work is harder than rolling a stateless web tier. Rolls happen often: configuration changes, node draining for autoscaler activity, image bumps for security patches, taints from cluster maintenance, every deploy of the proxy itself. None of these are rare events. If a rollout costs you a spike of PG::ConnectionBad every time, you're paying that cost weekly.

During a rolling update, Kubernetes brings up replacement pods and terminates old ones. Endpoint updates and pod shutdown happen concurrently; traffic-routing changes take time to propagate. A preStop hook can give that propagation time, but it also consumes the pod's termination grace period. Then there is the question of what the termination signal means to PgBouncer.²

In PgBouncer 1.23 and later, SIGTERM enters WAIT_FOR_CLIENTS³. It stops accepting new client connections and waits for existing clients to disconnect on their own. Earlier versions shut down immediately on SIGTERM. With graceful shutdown, long-lived Rails connections can keep the pod in Terminating until Kubernetes sends SIGKILL at the end of terminationGracePeriodSeconds. That cuts the sockets the clients still hold.

SIGINT enters WAIT_FOR_SERVERS. In transaction pooling, PgBouncer waits for active transactions to finish and disconnects clients as their server connections are released. This can finish sooner than waiting for every client to close voluntarily, but long transactions can still outlast Kubernetes' grace period. A client between transactions can also discover that its socket has been closed on the next query.

You can build either of these. Most teams running PgBouncer have variants of both lying around. Neither is enough on its own.

The reason neither is enough is the same in both cases: the client isn't participating in the rollout. The client doesn't know PgBouncer is rotating. The client believes the connection is fine until it discovers the connection isn't. The shutdown approach is trying to fix this from the proxy side, which is the wrong side.

PgBouncer's restart procedure makes the dependency explicit: start WAIT_FOR_CLIENTS, then cause the clients to reconnect.³ The discussion about pod rotation reaches the same practical problem.⁴ Sending the shutdown signal does not make an application pool recycle its existing sockets.

The missing piece is in the client

The fix is to make the client recycle its own PgBouncer connections on a schedule, before PgBouncer ever needs to rotate.

Concretely: every connection in the ActiveRecord pool gets a birth time stamped on first checkout. On every subsequent checkout, the pool checks the connection's age. If the age exceeds a configurable maximum lifetime, the pool removes the connection from the pool, closes the underlying socket, and checks out a fresh one. The fresh one gets routed by the Kubernetes service to whichever PgBouncer pod is currently healthy, which over time means traffic naturally redistributes off any pod that's about to be terminated.

End-to-end, a query lifecycle with the patch in place looks like this:

1. Check the connection’s age at checkout.
2. If it is too old, close it and connect through the service to a ready proxy.
3. Execute the query and check the connection back in.

The lifetime check is the extra piece. It runs when the application actually checks out a connection. An existing lease or transaction can reuse its connection without passing through another checkout.

Rails was the ecosystem holdout on this. Go's pgxpool has had MaxConnLifetime (and MaxConnLifetimeJitter) for years. Python's psycopg_pool has max_lifetime. Both ship the recycle-stale-connections pattern in the standard pool. Rails 7.x doesn't, so I shipped it as a small prepend patch on ActiveRecord::ConnectionAdapters::ConnectionPool. Rails 8.1 has since landed the same idea in core as a max_age connection-pool option⁵, with pool_jitter to avoid every connection in the fleet recycling at exactly the same wall-clock instant. The in-core implementation retires connections at check-in rather than at check-out, but it addresses the same root cause. If you're on 8.1, the patch below is one less thing to maintain. For everyone still on 7.x, this is what I ran⁶:

module ConnectionMaxLifetime
  MAX_LIFETIME_SECONDS = ENV.fetch("CONNECTION_MAX_LIFETIME_SECONDS", "480").to_i

  def checkout(*args)
    connection = super
    return connection unless MAX_LIFETIME_SECONDS > 0

    now = Process.clock_gettime(Process::CLOCK_MONOTONIC)
    unless connection.instance_variable_defined?(:@birth_time)
      connection.instance_variable_set(:@birth_time, now)
    end

    age = now - connection.instance_variable_get(:@birth_time)
    return connection if age <= MAX_LIFETIME_SECONDS

    remove(connection)
    connection.disconnect!
    checkout(*args)
  end
end

ActiveRecord::ConnectionAdapters::ConnectionPool.prepend(ConnectionMaxLifetime)

Rails 7.2's per-query leasing gives ordinary query paths more opportunities to return a connection and check it out again.⁷ It does not mean every SQL statement creates a new checkout. Transactions, explicit leases, and nested with_connection blocks can keep using the same connection.⁸ The patch can retire a connection between leases, never in the middle of one.

idle_timeout handles connections sitting unused in the pool, with cleanup occurring on a maintenance pass. The checkout patch handles old connections when they are borrowed again. Neither forcibly retires a connection held by live application code. A maximum age is a retirement threshold, not a deadline that interrupts a query.

The PgBouncer pod needs enough termination time for those client connections to drain. After endpoint changes propagate, fresh connections route to ready pods. Existing connections leave the old proxy through checkout-time retirement or idle cleanup. If every client disconnects before the deadline, PgBouncer exits without Kubernetes cutting a socket. A connection held beyond that deadline remains a failure case.

A shutdown deadline doesn’t close a client socket.

Five Rails connections still point at the old PgBouncer pod. Give them enough time to retire.

Max age

30s

Grace

45s

Hold C1 open

0s50s100s

C1

35s

C2

27s

C3

19s

C4

11s

C5

5s

kill

DeadlineTime

0s

All five connections can retire before the 45s deadline in this example.

Ages: 0, 8, 16, 24, 32s. Holding C1 adds 45s; max age is not a hard stop.

Tuning the timing math

I budget the grace period around the paths that can keep a client connected: remaining connection lifetime, the longest checked-out lease, idle cleanup and its scheduling interval, endpoint propagation, and preStop. Some of those overlap. They still need to be accounted for rather than hidden inside a fixed buffer.

An eight-minute lifetime and a five-minute idle timeout do not prove that a fifteen-minute grace period is safe. A connection checked out just before its age threshold can remain held for the rest of a long transaction. Without a bound on that lease, there is no finite shutdown guarantee from the pool settings alone.

The pods I observed drained in three to five minutes. I would still test long transactions, idle clients, and explicit leases before using the same settings elsewhere, and watch the old proxy's remaining client count rather than assuming elapsed time proves it has drained.

The rollout strategy matters too. A Deployment's maxSurge and maxUnavailable control how many pods rotate at once. A Pod Disruption Budget protects against voluntary disruptions using the Eviction API, such as a node drain; it does not constrain the Deployment's own rolling update. Keep replacement capacity ready and allow for endpoint propagation before the old proxy stops accepting connections.⁹

With a fixed lifetime, connections created together can also expire together. Rails 8.1's pool_jitter reduces the age threshold by a random fraction to spread retirement.⁵ A custom implementation can choose MAX_LIFETIME * (1 - rand(0.0..0.2)) once per connection and store it with the birth time. Subtracting jitter keeps the configured maximum as the upper threshold; choosing a new value at every checkout would make the expiry move around.

There's a parallel knob on the PgBouncer side worth knowing about. server_lifetime (default 3600s) is the same idea applied to backend connections from PgBouncer to Postgres. PgBouncer recycles a backend the next time it's released after that age. Different layer, same trick. The PgBouncer config docs are consistent with the client-side approach: they note that client_idle_timeout should be set larger than the client-side connection lifetime, which assumes such a thing exists on the other end.

The result and its boundary

In the rollouts I watched after this change, the connection-error spike disappeared without an application-side retry layer. That is an observed result, not a mathematical guarantee: a connection held longer than the grace window can still be cut. The cost is allowing a longer rollout and keeping the client's retirement settings, lease durations, and pod termination budget in sync. A pod can exit as soon as its clients leave; it does not have to consume the whole grace period.

Inside Model.transaction, queries must keep using the same connection until the transaction finishes.⁸ An eight-minute age threshold cannot retire a connection halfway through a ten-minute transaction. Explicit long-lived leases have the same problem. Session-scoped state is a separate compatibility concern: retiring a socket discards that state, and transaction pooling cannot promise the same backend across transactions in the first place.

PgBouncer offers one partial mitigation, idle_transaction_timeout, which forces a connection closed if it sits idle inside a transaction for too long. That helps with the idle-in-transaction case (a thread that opened a transaction and went off to do something else), but it doesn't help with a transaction that's actively running queries the whole time.

One option is a separate connection route for long-running or session-dependent work: direct to Postgres, or through a session-mode pool with its own drain policy. Rails migrations that use session advisory locks need that distinction. Transaction-scoped advisory locks do not have the same cross-transaction requirement.

Retries need their own correctness argument. PG::ConnectionBad does not tell the caller whether a write committed before the response was lost. If the connection disappears before commit, the unfinished transaction is rolled back; if the commit succeeded and its response disappeared, the outcome is uncertain to the client. Retrying one statement from a transaction is unsafe, and even an autocommit write can duplicate an effect. Retry only at a boundary whose idempotency contract makes repetition safe.

A separate route removes those jobs from this proxy's rollout boundary. Safe retries handle some failures after they happen. They solve different parts of the problem and can be used together.

What about PgBouncer's deployment shape

PgBouncer is one binary, but how you run it on Kubernetes shapes the rollout story as much as the configuration. Three patterns come up:

DaemonSet (one PgBouncer per node): keeps the proxy near its clients if routing selects the node-local instance. An ordinary application pod cannot reach a different pod through localhost; that needs an explicit node endpoint or a shared host-network arrangement.² Each proxy owns its backend pools, so the total connection budget grows with node count. DaemonSet update settings determine how many nodes drain at once.

Sidecar (one PgBouncer per application pod): shares the pod's network namespace, so localhost works. It can pool connections from multiple application workers in that pod. The trade-off is a separate proxy and backend pools per pod, with lifecycle and shutdown ordering coupled to the application. Scaling application pods also scales the possible database connection count.

Deployment + ClusterIP service: scales the proxy fleet independently of application pods. Each PgBouncer instance still owns separate backend pools; the service does not merge them into one shared pool. A TCP connection stays on its chosen proxy until it closes, so balancing happens at connection establishment rather than per query. Client recycling helps redistribute traffic during rollouts and scale changes. Replica limits must also fit the database's aggregate connection budget.

The Deployment + service pattern fit this workload because the proxy fleet could scale independently. The client-lifetime patch made its rollout behavior work for the application.

Other things PgBouncer ends up doing

Connection pooling is the primary job, but once you have PgBouncer in the path, a few other capabilities come for free.

Pause and resume for maintenance windows. PgBouncer's admin interface includes PAUSE and RESUME commands. PAUSE stops PgBouncer from forwarding queries to Postgres and waits for in-flight transactions to complete. The application keeps its connections open to PgBouncer, which now buffers them. This is useful during Postgres failovers and certain DDL operations that need a quiet moment, with the application none the wiser. RESUME flushes everything that queued up.

TLS termination. PgBouncer can speak TLS to the application and plaintext to Postgres, or vice versa. Useful when the application is on the other side of a network boundary and the database is on the same trusted network as the proxy, or the inverse.

Multi-database routing. The [databases] section in pgbouncer.ini lets one PgBouncer instance front many logical databases, mapping client connection strings to backend hosts and database names. A single proxy fleet can serve a primary, a read replica, multiple databases, and even databases on different hosts. This is the lever that makes "centralised PgBouncer for the whole org" practical.

Authentication proxy. PgBouncer can authenticate clients on its own without forwarding the credentials to Postgres, which keeps the real database password hidden inside the cluster.

Per-user, per-database configuration. Different pool modes, pool sizes, and connection limits per user or per database. Useful when you have a mix of long-running batch jobs and short transactional queries against the same database, or when you want service-level metrics on shared infrastructure.

What PgBouncer doesn't do, despite how often it gets asked, is read/write splitting on its own. PgBouncer doesn't inspect queries to decide whether they're reads or writes. The standard pattern when you want read/write splitting is to deploy two PgBouncer fleets, one in front of the primary and one in front of the read replica, and have the application route by connection target. PgCat does this natively if you'd rather have it in the proxy.

Other ways to solve the same problem

Connection pooling is a real problem, and PgBouncer is one of several answers. A few worth knowing:

AWS RDS Proxy manages the proxy for you. PostgreSQL extended-protocol prepared statements support multiplexing; SQL commands such as PREPARE, DEALLOCATE, and DISCARD can still pin a session.¹⁰ Check the application's actual protocol and session-state use, then price the service against the operational work it replaces.

PgCat is a Rust rewrite of PgBouncer with first-class sharding support and better multithreading. Less mature, smaller community, but a serious project worth tracking if you're hitting PgBouncer's single-threaded ceiling.

Application-side pooling only (no proxy at all) is viable if your application fleet is small enough that the total connection count stays inside Postgres's limits. The per-process Rails pool is well-engineered. You don't need PgBouncer until you do, and "until you do" is usually somewhere around the point where a single app fleet outgrows what one Postgres instance can host as direct connections.

Pgpool-II is the older alternative. Powerful but operationally heavier, with replication and load-balancing features that often aren't needed.

I would choose a proxy from the connection budget, transaction shape, session-state requirements, and operational cost. A pod-count threshold by itself cannot make that decision.

The part that mattered

If you're running PgBouncer on Kubernetes and the rollout spike is something you've learned to live with, try the lifetime patch. The implementation is small, the operational gains are large, and the trade-offs (slow rollouts, timing math) are honest. It's been running cleanly in production for me, and the rollout-spike alert has been silent since the patch landed.

For the SQL-side perils of running PgBouncer, JP Camara's PgBouncer is useful, important, and fraught with peril is the companion read I would use. He covers prepared statements, advisory locks, statement timeouts, and listen/notify. This article stays at the rollout boundary.

If you've worked through something similar with a different shape, or hit a failure mode I didn't, I'd love to hear about it. maria@runbookpages.com. Wrong-turns and what-broke stories most welcome. The closer to "tried this, here's what actually shipped" the better.

Footnotes

1. PgBouncer documentation, pool_mode. The canonical reference for the session/transaction/statement contract and the SQL features each one breaks. ↩

2. Kubernetes documentation, Termination of Pods and Pod networking. Endpoint updates run alongside shutdown. The grace-period countdown includes preStop. Containers in one pod share localhost; separate pods do not. ↩ ↩²

3. PgBouncer documentation, Signals. SIGTERM puts PgBouncer into the same SHUTDOWN WAIT_FOR_CLIENTS state available via the admin console (the named admin command), and SIGINT triggers WAIT_FOR_SERVERS. A second signal escalates to immediate shutdown. Note this graceful-SIGTERM behaviour is PgBouncer 1.23+. Pre-1.23, SIGTERM was an immediate shutdown. ↩ ↩²

4. PgBouncer GitHub issue #1468, Zero downtime on PgBouncer Pod Rotation. The discussion covers client cooperation during graceful proxy shutdown. ↩

5. ActiveRecord ConnectionPool API, max_age. Rails 8.1 ships connection-age retirement on the check-in path, complemented by pool_jitter (randomises expiry to avoid thundering-herd recycling), idle_timeout, and keepalive. The shape is different from the check-out patch but the goal is the same. ↩ ↩²

6. Prior art on the patch shape: Samuel Cochran's ActiveRecord Connection Lifetime gist, motivated by autoscaling rebalance rather than rotation drain. Same checkout-time recycle pattern, different reason to reach for it. The Rails 8.1 max_age work in core was driven by similar autoscaling concerns and lands on the check-in path. ↩

7. Rails 7.2 release notes, Per-query connection leasing. Connections are leased for the duration of a single query rather than the whole request, which makes mid-request connection recycling possible. ↩

8. ActiveRecord ConnectionPool API, with_connection and lease_connection. An existing lease can be reused without another checkout and is not necessarily released at the end of a nested block. Transactions require the same database connection through commit or rollback. ↩ ↩²

9. Kubernetes documentation, Deployments and Disruptions. Rolling updates are controlled by the workload resource's rollout strategy; PDBs limit voluntary evictions and do not govern a Deployment rollout. ↩

10. AWS, PostgreSQL extended-protocol multiplexing and RDS Proxy pinning conditions. Protocol-level prepared statements and SQL commands that manipulate prepared statements have different pooling behavior. ↩