postgres://internals

Connections & Pooling

// the problem

Open a connection to Postgres and it does something surprising: it forks a whole operating-system process — a dedicated backend — just for you. That process is powerful (it's where your queries run) but it isn't free, and there's a hard cap on how many can exist. A serverless app that opens a fresh connection per request will hit that wall fast. Understanding this — and the pooler that fixes it — is one of the most practical things you can know about running Postgres at scale.

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.

fig.01 · connections & pooling
clients
8
backends max
4
in use
0
waiting
0
rejected
0

server backends (processes)

·
·
·
·

clients

12345678

without a pooler, every client needs its own backend — extras are rejected

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:

sql · live postgresrun me first
⌘/ctrl + enter

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 — a burst of complex queries can each grab many multiples of work_mem and push the server into swap or the OOM killer. The healthy number of active backends is small — roughly a few per CPU core.

// why it matters · idle connections aren't harmless

An idle connection still holds its process and memory. Worse, an idle connection in a transaction pins the cleanup horizon (from the VACUUM lesson) and blocks vacuuming database-wide. “Thousands of mostly-idle connections” is the classic way a busy app melts a database that has plenty of CPU to spare.

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. 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 goes back 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).

// gotcha · transaction pooling breaks session state

Because a client doesn't keep the same backend between transactions, anything that lives in a session — SET parameters, session-level advisory locks, LISTEN/NOTIFY, some prepared statements, WITH HOLD cursors — won't behave as expected under transaction pooling. Apps designed for a pooler keep their transactions self-contained and avoid relying on cross-transaction session state.

Who's connected?

pg_stat_activity is your window into every backend — what it's running, its state, and how long it's been there. Hunting for idle in transaction rows with an old xact_start is how you find the connection that's blocking your vacuums.

sql · live postgreseditable — run it
⌘/ctrl + enter

Your turn

// challengewrite it yourself

What is this server's hard limit on the number of connections?

⌘/ctrl + enter
// challengewrite it yourself

Count how many backends are currently connected to the server.

⌘/ctrl + enter

// what you now understand

  • 01Postgres forks one OS process (a backend) per connection; each holds real memory even when idle, capped by max_connections.
  • 02work_mem is reserved per sort/hash per connection, so very high max_connections risks OOM under load — keep active backends to a few per core.
  • 03Idle connections waste memory, and idle-in-transaction ones pin the VACUUM horizon and block cleanup database-wide.
  • 04A connection pooler (PgBouncer / Supavisor) keeps a few backends and lends them to many clients; transaction pooling borrows a backend only per transaction.
  • 05Transaction pooling multiplexes best but breaks cross-transaction session state (SET, session advisory locks, LISTEN/NOTIFY).
  • 06pg_stat_activity shows every backend's state — use it to find idle-in-transaction connections and long-running queries.

// self-test

A serverless app opens a new Postgres connection on every request and starts failing with 'too many clients already' under load. Best fix?

// self-test

Why keep the number of active backends close to a small multiple of the CPU core count?

// go deeper

nextThe Executor