Monitor PostgreSQL Active Sessions with pg_stat_activity

Monitor PostgreSQL Active Sessions with pg_stat_activity

pg_stat_activity is the system view that shows every backend process running against a PostgreSQL server right now — one row per connection, with the client address, the query it is running, how long it has been running, and what kind of backend it is. A single query against it replaces guesswork about what a busy cluster is doing with a live, queryable snapshot, no log file tailing required.

Purpose and Overview

Every connection to PostgreSQL — whether it comes from an application server, a reporting tool, a background maintenance process, or a stray psql session left open on a laptop — shows up as a row in pg_stat_activity for as long as that backend is alive. That makes the view the single place to answer the question every DBA eventually needs answered under pressure: what, exactly, is this database doing right now, and who is asking it to do that.

The view earns its place over reading server logs because it reflects live, in-memory state rather than a historical record. A query that has been running for six minutes shows up with its actual query_start timestamp and current state the moment you run the check — no waiting for a log rotation, no parsing timestamps out of unstructured text. Combined with client_addr and usename, the same row also tells you where the connection came from and under which role it is operating, which turns a performance question ("what's slow") into an ownership question ("who do I need to talk to") in the same result set.

pg_stat_activity is deliberately broad rather than deep — it is a session-level snapshot, not a query-plan tool. For a slow query's execution plan, EXPLAIN is the right instrument; for aggregated statistics across every execution of a normalized query, PostgreSQL's query-statistics extensions are the right instrument. pg_stat_activity sits between those two: it is the first place to look when something is wrong right now, before drilling into either a specific plan or a historical aggregate.

Sample Code

 1SELECT
 2    pid,
 3    usename,
 4    client_addr,
 5    client_hostname,
 6    client_port,
 7    backend_start,
 8    query_start,
 9    state,
10    -- not available on PostgreSQL < 10
11    backend_type
12FROM
13    pg_stat_activity
14ORDER BY
15    -- not available on PostgreSQL < 10
16    backend_type;

Filtered variant, for isolating queries that have been running longer than a chosen threshold:

 1SELECT
 2    pid,
 3    usename,
 4    client_addr,
 5    state,
 6    query_start,
 7    now() - query_start AS running_for,
 8    backend_type
 9FROM
10    pg_stat_activity
11WHERE
12    state = 'active'
13    AND now() - query_start > interval '5 minutes'
14ORDER BY
15    running_for DESC;

Notes: The base query runs on any currently supported PostgreSQL release. The backend_type column is not available before PostgreSQL 10 — on older versions, drop the column from the SELECT list and the ORDER BY clause and the rest of the query is unaffected. The filtered variant's five-minute threshold is a starting point; a transactional web application backend and an overnight batch-reporting connection need very different thresholds for what counts as "too long."

Code Breakdown

The base query is a full read of the view; the filtered variant narrows it with a WHERE clause and adds a computed duration column.

Identifying the Session — pid, usename, client_addr

pid is the backend process ID, and it is the value every follow-up action needs — it is the argument to pg_terminate_backend(pid) when a session must be forcibly ended, and the value to grep for in server logs when a specific connection's activity needs to be traced after the fact. usename identifies the PostgreSQL role the session is authenticated as, which matters because a single application can run under several roles (a read-only reporting role versus a read-write application role, for instance), and client_addr shows where the connection physically originated. Together these three columns turn an anonymous process ID into an accountable session: who, running as what role, from where.

Timing Columns — backend_start and query_start

backend_start records when the session itself was established — the moment the connection was accepted, not the moment any particular query began. query_start records when the currently executing statement began. The gap between the two matters: a session with a backend_start from six hours ago and a query_start from thirty seconds ago is a long-lived, presumably pooled connection currently doing a quick piece of work — normal. A session with both timestamps six hours in the past and state showing active is a genuinely stuck query, and a very different problem.

The state Column and Session Lifecycle

state is the column that turns a list of connections into a list of what those connections are actually doing. active means a query is currently executing. idle means the backend is connected but has finished its last statement and is waiting for the next one — normal for a pooled connection between requests. idle in transaction means a transaction was opened with BEGIN and never committed or rolled back, and the backend is now sitting there holding whatever locks and resources that open transaction implies. waiting describes a backend blocked behind a resource — most often a lock — held by another session. Each of these four values calls for a different response, covered in more depth below.

backend_type and Process Categorization

backend_type (PostgreSQL 10 and later) separates ordinary client connections from the server's own internal workers. A row with backend_type of client backend is an actual application or user connection; a row showing autovacuum worker, logical replication worker, walsender, or similar is a PostgreSQL-internal process doing maintenance or replication work. Without this column, distinguishing "a client is running a slow query" from "autovacuum is doing its job on a large table" requires cross-referencing other statistics views; with it, the distinction is visible in the same result set as everything else.

Key Session State Concepts

Active vs Idle

An active session is doing work this instant — the query column (when queried directly rather than through this narrower column list) shows exactly what statement is executing, and query_start shows how long it has been running. An idle session, by contrast, is connected but has nothing outstanding. Neither state is inherently a problem; a healthy connection pool spends most of its time with sessions sitting idle, ready to pick up the next request without paying the cost of a fresh connection.

Idle in Transaction — the Risk State

