PostgreSQL pg_stat_activity shows information about server processes, including session state, query timing, and wait events. It is a useful first view when a database seems busy or an application reports slow requests. The data needs interpretation: an active backend can be waiting, and an old query timestamp can describe the last query of an idle session.
Start by asking a narrow operational question. Are requests waiting for locks, are transactions left open, or is the connection pool holding many idle sessions? The same view can support all three investigations, but the evidence and remedies differ.
Begin with a low-disclosure query
Query text can contain sensitive values, identifiers, or business information. A first inspection often does not need it. Collect state and timing fields before copying complete statements into an incident channel.
SELECT pid,
datname,
application_name,
state,
xact_start,
query_start,
wait_event_type,
wait_event
FROM pg_stat_activity
WHERE datname = current_database()
AND pid <> pg_backend_pid();This read-only example focuses on the current database and excludes the observing backend. The fields still reveal operational context, so use an appropriately authorized monitoring role and protect exported results.
Check the deployed PostgreSQL version when building a permanent dashboard. Available columns and detailed wait-event names can vary. Do not silently substitute guessed field names if the query fails.
PostgreSQL pg_stat_activity state is not CPU usage
The active state means the backend is executing a query. It does not mean it is continuously consuming CPU. A non-null wait event can show that the active query is waiting somewhere in the system.
idle means the backend is waiting for a new client command. Many idle sessions can be normal for a pool, but they still consume connection capacity. Do not cancel them merely because they appear in a large list.
idle in transaction means a transaction remains open without a query currently executing. This deserves attention because an open transaction can retain locks or affect cleanup. The aborted variant indicates an error occurred and the transaction still awaits resolution.
State and wait fields are independent and not perfectly synchronized at every instant. Treat a fleeting discrepancy as a reason to collect another observation, not automatically as evidence of a broken server.
Read each timestamp according to its meaning
xact_start marks the beginning of the current transaction, or is null when no transaction is active. query_start marks the current active query’s start, but for a non-active session it describes the last query’s start.
Therefore, subtracting query_start from the current time for every row does not yield a table of currently running query durations. Filter active sessions when that is the measurement you intend. Use transaction age when investigating transactions left open.
Separate the timing of a client request from database backend timing. An application request can wait in a connection pool before PostgreSQL sees it, or perform several queries. A database-only observation cannot explain the entire end-to-end latency without application evidence.
Use consistent time handling in reports. Preserve timestamp zones and the observation time so operators do not confuse local clock display with an actual difference in transaction age.
Wait events explain a category, not the whole cause
A lock wait can direct the investigation toward blocking sessions. Other waits can involve storage, write-ahead logging, client communication, or internal synchronization. Do not group every non-null wait under the word “deadlock.”
A backend waiting for client input may simply be idle between commands. A backend waiting for the client to consume output can point toward slow client processing or network behavior. Interpret the wait in combination with state, workload, and repeated observations.
For lock investigations, use supported blocker evidence such as pg_blocking_pids() and relevant lock details rather than guessing from whichever session has the oldest timestamp. The longest-running session is not necessarily the blocking owner.
Collect a short series of bounded observations when a single sample is ambiguous. Keep the polling rate reasonable; a diagnostic dashboard should not become a new source of database pressure.
Check freshness of the monitoring transaction
PostgreSQL can cache statistics and activity information within the observing transaction. Repeating the same query in a long-running transaction may show the same snapshot rather than fresh session state.
For interactive monitoring, use appropriately short transactions or explicitly clear the statistics snapshot when the investigation requires a fresh view. Understand the applicable statistics-fetch settings instead of assuming every repeated SELECT sees new data.
Caching is useful when correlating several observations against a consistent snapshot. The problem is not caching itself, but failing to recognize whether the current task requires consistency or freshness.
Record your observation method in automated tooling. A graph that appears flat because the monitoring connection kept one transaction open should not be presented as proof that application activity stopped.
Respect visibility limits and query-text limits
Monitoring permissions affect which details are visible for other sessions. A null or restricted field is not necessarily absent underlying activity. Use the least privilege that answers the investigation rather than granting superuser access to every dashboard.
The query field represents the current query for active sessions and the last query otherwise. It can also be truncated according to server configuration. Missing text at the end of a displayed query is not proof that the client sent an incomplete statement.
Do not increase query-text collection broadly without reviewing data exposure and operational cost. Parameterization helps avoid some literal-value disclosure, but query text and object names can still be sensitive.
Diagnose before canceling or terminating
Reading activity is a separate action from canceling a statement or terminating a backend. Establish the owner, business impact, transaction state, and recovery expectations before an authorized intervention.
PIDs identify current processes, not permanent application identities. A process can exit between inspection and action. Recheck the relevant context before intervening, particularly when an incident procedure spans several minutes.
Prefer fixing the cause: a leaked transaction, unsuitable pool size, blocking workflow, or slow consumer. Repeatedly terminating sessions can hide the symptom while causing retries, rollbacks, or user-visible failures.
Frequently asked questions
Does an old query_start mean a query is still running?
Only when interpreted with the active state. For idle sessions, it refers to the last query’s start.
Is every waiting session a problem?
No. Waiting can be normal. Identify the wait category, session state, and user impact before deciding it is abnormal.
Can this view identify the most expensive query overall?
It shows current and recent per-session activity, not a complete historical workload ranking. Use cumulative query statistics and application telemetry for that question.
Practical takeaway
Read state, transaction timing, wait events, and snapshot freshness together. Limit disclosure and preserve permission context. Pg_stat_activity is useful evidence for a specific investigation, not an automatic instruction to kill the busiest-looking backend.
Documentation and related reading
Check the official documentation for the exact behavior of your deployed version. For complementary implementation guidance, read the PostgreSQL pg_locks guide.
Continue with the PostgreSQL Guides for a broader practical reading path.
