PgBouncer’s transaction mode is surprisingly good at making Postgres work with serverless functions, but it’s not the automatic win you might think. The real magic is how it turns potentially millions of short-lived connections into a tiny, stable pool, but that stability can hide a subtle but critical failure mode if you’re not careful.
Let’s watch Pgbouncer in action with transaction mode. Imagine a serverless function, like AWS Lambda, triggered by an API Gateway.
// AWS Lambda function (Node.js)
const pg = require('pg');
exports.handler = async (event) => {
const client = new pg.Client({
host: 'your-pgbouncer-host',
port: 6432, // Pgbouncer port
user: 'your_user',
password: 'your_password',
database: 'your_database'
});
await client.connect();
try {
const res = await client.query('SELECT NOW()');
console.log(res.rows[0]);
} finally {
await client.end(); // Crucial for transaction mode
}
return {
statusCode: 200,
body: JSON.stringify('Hello from Lambda!'),
};
};
And here’s a snippet of Pgbouncer’s configuration (pgbouncer.ini):
[databases]
your_database = host=your_postgres_host port=5432 dbname=your_database
[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = admin_user
stats_users = stats_user
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000
When the Lambda function runs, it connects to Pgbouncer. Pgbouncer, in transaction mode, grabs a connection from its pool to the actual Postgres server. It then executes the SELECT NOW() query. The moment the query finishes and returns, Pgbouncer immediately releases that Postgres connection back to its pool. The Lambda function then calls client.end(), which signals to Pgbouncer that the client connection can be closed. Pgbouncer doesn’t care about the client connection’s lifecycle; it only cares about the transaction lifecycle. This is why client.end() in the Lambda is so important – it tells Pgbouncer to fully disconnect the client and free up its associated Pgbouncer internal state.
The problem this solves is the sheer cost and overhead of establishing a new Postgres connection for every single invocation of a serverless function. If your function is called 10,000 times per second, and each connection takes 50ms to establish, you’re looking at 500 seconds of connection time per second, which is impossible. Pgbouncer, by keeping a small pool of Postgres connections alive and handing them out one transaction at a time, amortizes this cost. Your Lambda function connects to Pgbouncer (fast), runs its query, and disconnects. Pgbouncer handles the heavy lifting of managing the actual Postgres connections.
The key lever you control here is pool_mode. transaction mode is the default and generally the best for serverless. session mode would keep a Postgres connection open for the entire duration of the client connection, which would defeat the purpose of using Pgbouncer with short-lived serverless functions. statement mode is too aggressive and can break many applications.
The one thing most people don’t realize is that Pgbouncer’s transaction mode doesn’t automatically clean up lingering client connections if the client application crashes or disconnects abruptly without calling client.end(). If your serverless function has an unhandled exception after client.connect() but before client.end(), Pgbouncer might hold onto a client connection slot indefinitely, even though the underlying Postgres connection was released. This can lead to max_client_conn being hit not by active transactions, but by these zombie client connections.
The next problem you’ll likely run into is understanding how Pgbouncer handles connection errors and retries when its pool is exhausted.