Web Dev
Database Connection Pooling in NestJS: PgBouncer and Why You Need It
April 202510 min read
Web Dev
PostgreSQL has a default max_connections of 100. A typical NestJS app with Prisma or TypeORM opens a connection pool with a default max of 10 connections per process. That sounds fine — until you run 20 Node.js processes (load balanced), each opening 10 connections, and you're at 200 connections. PostgreSQL starts rejecting connections. New users get 'too many connections' errors. This is one of the most common production failures I see in Node.js/PostgreSQL stacks, and it's almost entirely preventable with PgBouncer.
Each PostgreSQL connection requires a dedicated backend process (not a thread — a process) on the server. Creating a new connection involves forking a new OS process, which takes 5-30ms and allocates memory for the backend. With 100 connections, PostgreSQL is managing 100 OS processes, each consuming around 5-10MB of memory. Beyond memory, there's the connection establishment overhead: TCP handshake, SSL negotiation, authentication, and initial setup queries. For a high-throughput API that opens a new connection per request, this overhead dominates response time.
Libraries like `pg` (node-postgres), Prisma, and TypeORM maintain an application-level connection pool. The pool keeps a set of established connections open and hands them to queries as needed. When a query completes, the connection is returned to the pool rather than closed. This eliminates per-request connection establishment overhead. The pool has a minimum and maximum size — minimum keeps connections warm, maximum caps the total connections opened by the process. This works well for a single process, but breaks down when you scale horizontally.
In production, you typically run multiple Node.js processes — at minimum, one per CPU core. Each process has its own connection pool. If you have 8 CPU cores running NestJS and each has a max pool size of 20, that's 160 connections to PostgreSQL just from your application. Add a CI runner, a migration job, an analytics query, and background workers, and you're quickly approaching PostgreSQL's connection limit. The solution is to put a connection pooler between your application and PostgreSQL that manages the actual database connections centrally.
WITHOUT PgBouncer (dangerous at scale):
─────────────────────────────────────────────
NestJS Process 1 ──── 20 connections ────┐
NestJS Process 2 ──── 20 connections ────┼──► PostgreSQL (max 100)
NestJS Process 3 ──── 20 connections ────┤ OVERLOADED at 60+ procs
...8 processes ──── 20 x 8 = 160 ─────┘
WITH PgBouncer (production-ready):
─────────────────────────────────────────────
NestJS Process 1 ── 2 conns ──┐
NestJS Process 2 ── 2 conns ──┤
NestJS Process 3 ── 2 conns ──┼──► PgBouncer ──► PostgreSQL (10 conns)
...8 processes ── 2 x 8 = 16┘ Pool: 10 max_connections: 100
(80% headroom for other services)
PgBouncer pool_size formula:
optimal = (CPU_cores * 2) + number_of_disks
For 4-core server: (4 * 2) + 1 = 9 → round to 10From my experience running PostgreSQL in production: set the Prisma connection pool size to `DATABASE_URL` with `?connection_limit=2&pool_timeout=10` per process when you use PgBouncer in transaction mode. With PgBouncer handling the actual PostgreSQL connections, each application process only needs 1-2 concurrent connections to PgBouncer. PgBouncer multiplexes these over a smaller pool of real PostgreSQL connections. The formula: PgBouncer pool_size = (PostgreSQL max_connections * 0.8) / number_of_databases.
PgBouncer is a lightweight connection pooler for PostgreSQL, written in C. It sits between your application and PostgreSQL, accepting application connections and multiplexing them over a smaller number of real PostgreSQL server connections. It's been the standard solution for PostgreSQL connection pooling for over a decade. The key configuration knobs are: pool_mode (session, transaction, or statement), max_client_conn (how many application connections PgBouncer accepts), default_pool_size (PostgreSQL connections per database/user pair), and min_pool_size (connections to keep warm).
PgBouncer offers three pooling modes. Session pooling assigns a PostgreSQL connection for the duration of the client session — nearly equivalent to no pooling. Transaction pooling assigns a connection only for the duration of a transaction, then returns it to the pool — this is the mode that delivers the 10-50x connection efficiency. Statement pooling is the most aggressive but only works if you never use transactions, which rules it out for most applications. Use transaction pooling unless your application uses PostgreSQL session-level features like advisory locks, SET LOCAL, or prepared statements across transactions.
# docker-compose.yml — PgBouncer sidecar
services:
app:
image: my-nestjs-app
environment:
# Connect to PgBouncer, not PostgreSQL directly
DATABASE_URL: postgresql://user:pass@pgbouncer:5432/mydb?pgbouncer=true&connection_limit=2
depends_on:
- pgbouncer
pgbouncer:
image: edoburu/pgbouncer:latest
environment:
POSTGRES_HOST: db
POSTGRES_PORT: 5432
POSTGRES_USER: user
POSTGRES_PASSWORD: pass
POSTGRES_DB: mydb
POOL_MODE: transaction # key: transaction pooling
MAX_CLIENT_CONN: 100 # app connections to PgBouncer
DEFAULT_POOL_SIZE: 10 # PgBouncer → PostgreSQL connections
MIN_POOL_SIZE: 2 # keep warm
RESERVE_POOL_SIZE: 5 # burst capacity
SERVER_IDLE_TIMEOUT: 600
ports:
- "5432:5432"
depends_on:
- db
db:
image: postgres:16-alpine
environment:
POSTGRES_USER: user
POSTGRES_PASSWORD: pass
POSTGRES_DB: mydb
command: postgres -c max_connections=100
# Monitor PgBouncer:
# psql -h localhost -p 5432 -U pgbouncer pgbouncer
# SHOW POOLS; -- check cl_waiting (should be 0)
# SHOW STATS; -- check avg_query_timeFor containerized NestJS applications, the simplest PgBouncer setup is a sidecar container in the same Docker Compose service or Kubernetes pod. Use the `edoburu/pgbouncer` image which accepts configuration via environment variables — no INI file needed. Set POSTGRES_HOST, POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB, POOL_MODE=transaction, MAX_CLIENT_CONN=100, DEFAULT_POOL_SIZE=10. Your NestJS app connects to PgBouncer on localhost:5432, and PgBouncer connects to the real PostgreSQL on its host. No application code changes required.
If your ORM uses prepared statements (TypeORM does by default; Prisma uses named queries that behave similarly), you'll hit errors in PgBouncer transaction pooling mode. The fix for Prisma is to append `?pgbouncer=true` to the DATABASE_URL, which disables named query caching and uses simple protocol queries instead. For TypeORM, set `extra.statement_timeout` and disable `useUTC`. Always test your ORM's PgBouncer compatibility before deploying — prepared statement errors in production are subtle and hard to debug.
PgBouncer exposes a virtual database called `pgbouncer` that you can connect to with psql. The SHOW POOLS command shows current client connections, server connections, and wait queue for each pool. The SHOW STATS command shows query counts and latency. Watch for `cl_waiting` > 0 — this means application connections are waiting for a PgBouncer server connection, indicating your pool is exhausted. Set alerts on `cl_waiting` and tune `default_pool_size` up if you consistently see waits. Also monitor PostgreSQL's `pg_stat_activity` to verify that PgBouncer is keeping the actual connection count low.
PgBouncer isn't the only option. Supavisor (Supabase's connection pooler, written in Elixir) supports both transaction and session pooling with a web UI and multi-tenant routing. It's excellent if you're using Supabase. Neon (serverless PostgreSQL) has built-in connection pooling. RDS Proxy (AWS) is a managed pooler for Aurora and RDS PostgreSQL. For simple setups on a single VPS, PgBouncer as a sidecar is the lowest-overhead option. For managed cloud databases where you don't control the PostgreSQL server, use the provider's built-in pooler.