Database Connection Pooling at Scale: Configuring PgBouncer and Supabase Connection Pools

The Serverless Connection Exhaustion Problem

Every PostgreSQL connection consumes roughly 5 to 10 MB of RAM on your database server. In a traditional Node.js deployment running on a single long-lived container, your server opens 10 to 20 persistent connections and shares them across requests. But modern platforms like Vercel and AWS Lambda spin up new container instances for every concurrent request.

If 400 users hit your web app simultaneously, 400 isolated serverless functions launch. Each tries to open a direct PostgreSQL connection. If your database instance has a max_connections limit set to 100, 300 requests instantly fail with connection rejected or fatal: remaining connection slots are reserved errors. We resolved this exact issue when building [Aura Estate](/projects/15), a high-traffic real estate platform where property searches and valuation queries execute under heavy concurrent traffic.

How PgBouncer and Supabase Connection Pools Work

A connection pooler acts as a lightweight proxy between your application and your PostgreSQL database. Instead of your serverless workers talking directly to port 5432, they connect to the pooler on port 6543. The pooler keeps a fixed, healthy set of open database connections (for example, 15 to 25 connections) and queues or reallocates them instantly as incoming web requests arrive.

Configuring Prisma and Drizzle with Supabase Pooling

When using an ORM like Prisma or Drizzle in serverless production, you need two distinct connection strings in your environment variables: one for migration runs and one for daily application traffic.

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

// Transaction pooler connection string (Port 6543)
// Add ?pgbouncer=true or prepare=false to prevent prepared statement errors
const connectionString = process.env.DATABASE_URL!;

// Configure client with max 1 connection per serverless function instance
const client = postgres(connectionString, {
  max: 1,
  idle_timeout: 20,
  connect_timeout: 10,
  prepare: false, // Required for PgBouncer / Supabase transaction mode
});

export const db = drizzle(client);

Notice the prepare: false flag. Because PgBouncer in transaction mode assigns queries to different backend connections dynamically, prepared statements cached on connection A will fail if a follow-up query runs on connection B. Turning off client-side statement preparation ensures zero runtime errors.

Setting Proper Pool Sizes in Production

To keep your database fast and avoid timeouts, use these practical settings in your database configuration:

• Set pool_mode = transaction in pgbouncer.ini to free slots immediately after each query finishes.
• Set default_pool_size to roughly (2 * CPU Cores) + Disk spindles. For an 8-core database server, a pool size of 20 to 25 yields peak query throughput.
• Keep serverless client connections to max: 1 per instance so ephemeral containers do not hoard idle connections.
• Set statement_timeout = '15s' on the database level to kill runaway queries before they lock up pooled connections.

Need Help Implementing This in Your Product?

If your application is experiencing database crashes, connection timeouts, or slow query spikes under high traffic, I can help audit and optimize your database tier. Check out my [Full-Stack Web Development Services](/services) or [get in touch with Aafaque](/contact) to build a stable, scalable infrastructure.