PostgreSQL Server-Side Connection Diagnostics

This guide is part of Connection Pool Observability. Client-side pool metrics answer how many connections a service holds and how long callers wait for one. They cannot answer what those connections are doing, and that is the question that separates a pool that is too small from one whose connections are held by application code that is not querying. Only the database can answer it.

This guide covers the server-side views that make that determination: pg_stat_activity and its state column, the wait-event taxonomy that explains why an active backend is not progressing, and the connection-limit parameters that bound everything. It is written to be usable during an incident, with the distinguishing queries stated explicitly.

Key operational takeaways:

• state in pg_stat_activity separates active, idle, and idle in transaction — and that distinction is the single most valuable connection diagnostic available.
• idle in transaction accumulating is the definitive signature of connections held by application code rather than by database work.
• wait_event_type explains why an active backend is not making progress: a lock, an I/O wait, or a client the server is waiting on.
• application_name is what makes any of this attributable to a service, and it costs one connection-string parameter to set.
• max_connections cannot be raised without regard to memory: every backend is a process with its own allocation.

Foundational Mechanics

pg_stat_activity has one row per backend process, and the columns that matter for connection diagnosis are state, wait_event_type, wait_event, state_change, xact_start, query_start, and application_name.

state is the primary discriminator, and its three interesting values were described above. The one detail worth adding is that state_change gives the timestamp of the last transition, so now() - state_change on an idle in transaction row is how long that transaction has been open with nothing running — the single most useful derived value in the view.

wait_event_type explains a backend that is active but not progressing. Its values group into a small number of meaningful categories: Lock means blocked on a database lock, LWLock on an internal shared-memory lock, IO on storage, Client on the client itself, and IPC on another backend. A backend active with wait_event_type = 'Client' and wait_event = 'ClientRead' is the server waiting for the application to send something — which is what an idle connection in extended-query protocol frequently looks like.

xact_start versus query_start distinguishes a long transaction from a long query. A transaction open for four minutes whose current query started two seconds ago is an application holding a transaction across other work; a query running for four minutes is a slow query. They call for entirely different responses, and the two timestamps are what separates them.

-- The single most useful connection-diagnosis query.
SELECT application_name,
       state,
       count(*)                                            AS backends,
       max(now() - state_change)                           AS longest_in_state,
       max(now() - xact_start) FILTER (WHERE xact_start IS NOT NULL) AS longest_txn
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY application_name, state
ORDER BY backends DESC;

Running that during an incident answers, in one result set, which service holds the connections, what those connections are doing, and whether anything has been holding a transaction open for an unreasonable time. The backend_type filter excludes autovacuum workers and background writers, which otherwise clutter the result.

Operational Boundary: Connection-state diagnosis is covered here. Query plan analysis, index selection and vacuum tuning are database-performance concerns that this view points toward rather than resolves.

Precision Sizing & The Server-Side Limits

Three parameters bound how many connections PostgreSQL will accept, and their interaction is more subtle than the first one suggests.

max_connections is the total. It is a hard limit, it requires a restart to change, and it cannot be raised freely because every backend is a separate operating-system process with its own memory allocation — work memory, temporary buffers, catalogue caches. A rough figure is 5–10 MB per idle backend and considerably more under load, so raising max_connections from 200 to 2,000 on an unchanged instance trades connection rejections for swapping.

superuser_reserved_connections carves out slots that only superusers may use, defaulting to 3. Its purpose is to guarantee that an administrator can still connect when the database is full — which is exactly the moment it matters. The effective limit for ordinary users is max_connections - superuser_reserved_connections, and the FATAL: remaining connection slots are reserved message means that boundary, not the total, has been reached.

reserved_connections (PostgreSQL 16 and later) adds an intermediate tier for roles granted pg_use_reserved_connections, which lets monitoring agents and health checks keep working when application connections are exhausted. Assigning that role to the monitoring user is a cheap way to keep observability alive during a connection incident.

The last two are worth enabling deliberately. idle_in_transaction_session_timeout set to something like 60 seconds converts a leaked transaction from an indefinite lock holder into a prompt application error — which is worse for that one request and much better for everything else on the database. idle_session_timeout does the same for connections without an open transaction, and is particularly valuable when serverless environments are reclaimed without closing their connections.

Diagnostics & Telemetry

Three queries cover the great majority of connection investigations, and they are worth having saved rather than reconstructed under pressure.

Who holds the connections, and what are they doing — the grouped query above. Run it first, always.

What is blocking what — when backends are active with wait_event_type = 'Lock', the blocking chain is what matters:

