BeginnerIn-depth3 min readUpdated 2 Oct 2026

Why do too many connections hurt PostgreSQL, and how does a connection pooler like PgBouncer help?

SHORT ANSWER

Each PostgreSQL connection is a separate server process with its own memory, so thousands of mostly idle connections waste RAM and add CPU overhead. A pooler like PgBouncer lets many clients share a small number of real database connections, handing a connection to a client only while it runs a transaction.

Explain it simply

Think of a bank branch with five counters. If two hundred customers each demand their own personal counter for the whole day, the bank cannot cope, even though most of them are just sitting and filling forms. A token system fixes it: customers wait in the hall, and whenever a counter is free, the next customer walks up, finishes their work, and leaves the counter for the next person.

PgBouncer is that token system. Your application can open hundreds of connections to PgBouncer, which are cheap. PgBouncer keeps a small pool of real PostgreSQL connections and lends them out only when there is actual work to do.

Example

A small pgbouncer.ini:

text

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

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

The application now connects to port 6432 instead of 5432. Up to 2000 clients can connect, but PostgreSQL only ever sees about 20 connections for this database and user.

sql

-- On PostgreSQL: see what connections are doing
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

If most rows say idle, connections are being held without doing work, which is the classic sign you need pooling.

How it works

PostgreSQL uses a process-per-connection model. Every new connection starts a backend process, which takes time to create and holds its own memory. Each sort or hash inside a query can also use up to work_mem, so many active connections multiply memory use. Past a point, more connections make total throughput go down because the CPUs spend time switching between processes and fighting over locks. The default max_connections is 100 for this reason.

PgBouncer has three pool modes:

Session: a client keeps one server connection until it disconnects. Safest, but saves the least.
Transaction: a server connection is assigned for one transaction, then returned. This is the common choice for web apps.
Statement: returned after each statement; multi-statement transactions are not allowed.

Real-world use

A Node.js or Django app running on 30 containers, each with its own pool of 20, would open 600 connections. Putting PgBouncer in front keeps PostgreSQL at a few dozen.
Serverless functions that open a new connection on every request, which is expensive without a pooler.
Managed services often offer a built-in pooler that runs PgBouncer or something similar.

Common mistakes

Raising max_connections to 2000 as the fix. It hides the problem and can make the database slower.
Using transaction mode while relying on session state, such as SET commands that should persist, session-level advisory locks, LISTEN, or temporary tables across transactions. In transaction mode the next transaction may run on a different server connection.
Forgetting that the application's own pool and PgBouncer both exist. Size them together.

What to say in the interview

"PostgreSQL creates a separate process for every connection, with its own memory, so thousands of connections, most of them idle, waste RAM and cause CPU overhead. Throughput usually peaks at a modest number of active connections. A pooler like PgBouncer accepts many cheap client connections and multiplexes them onto a small pool of real connections. In transaction mode a server connection is lent only for the length of a transaction, which gives the biggest saving, but session-level features like SET, LISTEN and session advisory locks do not carry across transactions."

Follow-up questions

What is the difference between session and transaction pooling?
How would you choose the pool size?
What is the difference between an application-side pool and PgBouncer?

Go deeper

Read these once the basics above feel easy.

A common starting point for pool size is a small multiple of the database server's CPU cores, then adjusting by measuring latency and throughput under load. Prepared statements used to be a problem in transaction mode because they live on one server connection. PgBouncer 1.21 and later can track protocol-level prepared statements in transaction mode when max_prepared_statements is set; SQL-level PREPARE statements are still not supported in that mode.

#postgresql #connection-pooling #pgbouncer #performance #scaling