{
  "$type": "site.standard.document",
  "bskyPostRef": {
    "cid": "bafyreiejqxyucdygp7rq7tlimj32tgxfzzvcyacbldlcfws2rlczpctesa",
    "uri": "at://did:plc:25rdn5elo5izoxrmtis34zuk/app.bsky.feed.post/3mogxqrcyb6h2"
  },
  "coverImage": {
    "$type": "blob",
    "ref": {
      "$link": "bafkreiewalyxpsgust5qudmchue2spvuqwaqvh42fioesjytycpeakl2fm"
    },
    "mimeType": "image/webp",
    "size": 186968
  },
  "path": "/mahdi_benrhouma_fe1c6005/supabase-connection-pooling-with-pgbouncer-on-vercel-serverless-1o33",
  "publishedAt": "2026-06-16T22:59:31.000Z",
  "site": "https://dev.to",
  "tags": [
    "supabase",
    "pgbouncer",
    "connectionpooling",
    "vercel",
    "https://www.iloveblogs.blog",
    "@aws-0-",
    "@db.",
    "@supabase",
    "@prisma",
    "@neondatabase"
  ],
  "textContent": "#  Supabase Connection Pooling with PgBouncer on Vercel Serverless\n\nThe 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.\n\nThis 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.\n\n**Estimated read time: 12 minutes**\n\n##  Prerequisites\n\n  * Supabase project (any plan)\n  * Next.js app deployed or deploying to Vercel\n  * Basic understanding of environment variables and database connections\n  * Familiarity with Supabase client setup\n\n\n\n##  Why Serverless Breaks Traditional Connection Management\n\nIn 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.\n\nIn 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.\n\nPostgres itself has a connection limit based on your plan:\n\n  * Supabase Free: 60 connections\n  * Supabase Pro: 120 connections\n  * Larger plans: scales with compute\n\n\n\n60 connections sounds like a lot until you have 60 concurrent users each triggering a serverless function. At that point, the 61st request fails.\n\nPgBouncer sits between your application and Postgres, maintaining a small pool of actual Postgres connections and multiplexing many application connections through them.\n\n##  Supabase's Two Connection Endpoints\n\nEvery Supabase project has two ways to connect:\n\n| Direct Connection | Supabase Pooler (PgBouncer)\n---|---|---\nPort | 5432 | 6543\nUse case | Migrations, long-lived servers | Serverless, short-lived connections\nPrepared statements | Yes | No (transaction mode)\nConnection limit | Your Postgres limit | Effectively unlimited for app\n\nFind both in your Supabase dashboard: **Project Settings → Database → Connection string**.\n\n##  Configuring Your Environment Variables\n\n\n    # .env.local\n\n    # For application queries (use this in your app)\n    DATABASE_URL=\"postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres\"\n\n    # For migrations only (direct connection)\n    DIRECT_URL=\"postgresql://postgres.[project-ref]:[password]@db.[project-ref].supabase.co:5432/postgres\"\n\n    # Supabase client (unchanged)\n    NEXT_PUBLIC_SUPABASE_URL=\"https://[project-ref].supabase.co\"\n    NEXT_PUBLIC_SUPABASE_ANON_KEY=\"your-anon-key\"\n\n\nThe 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.\n\n##  Pool Modes: Transaction vs Session\n\nPgBouncer supports three modes. For Supabase + Vercel, you need **transaction mode**.\n\n**Transaction mode** (port 6543, what Supabase uses by default):\n\n  * A connection is borrowed from the pool for the duration of one transaction\n  * Released back to the pool immediately after `COMMIT` or `ROLLBACK`\n  * Supports hundreds of concurrent app connections with a small Postgres pool\n  * Does NOT support: prepared statements, `SET` commands that persist across transactions, advisory locks, `LISTEN/NOTIFY`\n\n\n\n**Session mode** (port 5432 direct, or configurable):\n\n  * One Postgres connection per client session\n  * Supports all Postgres features\n  * Not suitable for serverless — you're back to the original problem\n\n\n\nFor serverless, transaction mode is the only viable option.\n\n##  Using the Pooler with the Supabase JS Client\n\nThe Supabase JS client (`@supabase/supabase-js`) uses the REST API and Realtime, not a direct Postgres connection. It's not affected by PgBouncer configuration.\n\nPgBouncer matters when you're using a direct Postgres client — typically with an ORM like Prisma or Drizzle, or with `pg` directly.\n\n###  With Prisma\n\n\n    // prisma/schema.prisma\n    datasource db {\n      provider  = \"postgresql\"\n      url       = env(\"DATABASE_URL\")      // pooler URL (port 6543)\n      directUrl = env(\"DIRECT_URL\")        // direct URL (port 5432) for migrations\n    }\n\n\nPrisma uses `directUrl` for `prisma migrate` and `url` for all runtime queries. This is the correct setup for Vercel deployments.\n\nAlso disable prepared statements in your Prisma client for transaction mode compatibility:\n\n\n\n    // src/lib/prisma.ts\n    import { PrismaClient } from '@prisma/client'\n\n    const globalForPrisma = globalThis as unknown as {\n      prisma: PrismaClient | undefined\n    }\n\n    export const prisma =\n      globalForPrisma.prisma ??\n      new PrismaClient({\n        datasources: {\n          db: {\n            url: process.env.DATABASE_URL,\n          },\n        },\n      })\n\n    if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma\n\n\nThe global singleton pattern prevents creating a new Prisma client on every hot-reload in development.\n\n###  With Drizzle ORM\n\n\n    // src/lib/db.ts\n    import { drizzle } from 'drizzle-orm/postgres-js'\n    import postgres from 'postgres'\n\n    // For serverless: use pooler URL, disable prepare\n    const client = postgres(process.env.DATABASE_URL!, {\n      prepare: false, // required for PgBouncer transaction mode\n    })\n\n    export const db = drizzle(client)\n\n\nThe `prepare: false` option is critical. Without it, Drizzle will attempt to use prepared statements, which fail in transaction mode.\n\n###  With the `pg` Package Directly\n\n\n    // src/lib/db.ts\n    import { Pool } from 'pg'\n\n    // In serverless, a pool of 1 is often optimal\n    // The pooler handles the actual connection multiplexing\n    const pool = new Pool({\n      connectionString: process.env.DATABASE_URL,\n      max: 1, // one connection per function instance\n      idleTimeoutMillis: 0,\n      connectionTimeoutMillis: 5000,\n    })\n\n    export default pool\n\n\nSetting `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.\n\n##  Monitoring Connection Usage\n\nCheck your current connection count in Supabase:\n\n\n\n    -- Run in Supabase SQL editor\n    SELECT count(*) FROM pg_stat_activity;\n\n    -- See connections by state\n    SELECT state, count(*)\n    FROM pg_stat_activity\n    GROUP BY state;\n\n    -- See connections by application\n    SELECT application_name, count(*)\n    FROM pg_stat_activity\n    GROUP BY application_name\n    ORDER BY count DESC;\n\n\nIf 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.\n\nSupabase also exposes connection metrics in the dashboard under **Reports → Database**.\n\n##  Vercel-Specific Considerations\n\n**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.\n\n**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]\n\n**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.\n\n**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.\n\n##  Common Pitfalls\n\n**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.\n\n**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.\n\n**Running migrations through the pooler.** Some migration operations (like `CREATE INDEX CONCURRENTLY`) require a persistent session connection. Always run migrations using the direct URL.\n\n**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.\n\n##  Summary and Next Steps\n\nThe 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.\n\nThe 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.\n\nRelated reading:\n\n  * [INTERNAL LINK: deploying-nextjs-supabase-production]\n  * [INTERNAL LINK: nextjs-supabase-database-design-optimization]\n  * [INTERNAL LINK: nextjs-supabase-caching-strategies]\n\n\n\n_Originally published at https://www.iloveblogs.blog_",
  "title": "Supabase Connection Pooling with PgBouncer on Vercel Serverless"
}