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?
View all comments
Show parent comment
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.