SELECT blocked.pid          AS blocked_pid,
       blocked.application_name AS blocked_app,
       blocking.pid         AS blocking_pid,
       blocking.application_name AS blocking_app,
       blocking.state       AS blocking_state,
       now() - blocking.xact_start AS blocking_txn_age,
       left(blocked.query, 60) AS blocked_query
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bp(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = bp.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

The revealing column is blocking_state. A blocking backend in idle in transaction is holding a lock while doing nothing — the classic case where a connection-holding bug becomes a database-wide problem, and where killing the blocker is often the right immediate action.

What is idle in transaction, and for how long — the leak hunt:

SELECT pid, application_name, now() - xact_start AS txn_age,
       now() - state_change AS idle_for, left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '30 seconds'
ORDER BY state_change;

The query column here shows the last statement executed, not a running one, which is exactly what identifies the code path that opened the transaction and then went elsewhere. It is the server-side equivalent of the stack trace that leak detection provides on the client.

Continuous collection matters as much as ad-hoc querying. Exporting backend counts grouped by application_name and state on a short interval turns all of the above into a graph that can be consulted after the fact — and the state breakdown over time is what distinguishes a leak from load, exactly as it does client-side.

Making It Continuous

Ad-hoc queries answer questions during an incident. Continuous collection answers them afterwards, and more importantly reveals the slow trends — a leak accumulating over days, a pool that never shrinks, a service whose share of the budget has crept upward since the last release.

Two exporters cover it. postgres_exporter exposes pg_stat_activity aggregated by state and database out of the box, and its pg_stat_activity_count series with a state label is the single most useful thing it publishes for connection work. Adding a custom query gives the per-service breakdown that the built-in metric omits:

# postgres_exporter custom queries — per-service state breakdown
pg_connections_by_app:
  query: |
    SELECT coalesce(nullif(application_name, ''), 'unknown') AS application,
           state,
           count(*) AS backends,
           coalesce(extract(epoch FROM max(now() - state_change)), 0) AS max_state_seconds
    FROM pg_stat_activity
    WHERE backend_type = 'client backend'
    GROUP BY 1, 2
  metrics:
    - application: { usage: "LABEL" }
    - state:       { usage: "LABEL" }
    - backends:    { usage: "GAUGE", description: "Backends by application and state" }
    - max_state_seconds: { usage: "GAUGE", description: "Longest time in current state" }

Three alerts follow directly from those two series and are worth having on any shared database.

Total backends above 80% of the effective limit, sustained for five minutes. This is the capacity alert, and it should route to whoever owns the budget rather than to whichever service happens to be largest.

Any backend idle in transaction for longer than 60 seconds. This is the leak and lock-holding alert, and it should name the application_name so it routes to the owning team. It fires long before that transaction causes a visible problem, which is the point.

Backends for a single application_name above its allocated share. This catches a service that has silently grown past its allowance — through a replica-count change, a new worker tier, or an accidental second pool — before the growth exhausts the database.

The max_state_seconds column makes the second alert possible without a separate query, which is why it is worth including even though it looks redundant next to the count. A count of ten idle in transaction backends is unremarkable if each has been in that state for 50 ms and alarming if one has been there for ten minutes.

Operational Boundary: Collection and alerting on connection state are covered here. Alert routing, escalation policy and ownership mapping are operational-governance decisions.

Integration & Proxy Compatibility

A connection proxy changes what these views show, in ways worth anticipating.

Behind PgBouncer in transaction mode, pg_stat_activity shows the proxy’s server connections rather than the application’s client sessions. application_name reflects whatever the proxy passes through — which, unless application_name_add_host is enabled, is often the same value for every backend, collapsing the attribution these queries depend on. The backends also churn between clients, so a PID observed in one query may be serving a different service by the next.

The practical consequence is that server-side diagnosis behind a proxy must be paired with the proxy’s own statistics. pg_stat_activity answers what the database is doing; SHOW POOLS answers who is waiting for it. Neither alone is sufficient, which is covered in PgBouncer Metrics Monitoring.

On RDS Proxy the same applies with less visibility, since the proxy’s internals are not directly inspectable — CloudWatch metrics stand in for SHOW POOLS. The idle in transaction diagnosis remains valid and important: a pinned or long-held transaction behind RDS Proxy is exactly what drives the pinned-connection metric up.

Common Failure Patterns & Remediation

Frequently Asked Questions

Related

• Connection Pool Observability — the parent reference on metric taxonomy and pipelines.
• Interpreting pg_stat_activity Wait Events for Pool Issues — the wait-event taxonomy in operational detail.
• Tuning PostgreSQL max_connections and superuser_reserved_connections — sizing the server-side limits against memory.
• Detecting Connection Pool Saturation — the client-side half of the same diagnosis.
• PgBouncer Metrics Monitoring — what to read when a proxy sits between the pool and these views.