Postgres Connection Pooling, Explained Properly

The Problem#

It always shows up at the worst possible time. Your app has been running fine in development and through the first quiet days in production. Then a launch, a sale, or a spike in signups sends real traffic your way — and Postgres starts slamming the door: FATAL: sorry, too many clients already. Requests time out, dashboards go red, and the queries you'd carefully optimized are nowhere near the problem.

Nothing about your SQL changed. What changed is how many processes are talking to the database at once. On a serverless platform every function instance opens its own connections, and under load there can be hundreds of them — each one demanding a slice of a resource Postgres hands out very sparingly. The fix isn't a bigger database; it's putting something between your app and Postgres that lets thousands of clients share a handful of real connections. This article explains exactly how that works, and how to set it up without quietly breaking your app.

Why It Matters#

Each Postgres connection is a separate OS process with its own memory. The default max_connections is often 100, and each connection can cost several megabytes of RAM. Serverless functions scale horizontally and each instance opens its own pool, so 200 concurrent functions can demand far more connections than the database will ever allow.

Core Concepts#

A connection pool keeps a small set of established connections and hands them out to requests as needed, instead of opening a new connection per request. There are two layers people confuse:

• Application-side pools (e.g. pg.Pool in Node) reuse connections within a single process. They don't help when you have hundreds of processes.
• External poolers (e.g. PgBouncer) sit between your app and Postgres and multiplex many client connections onto few database connections.

The pooler is what lets thousands of clients share a handful of real Postgres connections:

Implementation#

A pooler like PgBouncer is configured with a pool mode. Transaction mode is the one that unlocks high concurrency:

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

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Here 1000 clients share a pool of 20 real Postgres connections. A client only holds a server connection for the duration of a transaction, then returns it.

Your app connects to the pooler instead of Postgres directly:

const pool = new Pool({
  connectionString: process.env.DATABASE_URL, // points at PgBouncer :6432
  max: 5, // keep app-side pools small; the pooler does the real work
});

Common Mistakes#

• Using prepared statements with transaction-mode pooling. Server connections are shared across clients, so a prepared statement created on one may not exist on the next. Disable them or use a driver flag.
• Oversizing app-side pools. Twenty functions each with max: 20 is 400 connection attempts. Keep app pools small and let the pooler multiplex.
• Holding transactions open. In transaction mode, a long-running transaction pins a server connection and starves everyone else.

Production Considerations#

Set default_pool_size based on your database's max_connections minus headroom for migrations, admin tools, and replicas. Monitor pooler wait time: if clients queue for a server connection, you need a bigger pool or shorter transactions, not a bigger database.

Security#

Terminate TLS at the pooler and require it from clients. Give the pooler its own limited database role rather than a superuser, so a compromised pooler can't do arbitrary damage.

Performance#

Transaction-mode pooling can lift effective concurrency by an order of magnitude without touching max_connections. The trade-off is feature compatibility: session-level state (prepared statements, advisory locks, SET that outlives a transaction) doesn't survive connection reuse.

Summary#

Postgres connections are expensive processes, and serverless multiplies how many you ask for. Put a transaction-mode pooler in front, keep app-side pools small, avoid session-level state, and watch pooler wait time. Done right, a handful of real connections can serve thousands of clients.