LLM Best Practices

Prisma Connection Pooling

4 min read

View markdown source

Overview

Every PrismaClient instance maintains an internal connection pool. By default, Prisma sizes the pool based on CPU count and keeps connections open for the lifetime of the process. In long-running servers this works. In serverless and edge runtimes, each function invocation may spawn a new client, exhausting postgres’s connection limit in minutes. Connection pooling sits between the application and Postgres to multiplex many client connections onto a small number of database connections. Choose the right pooling strategy for your runtime before you scale.

Use Prisma Accelerate for serverless and edge runtimes

Prisma Accelerate is a managed connection pooler and global cache offered by Prisma. It runs at the edge and keeps a warm pool of connections to your database.

// Replace DATABASE_URL with the Accelerate proxy URL in production.
// No code changes needed; the client connects to Accelerate instead of Postgres.
const prisma = new PrismaClient()
  • Use Accelerate when you deploy on Lambda, Vercel Functions, Cloudflare Workers, or any platform where the function process does not persist between requests.
  • Accelerate also caches query results at the edge. Enable it per query: prisma.user.findUnique({ ..., cacheStrategy: { ttl: 60 } }).
  • Accelerate does not support interactive transactions with custom isolation levels on all plans. Verify the feature matrix before relying on it. See prisma-transactions for transaction options.

Use PgBouncer for long-running Node servers

PgBouncer is a lightweight connection pooler you self-host in front of Postgres. It is the standard choice for VPS and container workloads.

# .env (long-running server behind PgBouncer)
DATABASE_URL="postgresql://user:pass@pgbouncer-host:5432/db?pgbouncer=true&connection_limit=1"
  • ?pgbouncer=true disables Prisma’s own prepared-statement caching, which is incompatible with PgBouncer’s transaction mode.
  • connection_limit=1 tells Prisma to open only one connection per client instance. PgBouncer multiplexes many such connections onto fewer database connections.
  • PgBouncer in transaction mode is the correct choice for most Prisma setups. Session mode defeats the purpose of pooling when you have many short-lived queries. See hostinger-vps for a typical PgBouncer deployment.

Know what breaks in PgBouncer transaction mode

Transaction mode resets server state between transactions. Several Postgres features rely on session state and break.

  • SET LOCAL and SET SESSION are not safe. Prisma uses SET LOCAL internally for some operations.
  • Advisory locks (pg_advisory_lock) require session affinity. They silently fail in transaction mode.
  • Prepared statements are not reusable across connections. ?pgbouncer=true disables Prisma’s prepared-statement cache to handle this.
  • LISTEN/NOTIFY requires a persistent session. Use Prisma Pulse or a dedicated non-pooled connection for change data capture.

If you need any of these features, configure a second connection string that bypasses PgBouncer and use it only for those operations.

Set connection_limit and pool_timeout explicitly

Prisma’s default pool limit is num_cpus * 2 + 1. On a 4-core machine that is 9 connections. This may be too many or too few depending on your workload.

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: `${process.env.DATABASE_URL}&connection_limit=5&pool_timeout=10`,
    },
  },
})
  • Set connection_limit to a value that matches PgBouncer’s max_client_conn divided by the number of app instances.
  • Set pool_timeout (in seconds) to fail fast when the pool is exhausted rather than queuing indefinitely.
  • Monitor pg_stat_activity on postgres to see actual connection counts and idle time.

Avoid cold-start connection exhaustion in serverless

Each cold Lambda or serverless container creates a new PrismaClient. If 100 containers start simultaneously, they attempt 100 connections to Postgres. Without a pooler, Postgres hits max_connections and starts refusing.

// Lambda: explicit connect/disconnect to return the connection promptly.
export const handler = async (event: unknown) => {
  await prisma.$connect()
  try {
    return await handleEvent(event)
  } finally {
    await prisma.$disconnect()
  }
}
  • Explicit $disconnect returns the connection to the pool (or closes it for direct connections) before the container goes idle.
  • With Prisma Accelerate or PgBouncer, the pooler absorbs the burst. Direct connections to Postgres do not. See prisma-client for the full Lambda lifecycle pattern.

Use Pulse for real-time change data capture

Prisma Pulse streams database change events over a persistent WebSocket. It requires session-level LISTEN/NOTIFY, which bypasses PgBouncer.

const subscription = await prisma.user.stream()
 
for await (const event of subscription) {
  console.log(event.action, event.created)
}
  • Pulse manages its own connection to Postgres, separate from the query pool.
  • Use Pulse for webhooks, cache invalidation, and audit trails where you need real-time row-level events.
  • Do not use Pulse as a general-purpose message queue. For fan-out workloads, pair it with a proper queue service.

Graph View

Backlinks