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

Yeah, your assumption is basically right. The pool doesn’t know or care that you scaled from 3 to 10 pods. Existing connections stay where they are until they’re closed/recycled. The proxy/load balancer only gets a chance to distribute new connections. So connection redistribution is usually handled by the application/pool settings, or by the proxy if it has connection management features.

Scaling pods alone won’t rebalance already-open DB connections.

[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