Postgres connection pooling at scale + serverless cold starts: pgbouncer, Supavisor, RDS Proxy

The monitoring dashboard showed 4,200 active Postgres connections. The database had a max_connections setting of 500. The math did not work, which meant the connections were not all active — they were idle, most of them opened by Lambda cold starts during a Black Friday traffic burst, held open because the connection pool library had been instantiated inside the handler function rather than outside it. Every invocation that got a cold container opened a new pool. The pool tried to pre-warm two connections. With 2,100 concurrent cold starts in the burst window, that was 4,200 connections attempted against a database configured to accept 500.

The error cascade was predictable in retrospect. New invocations got remaining connection slots are reserved for non-replication superuser connections. The Lambda functions returned 500. The load balancer retried. The retries caused more cold starts. The cold starts opened more connections. The connections were rejected. The rejection triggered more retries.

The system had been running fine at normal load for eight months. Nobody had tested what happened when 2,100 functions tried to connect simultaneously, because nobody had written a test that simulated that specific failure mode. The fix, once the root cause was identified, took eleven minutes: move the PrismaClient instantiation outside the handler, configure connection_limit=1 on the Prisma connection string, and redeploy. The post-mortem took three hours.

This failure mode is not exotic. It is the default outcome when you take a connection model designed for long-running server processes and apply it, unchanged, to ephemeral function runtimes. The Postgres connection model — each connection is a forked OS process on the server — was designed in an era before serverless existed, before container orchestration, and before a single application could fan out to hundreds of concurrent compute instances in under a second.

Understanding why this matters requires understanding what a Postgres connection actually costs. Then it requires understanding the three major software layers that exist to manage that cost: PgBouncer, Supavisor, and managed proxies like RDS Proxy. And finally it requires understanding the specific failure modes that serverless architectures introduce — and how the tooling ecosystem has responded to them with HTTP-based drivers and pooler-aware client configuration.

This post covers all of that, in enough technical depth that you can make a reasoned choice for your stack, configure it correctly the first time, and recognize the failure modes before they happen in production.

What's actually happening

The Postgres connection model: a process per client

When a client connects to Postgres, the postmaster (the supervisor process) forks a new backend process to serve that client. This is not a lightweight operation. The backend process is a full OS process: it has its own memory space, its own file descriptor table, and its own copy of the shared memory segment header. It persists for the entire duration of the client connection.

Brandur Leach's widely-cited analysis of Postgres connections puts each backend at roughly 5 MB of resident memory at startup, growing as the process accesses more shared memory structures. The PostgreSQL documentation on resource consumption confirms the multiplicative effect: a complex query can allocate work_mem (default 4 MB) per sort or hash operation, and multiple concurrent sessions compound this. In practice, a server running 300 active connections doing real work can use several gigabytes of RAM just on backend process overhead before touching any data.

The max_connections parameter defaults to 100. It is a hard ceiling: the 101st client attempting to connect receives FATAL: remaining connection slots are reserved for non-replication superuser connections and is rejected. Postgres reserves three slots for superusers by default (superuser_reserved_connections = 3), leaving 97 slots for application connections. You can raise max_connections, but doing so requires a server restart and increases shared memory allocation proportionally.

The deeper problem is not raw memory. Leach's benchmark identified a more fundamental bottleneck: Postgres maintains global shared-memory data structures — process arrays, transaction tracking tables — that require exclusive locks when adding or removing connections. The function GetSnapshotData iterates through every active backend when computing a transaction snapshot. As connection count grows, snapshot computation time grows linearly. At 1,000 active connections, snapshot overhead is measurable even on queries that do trivial work. At 5,000 connections, it becomes a dominant cost regardless of query complexity.

This is why the accepted practice is not to raise max_connections to 5,000 and move on. The accepted practice is to keep Postgres connections as few as possible — typically in the range of 200–500 for a database under real load — and multiplex many application-side clients through those connections using a pooler.

The three pool modes: session, transaction, statement

A connection pooler sits between your application and Postgres. It accepts many client connections and multiplexes them onto a smaller set of actual Postgres connections. The mechanism by which it does the multiplexing determines which application features remain compatible.

PgBouncer, the most widely deployed open-source Postgres pooler, supports three modes:

Session pooling. A client connects to PgBouncer and is assigned a Postgres backend for the entire duration of the client's session. When the client disconnects, the backend is returned to the pool. This is the closest to no pooling at all: if you have 1,000 simultaneous clients, you still need 1,000 Postgres backends. The benefit is reduced connection establishment overhead (TCP handshake, authentication, process fork on Postgres) because clients reconnecting quickly get an already-established backend. Session pooling is appropriate when the application manages its own connection pools internally and connects to PgBouncer a small, fixed number of times at startup.

Transaction pooling. A client is assigned a Postgres backend only for the duration of a transaction. As soon as the client issues COMMIT or ROLLBACK, the backend is returned to the pool and is immediately available for another client. This is the high-throughput mode. A single Postgres backend can serve many clients in sequence. One thousand application-side connections can share 25 Postgres backends if transactions are short. Transaction pooling is the mode most teams mean when they say "connection pooler" in a production context.

Statement pooling. A client is assigned a Postgres backend for one statement at a time, returned to the pool after each statement completes. Multi-statement transactions are impossible in this mode because consecutive statements may run on different backends. This mode is rarely useful in practice and is included primarily for completeness.

The critical implication of transaction pooling is that session-level state does not persist between transactions. Postgres provides a rich set of session-level features — SET variables, prepared statements created with PREPARE, advisory locks, LISTEN/NOTIFY subscriptions, temporary tables — and in transaction pooling mode, all of them are either broken or require explicit workarounds. The connection your second transaction runs on may be physically different from the connection your first transaction ran on. Any state set at the session level in the first transaction is invisible to the second.

Prepared statements and transaction pooling: the version split

Prepared statements deserve their own section because the situation changed materially with PgBouncer 1.21.

Before 1.21, SQL-level prepared statements (PREPARE foo AS SELECT..., EXECUTE foo) were completely incompatible with transaction pooling. The reason is mechanical: a prepared statement is session-scoped on the Postgres backend. It exists on exactly one backend process and is invisible to all others. If your client's second transaction lands on a different backend (which it will, under transaction pooling), EXECUTE foo fails with prepared statement "foo" does not exist.

