Understand the problem

// Without pooling - each request creates new connection
export async function handler(req) {
  const client = new Client(connectionString);
  await client.connect();  // ~50-100ms overhead
  const result = await client.query('SELECT...');
  await client.end();
  return result;
}

// 100 concurrent requests = 100 connections
// Most databases allow only 100-500 connections total
// Result: "too many connections" errors

Connection pooling reuses existing connections, reducing overhead and preventing exhaustion.

Supabase connection pooling

Supabase provides built-in connection pooling via Supavisor:

// Direct connection (for migrations, admin tasks)
DATABASE_URL="postgresql://postgres:password@db.xxx.supabase.co:5432/postgres"

// Pooled connection (for application use) - use port 6543
DATABASE_URL="postgresql://postgres:password@db.xxx.supabase.co:6543/postgres?pgbouncer=true"

// For Prisma with Supabase
// schema.prisma
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")  // For migrations
}

// .env
DATABASE_URL="postgresql://...@db.xxx.supabase.co:6543/postgres?pgbouncer=true"
DIRECT_URL="postgresql://...@db.xxx.supabase.co:5432/postgres"

Neon connection pooling

// Neon provides built-in pooling
// Use the pooled connection string from dashboard

// With Prisma
DATABASE_URL="postgresql://user:pass@ep-xxx.us-east-1.aws.neon.tech/mydb?sslmode=require&pgbouncer=true"

// Neon also supports serverless driver for edge
import { neon } from '@neondatabase/serverless';

const sql = neon(process.env.DATABASE_URL);
const result = await sql`SELECT * FROM users WHERE id = ${userId}`;

Prisma connection pooling

// For serverless (Vercel, Lambda, etc.)
// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

// Connection management for serverless
import { PrismaClient } from '@prisma/client';

// Prevent multiple instances in development
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
};

export const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: process.env.NODE_ENV === 'development' ? ['query'] : [],
});

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

// Connection string with pool settings
DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=5&pool_timeout=10"

node-postgres (pg) pooling

import { Pool } from 'pg';

// Create pool (do this once, not per request)
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                    // Maximum connections in pool
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  connectionTimeoutMillis: 5000, // Timeout for new connections
  maxUses: 7500,              // Close connection after N queries
});

// Use pool for queries
export async function getUser(id) {
  const client = await pool.connect();
  try {
    const result = await client.query(
      'SELECT * FROM users WHERE id = $1',
      [id]
    );
    return result.rows[0];
  } finally {
    client.release();  // Always release back to pool!
  }
}

// Or simpler syntax (auto-releases)
export async function getUsers() {
  const result = await pool.query('SELECT * FROM users');
  return result.rows;
}

PgBouncer for self-hosted databases

# Install PgBouncer
sudo apt install pgbouncer

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction    # Best for web apps
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5

# /etc/pgbouncer/userlist.txt
"myuser" "md5passwordhash"

# Connect through PgBouncer
psql -h localhost -p 6432 -U myuser mydb

Serverless-specific configuration

// For Vercel Edge Functions / Cloudflare Workers
// Use HTTP-based database connections

// Neon Serverless Driver
import { neon } from '@neondatabase/serverless';

export const config = { runtime: 'edge' };

export default async function handler(req) {
  const sql = neon(process.env.DATABASE_URL);
  const users = await sql`SELECT * FROM users LIMIT 10`;
  return Response.json(users);
}

// PlanetScale Serverless Driver
import { connect } from '@planetscale/database';

const conn = connect({
  host: process.env.DATABASE_HOST,
  username: process.env.DATABASE_USERNAME,
  password: process.env.DATABASE_PASSWORD,
});

const results = await conn.execute('SELECT * FROM users');