Connection pooling (pgbouncer)

A connection to the database is expensive

In Postgres, each connection is served by its own server process. It costs memory and setup time. While there aren't many apps, this goes unnoticed. But when ten copies of the app from the load-balancing lesson are running, and each holds twenty connections, that adds up to two hundred processes. The database hits the max_connections limit (100 by default, usually) and starts refusing new connections.

The refusal doesn't look the way articles often describe it. There's no too many connections phrase in Postgres, there are two other ones. By default, the last three slots are reserved for superusers (superuser_reserved_connections = 3), so a regular app role is the first to see this error:

FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

When every slot is taken, Postgres responds like this:

FATAL:  sorry, too many clients already

The first line is worded as in Postgres 16. In Postgres 14 and 15 the ending differs: reserved for non-replication superuser connections. There's also a per-role limit (CONNECTION LIMIT in CREATE ROLE), which gives the error too many connections for role "shop".

What a pool does

A connection pool holds a small set of already-open connections to the database and hands them to the app for the duration of a request, then takes them back. The app doesn't open a connection for every request, it grabs a ready one from the pool.

A pool comes in two kinds. In-app (a pooling library inside the service itself) and external (a separate service in front of the database). You need an external one when there are many copies of the app: then one shared pool caps the total number of connections to the database, instead of each copy capping its own.

pgbouncer

pgbouncer is a lightweight external pool for Postgres. Apps connect not to the database directly but to pgbouncer (port 6432 by default), and it keeps a small set of connections to the actual database. To the app, pgbouncer looks like a regular Postgres: only the host and port in the connection string change.

A hundred client connections can be served by two dozen real ones, because only part of the requests are actively running at any given moment.

A minimal pgbouncer.ini config looks like this:

[databases]
appdb = host=postgres port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 20
max_client_conn = 100

The [databases] section records where pgbouncer goes for the real database. max_client_conn limits how many clients pgbouncer will accept. default_pool_size sets how many connections to the database it opens for one "database + user" pair. Client passwords live in auth_file, as a line like "appuser" "password".

Pool modes

pool_mode sets the mode, and it decides when a server connection is returned to the pool.

• session pooling: the connection is handed to the client for its whole session and returned when the client disconnects. This is the default mode. It only saves anything if clients connect and disconnect often.
• transaction pooling: the connection is handed out for a single transaction. The savings are the largest, so this is usually what sits in front of web apps in production.
• statement pooling: the connection is handed out for a single query, and transactions made of several queries are not allowed. Rare in practice.

Transaction pooling has a cost. A client's next transaction can land on a different server connection, so anything that lives in the session breaks: SET and RESET, LISTEN, PREPARE, temporary tables without ON COMMIT DROP, WITH HOLD cursors. pgbouncer 1.21 and newer can carry protocol-level prepared statements (the ones drivers send) across connections if max_prepared_statements is set. If the app depends on session state, it needs session pooling.

When there are more clients than connections

pgbouncer doesn't refuse a client when there's no free connection to the database. The client gets in line and waits for a connection to free up. It can wait up to query_wait_timeout (120 seconds by default), after which pgbouncer disconnects it. While the queue is short, this is normal. A long queue looks like a slow app with a calm database: the query runs fast in Postgres, and the time goes into waiting in pgbouncer.

You can see the queue in the admin console. It's a pseudo-database called pgbouncer, and you connect to it with regular psql under a user from admin_users:

psql -h 127.0.0.1 -p 6432 -U appuser pgbouncer

The SHOW POOLS; command shows a row for each "database + user" pair:

 database |  user   | cl_active | cl_waiting | ... | sv_active | sv_idle | ... | maxwait | ... |  pool_mode
----------+---------+-----------+------------+-----+-----------+---------+-----+---------+-----+-------------
 appdb    | appuser |        10 |         20 | ... |        10 |       0 | ... |       1 | ... | transaction

• cl_active: clients that are either tied to a server connection right now or just connected and not waiting on anything.
• cl_waiting: clients that sent a request and are waiting for a free connection to the database.
• sv_active and sv_idle: connections to Postgres, busy and free.
• maxwait: how many seconds the oldest client in the queue has been waiting.

If cl_waiting and maxwait keep growing, the pool can't keep up. There are two causes: the database itself is overloaded, or the pool is too small for the number of concurrent requests. SHOW DATABASES; shows the settings for each database, including pool_size. After editing pgbouncer.ini, the settings are applied by running RELOAD; in the admin console or by restarting pgbouncer.

The pool size has an upper bound set by the database. The sum of the pools of all pgbouncers, plus any apps that connect to the database directly, must fit within max_connections minus the reserved slots, and within the role's CONNECTION LIMIT. If pgbouncer tries to open one connection too many, Postgres refuses it, and pgbouncer's log shows a line like server login failed: FATAL too many connections for role "...".

Where this shows up in production

The setup is simple: the app only knows pgbouncer's address, and pgbouncer sorts out the real connections. This takes connection pressure off the database, especially once the app scales horizontally and there are many copies of it.

A sign that it's time to add a pool: errors about exhausted connection slots, or the database eating memory with hundreds of connection processes while the actual load on queries is small.

Common mistakes

• Set up pgbouncer, but the app still goes straight to the database on 5432. Check: pgbouncer's SHOW CLIENTS; has no clients from that app.
• Raised max_connections instead of adding a pool. Every connection still stays a process with its own memory, and the problem comes back the next time it grows.
• Set default_pool_size higher than the database or the role allows. Works until the first spike, then pgbouncer starts getting refused by Postgres.
• Left a small pool in place with a large number of concurrent requests. No errors, but requests sit in a queue, visible in cl_waiting and maxwait.
• Turned on transaction pooling for an app that runs SET or PREPARE at the start of a session. Errors are intermittent: some requests land on a connection without the right settings.

What you'll need for the tasks

• docker compose -f setup/docker-compose.yml logs, ps, exec, and restart: checking a service's log, getting into a container, applying config edits.
• The app's connection string: the host and port it connects to.
• pgbouncer's admin console: SHOW POOLS;, SHOW CLIENTS;, SHOW DATABASES;, RELOAD;.
• The default_pool_size and max_connections settings, a role's CONNECTION LIMIT, and the cl_waiting, maxwait columns.

In the next lesson we'll look at Redis: cache and shared state for scalable services.