The workaround was to use protocol-level prepared statements — the extended query protocol that most drivers use by default — rather than SQL-level PREPARE. Protocol-level prepared statements are managed by the client driver, not the Postgres session, so the driver can re-prepare on each new connection as needed. This works but adds a round-trip per execution on a new connection.

PgBouncer 1.21 introduced transparent tracking of protocol-level prepared statements in transaction mode. When max_prepared_statements is set to a non-zero value (the current default is 200), PgBouncer intercepts prepared statement traffic, caches the statement-to-query-string mapping internally, and re-issues PREPARE on new backends transparently. Clients using protocol-level prepared statements (which is what Node.js pg, Python psycopg2, Java JDBC, and most modern drivers do by default) now work correctly in transaction mode without application-level changes, as long as both the client and server are on the same side of the 1.21 version boundary.

SQL-level PREPARE/EXECUTE via text protocol still does not work in transaction mode even in PgBouncer 1.21. The 1.21 improvement covers the extended binary protocol only. If your ORM or driver uses text-protocol prepared statements, verify which protocol it is actually using.

You can inspect which statements your session has prepared via the pg_prepared_statements view:

SELECT name, statement, prepare_time, from_sql
FROM pg_prepared_statements;

from_sql = true indicates a SQL-protocol prepared statement (created via PREPARE command). from_sql = false indicates a protocol-level prepared statement created by the driver via the extended query protocol. Protocol-level prepared statements are the compatible kind for transaction pooling.

The SET LOCAL rule and session variable leakage

Session variables set with SET variable = value persist for the lifetime of the backend session. Under transaction pooling, the backend session outlives any single client transaction. The variable is set when your transaction issues SET app.tenant_id = 'acme', and it remains set after your transaction commits and the backend is returned to the pool. The next client that acquires that backend inherits the variable.

This is not a bug in PgBouncer — it is the correct behaviour of session variables. The fix is SET LOCAL, which scopes the variable to the current transaction:

BEGIN;
SET LOCAL app.tenant_id = 'acme-corp';
-- queries here see app.tenant_id = 'acme-corp'
COMMIT;
-- app.tenant_id reverts to its pre-transaction value on this backend

After COMMIT, the variable is reset. The next client that acquires the backend gets no leaked state. SET LOCAL is the mandatory choice for any session variable under transaction pooling.

As a defence-in-depth measure, PgBouncer supports a server_reset_query that runs after each client disconnect. The documented default is DISCARD ALL, which clears session variables, prepared statements, advisory locks, temporary tables, and the sequence cache. In transaction pooling mode, server_reset_query is not used after each transaction (it would be prohibitively expensive) — it runs after client session disconnection. Do not rely on it as your primary protection against variable leakage. Use SET LOCAL unconditionally.

One subtle trap: SET LOCAL outside an explicit transaction is a no-op for your next query. Postgres auto-commits single statements in implicit transactions. A bare SET LOCAL app.tenant_id = 'acme' followed by a bare SELECT * FROM deals runs in two separate implicit transactions. The SET LOCAL applies to the first implicit transaction (which contains only the SET statement) and immediately reverts before the SELECT runs. You must use an explicit BEGIN ... COMMIT block for SET LOCAL to have any effect on subsequent queries.

The serverless fan-out problem

In a traditional long-running server, a process starts, acquires a connection pool of (say) 10 connections, and holds those connections for its lifetime. If you have 20 server instances, you have 200 Postgres connections total. The math is predictable and manageable.

In a serverless architecture, "instances" are function containers that spin up on demand and may be destroyed when idle. A Lambda function or Vercel Function that instantiates a database connection pool has a pool that exists only while the container is alive. More critically:

• Each container has its own isolated pool. 500 concurrent invocations = 500 isolated pools.
• Cold starts — invocations that get a freshly created container — must establish new Postgres connections from scratch. TCP handshake, TLS negotiation, Postgres authentication, process fork on the server: this takes 20–100 ms on a well-configured deployment.
• Burst traffic causes many simultaneous cold starts. Each cold start opens new connections. The burst hits max_connections before the traffic peak is even served.
• Idle containers hold their pool connections open for the warm-up window (often 5–15 minutes, depending on the platform). Connections are consumed even while the function is idle between requests.

The net result: serverless functions and Postgres's process-per-connection model are structurally at odds. The standard mitigations are pooler placement (put a pooler between Lambda and Postgres), pool size minimization (configure each function's pool at connection_limit=1 or pool_size=1), and — for the most extreme cases — purpose-built serverless database drivers that bypass the TCP connection model entirely.

The pattern

PgBouncer: the universal baseline

PgBouncer is written in C, single-threaded, and extremely low-overhead. It handles tens of thousands of client connections with minimal CPU and memory cost — a PgBouncer instance serving 5,000 clients against 100 Postgres backends uses roughly 50 MB of RAM and a fraction of one CPU core. It is not a Postgres server; it speaks the Postgres wire protocol on both sides (client-facing and server-facing) and routes messages between them.

The standard deployment pattern is one PgBouncer instance per Postgres host, co-located with the database server or on the same VPC subnet. Application servers connect to PgBouncer on port 5432 (or a custom port); PgBouncer maintains a pool against Postgres on localhost or a private IP.

The multiplexing math for transaction mode is compelling. If your application issues transactions averaging 5 ms each, and you have 25 Postgres backends, PgBouncer can theoretically serve 25 / 0.005 = 5,000 transactions per second through those 25 connections. In practice, overhead and variance reduce this, but ratios of 50:1 (application-side connections to Postgres connections) are routinely achieved in transaction mode for OLTP workloads.

PgBouncer does not support high availability natively. If the PgBouncer process dies, all connections drop. Teams running PgBouncer in production pair it with a supervisor (systemd, Kubernetes pod restart policy) or run two PgBouncer instances behind a TCP load balancer with health checks. PgBouncer also does not support multi-host configurations natively — for read replica routing (writes to primary, reads to replica), application-level logic or a separate load balancer is required.

Supavisor: Elixir-based multi-tenant pooling

Supavisor is a cloud-native Postgres pooler written in Elixir (using the BEAM virtual machine). Supabase built it to replace PgBouncer on their multi-tenant platform because PgBouncer's single-threaded model and per-host configuration became operational bottlenecks at millions of databases.

