PostgreSQL Connection Management

PostgreSQL forks a process per connection. Connections are therefore expensive to create and expensive to keep, and max_connections is a memory setting as much as a concurrency one.

Sizing max_connections

A larger value does not make the server faster. Beyond the point where active queries exceed available cores and I/O capacity, additional concurrency increases contention and slows everything down.

Size it as the total your pooler needs, plus replication connections, plus monitoring and backup sessions, plus a reserved margin:

max_connections = 200
superuser_reserved_connections = 5

If the application genuinely needs thousands of client connections, that is a job for PgBouncer in transaction mode, not for a larger max_connections.

pg_hba.conf

Access is evaluated top to bottom, and the first matching line wins — a common source of surprises when a permissive rule sits above a restrictive one.

# TYPE   DATABASE      USER            ADDRESS           METHOD
local    all           postgres                          peer
hostssl  shop          app_service     10.20.0.0/24      scram-sha-256
hostssl  replication   replicator      10.20.1.10/32     scram-sha-256
hostssl  replication   replicator      10.20.1.11/32     scram-sha-256
# No catch-all. Anything not listed above is rejected.

Reload after changes and confirm the rules loaded:

SELECT pg_reload_conf();
SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules WHERE error IS NULL;

Diagnosing sessions

-- What is actually happening right now, busiest first.
SELECT pid, usename, application_name, state,
       wait_event_type, wait_event,
       now() - xact_start AS xact_age,
       now() - query_start AS query_age,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND state <> 'idle'
ORDER BY xact_age DESC NULLS LAST;

Read state carefully — the three problem states mean different things:

• active — running a query. If many are active and slow, look at wait events.
• idle in transaction — the session holds an open transaction and is doing nothing. It retains locks and blocks vacuum from cleaning rows newer than its snapshot. This is the state that causes table bloat.
• idle — connected, no transaction. Harmless apart from its memory.

Set the timeouts that make these self-correcting:

ALTER ROLE app_service SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE app_service SET statement_timeout = '2s';

Terminating a session