Stop Allowing PostgreSQL Connections to Balloon

The "Database Connection Exhaustion" Incident

Last year, I got paged at 3:00 AM because our primary Postgres instance in AWS RDS was unresponsive. The CPU was pinned at 100%, but the actual query volume wasn't high. When I checked pg_stat_activity, I saw over 1,500 active connections. Most of them were idle, but they were holding open file descriptors and consuming memory in the postmaster process.

The problem wasn't our database capacity; it was our connection management. We had ten microservices, each with a connection pool set to a max of 50. During a horizontal autoscaling event, those pods multiplied, and the aggregate number of connections quickly exceeded max_connections on the database.

Postgres is not built to handle thousands of concurrent connections. Every connection consumes a non-trivial amount of RAM for the backend process, and context switching between those processes becomes a performance tax that eventually kills your throughput.

Why Direct Connections Fail at Scale

In a traditional application architecture, your backend service maintains a persistent connection pool to the database. This works fine when you have a monolithic application with a single connection pool.

But in a distributed system, you have dozens of microservices. If each service creates its own pool, the database sees a multiplication of connections:

Number of Pods × Max Pool Size per Pod = Total Database Load

If you have 50 pods of a service and each holds a pool of 20, you are demanding 1,000 connections. When a deployment happens, those pods restart, reconnect, and potentially overwhelm the database handshake process. This is the definition of "thundering herd" behavior against your database.

The Multiplexing Solution: PgBouncer

PgBouncer is a lightweight connection pooler that sits between your application and your database. Instead of each application pod holding a permanent connection to Postgres, they connect to PgBouncer. PgBouncer maintains a much smaller, highly optimized set of connections to the database and "multiplexes" incoming application requests onto them.

When your application finishes a transaction, the connection is returned to the pool. Postgres sees the connection stay open, but the application is free to reuse it for the next request. This decouples your application scaling from your database connection limits.

The Trade-offs

You aren't getting this for free. PgBouncer requires you to understand your transaction patterns:

1. Transaction Pooling: Best for most web apps. Connections are returned to the pool after every transaction.
2. Session Pooling: The connection is held until the client disconnects. This is safer but doesn't solve the connection explosion problem as well as transaction pooling.

If your code uses prepared statements or relies on session-level variables (like SET LOCAL), transaction pooling can break things. You have to audit your drivers to ensure they aren't relying on session state that gets wiped between pooled transactions.

What I Actually Do / My Take

I treat connection pooling as mandatory, not optional, for any production workload. Here is my standard playbook:

1. Set max_connections strictly: I configure the Postgres max_connections parameter to be significantly lower than what I think the system might demand. This forces us to use a pooler.
2. Deploy PgBouncer as a Sidecar or Proxy: In EKS, I prefer running PgBouncer as a dedicated deployment in the same namespace as the app, or using an RDS Proxy if the infrastructure budget allows for it.
3. Audit the Driver: I verify that our application code is not doing SET commands inside a transaction scope that expects that state to persist across calls.

If you are using Node.js with pg, your configuration should look like this:

// Connect to PgBouncer, not the RDS instance directly
const pool = new Pool({
  host: 'pgbouncer-service.production.svc.cluster.local',
  port: 6432, 
  database: 'myapp',
  max: 20 // Keep this conservative
});

I never let an application open more than 20 connections. If the application needs more, the problem isn't the connection count—it's the query performance or the lack of an efficient pooler.

Closing / TL;DR

Stop letting your application instances dictate your database load. If your pg_stat_activity shows more than 200 connections, you are already running on borrowed time.

Implement a pooler, cap your application's connection limit to 20, and let PgBouncer handle the multiplexing. It’s the difference between a database that stays up during a traffic spike and one that requires a manual reboot at 3:00 AM.

Tags: postgresql · pgbouncer · connection-pooling · database-reliability · devops · backend-engineering · infrastructure · kubernetes · aws-rds · scalability · performance-tuning