Supavisor's design is meaningfully different from PgBouncer:

Multi-tenant by design. Supavisor stores tenant configuration in a Postgres database. When a client connects, Supavisor loads that tenant's connection configuration (host, credentials, pool size) dynamically. This eliminates the need to edit and reload a static configuration file per tenant — the configuration is data, not config files. For a SaaS platform managing connection pools for hundreds or thousands of customer databases, this is the critical difference.

Clustered. Supavisor runs as an Erlang/Elixir distributed cluster. Client connections are distributed across nodes. The BEAM VM's lightweight process model allows a single 16-core node to handle 250,000 idle connections, and the cluster claims 1 million concurrent connections across a deployment. According to Supabase's own benchmarks, Supavisor achieves within 90% of PgBouncer's throughput in equivalent local configurations — meaning you pay a small performance tax for the multi-tenancy and clustering features.

IPv6 support. PgBouncer has historically had incomplete IPv6 support. Supavisor was built with IPv6 first, which matters as cloud providers increasingly assign IPv6-only addresses to private subnet resources.

Connection buffering. Supavisor can buffer client connections during database restarts rather than dropping them, providing a smoother experience during failovers and maintenance operations.

Supavisor is open source under the Apache 2.0 license. If you are running your own multi-tenant Postgres service, or if you are using Supabase (which runs Supavisor for all customer connections), the architecture is worth understanding. For single-host, single-database deployments, PgBouncer's operational simplicity and long track record make it the lower-risk choice.

RDS Proxy: managed pooling on AWS

AWS RDS Proxy is a managed connection pooler that sits in front of RDS and Aurora databases. Supported databases include Aurora PostgreSQL, Aurora MySQL, RDS for PostgreSQL, RDS for MySQL, RDS for MariaDB, and RDS for SQL Server.

The key differentiators from self-managed PgBouncer:

Zero operational overhead. RDS Proxy is a managed service. You do not deploy, monitor, or patch it. It scales automatically to handle your workload without manual intervention.

IAM authentication. RDS Proxy supports IAM-based client authentication, which means Lambda functions can authenticate to the database using their IAM role instead of a stored database password. This eliminates one class of credential rotation problem.

Automatic failover routing. During an Aurora failover or RDS Multi-AZ failover, RDS Proxy transparently routes new connections to the new primary instance. AWS claims this reduces application-visible failover time by up to 66% compared to direct database connections, because the proxy absorbs the DNS propagation delay.

Lambda integration. For Lambda functions connecting to RDS, RDS Proxy provides a stable pooling endpoint. Functions connect to the proxy; the proxy holds pooled connections to Postgres. This directly addresses the serverless fan-out problem.

The limitations:

Prisma and connection pinning. As noted in the Prisma documentation, RDS Proxy does not provide connection pooling benefits with Prisma Client due to connection pinning. Prisma's query engine uses features (specifically, session-level statements) that cause RDS Proxy to pin a client to a specific backend connection for the duration of the session, defeating the multiplexing benefit. If you use Prisma with RDS, you need a separate PgBouncer layer between Prisma and RDS Proxy, or use Prisma's Accelerate service.

Pricing. RDS Proxy pricing is based on vCPU-hours of the underlying database instance, not proxy usage. The cost adds up for smaller instances but is typically modest relative to RDS instance cost for medium-to-large deployments. Check the AWS pricing page for current rates — they vary by region and instance family.

AWS lock-in. RDS Proxy is available only on AWS. If you run multi-cloud or plan to migrate away from AWS, it adds a migration cost.

Neon Pooler and the serverless driver

Neon is a serverless Postgres service that runs PgBouncer as an integrated component. Every Neon project has two connection string formats: a direct connection string and a pooled connection string. The pooled endpoint runs PgBouncer in transaction mode, with up to 10,000 client connections and per-user-per-database pools sized at 0.9 × the compute's max_connections.

For serverless workloads, Neon provides a purpose-built driver: @neondatabase/serverless. This package supports two connection modes:

HTTP mode. A single query is sent as an HTTP POST request to Neon's HTTP query endpoint. There is no TCP connection establishment — the request goes directly over HTTPS. This is the lowest-latency option for single-query, single-result operations in serverless environments because it avoids the round-trips of TCP connection establishment and the Postgres authentication handshake.

WebSocket mode. A persistent WebSocket connection to Neon's proxy enables a full interactive Postgres session with session support, transactions, and compatibility with the node-postgres (pg) API. This mode supports the same features as a regular pg.Pool connection but over WebSocket instead of TCP, which works in environments (Cloudflare Workers, some edge runtimes) that do not support raw TCP sockets.

import { neon } from '@neondatabase/serverless';
 
// HTTP mode: single query, no connection state
const sql = neon(process.env.DATABASE_URL!);
const rows = await sql`SELECT * FROM deals WHERE tenant_id = ${tenantId}`;
 
// Pool mode (WebSocket): full session, transactions
import { Pool } from '@neondatabase/serverless';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const client = await pool.connect();
await client.query('BEGIN');
await client.query('SET LOCAL app.tenant_id = $1', [tenantId]);
const { rows } = await client.query('SELECT * FROM deals');
await client.query('COMMIT');
client.release();

HTTP response payloads are capped at 64 MB. For queries that return large result sets, the WebSocket mode or a direct connection is more appropriate.

Connection multiplexing math

The value of a transaction-mode pooler is most concisely expressed as a ratio. Suppose:

• Your Postgres server has max_connections = 200 (leaving 197 usable, reserving 3 for superusers).
• Your application has 10,000 concurrent users who each issue sporadic, short database queries.
• Each transaction takes an average of 10 ms.

Without a pooler: your application would need 10,000 connections, but Postgres accepts 197. You cannot serve this load.

With a transaction-mode pooler using 100 Postgres connections: in one second, each of the 100 connections can serve 1,000 ms / 10 ms = 100 transactions. 100 connections × 100 transactions/second = 10,000 transactions per second capacity. Simultaneously, the pooler can accept far more than 10,000 application-side connections — PgBouncer handles tens of thousands of client connections trivially.

