Connections & Pooling

Below, clients ask to run transactions against a server with a limited number of backends. Flip the pooler on and off and watch what happens to the overflow.

With the pooler off, every client needs its own backend; once they're exhausted, extra clients are simply rejected (FATAL: sorry, too many clients already). Turn the pooler on and those same clients queue for a small, shared set of backends — nobody is rejected, they just take turns.

One process per connection

Postgres's postmaster forks a backend process for each connection. That process holds its own memory — catalog caches, prepared statements, per-backend buffers — so even an idle connection costs on the order of a few megabytes. On top of that baseline, every sort or hash a query runs can allocate up to work_mem while it runs (and a complex query can run several at once). The total number of backends is capped by max_connections:

SHOW max_connections;      -- the hard cap on backends
SHOW work_mem;             -- memory PER sort/hash, PER connection
SHOW shared_buffers;       -- the shared cache (not per-connection)
SELECT count(*) AS backends FROM pg_stat_activity;  -- backends now (incl. background procs)

Because each backend can allocate work_mem for every sort or hash in a query, raising max_connections into the thousands is a memory time-bomb. Do the arithmetic — even one sort per connection at the cap:

SELECT current_setting('max_connections')      AS max_conn,
     current_setting('work_mem')            AS work_mem,
     pg_size_pretty(
       current_setting('max_connections')::int
       * pg_size_bytes(current_setting('work_mem'))
     )                                        AS worst_case_one_sort_each;

That's the ceiling if every connection runs one sort. A real query runs several sorts and hashes at once, so multiply again: 100 connections × 4 MB × 5 operations is 2 GB — from work_mem alone, on top of shared_buffers and each backend's own baseline. Push max_connections into the thousands and one traffic spike can OOM a box with plenty of idle CPU. The healthy number of active backends is small — a few per CPU core; everything past that just context-switches and contends.

The fix: a connection pooler

A pooler sits between your app and Postgres and keeps a small set of real backends open, lending them out to many client connections. It solves two problems at once. The obvious one: many clients share a few backends, so you never hit max_connections. The subtler one: opening a backend isn't free — each new connection costs an OS fork, authentication, and warming that backend's catalog caches, roughly a millisecond or two plus a few MB every time. An app that connects, runs one query, and disconnects on every request (the classic serverless pattern) pays that toll constantly. A pooler keeps backends warm and reused, so clients skip the setup entirely.

The most impactful mode is transaction pooling (PgBouncer's transaction mode, and what Supabase's Supavisor does): a client is assigned a backend only for the duration of a transaction, then it returns to the pool. Since most connections are idle most of the time, a few dozen backends can serve thousands of clients.

• Session pooling — a backend is tied to a client for its whole session (simple, but doesn't multiplex much).
• Transaction pooling — a backend is borrowed per transaction (huge multiplexing; the default choice at scale).
• Statement pooling — per statement (most aggressive, most restrictions).

Who's connected? Reading pg_stat_activity

pg_stat_activity is your window into every backend, and its state column is the one you'll read most:

• active — running a query right now.
• idle — connected but doing nothing. Harmless beyond the memory it holds.
• idle in transaction — inside a BEGIN, waiting on the application, not the database. This is the dangerous one: it pins the VACUUM cleanup horizon and can hold locks while doing no work (the VACUUM and Locks lessons).
• idle in transaction (aborted) — the same, but the transaction already errored and is just sitting there until someone sends ROLLBACK.

Add wait_event_type / wait_event and you can see what an active backend is blocked on — a lock, an I/O, the client. The classic firefight is WHERE state = 'idle in transaction' ORDER BY xact_start to find the forgotten transaction throttling the whole database.

SELECT pid, state, wait_event_type, backend_type
FROM pg_stat_activity
ORDER BY backend_type;

Your turn

SHOW max_connections;

SELECT count(*) AS backends FROM pg_stat_activity;