sdlc.dev

SQL and PostgreSQL path · Running it for real · Topic 11 of 13

Connection limits and pooling (PgBouncer)

Not started

PostgreSQL serves each connection with its own operating-system process. That design is robust, but each process costs memory and the server has to coordinate all of them, so the number is capped by max_connections, 100 by default (a few of those slots are reserved for superusers). Opening a connection also takes time: TCP, often TLS, authentication, then a fork. So applications keep pools of open connections, and large deployments put a pooler such as PgBouncer between the applications and the database.

Why it matters: Running out of connections is one of the most common ways a PostgreSQL-backed service falls over during a traffic spike or a deploy.

  1. 1Each connection is a process

    PostgreSQL forks a backend process per connection, with its own memory, so max_connections (default 100) is a real limit.

  2. 2Pools multiply

    Every application instance has its own pool; twenty pods with twenty connections each is 400 connections.

  3. 3PgBouncer shares server connections

    In transaction mode a server connection is lent to a client only for the length of one transaction, so thousands of clients can share a few dozen.

  4. 4Transaction mode has rules

    Session state (SET, LISTEN, session advisory locks, WITH HOLD cursors, SQL-level PREPARE) does not survive between transactions.

1

How it works

The explanation, with examples you can run.

Counting connections

The limit is per server. Every application instance has its own pool, and they add up:

pods x pool size per pod  = connections on the database  30 x 10                 = 300   (max_connections = 100: most of them fail)

The error the client sees is FATAL: sorry, too many clients already. It tends to appear at the worst moments: during a deploy, when old and new pods both hold pools, or when autoscaling adds instances in a spike. Check what is connected:

SELECT usename, application_name, state, count(*)FROM pg_stat_activityGROUP BY 1, 2, 3ORDER BY 4 DESC;

Raising max_connections is rarely the answer. The server can only run as many queries truly in parallel as it has cores, and thousands of mostly idle backends still cost memory and coordination. Many idle connections usually mean the pools are bigger than they need to be.

PgBouncer's pool modes

PgBouncer accepts many client connections and keeps a small number of real server connections, per database and user pair (default_pool_size, 20 by default). How long a client keeps a server connection depends on the mode:

ModeA client holds a server connectionSession features
sessionFor as long as the client stays connectedAll work
transactionOnly for the length of each transactionSome do not (below)
statementOnly for one statement; multi-statement transactions are refusedFewer still

Transaction mode is what makes pooling pay off: a client that spends most of its time between transactions holds nothing.

[databases]shop = host=10.0.0.5 port=5432 dbname=shop [pgbouncer]pool_mode = transactiondefault_pool_size = 20max_client_conn = 2000max_prepared_statements = 200

What breaks in transaction mode

Anything that relies on a session lasting beyond one transaction, because your next transaction may run on a different server connection:

  • SET and RESET (use SET LOCAL inside the transaction instead);
  • LISTEN;
  • session-level advisory locks (use pg_advisory_xact_lock);
  • WITH HOLD cursors and temporary tables that outlive a transaction;
  • SQL-level PREPARE / DEALLOCATE.

Protocol-level prepared statements, which most drivers use, work in transaction mode from PgBouncer 1.21 when max_prepared_statements is above zero; since 1.24 it defaults to 200, so the support is on unless you turn it off.

Sizing

Start small. A pool a few times the database's core count is usually plenty, and a query waiting briefly for a pooled connection is cheaper than one fighting hundreds of others for CPU and locks. Watch the time clients wait for a connection, not just the number in use; a long wait means queries hold connections too long, often because a transaction stays open around slow application work.

2

Check yourself

3 questions. Get them all right to finish this topic.

  1. 1.Thirty application pods each open a pool of 10 connections to a server with max_connections = 100. What happens as they start?
  2. 2.Behind PgBouncer in transaction mode, a client runs SET search_path = tenant_7 and then, in a later transaction, a query. Which schema does the query use?
  3. 3.Your driver uses protocol-level prepared statements, and you move to PgBouncer transaction mode. What do you need?

0 of 3 right so far. Get all three to finish this topic.

Go deeper