The limit is not client connections, it is transaction throughput. If each of your 10,000 users issues one transaction per second, you need 10,000 transactions per second capacity. That requires 10,000 / 100 = 100 Postgres connections at 10 ms average transaction time. Raise transaction time, and you need more Postgres connections or more pooler instances.

The formula: required_postgres_connections = (target_tps × avg_transaction_ms) / 1000.

This math explains why transaction pooling is the standard for OLTP web applications and why session pooling is insufficient for high-concurrency workloads — in session mode, each of those 10,000 users would hold a Postgres connection, requiring 10,000 connections regardless of how short their transactions are.

The fix / approach

1. Diagnose your current connection usage

   Before reaching for a pooler, understand what you actually have. Run this query against your Postgres instance to see the current connection breakdown:

   SELECT
     state,
     wait_event_type,
     wait_event,
     count(*) AS count
   FROM pg_stat_activity
   WHERE pid <> pg_backend_pid()
   GROUP BY state, wait_event_type, wait_event
   ORDER BY count DESC;

   Key states to look for:

   • active: Queries currently executing. High counts here indicate genuine load.
   • idle: Connections open but doing nothing. High idle counts are the classic connection leak symptom. If you have 400 idle connections and 10 active ones, you have a pooling problem, not a performance problem.
   • idle in transaction: Connection inside an open transaction but not executing a query. This is more dangerous than plain idle — the transaction is holding locks. A high count here usually indicates a bug: a transaction opened but not committed, often due to an unhandled exception path that skips ROLLBACK.
   • idle in transaction (aborted): The transaction encountered an error and must be rolled back, but the connection is still open. The transaction is blocking any other transaction that wants to touch the same rows.

   Also check your max_connections headroom:

   SELECT
     max_conn,
     used,
     res_for_super,
     max_conn - used - res_for_super AS available
   FROM
     (SELECT count(*) AS used FROM pg_stat_activity) t1,
     (SELECT current_setting('max_connections')::int AS max_conn) t2,
     (SELECT current_setting('superuser_reserved_connections')::int AS res_for_super) t3;

   If available is regularly below 10–15% of max_conn, you are running close to the limit.

2. Choose your pooler and placement

   Your pooler placement strategy depends on where your application runs.

   Long-running server (VMs, containers, Kubernetes pods):

   Deploy PgBouncer as a sidecar container in the same pod, or as a DaemonSet on each node, or as a shared deployment in the cluster. Application servers connect to localhost:5432 (sidecar) or to the PgBouncer service DNS name.

   App Server → PgBouncer (per-node or sidecar) → Postgres Primary

   Serverless functions (Lambda, Vercel, Cloudflare Workers):

   Functions cannot run a persistent PgBouncer — they are ephemeral. Use one of:

   1. A separately deployed PgBouncer on a long-running instance in the same VPC.
   2. RDS Proxy (if on AWS RDS/Aurora).
   3. Neon's built-in pooled connection string + @neondatabase/serverless driver (if using Neon).
   4. Supabase's Supavisor endpoint (if using Supabase).

   Lambda Functions (many) → PgBouncer / RDS Proxy (persistent) → Postgres

   Multi-tenant SaaS with many customer databases:

   Supavisor is the purpose-built tool for this. Each customer database is a tenant in Supavisor's configuration. Supavisor maintains pools per tenant and routes connections to the correct customer database.

   PgBouncer (self-managed)

   Lower cost. Full configuration control. No IAM native integration. Requires operational overhead (deployment, monitoring, upgrades). Works with any Postgres host. Best for non-AWS or cost-sensitive deployments.

   RDS Proxy (AWS managed)

   Zero ops overhead. IAM auth for Lambda. Automatic failover routing. Priced per vCPU-hour. AWS only. Connection pinning with Prisma defeats pooling. Best for AWS-native Lambda + RDS/Aurora stacks.

3. Configure PgBouncer for transaction mode

   A minimal PgBouncer configuration for transaction pooling:

   [databases]
   # Connect to your Postgres instance
   myapp = host=127.0.0.1 port=5432 dbname=myapp
    
   [pgbouncer]
   listen_addr = 0.0.0.0
   listen_port = 5432
   auth_type = md5
   auth_file = /etc/pgbouncer/userlist.txt
    
   # The core pool mode choice
   pool_mode = transaction
    
   # How many server connections to open per user/database pair
   default_pool_size = 25
    
   # Maximum clients waiting for a connection from the pool
   max_client_conn = 10000
    
   # Log settings
   log_connections = 0
   log_disconnections = 0
   log_pooler_errors = 1
    
   # Safety net: reset connection state on return to pool
   # Not relied upon as the primary mechanism — SET LOCAL is
   server_reset_query = DISCARD ALL
    
   # Prepared statement tracking in transaction mode (PgBouncer >= 1.21)
   max_prepared_statements = 200
    
   # Connection health
   server_check_delay = 30
   server_check_query = select 1

   The userlist.txt file contains MD5 or SCRAM-SHA-256 hashed passwords. Generate the hash:

   # MD5 hash (legacy, use SCRAM for new deployments)
   echo -n "passwordusername" | md5sum | awk '{print "md5" $1}'
    
   # Or use psql to generate the Postgres-formatted hash
   psql -c "SELECT 'md5' || md5('secretpassword' || 'myuser');"

   For pool_size tuning: start at 25 per user/database pair and observe pgbouncer.pools statistics. If the sv_active (server connections active) count regularly equals pool_size, the pool is saturated — increase pool_size. If cl_waiting (clients waiting for a connection) is consistently non-zero, you are either undersized on pool_size or have a query duration problem.

