Configuring PgBouncer for Serverless High Concurrency

A long-running API server opens a small pool of database connections at startup and reuses them for millions of requests. The connection count stays flat, and Postgres doesn't mind. Serverless breaks that model. Every concurrent function instance is its own process with its own connections, so the number of database connections rises and falls with traffic. Traffic is the one thing you don't control.

The usual symptom is FATAL: sorry, too many clients already during a spike. The less obvious symptom is a database that degrades long before it hits max_connections, because it spends its memory and CPU on connections instead of queries.

This article covers why that happens, how PgBouncer fixes it, a production-oriented configuration for transaction pooling, the application changes transaction pooling forces on you, and when a managed pooler is the better choice.

Why serverless and Postgres connections clash

Postgres uses a process-per-connection architecture. Every client connection is a separate backend process forked by the postmaster, with its own memory for catalog caches, query plans and sort buffers. Opening one involves a TCP handshake, usually a TLS handshake, authentication (SCRAM-SHA-256 does a deliberately expensive key derivation), and backend startup. That cost is fine once per server boot. It isn't fine once per function cold start, multiplied by hundreds.

AWS Lambda makes this worse in three ways:

• Concurrency equals connections. Each execution environment handles one request at a time. A burst that needs 500 concurrent invocations creates up to 500 environments, and each one opens its own connection.
• Frozen environments keep their sockets. After an invocation, Lambda freezes the environment and may reuse it later. A connection opened outside the handler stays open on the database side while the function sits idle, sometimes for many minutes. You pay for connections that do no work.
• No graceful shutdown. When an environment is recycled, nothing reliably closes the connection. Postgres finds out when TCP keepalives or timeouts detect the dead peer.

Raising max_connections doesn't solve this. Each backend consumes memory, and a large number of active backends competing for a few CPU cores leads to contention on locks and shared structures. Past a point, throughput goes down as connections go up. The PostgreSQL wiki page on the number of database connections explains why the useful number of concurrently active connections is roughly proportional to cores, not to clients.

What PgBouncer actually does

PgBouncer is a lightweight proxy that speaks the Postgres wire protocol. Clients connect to it as if it were Postgres. PgBouncer keeps a small set of real server connections and lends them out to clients as needed.

A client connection to PgBouncer is cheap. It's a socket and a little memory in a single event-driven process, not a forked backend. PgBouncer can hold thousands of mostly idle client connections, the frozen Lambdas included, while Postgres sees only the pool. The pool mode decides when a lent-out server connection goes back to the pool.

In transaction mode, a server connection is busy only while a transaction is running. A thousand clients that each run a 5 ms transaction a few times per second can share a few dozen server connections. That's the multiplexing that makes serverless workable.

A production-oriented pgbouncer.ini

Here's a configuration that works as a starting point for a serverless workload. The comments explain the reasoning. Tune the numbers to your hardware and traffic rather than copying them.

[databases]
; Clients connect to "app" on PgBouncer; it forwards to the real database.
app = host=10.0.1.20 port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; The core decision for serverless.
pool_mode = transaction

; Client side: be generous. Idle clients are cheap here.
; Make sure the process file descriptor limit (ulimit -n) is higher than this.
max_client_conn = 5000

; Server side: be stingy. This is what Postgres actually sees.
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
; Hard cap per database across all users, for this PgBouncer process.
max_db_connections = 40

; Protocol-level prepared statements in transaction mode (PgBouncer 1.21+).
max_prepared_statements = 200

; Fail fast instead of queueing forever during overload.
query_wait_timeout = 30
; Kill clients that open a transaction and wander off.
idle_transaction_timeout = 30

server_idle_timeout = 300
server_lifetime = 3600

; TLS between clients and PgBouncer, and between PgBouncer and Postgres.
client_tls_sslmode = require
client_tls_key_file = /etc/pgbouncer/tls/server.key
client_tls_cert_file = /etc/pgbouncer/tls/server.crt
server_tls_sslmode = require

admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

A few settings deserve more than a comment.

Pool sizing

default_pool_size applies per database/user pair. Two application roles hitting the same database means two pools. max_db_connections is the backstop that caps the database total no matter how many roles connect.

These limits are per PgBouncer process. If you run three PgBouncer instances behind a load balancer, each with max_db_connections = 40, Postgres can see 120 connections. Do that arithmetic against max_connections and leave headroom for migrations, admin sessions and replication.

For the pool size itself, start small. The PostgreSQL wiki's starting formula, (core_count * 2) + effective_spindle_count, is a rough heuristic, not a law. Measure from there. If clients are waiting while the database CPU is idle, grow the pool. If the database is saturated, a bigger pool only adds contention.

Timeouts that protect you during spikes

When every server connection is busy, new transactions wait in PgBouncer's queue. That's the point: the queue absorbs the burst instead of the database. An unbounded queue just moves the outage, though. query_wait_timeout fails waiting clients after a set time, which is better than letting Lambdas wait until their own timeout. idle_transaction_timeout matters more than it looks. In transaction mode, a client that runs BEGIN and then stalls on an HTTP call holds a server connection the whole time.

Be careful with client_idle_timeout. It cleans up connections from frozen Lambdas, but a thawed function then finds a dead socket on its first query. Set it only if your client code handles reconnects, and keep it longer than your typical gap between invocations.

Authentication

auth_file holds usernames and SCRAM secrets, which you can copy from pg_authid. That works for a handful of service roles. If you manage many roles, use auth_user with auth_query so PgBouncer looks up credentials in the database. The usual pattern is a SECURITY DEFINER lookup function, so the auth user doesn't need superuser access to pg_authid.

"app_user" "SCRAM-SHA-256$4096:<salt>$<stored_key>:<server_key>"

What transaction pooling breaks

This is the part people skip, and it's where the production bugs come from. In transaction mode, two consecutive transactions from the same client can run on different server connections. Anything that relies on session state either fails or, worse, leaks into another client's session. The PgBouncer feature matrix lists these as unsupported in transaction mode:

• SET / RESET of session parameters (a few startup parameters such as TimeZone and application_name are tracked)
• LISTEN
• WITH HOLD cursors
• SQL-level PREPARE / DEALLOCATE
• Temporary tables with PRESERVE ROWS or DELETE ROWS
• LOAD
• Session-level advisory locks

Most of these have a transaction-scoped replacement:

• Instead of SET, use SET LOCAL or set_config(..., true) inside the transaction. This is also the safe way to pass a tenant ID to row-level security policies.
• Instead of pg_advisory_lock, use pg_advisory_xact_lock, which is released at commit.
• Instead of session temp tables, use ON COMMIT DROP.
• For LISTEN, migrations and long admin sessions, connect directly to Postgres, or through a separate session-mode pool.

BEGIN;
SELECT set_config('app.tenant_id', '42', true);  -- true = local to this transaction
SELECT pg_advisory_xact_lock(hashtext('invoice-run'));
-- ... work that RLS policies scope via current_setting('app.tenant_id')
COMMIT;

Prepared statements

This used to be the biggest headache. Many drivers and ORMs use protocol-level named prepared statements. Before PgBouncer 1.21, a statement prepared on one server connection didn't exist on the next, so you got prepared statement "s1" does not exist errors. Since 1.21, PgBouncer can track and re-prepare protocol-level statements in transaction mode when max_prepared_statements is non-zero. Since 1.24 the default is 200.

If you control the PgBouncer version, run a current release and leave this on. If you're behind an older or managed pooler that doesn't support it, disable named prepared statements in the driver:

• Prisma: add ?pgbouncer=true only for PgBouncer older than 1.21. Prisma's docs recommend not setting it on 1.21+. Run migrations against a direct URL either way.
• postgres.js: postgres(url, { prepare: false })
• asyncpg: statement_cache_size=0
• psycopg 3: prepare_threshold=None
• PgJDBC: prepareThreshold=0

