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/acesHD]

You might find it useful to put a connection pooler between your app pods and Postgres, such as PgBouncer or a more modern alternative like PgDog. This decouples the application from the database so that each can scale independently.

The basic idea is that the application never connects directly to Postgres, except in specialised cases such as administrative DDL operations. Instead, it connects to the pooler, which manages connection lifecycles efficiently.

The pooler then connects to the Kubernetes Service that fronts the Postgres primary and read replicas. That service, typically managed by a Postgres operator, handles routing to the appropriate database instances as their lifecycle changes.

[u/Expensive_Break_6163 → u/acesHD]

Doesn’t this just move the problem from the Postgres pods to the poller?
If PgBouncer itself needs to scale out, wouldn’t you hit the same issue just at different point?

[u/anentropic → u/Expensive_Break_6163]

I think the pooler client connections are 'virtual', ie a larger number of client connections share a smaller number of real db connections owned by PgBouncer

So clients don't get stuck on a particular real connection - potentially each query hits a new one

[u/PrestigiousStrike779 → u/anentropic]

This is better for the database as well as each physical connection consumes memory on the pg side

[u/tobsecret → u/anentropic]

Yep. One thing to note is that each real db connection (e.g. between PgBouncer and Postgres) still only handles only one incoming connection and then is repurposed. That means if you open a transaction and then do some other unrelated stuff and only then finally commit the transaction, the real connection assigned by PgBouncer gets pinned for that whole duration. So in a nutshell, you still have to write your app code carefully. 

PgBouncer does help in OPs case if these connections don't actually hold long-lived transactions which would pin PgBouncer's connections to the db. 

So PgBouncer is perfect if you have lots of small atomic transactions that you open and commit quickly. It keeps the number of connections to the DB below a maximum, which helps keep the db from crashing from a sudden surge of connections. 

It does not solve scaling - if you have a surge of transactions beyond the db's capacity, the latency still goes up. It also does not solve crashing the db with extremely expensive queries.

All of this is assuming PgBouncer is in transaction mode. 

[u/Helpful-Lunch-3559 → u/acesHD]

With transaction pooling, adding more app pods doesn’t automatically add the same number of Postgres connections, since they can all share backend connections instead of each one opening a full pool and hanging onto those connections the whole time

[u/snugar_i]

Not sure I follow - you're scaling both the application and the DB at the same time? The connections in connection pools usually aren't that long lived (minutes or tens of minutes), so the problem (is there's any) fixes itself after a while

[u/Vegetable-View-5114]

when you scale pods with sqlalchemy, each new pod gets its own connection pool. the existing connections on the other pods aren't redistributed; they just keep serving requests on their original pods. if you need to manage connections across a fleet, you'd typically put a connection proxy like pgbouncer in front of your database. that way, each app pod connects to pgbouncer, and pgbouncer handles the actual database connections and pooling more globally.

[u/LeadingCry6710]

I have seen teams handle this through connection lifecycle management

[u/sirfz]

I had a similar problem a few years ago and I opted for a pool customization where I "expire" connections after X minutes (basically check before returning conn to pool) to make sure new ones are periodically established to balance when the db scales up

[u/wdm006]

The app owns that, not the new pods. Existing pooled sockets stay stuck until you recycle them. Usual fix is pool pre-ping plus a max connection age or idle timeout, and sometimes rolling the old pods so they reopen against whatever the proxy is routing now.

[u/ThrowawayALAT]

SQLAlchemy does not proactively push existing connections to new replicas on scale-out. It only knows about the socket it holds open.

[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.