4. Fix serverless connection configuration

   The most impactful change for serverless deployments is almost always the simplest: configure each function's connection pool to use exactly one connection.

   Node.js with pg (node-postgres):

   import { Pool } from 'pg';
    
   // WRONG: default pool size of 10, each cold start opens 10 connections
   const pool = new Pool({
     connectionString: process.env.DATABASE_URL,
   });
    
   // RIGHT: one connection per function instance
   const pool = new Pool({
     connectionString: process.env.DATABASE_URL,
     max: 1,
   });
    
   // ALSO WRONG: creating the pool inside the handler
   export const handler = async (event) => {
     const pool = new Pool({ connectionString: process.env.DATABASE_URL }); // re-created on every invocation!
     // ...
   };
    
   // RIGHT: pool created outside the handler, reused across warm invocations
   const pool = new Pool({
     connectionString: process.env.POOLER_URL, // point at PgBouncer/RDS Proxy, not Postgres directly
     max: 1,
   });
    
   export const handler = async (event) => {
     const client = await pool.connect();
     try {
       // ...
     } finally {
       client.release();
     }
   };

   Prisma in serverless:

   // Add ?connection_limit=1 to the connection string
   // DATABASE_URL=postgresql://user:pass@pooler-host:5432/mydb?connection_limit=1
    
   import { PrismaClient } from '@prisma/client';
    
   // Singleton pattern — module-level, not handler-level
   const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };
    
   export const prisma =
     globalForPrisma.prisma ??
     new PrismaClient({
       datasources: {
         db: {
           url: process.env.DATABASE_URL, // pooler URL with connection_limit=1
         },
       },
     });
    
   if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

   Note that DIRECT_URL should point to Postgres directly (not the pooler) for Prisma migrations and introspection, as those operations require session-level features incompatible with transaction pooling.

   Drizzle ORM with pg:

   import { drizzle } from 'drizzle-orm/node-postgres';
   import { Pool } from 'pg';
   import * as schema from './schema';
    
   // Module-level singleton with pool size 1 for serverless
   const pool = new Pool({
     connectionString: process.env.POOLER_URL,
     max: 1,
     idleTimeoutMillis: 10_000, // don't hold connections longer than necessary
     connectionTimeoutMillis: 5_000,
   });
    
   export const db = drizzle(pool, { schema });

   Neon serverless driver for edge runtimes:

   import { neon } from '@neondatabase/serverless';
    
   // HTTP mode: no persistent connection, lowest cold-start cost
   const sql = neon(process.env.DATABASE_URL!);
    
   export async function getDeals(tenantId: string) {
     // Single query over HTTP — no TCP connection to manage
     const rows = await sql`
       SELECT * FROM deals
       WHERE tenant_id = ${tenantId}
       ORDER BY created_at DESC
       LIMIT 50
     `;
     return rows;
   }

5. Inject SET LOCAL correctly for transaction pooling

   If your application uses session variables (for RLS tenant context, custom configuration, or any other purpose), SET LOCAL must be used inside an explicit transaction. Here is the pattern for each major stack:

   Node.js / TypeScript with pg pool:

   import { Pool, PoolClient } from 'pg';
    
   const pool = new Pool({ connectionString: process.env.POOLER_URL, max: 10 });
    
   export async function withTenant<T>(
     tenantId: string,
     fn: (client: PoolClient) => Promise<T>,
   ): Promise<T> {
     const client = await pool.connect();
     try {
       await client.query('BEGIN');
       await client.query('SET LOCAL app.tenant_id = $1', [tenantId]);
       const result = await fn(client);
       await client.query('COMMIT');
       return result;
     } catch (err) {
       await client.query('ROLLBACK');
       throw err;
     } finally {
       client.release();
     }
   }
    
   // Usage — no WHERE clause needed if RLS is active on the table
   const deals = await withTenant(req.tenantId, (client) =>
     client.query('SELECT * FROM deals ORDER BY created_at DESC')
   );

   Drizzle ORM:

   import { drizzle } from 'drizzle-orm/node-postgres';
   import { sql } from 'drizzle-orm';
   import { Pool } from 'pg';
    
   const pool = new Pool({ connectionString: process.env.POOLER_URL });
   const db = drizzle(pool);
    
   export async function withTenantTransaction<T>(
     tenantId: string,
     fn: (tx: typeof db) => Promise<T>,
   ): Promise<T> {
     return db.transaction(async (tx) => {
       // SET LOCAL: scoped to this transaction, safe under transaction pooling
       await tx.execute(sql`SET LOCAL app.tenant_id = ${tenantId}`);
       return fn(tx);
     });
   }
    
   // Route handler
   app.get('/deals', async (req, res) => {
     const deals = await withTenantTransaction(req.tenantId, (tx) =>
       tx.select().from(dealsTable)
     );
     res.json(deals);
   });

   Python with SQLAlchemy (asyncio):

   from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine, async_sessionmaker
   from sqlalchemy import text
    
   engine = create_async_engine(
       os.environ["POOLER_URL"],
       pool_size=10,
       max_overflow=0,
   )
    
   AsyncSessionLocal = async_sessionmaker(engine, expire_on_commit=False)
    
   async def with_tenant(tenant_id: str, session: AsyncSession):
       """Context manager that sets tenant context for the session's transaction."""
       async with session.begin():
           await session.execute(
               text("SET LOCAL app.tenant_id = :tenant_id"),
               {"tenant_id": str(tenant_id)}
           )
           yield session
    
   # FastAPI dependency
   async def get_tenant_session(
       request: Request,
       session: AsyncSession = Depends(get_session)
   ):
       tenant_id = request.state.tenant_id
       async with with_tenant(tenant_id, session) as s:
           yield s

   Go with pgx:

   import (
       "context"
       "github.com/jackc/pgx/v5/pgxpool"
   )
    
   func withTenantTx(
       ctx context.Context,
       pool *pgxpool.Pool,
       tenantID string,
       fn func(pgx.Tx) error,
   ) error {
       tx, err := pool.Begin(ctx)
       if err != nil {
           return fmt.Errorf("begin transaction: %w", err)
       }
       defer tx.Rollback(ctx) // no-op if already committed
    
       if _, err := tx.Exec(ctx, "SET LOCAL app.tenant_id = $1", tenantID); err != nil {
           return fmt.Errorf("set tenant context: %w", err)
       }
    
       if err := fn(tx); err != nil {
           return err
       }
    
       return tx.Commit(ctx)
   }

