Managing Postgres Connections

---
name: managing-postgres-connections
description: Guides Postgres connection pooling when applications add instances, connection churn or storms appear, max_connections is exhausted, PgBouncer is introduced, or pgxpool and server pool limits must be sized safely.
---

# Managing Postgres Connections

## Overview

A Postgres connection is a server process with memory and scheduling cost. Bound concurrency across all application instances and preserve an operational reserve. Raising `max_connections` does not create capacity.

## Choose the pooling layer

Prefer PgBouncer when many processes share a database: it enforces one server budget. Use an in-process pool such as Go's `pgxpool` when necessary, but multiply its maximum by every replica and process.

Use this mode decision for PgBouncer:

| Mode | Use | Required warning |
|---|---|---|
| **Transaction (default)** | Stateless web/request workloads | A later transaction may use another backend; session state is not preserved |
| Session | Compatible fallback for session-dependent code | Idle clients retain server connections, so multiplexing is less efficient |
| Statement | Rare, independent autocommit statements only | Cannot run multi-statement transactions; unsuitable for most apps |

**Transaction pooling breaks assumptions about session-level prepared statements, session-level advisory locks, `LISTEN`/`NOTIFY`, session variables, cursors held across transactions, and temporary tables.** Use transaction-level locks, `SET LOCAL`, and transaction-scoped cursors where possible; otherwise use a dedicated session-pooled or direct connection.

For pgx, never assume its implicit prepared-statement cache is compatible with transaction pooling. Either configure and test PgBouncer protocol-level prepared-statement tracking, or disable implicit preparation with a compatible mode such as `default_query_exec_mode=exec`. Read [reference/pgbouncer-pooling-modes.md](reference/pgbouncer-pooling-modes.md) for the detailed compatibility choices.

## Size from the database inward

**Always bound the total pool; never maximize it to eliminate waiting.** Budget **server** connections from CPU, memory, storage, and measured concurrency, reserve administration capacity, then divide the remainder among workloads. For ordinary OLTP, start testing near twice the database vCPU count and increase only when measurements show useful I/O wait. Load testing decides the final number.

Calculate the autoscaling ceiling explicitly: `replicas × processes per replica × pool max`. For example, 40 pods with a maximum of 20 can demand 800 connections. **Do not answer that storm by simply raising `max_connections`: more backends add memory and scheduling pressure; they do not add database capacity.** Put PgBouncer in front of replicas when they need to share one bounded server budget.

```text
total application server pools
  + migration/worker pools
  + monitoring
  + operational reserve
  <= usable Postgres connection budget
```

Pool wait time is backpressure. Brief waiting is safer than saturating CPU, memory, storage, and lock managers. Cap acquisition time so overload fails predictably.

## Configure both layers deliberately

- Keep application connections long-lived and return them promptly after each unit of work.
- Set in-process maxima so their aggregate cannot bypass the server budget.
- Give long-running jobs a separate small pool so they cannot starve latency-sensitive traffic.
- Set idle and lifetime recycling with jitter; synchronized expiry can create a reconnection storm.
- Monitor active, idle, and waiting clients at the application, pooler, and Postgres layers.
- Keep transactions short. Pooling cannot compensate for sessions left `idle in transaction`.

For pgx behind transaction pooling, do not rely on its session-level statement cache unless PgBouncer tracks protocol-level prepared statements. A conservative setting is `default_query_exec_mode=exec`; test both layers together. Session advisory locks, `LISTEN`, session variables, and cross-transaction state require session pooling or redesign.

## Diagnose saturation

```sql
SELECT state, wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state, wait_event_type, wait_event
ORDER BY count(*) DESC;
```

Also inspect checkout latency and PgBouncer waiters. A full pool with low database utilization suggests leaks, long transactions, or external work inside a transaction.

## Common Mistakes

- Opening a connection per request or retrying connection failures without backoff and jitter.
- Multiplying a “safe” per-process maximum across autoscaled replicas.
- Increasing pool size because callers wait, without checking database saturation.
- Holding a transaction open during HTTP/RPC work.
- Enabling transaction pooling while depending on session state.