PostgreSQL connection pooling: why you need it and how PgBouncer works

Short answer: PostgreSQL runs one operating-system process per connection, so thousands of idle connections waste memory and slow the server. A pool keeps a small number of real connections and shares them among many clients. Use the pool built into your application driver first. Add PgBouncer when many app instances, serverless functions or workers would otherwise exceed the database's limit.

Why can't you just open more connections?

Each connection is a backend process with its own memory. The default max_connections is 100. Raising it to 2,000 does not give you 20 times the throughput. A database does its work on a limited number of CPU cores, and past a point more concurrent queries mean more context switching and lock contention. A pool of 20 to 50 busy connections often outperforms 1,000 competing ones.

A useful starting rule for pool size is a small multiple of CPU cores, then measure. It is almost never in the hundreds.

What is an application-side pool?

Most drivers and ORMs include one: pgxpool in Go, pg.Pool in Node, HikariCP in Java, SQLAlchemy's pool in Python. Each app process keeps, say, 10 connections open and lends them out per request. This is enough when you run a handful of long-lived processes.

It stops being enough when you multiply. Twenty app instances times 10 connections is 200 connections. Add workers and a cron job and you hit the limit. Serverless is worse, since each short-lived function can open its own connection.

What does PgBouncer do?

PgBouncer is a lightweight proxy that sits between your apps and PostgreSQL. Apps connect to it as if it were the database. It maintains a small set of server connections and assigns them to clients. A minimal config:

[databases]
appdb = host=127.0.0.1 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 = 1000

Which pool mode should you use?

• Session: a client keeps a server connection until it disconnects. Safest and least efficient. Everything works.
• Transaction: a server connection is lent only for the duration of one transaction. Highly efficient and the usual choice. Session-level features break.
• Statement: each statement may use a different connection. Multi-statement transactions are not allowed. Rarely appropriate.

What breaks in transaction mode?

Anything that relies on session state, since the next transaction may land on a different server connection.

• SET commands without LOCAL, and session-level set_config. This matters if you use row-level security with a per-request tenant setting: use set_config(..., true) inside the transaction so it resets at commit.
• Advisory locks taken at session level.
• LISTEN and NOTIFY.
• Temporary tables that live across transactions.
• Prepared statements, in older PgBouncer versions. Recent versions support protocol-level prepared statements in transaction mode. Check your version and driver settings, and disable named prepared statements if you see "prepared statement does not exist" errors.

What are the symptoms of getting it wrong?

• FATAL: sorry, too many clients already from PostgreSQL means you hit max_connections.
• Requests hang with the database nearly idle: the app pool is exhausted, often because a transaction was never closed or a connection was never returned.
• no more connections allowed from PgBouncer means max_client_conn or a pool limit is too low.
• Cross-request data leaks from a session-level setting leaking through a shared connection. This one is serious.

How do you size it?

Add up the connections each app instance may open, add admin and migration headroom, and compare with max_connections. If the sum is larger than the database can run efficiently, put PgBouncer in transaction mode in front and set default_pool_size near what the database handles well. Watch SHOW POOLS; in PgBouncer for cl_waiting. If clients wait often, either queries are too slow or the pool is too small, and finding the slow queries comes before raising the number.