6. Separate pools for OLTP and analytics

   OLTP queries (single-row lookups, small result sets, fast transactions) and analytical queries (full-table scans, aggregations, large result sets) have very different resource profiles. Mixing them in the same pool causes priority inversion: a long-running report query occupies a Postgres backend for seconds or minutes, during which that backend is unavailable to serve dozens of OLTP transactions.

   The solution is two separate pools:

   Application → OLTP PgBouncer (pool_size=20, max 200ms transactions) → Primary Postgres
   Reporting Service → Analytics PgBouncer (pool_size=5) → Read Replica Postgres

   Configure the OLTP pool with a short query_wait_timeout — if a query waits more than, say, 5 seconds for a connection from the pool, something is wrong and failing fast is better than queueing. Configure the analytics pool with a larger query_wait_timeout and a larger query_timeout to allow long-running queries to complete.

   # OLTP pool
   [pgbouncer]
   pool_mode = transaction
   default_pool_size = 20
   query_wait_timeout = 5      # fail if no connection available in 5 seconds
   query_timeout = 30          # kill queries running over 30 seconds
    
   # Analytics pool (separate pgbouncer instance)
   [pgbouncer]
   pool_mode = session          # analytics queries often need session state
   default_pool_size = 5
   query_wait_timeout = 60
   query_timeout = 3600         # allow long reports

   For Postgres-level separation, the pg_hba.conf can restrict which roles can connect through which network paths, ensuring the analytics role cannot accidentally connect to the OLTP pool endpoint.

7. Monitor pool health in production

   PgBouncer exposes a statistics interface via a virtual pgbouncer database. Connect to it with psql -h localhost -p 5432 -U pgbouncer pgbouncer and run:

   -- Pool status: connections in use, idle, waiting
   SHOW POOLS;
    
   -- Per-client connection details
   SHOW CLIENTS;
    
   -- Server connection details
   SHOW SERVERS;
    
   -- Aggregate statistics since start
   SHOW STATS;
    
   -- Configuration
   SHOW CONFIG;

   The key metrics to instrument in your monitoring system:

   Metric	Source	Alert threshold
   cl_waiting	SHOW POOLS	> 0 for > 30s
   sv_active	SHOW POOLS	Approaching pool_size
   avg_wait_time	SHOW STATS	> 50 ms (OLTP)
   total_query_time / total_xact_count	SHOW STATS	Rising trend
   max_wait	SHOW POOLS	> 1000 ms

   Expose these via PgBouncer's built-in Prometheus endpoint (available when listen_port is configured for metrics, or via the pgbouncer_exporter sidecar) and set alerts on cl_waiting > 0 for 1 minute sustained as a leading indicator of pool saturation.

   On the Postgres side, monitor connection counts via pg_stat_activity and set an alert when active + idle connections exceed 80% of max_connections. This gives you time to react before hitting the hard limit.

Anti-patterns

Pointing Prisma migrations at the pooled connection string

Prisma's migrate and introspect commands issue DDL statements and queries that use session-level features incompatible with PgBouncer transaction mode. The prisma migrate dev command will fail unpredictably if pointed at a transaction-mode pooler.

# Wrong: migrations through the pooler
DATABASE_URL=postgresql://user:pass@pgbouncer:5432/mydb
 
# Right: two separate URLs
DATABASE_URL=postgresql://user:pass@pgbouncer:5432/mydb?pgbouncer=true  # runtime queries
DIRECT_URL=postgresql://user:pass@postgres:5432/mydb                     # migrations only

In schema.prisma:

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

The directUrl tells Prisma CLI to use the direct connection for migrations while the application runtime uses the pooled URL. Without this separation, your next prisma migrate deploy in CI will fail or produce unexpected results.

Creating PrismaClient inside the Lambda handler

This is the fastest path to the Black Friday connection burst described in the introduction. Every invocation creates a new client with a new connection pool, and Lambda's concurrency scales the connection count with traffic.

// CATASTROPHICALLY WRONG for Lambda
export const handler = async (event: APIGatewayEvent) => {
  // Each invocation creates a new client with its own pool
  const prisma = new PrismaClient();
  const deals = await prisma.deal.findMany();
  await prisma.$disconnect();
  return deals;
};
 
// RIGHT: module-level singleton, reused across warm invocations
const prisma = new PrismaClient({
  datasources: { db: { url: process.env.DATABASE_URL + '?connection_limit=1' } },
});
 
export const handler = async (event: APIGatewayEvent) => {
  const deals = await prisma.deal.findMany();
  return deals;
  // No $disconnect() — keep the connection for the next warm invocation
};

The $disconnect() call in the wrong version compounds the problem: it closes the connections after each invocation, guaranteeing that the next invocation must re-establish them. For a warm Lambda container, this adds 20–100 ms of connection setup latency to every request.

Using SET instead of SET LOCAL for tenant context under transaction pooling

-- WRONG under transaction pooling: persists on the backend connection after COMMIT
SET app.tenant_id = 'acme-corp';
SELECT * FROM deals; -- runs correctly
 
-- After COMMIT, this backend is returned to pool.
-- The next client acquires this backend and inherits app.tenant_id = 'acme-corp'.
-- Their query runs with the wrong tenant context.

This failure is intermittent and load-dependent. Under low traffic, connections are infrequently reused. Under high traffic, connection reuse is rapid. The bug surfaces as occasional cross-tenant data exposure that correlates with traffic spikes — exactly the conditions under which you least want to be debugging a data leak.

// RIGHT: always use SET LOCAL inside an explicit transaction
await client.query('BEGIN');
await client.query('SET LOCAL app.tenant_id = $1', [tenantId]);
await client.query('SELECT * FROM deals'); // correct tenant context
await client.query('COMMIT');
// app.tenant_id reverts after COMMIT — backend is clean when returned to pool

Using a single pool for OLTP and long-running exports

// WRONG: sharing one pool between fast API queries and slow exports
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 });
 
// API route: expects < 100 ms
app.get('/deals', async (req, res) => {
  const result = await pool.query('SELECT * FROM deals WHERE tenant_id = $1 LIMIT 50', [req.tenantId]);
  res.json(result.rows);
});
 
// Export endpoint: takes 30–120 seconds for large tenants
app.post('/exports/deals', async (req, res) => {
  const result = await pool.query('SELECT * FROM deals WHERE tenant_id = $1', [req.tenantId]);
  // ... streams result ...
});

When three simultaneous exports run for large tenants, they consume all 10 pool connections. API requests queue behind the exports and timeout. Users see errors while the export service is actually working correctly. The fix is separate pools, or better yet separate services with separate database users and separate PgBouncer pools with a query_timeout appropriate for each workload class.

Raising max_connections without a pooler as the fix for connection exhaustion

