Module 16 — Connection Pooling Failure Modes: PgBouncer, Serverless, and the Edge

Why Connection Pooling Breaks Things

A connection pool is not a transparent proxy. It multiplexes multiple application connections onto fewer Postgres connections, which means application-level state tied to a connection is not preserved between statements.

This creates a class of bugs that are invisible in development (where the pool is often not used, or uses session mode) and only appear in production under load. The symptoms look like data corruption, missing data, or mysterious errors — not "this is a pooling problem."

Session vs Transaction vs Statement Mode: The Exact Breakage

Session Mode

One Postgres backend is assigned to one application connection for the entire session. The application and the Postgres backend have a 1:1 relationship for the session's duration.

What works: everything. Prepared statements, temporary tables, advisory locks, SET parameters, LISTEN/NOTIFY, cursors, transaction control.

Pool savings: minimal — you need roughly as many Postgres backends as peak concurrent application connections. Session mode pooling is mainly useful for reducing connection establishment overhead (TLS handshake, authentication).

ini
[pgbouncer]
pool_mode = session

Transaction Mode

A Postgres backend is held only during an active transaction. Between transactions, the backend is returned to the pool and may be given to a different application connection.

What breaks:

1. Prepared statements:

sql
-- Application connection A prepares a statement on backend B1:
PREPARE get_tx AS SELECT * FROM transactions WHERE id = $1;

-- Transaction ends. Backend B1 returned to pool.

-- Application connection A starts a new transaction.
-- Gets backend B2 (different backend).

-- Application tries to use the prepared statement:
EXECUTE get_tx(12345);
-- ERROR: prepared statement "get_tx" does not exist
-- (It exists on B1, not on B2)

2. Temporary tables:

sql
-- Application creates a temp table on backend B1:
CREATE TEMP TABLE work_items AS SELECT id FROM jobs WHERE status = 'pending';

-- Transaction ends. Backend B1 returned to pool.
-- Next transaction gets backend B2.

SELECT * FROM work_items;
-- ERROR: relation "work_items" does not exist
-- (Temp table exists only on B1's session)

3. Session-level SET parameters:

sql
-- Application sets timezone on backend B1:
SET timezone = 'America/New_York';

-- Next transaction may get B2 — timezone is back to default.
-- Date/time calculations silently produce wrong results.

4. Advisory locks:

sql
-- Application acquires session-level advisory lock on B1:
SELECT pg_advisory_lock(12345);

-- Transaction ends. B1 returned to pool.
-- Lock is held on B1 indefinitely (session-level locks don't release on transaction end).
-- Another transaction on B1 gets the lock unexpectedly.
-- The original application connection can never release it (it's now on B2).

5. LISTEN/NOTIFY:

sql
-- Application subscribes on B1:
LISTEN new_transactions;

-- Transaction ends. B1 returned to pool (or given to another client).
-- Notifications arrive on B1 but the original application connection is no longer on B1.
-- Application never receives the notification.

ini
[pgbouncer]
pool_mode = transaction
# Use this mode for high-throughput OLTP
# Never use prepared statements or temp tables, or use Postgres-side prepared statements

Statement Mode

Each statement gets its own backend. Multi-statement transactions are broken: each BEGIN, UPDATE, COMMIT goes to a different backend.

sql
BEGIN;  -- backend B1
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- backend B2
COMMIT; -- backend B3
-- The UPDATE was not committed — each statement ran in its own autocommit transaction

Statement mode is almost never correct for application use. It exists for very specific query-routing scenarios. Avoid it.

The Prepared Statement Problem: The Correct Solutions

If you need prepared statements with transaction-mode PgBouncer, there are three correct approaches:

Solution 1: Disable Prepared Statements at the Driver Level

Most database drivers support disabling prepared statements. When disabled, every query is sent as a simple query string without preparation.

javascript
// Node.js with pg (node-postgres)
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  // Disable prepared statements — compatible with PgBouncer transaction mode
  // In pg, this means avoiding pool.connect() + client.query() with named statements
});

// Use simple queries (no named prepared statements)
await pool.query('SELECT * FROM transactions WHERE id = $1', [id]);
// pg sends this as a simple parameterized query, not a named prepared statement

python
# Python with psycopg2
import psycopg2
conn = psycopg2.connect(dsn, options="-c statement_timeout=30s")
# psycopg2 uses server-side prepared statements by default for % params
# Use execute_values or mogrify for non-prepared execution

python
# Python with asyncpg — disable prepared statement caching
conn = await asyncpg.connect(dsn, statement_cache_size=0)

Solution 2: Use pgbouncer_prepared_statements = 1

PgBouncer 1.21+ supports transparent prepared statement handling in transaction mode:

ini
[pgbouncer]
pool_mode = transaction
# Enable prepared statement tracking (PgBouncer 1.21+)
# PgBouncer tracks which prepared statements exist on which backends
# and ensures the correct backend is used for each EXECUTE
max_prepared_statements = 100

This makes prepared statements work transparently in transaction mode — PgBouncer handles the routing. Check your PgBouncer version before relying on this.

Solution 3: Session Mode for Prepared-Statement-Heavy Connections

Route connections that use prepared statements to a session-mode pool, and connections that don't to a transaction-mode pool:

ini
[databases]
# Session mode pool for connections using prepared statements
mydb_session = host=primary dbname=mydb

# Transaction mode pool for high-throughput OLTP
mydb_txn = host=primary dbname=mydb

[pgbouncer_session]
listen_port = 5433
pool_mode = session
default_pool_size = 20

[pgbouncer_txn]
listen_port = 5432
pool_mode = transaction
default_pool_size = 100

Application uses port 5433 for sessions that need prepared statements, port 5432 for short OLTP transactions.

Serverless and Edge: The Connection Spike Problem

The Problem

Traditional connection pooling assumes a stable pool of long-lived application processes. Each process maintains a connection pool of N connections, and the total connection count is N × num_processes.

Serverless functions break this model:

• Each invocation may create a new process (cold start)
• Each process creates its own connection pool
• With 100 concurrent Lambda/Edge invocations, each with a pool of 5: 500 connections
• With 1,000 concurrent invocations: 5,000 connections
• Postgres's max_connections is typically 200–500

The result: connection exhaustion under moderate serverless load.

Why pg.Pool in Next.js API Routes Is Wrong

javascript
// WRONG: creates a new pool on every module import
// In serverless, this can mean a new pool per invocation
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10  // 10 connections per pool × many invocations = exhaustion
});