idle in transaction is the state that deserves the closest attention. A transaction that has been opened but not closed holds whatever row and table locks it acquired for as long as it stays open, and it also prevents PostgreSQL's vacuum process from cleaning up dead tuples that the open transaction might still theoretically need to see. An application bug that opens a transaction and then waits on a slow external call before committing — a payment gateway, an email send, a synchronous webhook — can leave a session in this state for minutes or hours, quietly holding locks and blocking cleanup the whole time. Any recurring pattern of long idle in transaction durations is worth tracing back to the specific code path that opens the transaction.

Waiting Sessions and Lock Contention

A waiting session is blocked behind another session holding a conflicting lock. Seeing one waiting row in isolation is normal — brief lock waits happen constantly in any concurrent system. Seeing a growing cluster of waiting sessions, especially all waiting behind the same pid, is the signature of a session that took a lock and then stalled, and it identifies exactly which backend to investigate (or terminate) to unblock everything queued behind it.

Backend Types Beyond Client Connections

Because backend_type surfaces PostgreSQL's own internal workers in the same view as client connections, it is possible to confirm autovacuum is actually running against a specific table, or that a logical replication worker is connected and active, without switching to a different diagnostic view. This matters most when a database "feels slow" during a maintenance window — the query results make it immediately clear whether the load is coming from application traffic or from the server's own housekeeping.

Practical Applications

Long-Running Query Detection

The filtered query above, run on a schedule or ad hoc during a performance incident, immediately surfaces every session that has been active past a chosen threshold. This is usually the fastest first step when an application reports slowness — confirm whether a specific query is the bottleneck before reaching for EXPLAIN on a guess.

Idle-in-Transaction Cleanup

Filtering to state = 'idle in transaction' and sorting by query_start (or, more precisely, the time the transaction began) finds every open transaction that has stopped doing anything. Cross-referencing the pid and usename against application logs usually traces the behavior to a specific code path, which is the real fix — terminating the individual session only clears the symptom for that one instance.

Connection Pool Diagnosis

A large number of idle rows all sharing the same usename and client_addr is expected behind a connection pooler like PgBouncer — it means the pool is holding connections open and ready rather than paying reconnection overhead per request. A large number of rows in idle in transaction, on the other hand, usually means the application layer sitting in front of the pool is not committing transactions promptly, which is a pooling configuration question worth separating from a genuine query-performance question.

Autovacuum and Background Worker Visibility

Filtering to backend_type = 'autovacuum worker' confirms whether autovacuum is actually active against a specific table during a maintenance window, which is a faster check than cross-referencing timestamp columns in the vacuum-history statistics views. This is particularly useful right before a bulk load or schema change, to confirm no conflicting autovacuum run is already in progress against the target table.

Security Review of Client Connections

Periodically reviewing client_addr and usename together — outside of expected application server IP ranges — surfaces connections from unexpected sources. Combined with dashboards built in Grafana or ad hoc review in pgAdmin, this turns a one-off query into a repeatable check against a known-good baseline of where connections should be coming from.

Version Compatibility

backend_type is the version-sensitive column in this query — it is available starting with PostgreSQL 10 and is not present on earlier releases. A query written against PostgreSQL 9.6 or older needs to drop it from both the SELECT list and the ORDER BY clause; attempting to select a nonexistent column returns a straightforward "column does not exist" error rather than a silent failure, so the incompatibility surfaces immediately rather than producing misleading output.

The remaining columns in the base query — pid, usename, client_addr, client_hostname, client_port, backend_start, query_start, and state — are part of the core pg_stat_activity view and behave consistently across the currently supported PostgreSQL release range. Because backend_type is the one column that distinguishes client connections from internal workers, its absence on pre-10 servers means older clusters require a separate, manual method (typically pattern-matching on usename or application_name) to separate application traffic from background processes in the result set.

Best Practices

• Terminate through the server, not the OS — use pg_terminate_backend(pid) rather than killing the OS process directly; this ends the session cleanly instead of risking an unclean shutdown of the whole postmaster.
• Investigate before terminating an idle-in-transaction session — the open transaction is a symptom of an application code path, and killing the session without tracing the cause only guarantees the same pattern recurs.
• Build a recurring dashboard, not a one-off check — feeding this query into Grafana or a similar dashboard on a schedule turns a manual troubleshooting step into an early-warning signal for creeping connection or query problems.
• Review connection pooling if idle counts run high — a large steady population of idle rows behind PgBouncer is expected; a large population of raw, unpooled idle connections usually signals an application not closing connections properly.
• Correlate query text with query-level statistics tools — pg_stat_activity shows what is running right now; pairing it with query-aggregation tooling explains whether a slow query seen here is a one-off or a recurring pattern across the whole workload.
• Filter on backend_type before assuming client traffic is the problem — confirm a slow period is not simply autovacuum or a replication worker doing expected background work before chasing an application-side cause.

References

• PostgreSQL Documentation — pg_stat_activity — full column reference for the view, including state values and backend_type categories.
• PostgreSQL Documentation — Connection and Authentication Settings — runtime parameters governing connection limits and timeouts relevant to session monitoring.
• PostgreSQL Documentation — Server Signaling Functions — reference for pg_terminate_backend() and related session-control functions.
• Crunchy Data — PostgreSQL Engineering Blog — ongoing coverage of PostgreSQL monitoring and operational practices.