Connection Pooling Is Not Optional — PostgreSQL at Scale for Multi-Tenant SaaS

PostgreSQL spawns a separate OS process for every client connection. Each process consumes 5-10 MB of RAM and adds scheduling overhead. At 100 connections, most servers hum along fine. At 300, you notice. At 500, you're restarting the database at 3 a.m. wondering why pool: 5 in your database.yml is suddenly a production incident.

Connection pooling isn't an optimization. It's a precondition for running multi-tenant SaaS on PostgreSQL without hiring a DBA to manage connection counts.

The Math That Catches Every Team Off Guard

Here's the formula that breaks your database:

max_connections_needed = (app_instances × pool_size_per_instance) + admin_connections

A realistic Indian SaaS setup:

PostgreSQL's default max_connections is 100. Most teams bump it to 200-300. After that, performance degrades non-linearly — each new connection makes every existing connection slightly slower because the postmaster has more processes to schedule.

What PgBouncer Does

PgBouncer sits between your app and PostgreSQL as a lightweight connection pooler. It maintains a small pool of persistent connections to PostgreSQL and multiplexes hundreds or thousands of client connections through them.

App → PgBouncer (port 6432) → PostgreSQL (port 5432)
     1000 client conns  →     25 actual PG conns

Three pooling modes:

Transaction pooling is the right default for most SaaS backends. It reduces PG connections from hundreds to the number of concurrent transactions your server can actually process — typically 10-30.

Install and Configure PgBouncer

# Ubuntu/Debian
apt install -y pgbouncer

# Configuration: /etc/pgbouncer/pgbouncer.ini

Minimal production config:

[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Pool sizing — the numbers that matter
default_pool_size = 25
max_client_conn = 500
max_db_connections = 25
pool_mode = transaction

# Keep idle connections alive across pool re-use
server_idle_timeout = 600
client_idle_timeout = 0

# Logging for debugging connection leaks
log_connections = 1
log_disconnections = 1
stats_period = 60

Create the userlist:

# Generate SCRAM hash
# In psql: SELECT rolpassword FROM pg_authid WHERE rolname='your_app_user';
# Or use: pgbouncer-auth /etc/pgbouncer/userlist.txt
echo '"your_app_user" "SCRAM-SHA-256$4096:..."' > /etc/pgbouncer/userlist.txt
systemctl restart pgbouncer

Update Your Application Config

Change the database connection string from port 5432 to 6432:

# Before
DATABASE_URL=postgresql://user:pass@localhost:5432/myapp

# After
DATABASE_URL=postgresql://user:pass@localhost:6432/myapp

That's it. Your app now goes through PgBouncer. All existing queries, ORM usage, and migrations work identically — PgBouncer is transparent to SQL.

What Breaks With Transaction Pooling

Transaction pooling releases the PG connection between transactions. This breaks features that depend on session state:

1. SET statements — SET timezone, SET search_path, etc. These are per-session and reset between transactions. Move these to per-transaction SET LOCAL or configure them at the user level (ALTER USER ... SET timezone = 'Asia/Kolkata').

2. Prepared statements — Named prepared statements (PREPARE foo AS ...) are per-session. With transaction pooling, use unnamed prepared statements or let your ORM handle this (Prisma, ActiveRecord, and SQLAlchemy all handle it automatically).

3. LISTEN/NOTIFY — Requires a persistent session. If you use Postgres NOTIFY for real-time events, keep a separate direct connection for your listener or switch to Redis pub/sub.

4. Advisory locks — Also session-scoped. If you're using pg_advisory_lock for distributed locking, switch to Redis or PostgreSQL's pg_advisory_xact_lock (transaction-scoped, auto-released).

Monitoring: Know When Pooling Is Saturated

PgBouncer exposes an admin console. Connect to it:

psql -p 6432 -U pgbouncer pgbouncer

Key metrics:

-- Active vs waiting clients
SHOW POOLS;
-- cl_active: clients with an active PG connection
-- cl_waiting: clients queued, waiting for a free connection
-- sv_active: PostgreSQL connections in use
-- sv_idle: PostgreSQL connections idle in pool

-- Aggregate stats
SHOW STATS;
-- total_xact_count, total_query_count, avg_query_time

-- Client connections
SHOW CLIENTS;

The metric that should trigger an alert: cl_waiting > 0 for more than 30 seconds. If clients are queuing, increase default_pool_size or add more PgBouncer instances behind a TCP load balancer.

The Indian SaaS Reality

Most Indian SaaS products start with a single PostgreSQL instance on the same VPS as the app. At 10 tenants, that's fine. At 50 tenants with 4 PM2 workers, each opening 10 connections, you're at 200+ connections. The database hasn't changed — max_connections default is still 100. You bumped it to 300, which works for now.

PgBouncer changes the math from "connections = app instances × pool size" to "connections = concurrent transactions." A 4-worker app with 10 concurrent requests maxes out at about 10 PG connections, not 40. Add 10 more workers — still 10 PG connections because PostgreSQL only processes 10 transactions at a time on that hardware anyway.

Setup time: 20 minutes. Monthly cost: ₹0. Production incident avoided: one at 3 a.m. you'll never know about.