export default async function handler(req, res) {
  const result = await pool.query('SELECT * FROM transactions LIMIT 10');
  res.json(result.rows);
}

In a long-running Node.js server, const pool is created once and reused across all requests — correct. In a serverless function, the module may be re-imported on each cold start, creating a new pool with new connections each time.

Solution 1: Neon, Supabase, or PgBouncer in Front of Postgres

The cloud-native solution: use a connection pooler that lives outside your serverless functions and maintains the Postgres connection pool centrally.

javascript
// Neon serverless driver — uses HTTP for single queries (no persistent connection)
import { neon } from '@neondatabase/serverless';

const sql = neon(process.env.DATABASE_URL);

export default async function handler(req, res) {
  // Each call uses an HTTP request — no persistent connection held
  const rows = await sql`SELECT * FROM transactions LIMIT 10`;
  res.json(rows);
}

Neon's serverless driver uses HTTP/WebSocket for queries instead of the Postgres wire protocol. No persistent connection = no pool exhaustion. The trade-off: slightly higher per-query latency (~5–10ms for the HTTP overhead) and no multi-statement transactions per invocation.

Solution 2: Connection Pool Singleton with Module Caching

Next.js and similar frameworks cache module imports across invocations in the same container (warm starts). Use this to share a pool:

javascript
// lib/db.ts
import { Pool } from 'pg';

// Module-level singleton — cached across warm invocations
const globalPool = global as typeof global & { pgPool?: Pool };

if (!globalPool.pgPool) {
  globalPool.pgPool = new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 2,              // SMALL pool per instance — pooler handles total count
    idleTimeoutMillis: 10_000,
    connectionTimeoutMillis: 5_000,
  });
}

export const pool = globalPool.pgPool;

javascript
// API route
import { pool } from '@/lib/db';

export default async function handler(req, res) {
  const result = await pool.query('SELECT * FROM transactions LIMIT 10');
  res.json(result.rows);
}

Key: max: 2 — each serverless instance holds at most 2 Postgres connections. With 100 concurrent instances: 200 connections total. Manageable.

Solution 3: PgBouncer on the Same Server (Low Latency)

For self-hosted deployments where latency matters:

Application (serverless) → PgBouncer (always running) → Postgres

PgBouncer is the stable pool. Each serverless invocation connects to PgBouncer (fast, lightweight), and PgBouncer manages the actual Postgres connections.

ini
[pgbouncer]
pool_mode = transaction         # transaction mode for short-lived functions
max_client_conn = 10000         # many serverless connections allowed
default_pool_size = 50          # only 50 Postgres connections used

server_idle_timeout = 30        # aggressively reclaim idle connections
client_idle_timeout = 10        # disconnect idle clients quickly

Solution 4: Prisma Accelerate or Drizzle + External Pooler

For Next.js specifically, managed poolers designed for serverless:

javascript
// Prisma with Accelerate (built-in connection pooling for serverless)
import { PrismaClient } from '@prisma/client/edge';
import { withAccelerate } from '@prisma/extension-accelerate';

const prisma = new PrismaClient().$extends(withAccelerate());

export default async function handler(req, res) {
  const transactions = await prisma.transactions.findMany({
    take: 10,
    cacheStrategy: { ttl: 60 }  // optional query cache
  });
  res.json(transactions);
}

The Edge Runtime Problem

Next.js Edge Runtime (used for Middleware and Edge API Routes) runs in a V8 isolate, not a Node.js environment. It has additional constraints:

• No TCP sockets — the standard Postgres wire protocol is TCP-based. It does not work in Edge Runtime.
• No pg, postgres.js, or prisma standard clients — these all require TCP

Solutions for Edge Runtime:

javascript
// Option 1: Neon serverless driver (HTTP-based, works in Edge)
import { neon } from '@neondatabase/serverless';
export const runtime = 'edge';

const sql = neon(process.env.DATABASE_URL);

export async function GET() {
  const data = await sql`SELECT count(*) FROM transactions`;
  return Response.json(data);
}

javascript
// Option 2: Supabase (HTTP-based client)
import { createClient } from '@supabase/supabase-js';
export const runtime = 'edge';

const supabase = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_ANON_KEY
);

export async function GET() {
  const { data } = await supabase.from('transactions').select('*').limit(10);
  return Response.json(data);
}

javascript
// Option 3: Move DB access out of Edge Runtime
// Use Node.js runtime for routes that need database access
// Reserve Edge Runtime for routes that only need KV/cache
export const runtime = 'nodejs';  // explicit — do not use 'edge' for DB routes

The simplest advice: do not run database queries in Edge Runtime unless you are using an HTTP-based Postgres driver. Move database logic to Node.js API routes or Server Actions, and use Edge Runtime only for middleware that does not need database access.

Monitoring PgBouncer in Production

ini
# Enable PgBouncer admin console
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

sql
-- Connect to PgBouncer admin database
-- psql -p 6432 -U pgbouncer_admin pgbouncer

-- Pool status — the most important view
SHOW POOLS;
-- cl_active:  clients using a server connection right now
-- cl_waiting: clients waiting for a server connection → THIS SHOULD BE 0
-- sv_active:  server connections currently in use
-- sv_idle:    server connections available in the pool
-- sv_used:    server connections used recently but not right now

-- Connection counts
SHOW CLIENTS;   -- all client connections
SHOW SERVERS;   -- all Postgres connections

-- Stats
SHOW STATS;     -- queries/sec, bytes in/out, avg query time

-- Configuration
SHOW CONFIG;

-- Force pool reconnection (after Postgres restart)
RECONNECT mydb;

-- Kill a specific client connection
KILL <pid>;

The critical alert: cl_waiting > 0 consistently. Waiting clients mean the pool is saturated — either increase default_pool_size (and ensure Postgres can handle more connections) or reduce application connection creation rate.

Summary

Serverless checklist:

1. Use max: 2–5 per serverless instance, not default pool size
2. Use module-level singletons to share pool across warm invocations
3. Use HTTP-based drivers (Neon, Supabase) for Edge Runtime
4. Put PgBouncer in transaction mode between serverless and Postgres
5. Alert on cl_waiting > 0 in PgBouncer

The most common bugs:

• Prepared statement "does not exist" in production but not locally → transaction mode PgBouncer, prepared statements in driver
• Temp table "does not exist" mid-request → same cause
• Silent timezone/setting drift → session SET not preserved across transactions
• Advisory lock never released → session-level lock acquired, connection returned to pool