SQL-level PREPARE is still unsupported in transaction mode on every version.

The Lambda side

PgBouncer handles fan-in, but the function code still matters. Create the client outside the handler so warm invocations reuse it, and cap the in-function pool at one connection. A Lambda environment serves one request at a time, so a larger pool only wastes PgBouncer client slots.

import pg from "pg";

// Created once per execution environment, reused across warm invocations.
// DATABASE_URL points at PgBouncer (port 6432), not at Postgres.
const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 1,
  idleTimeoutMillis: 60_000,
  connectionTimeoutMillis: 5_000,
});

export const handler = async (event) => {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("SELECT set_config('app.tenant_id', $1, true)", [event.tenantId]);
    const { rows } = await client.query(
      "SELECT id, status FROM orders WHERE id = $1",
      [event.orderId]
    );
    await client.query("COMMIT");
    return rows[0] ?? null;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
};

Keep transactions short, and never hold one open across a network call to another service. In transaction mode, the length of your transactions sets the size of your effective pool.

Where to run it and how to watch it

PgBouncer is single-threaded, so one process uses one CPU core. For most workloads that's plenty. If a single process becomes CPU-bound, run several processes on the same port with so_reuseport = 1, and configure peering (peer_id and a [peers] section) so cancel requests reach the right process. You can also run several instances behind a network load balancer. Either way, remember that the pool limits multiply.

Run it inside the same network as the database, for example on a small container service behind an internal load balancer or on the database host itself. Running PgBouncer inside each function defeats the purpose, because pooling only helps when many clients share one pooler.

To watch it, use the admin console:

psql "host=pgbouncer.internal port=6432 dbname=pgbouncer user=pgbouncer_stats" -c "SHOW POOLS;"

The columns to alert on are cl_waiting (clients queued for a server connection) and maxwait (how long the oldest one has waited). Sustained waiting with an idle database means the pool is too small. Waiting with a busy database means the database is the bottleneck, and a bigger pool won't help. SHOW STATS gives per-database transaction and query rates plus average wait times.

Managed alternatives

You don't always have to operate PgBouncer yourself.

• Amazon RDS Proxy pools connections for RDS and Aurora, integrates with IAM and Secrets Manager, and handles failover. Watch for pinning: when it detects session state such as SET statements or certain prepared statement patterns, it pins the client to one server connection and you lose multiplexing. It's billed on the capacity of the underlying database, which matters for small instances.
• Supabase provides a transaction-mode pooler endpoint alongside the direct connection.
• Neon runs PgBouncer in transaction mode behind its -pooler connection hostnames, and also offers a serverless driver that queries over HTTP or WebSockets.

The transaction-mode rules above apply to all of them. A managed pooler removes the operational work, not the session-state constraints.

Checklist

• Never point serverless functions straight at Postgres. Put a pooler in between.
• Use pool_mode = transaction. Session mode only moves the problem.
• Set max_client_conn high and the server-side pools low. Cap totals with max_db_connections, and multiply by the number of PgBouncer processes.
• Set query_wait_timeout and idle_transaction_timeout so overload fails fast.
• Run PgBouncer 1.21 or later with max_prepared_statements above zero, or disable named prepared statements in your driver.
• Replace session state with SET LOCAL, pg_advisory_xact_lock and ON COMMIT DROP.
• Send migrations, LISTEN and admin work over a direct connection.
• In functions, create the client outside the handler with a pool size of one.
• Alert on cl_waiting and maxwait from SHOW POOLS.

When you don't need any of this: if your API runs on a fixed number of long-lived containers with in-process pools and the total stays well below what the database handles comfortably, PgBouncer adds a hop and another component to operate without much benefit. It earns its place when the client count is large, unpredictable or bursty, and serverless is all three.