Managing Postgres Connections

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:

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 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.

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

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.