TIL - Connection pooling and PgBouncer
Connection pooling is the technique used to reduce the workload on a database server by pooling/reusing connections.
Connection pooling has its upside and downsides, Upside is, A connection pooler can reduce the total number & number of idle connection to a db server, while downside it can add latency.
In this post, I am covering PgBouncer and It’s usage with Ruby on Rails. PgBouncer is a popular and lightweight connection pooler for PostgreSQL. It supports two modes of connection pooling
Session pooling
Simplest mode of pooling, it keeps a pool of connections and hands one connection to a client for whole the session, when a client disconnects it will put that connection back in the pool.
Session pooling is not very useful if you have clients with long-lived connections but all features work in this mode.
You will see gains in this mode if you have a lot of short-lived client connections. For long-lived client connections, you can think of this as one to one mapping of the client to server connections.
Transaction pooling
This is a more aggressive approach, it gives the most memory gains but you may have sacrifice some features in this mode.
In this mode, I have seen 1500-2000 client connection pooled to around 200 server connections.
In the transaction-pooling mode, you can not use session-level features of PostgreSQL. session-level config, session-level advisory-locks to name few unsupported features. See wiki for full list.
Another downside of transaction pooling is that you can not use prepared statements in transaction pooling mode.
In transaction pooling, I had some strange behavior with Rails. At work, we switched to session pooling due to issues with transaction pooling in Rails. We were having an issue with migrations, see #32622. To mention few more, here is another issue #22102 reported in Rails issue tracker.
On the side note, after our switch to session pooling, we started seeing dropped server connections. See this blog post on how we Debugged and fixed it
Rails team don’t recommend using transaction pooling with Rails. If you don’t need session level features and you know what you are doing then go ahead and use it. If that’s not the case then Session pooling is the safe bet.
These were my two cents on this topic, I recommend this talk by Peter Eisentraut (@petereisentraut) for all the ins and outs of PostgreSQL connection pooling.