Supabase Connection Pooling with PgBouncer on Vercel Serverless

The most common production failure pattern for Next.js + Supabase apps on Vercel isn't a code bug — it's connection exhaustion. Your app works fine in development, handles moderate traffic in staging, then falls over under real load with too many connections errors.

This happens because serverless functions don't maintain persistent database connections. Every function invocation opens a new connection. Under load, you can have hundreds of concurrent connections, and Postgres has a hard limit. PgBouncer is the solution, and Supabase ships it built-in. This guide explains how to configure it correctly.

Estimated read time: 12 minutes

Prerequisites

• Supabase project (any plan)
• Next.js app deployed or deploying to Vercel
• Basic understanding of environment variables and database connections
• Familiarity with Supabase client setup

Why Serverless Breaks Traditional Connection Management

In a traditional Node.js server, you create a connection pool once at startup and reuse connections across requests. The pool might have 10–20 connections serving thousands of requests.

In serverless (Vercel Functions, Edge Functions), there is no persistent process. Each function invocation is potentially a new process. Connection pooling at the application level doesn't work because the pool is destroyed when the function exits.

Postgres itself has a connection limit based on your plan:

• Supabase Free: 60 connections
• Supabase Pro: 120 connections
• Larger plans: scales with compute

60 connections sounds like a lot until you have 60 concurrent users each triggering a serverless function. At that point, the 61st request fails.

PgBouncer sits between your application and Postgres, maintaining a small pool of actual Postgres connections and multiplexing many application connections through them.

Supabase's Two Connection Endpoints

Every Supabase project has two ways to connect:

Find both in your Supabase dashboard: Project Settings → Database → Connection string.

Configuring Your Environment Variables

# .env.local

# For application queries (use this in your app)
DATABASE_URL="postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres"

# For migrations only (direct connection)
DIRECT_URL="postgresql://postgres.[project-ref]:[password]@db.[project-ref].supabase.co:5432/postgres"

# Supabase client (unchanged)
NEXT_PUBLIC_SUPABASE_URL="https://[project-ref].supabase.co"
NEXT_PUBLIC_SUPABASE_ANON_KEY="your-anon-key"

The pooler URL uses aws-0-[region].pooler.supabase.com as the host. The direct URL uses db.[project-ref].supabase.co. These are different hosts — make sure you're using the right one.

Pool Modes: Transaction vs Session

PgBouncer supports three modes. For Supabase + Vercel, you need transaction mode.

Transaction mode (port 6543, what Supabase uses by default):

• A connection is borrowed from the pool for the duration of one transaction
• Released back to the pool immediately after COMMIT or ROLLBACK
• Supports hundreds of concurrent app connections with a small Postgres pool
• Does NOT support: prepared statements, SET commands that persist across transactions, advisory locks, LISTEN/NOTIFY

Session mode (port 5432 direct, or configurable):

• One Postgres connection per client session
• Supports all Postgres features
• Not suitable for serverless — you're back to the original problem

For serverless, transaction mode is the only viable option.

Using the Pooler with the Supabase JS Client

The Supabase JS client (@supabase/supabase-js) uses the REST API and Realtime, not a direct Postgres connection. It's not affected by PgBouncer configuration.

PgBouncer matters when you're using a direct Postgres client — typically with an ORM like Prisma or Drizzle, or with pg directly.

With Prisma

// prisma/schema.prisma
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")      // pooler URL (port 6543)
  directUrl = env("DIRECT_URL")        // direct URL (port 5432) for migrations
}

Prisma uses directUrl for prisma migrate and url for all runtime queries. This is the correct setup for Vercel deployments.

Also disable prepared statements in your Prisma client for transaction mode compatibility:

// src/lib/prisma.ts
import { PrismaClient } from '@prisma/client'

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    datasources: {
      db: {
        url: process.env.DATABASE_URL,
      },
    },
  })

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

The global singleton pattern prevents creating a new Prisma client on every hot-reload in development.

With Drizzle ORM

// src/lib/db.ts
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'

// For serverless: use pooler URL, disable prepare
const client = postgres(process.env.DATABASE_URL!, {
  prepare: false, // required for PgBouncer transaction mode
})

export const db = drizzle(client)

The prepare: false option is critical. Without it, Drizzle will attempt to use prepared statements, which fail in transaction mode.

With the pg Package Directly

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

// In serverless, a pool of 1 is often optimal
// The pooler handles the actual connection multiplexing
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 1, // one connection per function instance
  idleTimeoutMillis: 0,
  connectionTimeoutMillis: 5000,
})

export default pool

Setting max: 1 might seem counterintuitive, but in serverless each function instance only handles one request at a time. The PgBouncer pool handles multiplexing across instances.

Monitoring Connection Usage

Check your current connection count in Supabase:

-- Run in Supabase SQL editor
SELECT count(*) FROM pg_stat_activity;

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

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

If you see many connections in idle state, your application isn't releasing connections properly. If you see connections in idle in transaction state, you have transactions that aren't being committed or rolled back.

Supabase also exposes connection metrics in the dashboard under Reports → Database.

Vercel-Specific Considerations

Function concurrency: Vercel can run many function instances simultaneously. Each instance may hold a connection. The pooler absorbs this, but you should still set reasonable connection limits per instance.

Edge Runtime: If you're using Next.js Edge Runtime (export const runtime = 'edge'), you cannot use Node.js database drivers. Use the Supabase JS client (which uses HTTP) or a Postgres driver that supports the Edge Runtime like @neondatabase/serverless with a compatible adapter. [NEEDS VERIFICATION: check current Supabase Edge Runtime Postgres driver support]

Warm vs cold starts: On cold starts, a new connection must be established. This adds 50–200ms to the first request. Subsequent requests on a warm function reuse the connection. This is normal behavior — don't try to pre-warm connections.

Connection string in environment variables: Never hardcode connection strings. Use Vercel's environment variable system and ensure DATABASE_URL is set for all environments (development, preview, production) with the appropriate values.

Common Pitfalls

Using the direct connection URL in production. The direct URL (port 5432) is for migrations and admin tasks. Using it for application queries in serverless will exhaust connections under load.

Not setting prepare: false with Drizzle or pg. Transaction mode doesn't support prepared statements. This causes cryptic errors that are hard to trace back to the connection mode.

Running migrations through the pooler. Some migration operations (like CREATE INDEX CONCURRENTLY) require a persistent session connection. Always run migrations using the direct URL.

Ignoring idle in transaction connections. These are connections that started a transaction but never committed. They hold a Postgres connection indefinitely. Always use try/catch/finally to ensure transactions are committed or rolled back.

Summary and Next Steps

The setup is straightforward: use the pooler URL (port 6543) for all application queries, the direct URL (port 5432) for migrations only, and disable prepared statements in your ORM. That's it.

The deeper lesson is that serverless and traditional connection management are fundamentally incompatible. PgBouncer bridges that gap, but you need to understand what it trades away (prepared statements, session-level state) to use it correctly.

Related reading:

• [INTERNAL LINK: deploying-nextjs-supabase-production]
• [INTERNAL LINK: nextjs-supabase-database-design-optimization]
• [INTERNAL LINK: nextjs-supabase-caching-strategies]

Originally published at https://www.iloveblogs.blog