How-To Guide

How to Set Up Database Connection Pooling

Essential for serverless and high-traffic applications

TL;DR

TL;DR (20 minutes): Use your database provider's pooler (Supabase, Neon, PlanetScale all have built-in options). For Prisma, enable connection pooling with ?pgbouncer=true . Set pool size to 2-5 connections for serverless, 10-20 for traditional servers. Always handle connection errors and implement retries.

Prerequisites:

PostgreSQL, MySQL, or similar database
Understanding of your deployment environment (serverless vs. traditional)
Database connection string

Why This Matters

Database connections are expensive to create. Each connection uses memory, requires authentication, and takes time to establish. Without pooling, serverless functions can exhaust your database's connection limit within seconds during traffic spikes.

Connection pooling also improves security by centralizing connection management, enabling connection-level monitoring, and reducing the attack surface of your database.

Step-by-Step Guide

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');

Pool Sizing Guidelines:

Serverless functions: 1-5 connections per function instance
Traditional Node.js server: 10-20 connections
Formula: connections = (cores * 2) + spindle_count (for traditional DBs)
Never exceed: Database max_connections / number_of_app_instances
Monitor connection usage and adjust based on actual needs

How to Verify It Worked

Check connection count: Monitor active connections in your database
Verify pooling: Ensure connections are being reused
Load test: Simulate concurrent requests

-- PostgreSQL: Check current connections
SELECT count(*) FROM pg_stat_activity;

-- See connections by application
SELECT application_name, count(*)
FROM pg_stat_activity
GROUP BY application_name;

-- Check connection states
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

Common Errors & Troubleshooting

Error: "too many connections"

Reduce pool size, ensure connections are being released, or use a connection pooler like PgBouncer.

Error: "connection terminated unexpectedly"

Pool timeout or idle connection closed. Add retry logic or adjust idle timeout settings.

Slow queries after idle period

Connections being re-established. Use min_pool_size to keep some connections warm.

Prisma + PgBouncer prepared statements error

Add ?pgbouncer=true to your connection string, which disables prepared statements (incompatible with transaction pooling).

Transaction vs. session pooling?

Transaction pooling (recommended for web apps) assigns a connection per transaction. Session pooling assigns per client session. Transaction mode is more efficient but doesn't support certain features like prepared statements or SET commands.

Do I need external pooling if my ORM has built-in pooling?

For serverless, yes - each function instance creates its own pool. External poolers like PgBouncer or managed database poolers can aggregate connections across all instances.

Why are my Vercel functions exhausting connections?

Each function invocation can create new connections. Use your database provider's pooler, reduce pool size to 1-2, and consider using HTTP-based serverless database drivers.

Related guides:Prisma Security · PostgreSQL Roles · Database Encryption