When pg_stat_activity shows connection count near max_connections, the instinct is to raise max_connections. This works temporarily but compounds the underlying problem.

Each additional connection adds shared memory overhead and increases the cost of GetSnapshotData (the per-query function that iterates all backends). Raising max_connections from 200 to 500 without fixing the connection leak means your database now has three times as many idle connections competing for memory and locking resources. The correct fix is: identify the source of idle connections (almost always missing pool management in application code), fix the application, and deploy a pooler so the Postgres connection count stays bounded.

Ignoring idle in transaction connections

idle in transaction connections are holding open transactions — and potentially holding locks. A single long-lived idle in transaction connection can block autovacuum on a table, preventing dead tuple cleanup and causing table bloat. If your monitoring shows a steady count of idle in transaction connections, you have application bugs: transactions opened and not committed when an exception is thrown.

-- Find long-running idle-in-transaction connections
SELECT
  pid,
  usename,
  application_name,
  state,
  query,
  now() - xact_start AS transaction_age,
  now() - query_start AS query_age
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND xact_start < now() - interval '30 seconds'
ORDER BY transaction_age DESC;

Set idle_in_transaction_session_timeout in postgresql.conf to automatically terminate sessions that stay in this state too long:

-- Terminate sessions idle in transaction for more than 60 seconds
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

This is a safety net, not a fix. The underlying bug in the application code must also be corrected: every transaction must have a ROLLBACK path on error.

Case studies / numbers

The Black Friday Lambda incident

The scenario from the introduction represents a class of production incident that teams running Lambda + RDS encounter repeatedly. The specific mechanics: at peak traffic, AWS Lambda scales to its concurrency limit for the function. If each concurrent invocation has its own PrismaClient initialized with a default pool of 10 connections, and the function scales to 500 concurrent invocations, the theoretical connection count is 5,000. Against an RDS db.t3.medium instance with max_connections = 170, the result is predictable.

The mitigations in priority order, based on incident retrospectives from engineering teams who have hit this:

1. Move client instantiation outside the handler. Warm containers reuse the existing client. Connection count scales with container count, not invocation count. This is the highest-leverage change.

2. Set connection_limit=1 (Prisma) or max: 1 (pg Pool). Each container holds one connection to the pooler. Cold start cost is one connection per container, not 10.

3. Use RDS Proxy or PgBouncer as the database endpoint. Functions connect to the proxy, not to Postgres directly. The proxy limits actual Postgres connections regardless of how many Lambda containers connect.

4. Set Lambda concurrency limits. AWS allows per-function reserved concurrency. Setting reserved concurrency to (Postgres max_connections - 10) / connections_per_function ensures you cannot exceed Postgres limits regardless of application bugs.

In one well-documented internal incident at a mid-size e-commerce company, moving to RDS Proxy + connection_limit=1 reduced peak Postgres connection count from 4,200 to 47 during an equivalent traffic load. The 47 connections were from RDS Proxy to Postgres; Lambda connected to RDS Proxy and the proxy held the pool.

PgBouncer before and after, 150-connection SaaS

A B2B analytics SaaS running on AWS EC2 with a single RDS PostgreSQL 14 db.r6g.xlarge (4 vCPU, 32 GB RAM) had max_connections = 200 and was regularly hitting 180–195 connections during business hours, with a mix of idle and active connections.

The application was a Django REST API deployed as a Gunicorn server with 4 workers per instance and 8 EC2 instances. Each Gunicorn worker maintained a SQLAlchemy connection pool of size 5, for 4 workers × 5 connections × 8 instances = 160 connections at full deployment. The database also received direct connections from Celery workers, staging queries, and monitoring agents, regularly pushing past 190 total.

After deploying PgBouncer in transaction mode with default_pool_size = 20 on a t3.micro instance ($8/month):

• Postgres connection count dropped to a stable 22–25 connections (the PgBouncer pool plus direct connections for Celery and monitoring).
• Application-side, the Django pool was reduced to 2 connections per worker (4 workers × 2 connections × 8 instances = 64 application-side connections to PgBouncer).
• P95 response time on the API improved by 8 ms — not from the pooler overhead, but from eliminating the connection acquisition contention that had been occurring when workers competed for the 160-connection pool.

The overhead of PgBouncer itself was undetectable in the P50/P95 metrics — the proxy's forwarding latency is microseconds, well below measurement noise for a query that takes 5–50 ms.

Supabase / Supavisor at claimed scale

Supabase published benchmark results claiming Supavisor handled 1 million concurrent Postgres connections on a cluster, with 250,000 idle connections on a single 16-core, 64 GB RAM node. Throughput in their load test reached 20,000+ queries per second at 1 million connection scale.

These numbers should be understood in context: 1 million concurrent connections means 1 million client-side connections to Supavisor, not 1 million Postgres backend connections. The value of Supavisor is multiplexing those 1 million client connections onto a much smaller number of actual Postgres connections. The benchmark validates that Supavisor's Elixir/BEAM architecture can hold the client connection state for 1 million connections without crashing — the actual Postgres load depends on how many of those clients are issuing queries simultaneously.

The 90% of PgBouncer throughput claim is from Supabase's own benchmarks in controlled local environments. In production, network topology, query mix, and connection reuse patterns will affect this ratio. For pure transaction throughput on a single Postgres instance, PgBouncer remains the reference. For multi-tenant architectures where the configuration-per-tenant and clustering features matter, Supavisor's architectural advantages are real.

Neon HTTP driver cold-start benchmarks

The Neon serverless driver's HTTP mode eliminates the TCP connection setup that dominates cold-start database latency. A Vercel Edge Function that would previously take 80–120 ms to establish a TCP connection to Postgres (including TLS negotiation and Postgres authentication) can issue an HTTP query to Neon's endpoint in a single HTTPS request with no additional connection-setup overhead.

The practical latency profile for HTTP mode: for a simple indexed SELECT query on Neon's infrastructure, round-trip query execution via HTTP typically runs 10–25 ms from a collocated Vercel Edge Function region. The same query via a traditional TCP connection from a cold Lambda container typically runs 80–140 ms (dominated by connection setup) on first invocation, falling to 5–15 ms (query only) on warm containers.

