Postgres connection pooling, explained with diagrams

Postgres forks a backend process per connection. That's cheap to reason about and expensive at scale — a few thousand idle connections can eat gigabytes of RAM before a single query runs.

A connection pool sits between the app and the database, keeping a small set of real connections open and handing them out to whichever request needs one. When a request finishes, the connection goes back to the pool instead of closing.

Where this gets interesting is pooling mode. Session mode gives a request the connection for its full lifetime — safe for anything, including advisory locks and prepared statements. Transaction mode hands the connection back the moment a transaction commits, which multiplexes far more clients onto the same number of real connections, but breaks anything that depends on session state surviving between statements.

Supabase's pooler runs in transaction mode by default, which is why prepared statements have to be disabled in the driver — the next statement on that logical connection might physically land on a different backend process entirely.

The practical takeaway: pick the pool size based on what the database can actually hold, not how many requests you expect concurrently. A pool that's too large just moves the queueing from your app to Postgres itself.