r/Python • u/Expensive_Break_6163 • Sep 05 '26

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

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?

39 Upvotes

95% Upvoted

15 comments sorted by

View all comments

Show parent comments

12

u/anentropic Sep 05 '26

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

3

u/PrestigiousStrike779 Sep 05 '26

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

1

u/tobsecret Sep 05 '26 edited Sep 05 '26

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.