Module P-11·16 min read

The silent bugs that only appear in production: prepared statements, temp tables, and serverless connection spikes.

Written by Jatin Jain Saraf · Architect of SupraScan

P-11 — Connection Pooling Failure Modes: PgBouncer, Serverless, and the Edge

Q: A Next.js application using the `pg` driver and PgBouncer in `transaction` mode experiences intermittent `ERROR: prepared statement "..." does not exist` in production, but not in local development. Which of the following is the most accurate explanation for this behavior?

C: PgBouncer in `transaction` mode returns the Postgres backend to the pool between transactions, causing subsequent `EXECUTE` calls from the same application connection to land on a different backend where the statement was not prepared. — Option C is correct. In PgBouncer's `transaction` mode, a Postgres backend is only held for the duration of a single transaction. Between transactions, the backend is returned to the pool and can be assigned to a different application connection. If an application prepares a statement on backend B1, and then in a subsequent transaction (from the same application connection) gets backend B2, the prepared statement will not exist on B2, leading to the error. Option A is incorrect because prepared statements are server-side and tied to the backend session, not directly to the application connection's lifecycle in a pooling context. Option B is incorrect because the error message 'prepared statement does not exist' comes from Postgres itself, indicating a server-side issue, not a client-side driver cache problem. Option D is unlikely to be the cause of *intermittent* errors, as a server restart would typically cause a more widespread and consistent outage, not just intermittent prepared statement issues.

Q: A high-traffic Next.js application deployed to a serverless platform (e.g., Vercel, AWS Lambda) is experiencing `max_connections` exhaustion on its Postgres database, even with a seemingly small `max: 5` setting in its `pg.Pool` configuration. The application uses the standard `pg` client. What is the fundamental architectural problem causing this exhaustion?

B: Each serverless invocation, especially during cold starts, creates its own independent `pg.Pool` instance, leading to `N * max` total connections where N is the number of concurrent invocations. — Option B is correct. In serverless environments, each invocation (especially cold starts) can spin up a new execution environment. If `pg.Pool` is initialized at the module level without a singleton pattern, each new environment will create its own `pg.Pool` instance. Even if each pool has a small `max: 5` setting, 100 concurrent serverless instances would result in 500 active Postgres connections (100 * 5), quickly exceeding typical `max_connections` limits on the database. Option A is incorrect; the `max` setting is respected by each individual `pg.Pool` instance. Option C is incorrect because `idleTimeoutMillis` affects how long idle connections are kept, not the total number of *active* connections created by many concurrent pools. Option D is incorrect because the problem states the use of `pg.Pool`, which implies a Node.js runtime, not the Edge Runtime, and the issue is exhaustion of connections, not a lack of TCP support.

Q: A developer attempts to integrate a standard `pg` client directly into a Next.js Edge API Route to fetch data from Postgres. The deployment fails with errors indicating an inability to establish a database connection. What is the primary technical reason for this failure?

B: The Edge Runtime operates in a V8 isolate and fundamentally lacks support for standard Node.js TCP sockets, which the `pg` client relies on for the Postgres wire protocol. — Option B is correct. The Next.js Edge Runtime is a V8 isolate environment, not a full Node.js environment. It has significant limitations, including the absence of Node.js's `net` module, which provides TCP socket functionality. Standard Postgres drivers like `pg` communicate with the database using the Postgres wire protocol over TCP sockets. Since the Edge Runtime does not support TCP sockets, these drivers cannot establish a connection. Option A is incorrect; while environment variables are crucial, the fundamental issue is the runtime's capability, not just misconfiguration. Option C describes a potential network issue, but the core problem in Edge Runtime is the inability to even *attempt* a TCP connection. Option D is incorrect; the issue is not about pooling incompatibility but the inability to establish any TCP connection at all.

What this module covers: P-8 introduced PgBouncer. This module goes deeper into the failure modes that silently break applications when pooling is misconfigured — the session vs transaction mode divide that breaks prepared statements and temporary tables, the specific ways Next.js and serverless environments create connection spike patterns that exhaust pools, and the correct architecture for each deployment context.

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).

[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:

-- 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:

-- 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:

-- 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:

-- 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:

-- 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.

[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.

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.

// 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 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 with asyncpg — disable prepared statement caching
conn = await asyncpg.connect(dsn, statement_cache_size=0)

Solution 2: Use `max_prepared_statements`

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

[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:

[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

// 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.

// 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:

// 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;

// 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.

[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:

// 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:

// 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);
}

// 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);
}

// 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

# Enable PgBouncer admin console
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

-- 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

Mode	Works	Breaks	Use When
Session	Everything	Pool savings (minimal)	Need prepared statements, temp tables, advisory locks
Transaction	High-throughput OLTP	Prepared statements, temp tables, session SET, advisory locks	Most OLTP workloads — disable prepared statements in driver
Statement	Nothing useful	Multi-statement transactions	Avoid

Serverless checklist:

Use max: 2–5 per serverless instance, not default pool size
Use module-level singletons to share pool across warm invocations
Use HTTP-based drivers (Neon, Supabase) for Edge Runtime
Put PgBouncer in transaction mode between serverless and Postgres
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

Knowledge Check

A Next.js application using the pg driver and PgBouncer in transaction mode experiences intermittent ERROR: prepared statement "..." does not exist in production, but not in local development. Which of the following is the most accurate explanation for this behavior?

A high-traffic Next.js application deployed to a serverless platform (e.g., Vercel, AWS Lambda) is experiencing max_connections exhaustion on its Postgres database, even with a seemingly small max: 5 setting in its pg.Pool configuration. The application uses the standard pg client. What is the fundamental architectural problem causing this exhaustion?

A developer attempts to integrate a standard pg client directly into a Next.js Edge API Route to fetch data from Postgres. The deployment fails with errors indicating an inability to establish a database connection. What is the primary technical reason for this failure?

Test your knowledge with more question sets

Sign in to keep reading

The rest of this module is free — sign in with Google to unlock it and track your progress.

PreviousModule P-10: Zero-Downtime Schema Migrations Next Module A-0: Before You Proceed: Mental Model Reset

Discussion

Join the discussion

Loading comments...