PostgreSQL Connections Are a Capacity Budget

Aug 26

By

postgresqldatabase engineeringcapacity planningreliability

An application pool is not a performance knob. It is a claim on a finite database capacity budget.

If twenty application instances each open fifty connections, the architecture has requested one thousand database sessions—even if PostgreSQL can execute only a fraction of those queries concurrently. Autoscaling then multiplies contention at the exact moment the database is already slow.

Start from the database budget

PostgreSQL’s max_connections is a ceiling, not a target. PostgreSQL allocates resources based on it, and increasing it raises resource requirements. Reserve capacity for operations, replication, migrations, and incident response.

max_connections                     300
- superuser and reserved slots       15
- replication and maintenance        25
- migrations and support             10
= application budget                250

Allocate those 250 connections by workload, not by whichever service starts first:

checkout       70
orders         60
workers        50
reporting      20
other services 30
surge reserve  20

The allocation forces a product decision: which work may queue or degrade when demand exceeds the safe database concurrency?

Size from concurrency, not request rate

Little’s Law gives a useful first estimate:

concurrency ≈ throughput × time in system

At 400 database operations per second and 25 ms average database time, average active concurrency is about 10. Tail latency, transactions with multiple statements, and bursts require headroom, but they do not justify a pool of 200 by default.

Measure active sessions, not merely checked-out client connections:

SELECT state, wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state, wait_event_type, wait_event
ORDER BY count(*) DESC;

Many idle in transaction sessions indicate an application correctness problem. Many active sessions waiting on locks indicate contention. More connections amplify both.

Queue before the database

When all pool slots are busy, callers should wait in a bounded queue with a deadline. This is admission control.

request deadline:       800 ms
pool acquisition limit: 100 ms
query timeout:          500 ms
response budget:        200 ms

Reject when the acquisition queue is full or its deadline expires. A fast explicit failure protects useful work already admitted. Creating an emergency connection outside the pool defeats the budget.

Observe pool wait duration and timeout rate. A saturated pool with low database utilization may be undersized; a saturated pool with high database latency is often correctly preventing collapse.

Understand pooling modes

PgBouncer can multiplex many clients over fewer server connections. Its modes change application semantics:

  • session pooling: one server connection for the client session;
  • transaction pooling: server connection returned after each transaction;
  • statement pooling: returned after each statement, with stronger restrictions.

Transaction pooling improves multiplexing but breaks assumptions tied to a server session: session-level settings, some prepared-statement behavior, temporary tables, advisory locks, and LISTEN state need careful review.

Do not deploy transaction pooling because a benchmark looks good. Inventory session features, test migrations and administrative tools, and document which connection endpoint each workload uses.

Pool hierarchy also matters:

application pool → PgBouncer client slots
                 → PgBouncer server pool
                 → PostgreSQL connections

If every layer queues without bounded deadlines, latency becomes invisible until requests time out at the edge.

Make autoscaling database-aware

Suppose each pod has a pool maximum of 20. Scaling from 10 to 40 pods changes potential demand from 200 to 800 connections. CPU-based autoscaling can therefore attack the database during a latency event.

Use a global budget:

per-pod maximum = floor(service allocation / maximum pods)

Or place PgBouncer in front of PostgreSQL and treat client concurrency separately from server concurrency. Either way, maximum replicas, job parallelism, and pool settings must be reviewed together.

Background work should have a distinct, smaller pool. A backfill that consumes every connection can make the health endpoint fail and trigger more replicas—the classic positive feedback loop.

Protect operational access

PostgreSQL supports reserved connection slots. Use them for emergency administration, but do not consider them a substitute for workload isolation. Test that on-call access still works at saturation.

Set per-role and per-database connection limits where they contain a clear failure domain. Use statement_timeout, lock_timeout, and idle-transaction limits appropriate to the workload. A connection admitted forever is not bounded capacity.

Operate the budget

Track:

  • active, idle, and idle-in-transaction sessions;
  • pool utilization and acquisition wait;
  • connection creation rate;
  • queries and transactions per connection;
  • wait events and lock queues;
  • transaction duration;
  • rejected work by service;
  • remaining operational reserve.

Run load tests with the configured maximum replica count. Test database slowdown, not only high request volume. The dangerous case is when query duration expands and every caller holds capacity longer.

CTO review

  1. What is the application connection budget after reserves?
  2. Who owns allocations across services and jobs?
  3. How were pools sized from observed database concurrency?
  4. Where does excess work queue, and what is its deadline?
  5. Can autoscaling exceed the global budget?
  6. Which session features constrain PgBouncer mode?
  7. Can operators connect during saturation?
  8. Does the load test include slow queries and lock contention?

The goal is not the largest pool. It is the smallest bounded concurrency that meets latency objectives while leaving the database able to recover.

References

>