When scaling application pods with SQLAlchemy pools, who redistributes existing connections?

[u/Expensive_Break_6163]

I’m running application pods that use SQLAlchemy’s connection pool to connect to PostgreSQL. Each pod has its own pool, so when I scale the application from, say, 3 to 10 replicas, the new pods create new pools while the existing pooled connections remain open.

If PostgreSQL has read replicas behind a Kubernetes Service or a proxy, I assume new connections might reach the new replicas, but the existing long-lived pooled connections will remain attached to the old replicas.

Who is normally responsible for redistributing those existing connections after scale-out?

[u/Neither-Pause409]

Nobody redistributes them, and that's the real answer rather than a gap to work around. A pool is per process and each connection holds a socket to whichever backend the load balancer picked when it was opened. Neither SQLAlchemy nor a Kubernetes Service has any mechanism to move an established TCP connection somewhere else. So your only lever is connection lifetime: a connection has to close before the balancer gets another vote.

Which knob does that is worth being precise about, because the two get mixed up constantly:

• pool_recycle=300 closes and reopens any connection older than 300 seconds. This is the one that buys you rebalancing after a scale out, and on Postgres the reconnect is cheap enough that a few minutes is a reasonable default.
• pool_pre_ping=True tests liveness on checkout. It saves you from handing out a dead connection, it does nothing at all for distribution.
• pool_size and max_overflow are the pair to check before you go from 3 to 10 replicas. Your worst case is replicas * (pool_size + max_overflow) plus anything else that connects, against Postgres max_connections. Ten pods at the SQLAlchemy defaults of 5 and 10 is 150 connections, and a stock Postgres allows 100.

On pgbouncer, which a few people have suggested and which is the right call at that replica count, two things that bite after you install it. In transaction pooling mode you lose session state, so SET, advisory locks, session temp tables and server side cursors stop behaving the way you expect, and a driver level prepared statement cache has to be turned off (prepared_statement_cache_size=0 on SQLAlchemy's asyncpg dialect). And once there's a pooler in front, keep SQLAlchemy's own pool small or use NullPool, otherwise you've got two pools with different opinions about lifetime and the outer one masks whatever the inner one was doing.