PgBouncer and Connection Pooling for PostgreSQL at SaaS Scale

PostgreSQL's connection limit is the scaling wall most SaaS backends hit before any other bottleneck. Each database connection in PostgreSQL is a separate OS-level process consuming roughly 5 to 10 MB of server RAM. A database with max_connections set to 200 is saturated at 200 concurrent connections, regardless of how many application instances you run or how much RAM those instances have.

For Go SaaS backends serving multiple tenants in Lebanon and the MENA region, this becomes an operational problem earlier than teams expect. This is how we configure PgBouncer correctly and what the tradeoffs actually look like in production.

Why does Go make this problem worse?

Go's standard database/sql package maintains a connection pool per process. Each running Go service maintains multiple open PostgreSQL connections. When you run four services (API, worker, scheduler, admin) each with a pool of 25 connections, you are at 100 connections before a single user sends a request.

Add multiple instances of each service for redundancy and you exceed 200 connections quickly. A Fargate deployment with two API tasks, two worker tasks, and one scheduler task, each with a pool of 25 connections, uses 125 connections at idle. Any traffic spike that triggers auto-scaling to four API tasks and four worker tasks pushes this to 225, saturating a default-configured PostgreSQL instance.

The symptom is not an error message. It is gradual query slowdown as PostgreSQL spends increasing time managing connection overhead, followed by sudden connection refused errors when max_connections is exceeded.

What PgBouncer does

PgBouncer is a lightweight connection pooler that sits between your application and PostgreSQL. Your application services connect to PgBouncer, which maintains a smaller pool of real PostgreSQL connections and routes application queries through them.

The critical insight is that most application connections are idle most of the time. A connection pool of 25 per service instance does not mean 25 simultaneous queries, it means up to 25 connections held open waiting for work. PgBouncer exploits this by multiplexing many application connections across fewer real database connections.

PgBouncer operates in three modes:

Session mode assigns a PostgreSQL connection to a client for the entire session. This is equivalent to a direct connection with a connection limit benefit: you can have many client connections without exceeding PostgreSQL's limit, as long as total concurrent sessions stays below the PgBouncer pool size. It does not reduce the number of actual PostgreSQL connections significantly.

Transaction mode releases the PostgreSQL connection back to the pool after each transaction completes. This is the high-value mode. Hundreds of application connections can share a small pool of PostgreSQL connections because connections are only held during actual transactions, not between them.

Statement mode releases after each statement. This is rarely useful for typical web applications because it breaks multi-statement transactions.

For most Go SaaS backends, transaction mode is the correct choice.

Configuring PgBouncer for a Go SaaS backend

A production PgBouncer configuration for a Go SaaS backend with multiple tenants:

[databases]
voxire_db = host=postgres.internal port=5432 dbname=voxire

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

pool_mode = transaction
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

server_idle_timeout = 600
client_idle_timeout = 0
server_lifetime = 3600

log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

max_db_connections = 80

Key parameters explained:

default_pool_size = 20 means PgBouncer maintains at most 20 real PostgreSQL connections per database/user combination. 20 real connections serve 500 application connections in transaction mode because connections are released between transactions.

max_client_conn = 500 is the ceiling on application-side connections to PgBouncer. Set this high. It does not correspond to PostgreSQL connections, just PgBouncer client slots.

reserve_pool_size = 5 keeps five extra connections available for burst traffic above the default pool size. reserve_pool_timeout = 3 means clients wait up to three seconds for a reserve connection before receiving an error.

max_db_connections = 80 caps total real connections to PostgreSQL regardless of pool math. This prevents PgBouncer from exceeding your PostgreSQL max_connections budget if you have multiple PgBouncer instances or multiple databases.

What breaks in transaction mode

Transaction mode is powerful but it has hard incompatibilities that will break your application if you do not account for them.

Session-level SET statements do not persist. SET search_path = tenant_123 in one transaction does not carry over to the next transaction on the same client connection, because a different real PostgreSQL connection may serve that next transaction. For schema-per-tenant multi-tenancy, this means you cannot use SET search_path for tenant routing and must instead include the schema explicitly in every query or use a different tenant isolation approach.

LISTEN/NOTIFY does not work. PostgreSQL's LISTEN command registers a channel listener on a specific server connection. In transaction mode, PgBouncer may route subsequent commands to a different connection, losing the listener registration. If your application uses PostgreSQL's pub/sub, use a dedicated non-pooled connection or use a separate Redis channel.

Prepared statements require special handling. PostgreSQL prepared statements are connection-scoped. In transaction mode, PgBouncer can route different uses of the same prepared statement name to different PostgreSQL connections. Set prepare_threshold = 0 in PgBouncer to disable server-side prepared statements and use protocol-level prepared statements instead, or use pgx in simple query mode.

Advisory locks are connection-scoped. pg_advisory_lock locks a resource on a specific server connection. In transaction mode, the connection may change between calls. Use advisory locks only within explicit transactions or use application-level locking instead.

Configuring the Go side

With PgBouncer in transaction mode, configure the Go database/sql pool to keep fewer idle connections and shorter lifetimes:

db, err := sql.Open("pgx", pgBouncer DSN)
if err != nil { ... }

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(5 * time.Minute)
db.SetConnMaxIdleTime(60 * time.Second)

SetMaxOpenConns(25) limits each Go process to 25 PgBouncer connections. Across five service instances this is 125 PgBouncer client connections, served by 20 real PostgreSQL connections. The math works because transaction mode is efficient.

SetConnMaxLifetime(5 * time.Minute) ensures Go recycles its PgBouncer connections periodically, which prevents stale connection issues if PgBouncer restarts or rebalances.

Monitoring PgBouncer in production

PgBouncer exposes a virtual database called pgbouncer that you can query with a standard PostgreSQL client:

-- Current pool status
SHOW POOLS;

-- Overall statistics
SHOW STATS;

-- Active clients and servers
SHOW CLIENTS;
SHOW SERVERS;

The metrics to watch continuously:

• cl_waiting: clients waiting for a server connection. Should be near zero. Sustained cl_waiting means pool_size is too small or PostgreSQL queries are too slow.
• sv_idle: idle server connections. If this is always at max, you may be able to reduce pool_size.
• avg_wait_time: average client wait time in microseconds. Values above 5ms indicate pool pressure.

In production SaaS backends, we export these metrics to Prometheus via pgbouncer_exporter and alert when cl_waiting exceeds 5 for more than 60 seconds.

Sizing the PostgreSQL max_connections budget

For a PostgreSQL instance serving a SaaS backend in the MENA region, the final connection budget looks like:

• 80 connections: PgBouncer pool (set in max_db_connections)
• 10 connections: administrative tools (psql, monitoring, migration runners)
• 10 connections: reserve and buffer
• Total: 100 connections on max_connections

This allows the same PostgreSQL instance to serve 500+ concurrent application connections through PgBouncer, which is sufficient for most SaaS products through the first several hundred tenants and meaningful traffic.

Key lessons from production

Transaction mode is the right default for Go SaaS backends. The connection multiplexing ratio is 10x to 25x in typical workloads, which is the difference between needing one database instance and needing several.

Test your application for session-level incompatibilities before enabling transaction mode. Schema-per-tenant with SET search_path is the most common incompatibility, and discovering it in production is expensive.

Export PgBouncer metrics to Prometheus from day one. cl_waiting is the single most important signal for pool health, and you want historical data when investigating incidents.

PgBouncer should run as a sidecar next to your application, not as a separate infrastructure service, during the early startup phase. This reduces the latency between application and pooler and simplifies deployment.