Skip to content
·7 min read

PostgreSQL Connection Pooling: Why Your API Falls Over at 100 Concurrent Users

Most Node.js apps open a new Postgres connection per request and wonder why the database refuses connections at scale. Here's how connection pooling actually works.

PostgreSQLNode.jsBackendPerformance

I have been called in for the same emergency four times this year. A Node.js API handles daily traffic fine. Then a launch, a press hit, or a viral post sends 100 concurrent users instead of 10. The API throws too many connections errors, requests time out, and the database CPU spikes to 100%.

The cause is almost always the same: the app opens database connections without a real pool, or the pool is sized wrong, or PgBouncer is misconfigured. Here is how connection pooling actually works, why your app falls over, and how to fix it.

Why pooling matters

Opening a new Postgres connection is expensive. Postgres forks a backend process, authenticates, sets up session state, and only then runs your query. That is 20-50ms per connection on a typical setup. Under load, this overhead dominates.

A connection pool keeps a warm set of connections and reuses them. Your code asks for a connection, runs a query, and returns it to the pool. No fork, no auth, no setup overhead.

Without a pool, every request is a fresh connection. With 100 concurrent requests, you fork 100 backends. Postgres has a max_connections setting (default 100), and once you hit it, new connections are refused. Your API errors out.

The Node.js pool

Most Node.js Postgres drivers (pg, postgres.js, Prisma's driver) include a pool. Use it.

import { Pool } from 'pg'
 
const pool = new Pool({
  host: process.env.DB_HOST,
  port: 5432,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  max: 20,                // max connections in the pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
})
 
// Each call checks out a connection, runs, returns it
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId])

The default max of 10 is fine for low traffic. For a typical production API handling a few hundred requests per second, 20-25 is the sweet spot per Node.js process.

Why not more? Because Postgres has overhead per connection. Hundreds of idle connections waste memory and slow down the database's bookkeeping. Run more Node.js processes if you need parallelism, not bigger pools.

Serverless changes the math

Serverless platforms (Vercel, Lambda, Cloud Run) scale by spawning many short-lived instances. Each instance opens its own pool. With 50 instances and a pool size of 10, you have 500 connections to Postgres. The database falls over.

The fix is PgBouncer in transaction mode. PgBouncer sits between your app and Postgres. It accepts thousands of client connections and multiplexes them onto a small number of real Postgres connections.

50 Lambda instances
  → 50 client connections to PgBouncer
    → 25 real Postgres connections

Supabase, Neon, and most managed Postgres providers ship a PgBouncer-compatible pooler out of the box. Turn it on and point your connection string at the pooler URL.

Transaction mode vs session mode

PgBouncer has two main modes:

  • Session mode — one client owns a backend connection for the lifetime of the session. Behaves like direct Postgres. Works with prepared statements, session-level features, SET commands.
  • Transaction mode — one client owns a backend connection for the duration of a transaction only. Returns it to the pool when the transaction commits or rolls back. Scales much further.

Transaction mode is required for serverless. Session mode works for traditional long-running app servers.

The prepared statement trap

Transaction mode breaks traditional prepared statements. Here is why.

A prepared statement is bound to a specific backend connection. In transaction mode, your query might run on connection A, the next query on connection B. The prepared statement exists on A but not on B. The query fails with "prepared statement does not exist."

Three fixes:

  1. Disable prepared statements in your driver

    import { Pool } from 'pg'
 
const pool = new Pool({
  // ...
  // For pg, set this in options or use a query format that does not prepare
})

    For Prisma, set ?pgbouncer=true in the connection string.

  2. Use the new protocol-level prepared statements (Postgres 17+, libpq 17+) — these live below the session layer and work with PgBouncer.

  3. Use session mode — only viable if you have long-running app processes, not serverless.

Sizing the pool

The right pool size depends on:

  • How many Node.js processes you run
  • How many concurrent queries each process handles
  • What max_connections is on your Postgres server

A starting point:

processes * pool_size + admin_margin < max_connections

Example: 4 Node.js processes with pool size 20 each = 80 connections. Add 20 for migrations, admin queries, monitoring. Stay under max_connections (default 100, often raised to 200-300 on managed databases).

If you are using PgBouncer, set the Node.js pool small (2-5 per instance). PgBouncer is the real pool. No point having two large pools.

Monitoring

Watch these metrics:

  • Active connections — currently executing a query.
  • Idle connections — open but not doing anything.
  • Waiting connections — clients blocked waiting for a connection from the pool. This number should be zero. If it is non-zero, your pool is too small or queries are too slow.

Query pg_stat_activity to see what is happening:

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

A common pattern: 5 active connections, 95 idle connections. The idle ones are holding memory for nothing. Lower the pool size.

Another pattern: 20 active connections, 30 waiting. Your queries are slow. Add indexes, fix the slow ones, then revisit pool size.

Set aggressive timeouts

Long-running idle transactions are silent killers. They hold locks, block migrations, and consume a backend process.

Set this on every connection:

SET idle_in_transaction_session_timeout = '30s';

In pg:

const pool = new Pool({
  // ...
  options: '-c idle_in_transaction_session_timeout=30000',
})

Also set a statement_timeout for runaway queries:

SET statement_timeout = '10s';

These protect you from a single bad query taking down the whole database.

The migration concern

Migrations are a special case. They hold schema locks. They need their own dedicated connection (not from the pool, or the pool will multiplex them and you get weird behavior).

Most migration tools (Prisma migrate, node-pg-migrate, Drizzle Kit) handle this correctly by using a direct connection, not the pool. If your migrations use the pool, switch them to direct.

The checklist

Before you scale:

  • Use a real connection pool (pg Pool, postgres.js pool, or PgBouncer)
  • Pool size 10-25 per Node.js process, not 100
  • PgBouncer in transaction mode if you are serverless
  • Disable or upgrade prepared statements if using transaction mode
  • Set idle_in_transaction_session_timeout (30s)
  • Set statement_timeout (10s)
  • Monitor active, idle, and waiting connections
  • Migrations use a direct connection, not the pool
  • Total connections across all processes stays under max_connections

Connection issues are the most common database scaling wall. The fix is rarely "more hardware." It is using a pool, sizing it correctly, and adding PgBouncer when you go serverless.

Need help scaling your Postgres-backed API?

I have rescued apps that fell over on launch day and tuned Postgres setups for sustained scale. Let's talk.

Frequently Asked Questions

What is a PostgreSQL connection pool?

A connection pool is a cache of database connections that the application reuses across requests. Opening a new Postgres connection is expensive (fork a process, authenticate, set up state), so pools keep connections warm and hand them out on demand.

What is PgBouncer and when should I use it?

PgBouncer is a lightweight connection pooler for PostgreSQL. Use it when you have many application processes or serverless functions that would otherwise open too many direct connections. Run it in transaction mode for serverless, session mode for traditional apps.

How many Postgres connections should I allow?

Start with 10-25 per application process. Postgres can technically handle hundreds but performance degrades. Total across all processes, stay under 100 for a small database, scale up your database if you need more.

Why do my prepared statements fail with PgBouncer?

PgBouncer in transaction mode routes each transaction to a different backend connection, but prepared statements are bound to a specific connection. Use the new prepared statement protocol in libpq, set statement_cache_size to 0 in your driver, or run PgBouncer in session mode if you must use prepared statements.

← Back to blog