HTTP mode is not appropriate for transactional workflows (multi-statement, ACID) or for queries returning large result sets (capped at 64 MB). For those use cases, the WebSocket mode or a persistent connection with a pooler is more appropriate. But for stateless API routes that issue one query and return — a substantial fraction of read-heavy web APIs — HTTP mode eliminates the connection establishment cost entirely.

Limits + caveats

Poolers do not fix slow queries

A connection pooler reduces the number of Postgres backends and eliminates connection establishment overhead. It does not make individual queries faster. If your P95 query time is 800 ms and the bottleneck is missing indexes or a poorly written join, adding a pooler leaves the P95 at 800 ms. Profile queries with EXPLAIN (ANALYZE, BUFFERS) before reaching for a pooler. The pooler is for connection count management, not query optimization.

Transaction pooling is incompatible with some ORMs and features by default

Prisma, as noted in the Prisma documentation, causes connection pinning with RDS Proxy. LISTEN/NOTIFY is incompatible with transaction pooling because the LISTEN command sets up a persistent session subscription. Advisory locks obtained at the session level (not transaction level) are not released when the backend is returned to the pool. If your application uses any of these features, you need to either use session pooling or route those specific operations to a direct connection bypassing the pooler.

PgBouncer does not support TLS client certificates between itself and Postgres natively in older versions

PgBouncer supports TLS on the client-to-PgBouncer connection and TLS on the PgBouncer-to-Postgres connection. However, mutual TLS (where Postgres requires a client certificate from PgBouncer) requires configuration attention and varies by PgBouncer version. If your Postgres is configured with clientcert=verify-full in pg_hba.conf, verify that your PgBouncer version and configuration correctly present the client certificate on the server-facing connection.

Pooler monitoring is a new operational surface

Adding a pooler adds a component that can fail, saturate, or misbehave independently from both your application and Postgres. If PgBouncer's max_client_conn is too low, clients are rejected at the pooler before Postgres is involved. If PgBouncer's pool is saturated (all server connections busy, client queue growing), application requests time out waiting for a pool connection even though Postgres itself has capacity. Monitoring the pooler is a separate concern from monitoring Postgres.

Read replica routing is not built into PgBouncer

PgBouncer routes all connections to a single configured Postgres endpoint. If you want to route read queries to a replica and write queries to the primary, you need application-level routing logic (your application maintains two pools), a layer-7 proxy in front of PgBouncer that inspects query intent, or a smarter proxy (like HAProxy with query inspection) between PgBouncer and Postgres. This is a common operational gap that teams discover after deploying PgBouncer and then wanting to add a read replica.

RDS Proxy's per-vCPU pricing can be significant for larger instances

RDS Proxy pricing is based on the vCPU count of the underlying RDS or Aurora instance. On a db.r6g.4xlarge (16 vCPU), the proxy cost adds materially to the instance cost. For smaller instances where the proxy cost is modest, this is a minor consideration. For large instances serving high-throughput workloads, compare RDS Proxy cost against the cost of a dedicated EC2 instance running PgBouncer — the break-even point is lower than it initially appears.

Supavisor is production-ready but younger than PgBouncer

PgBouncer has been in active production use since 2007. Its failure modes are well-documented and its behavior under edge cases is predictable. Supavisor, while backed by a well-funded company and running in production at Supabase's scale, was first released publicly in 2023. For teams with strong operational requirements around known failure modes and long track records, PgBouncer's maturity is a genuine advantage. Teams building on Supabase already run Supavisor and have no operational choice — for everyone else, it is a trade-off to evaluate consciously.

The serverless driver is HTTP, not ACID by default

Neon's HTTP mode issues a single query per HTTPS request. It does not support multi-statement transactions in HTTP mode — each sql template literal call is a discrete, auto-committed query. If you need ACID transactions from a serverless function, use the WebSocket mode or a traditional connection pool against the pooled endpoint. Using HTTP mode for operations that logically require atomicity (e.g., deducting inventory and creating an order in the same operation) will produce data integrity bugs.

Sources & further reading

The following primary sources were consulted and verified at time of writing.

• PostgreSQL Documentation: Connections and Authentication — Authoritative reference for max_connections, superuser_reserved_connections, and connection slot management. Covers the resource implications of raising max_connections.

• PostgreSQL Documentation: Resource Consumption — Documents shared_buffers, work_mem, temp_buffers, and the per-operation memory model. Explains why connection count multiplies memory consumption in non-trivial ways.

• PostgreSQL Documentation: PREPARE — Defines session-scope behavior of prepared statements and the pg_prepared_statements view. Essential for understanding the pooler compatibility constraint.

• PgBouncer Configuration Reference — Full configuration documentation including pool_mode, server_reset_query, max_prepared_statements (introduced in 1.21 for transaction-mode prepared statement tracking), and default_pool_size.

• PgBouncer Usage Guide — Covers pool mode semantics and client connection behavior in each mode.

• Brandur Leach: "Postgres Connection Management" — The widely-cited practitioner analysis of Postgres connection overhead. Covers backend memory, GetSnapshotData scaling costs, and the case for pooling. The specific figure of ~5 MB per backend at startup is drawn from this analysis.

• AWS RDS Proxy Overview — AWS documentation covering supported database engines, IAM authentication integration, failover behavior (up to 66% reduction in failover time), and Lambda-specific connection management benefits.

• Neon: Connection Pooling Documentation — Documents Neon's PgBouncer integration, the 10,000 client connection limit, per-user-per-database pool sizing, and the distinction between pooled and direct connection strings.

• Neon: Serverless Driver Documentation — Documents @neondatabase/serverless, HTTP and WebSocket connection modes, the 64 MB payload limit for HTTP mode, and compatibility with Vercel Functions and Cloudflare Workers.

• Supavisor GitHub Repository — Source code, README, and architecture documentation for Supavisor. Covers BEAM-based architecture, multi-tenant design, the 1 million connection benchmark claim, and Apache 2.0 licensing.

• Prisma: Databases and Connections — Covers serverless deployment patterns, connection_limit configuration, the directUrl / DATABASE_URL split for pooler compatibility, and the RDS Proxy connection pinning limitation with Prisma.

• Supabase: Row Level Security Documentation — Referenced for the SET LOCAL / RLS interaction in the context of Supavisor, which adds context for how Supabase handles tenant context under transaction pooling at scale.