pg_stat_activity, Sessions, Transactions, Blocking, and Long-Running Work

Learning outcomes

ServiceHub's API suddenly reports timeouts. One dashboard says “12 long queries,” but that label does not tell us whether those sessions are computing, waiting for a lock, waiting for the client, or sitting idle inside old transactions while holding locks and Multiversion Concurrency Control (MVCC) snapshots. PostgreSQL observability starts by distinguishing current session state from cumulative historical counters.

1. Build one disposable table and name every lab session

DROP TABLE IF EXISTS app.ch21_work_order_lock;SET ROLE servicehub_owner;CREATE TABLE app.ch21_work_order_lock (  work_order_id bigint PRIMARY KEY,  status text NOT NULL,  version integer NOT NULL DEFAULT 1,  note text);INSERT INTO app.ch21_work_order_lock VALUES(21001,'assigned',1,'chapter 21 lock target'),(21002,'queued',1,'independent row');RESET ROLE;

Use three psql terminals: blocker, waiter, and observer. Set a different application_name in each session. Application names make the same sessions recognizable in pg_stat_activity, CSV/JSON logs, and many external monitoring tools.

2. Session A holds a row-changing transaction open

SET application_name = 'ch21_blocker';SELECT pg_backend_pid() AS blocker_pid;BEGIN;UPDATE app.ch21_work_order_lockSET status = 'in_progress',    version = version + 1WHERE work_order_id = 21001;-- Do not COMMIT yet.

After the UPDATE finishes, psql waits for the next client command. The backend is no longer executing SQL, but the transaction remains open and still owns locks plus its transaction state. In pg_stat_activity it should become idle in transaction, not ordinary idle.

3. Session B waits on the same row

SET application_name = 'ch21_waiter';SELECT pg_backend_pid() AS waiter_pid;UPDATE app.ch21_work_order_lockSET note = 'waiting update'WHERE work_order_id = 21001;-- This statement waits until session A ends its transaction.

Session B is active because a statement is executing, but it is not necessarily consuming CPU. Its wait-event fields should indicate a lock-related wait while the conflicting transaction remains open.

4. Observe state, age, query identity, and wait separately

SET application_name = 'ch21_observer';SELECT pid,       usename,       application_name,       state,       backend_start,       xact_start,       query_start,       state_change,       now() - xact_start AS transaction_age,       now() - query_start AS query_age,       wait_event_type,       wait_event,       backend_xid,       backend_xmin,       query_id,       left(query, 120) AS query_excerptFROM pg_stat_activityWHERE application_name LIKE 'ch21_%'ORDER BY pid;

xact_start is the right timestamp for an open-transaction age; query_start can instead describe the last statement start even after the session is idle. state_change tells when the current state began. A monitoring rule that uses only “query age” can incorrectly label an idle session as a currently running query.

Cross-user activity visibility is privilege-sensitive. Ordinary roles can inspect their own sessions, while an observability role typically needs pg_read_all_stats (or superuser-equivalent administration) to see complete information about other users' sessions and query text.

5. Ask PostgreSQL who blocks whom

SELECT w.pid AS waiting_pid,       w.application_name AS waiting_app,       w.wait_event_type,       w.wait_event,       b.blocking_pid,       ba.application_name AS blocking_app,       ba.state AS blocker_state,       now() - ba.xact_start AS blocker_xact_ageFROM pg_stat_activity AS wCROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS b(blocking_pid)LEFT JOIN pg_stat_activity AS ba  ON ba.pid = b.blocking_pidWHERE w.application_name = 'ch21_waiter';

pg_blocking_pids() understands PostgreSQL lock compatibility and wait-queue ordering, including soft blockers ahead in a wait queue. It is safer than writing a casual self-join on pg_locks and guessing lock conflicts.

6. Use pg_locks to explain the lock objects after identifying the blocker

WITH lab AS (  SELECT pid  FROM pg_stat_activity  WHERE application_name IN ('ch21_blocker','ch21_waiter'))SELECT l.pid,       a.application_name,       l.locktype,       l.mode,       l.granted,       l.relation::regclass AS relation,       l.page,       l.tuple,       l.transactionid,       l.virtualxidFROM pg_locks AS lJOIN lab USING (pid)LEFT JOIN pg_stat_activity AS a USING (pid)ORDER BY l.pid, l.granted, l.locktype, l.mode;

Row-change waits often surface through transaction-ID locking rather than as a simple “tuple row” in pg_locks. PostgreSQL stores tuple lock information partly in row headers; the lock manager view is not a complete row-lock inventory. Use pg_locks to understand the current lock-manager objects, not as a promise that every locked row appears as one tuple row.

7. Idle in transaction is a risk even without visible blocking

An old idle-in-transaction session can hold locks, keep an MVCC snapshot alive, delay vacuum cleanup, preserve catalog versions, and consume connection capacity. It is therefore a different operational state from a session that is simply idle outside a transaction.

SELECT pid,       usename,       application_name,       state,       xact_start,       now() - xact_start AS xact_age,       backend_xmin,       left(query,120) AS last_queryFROM pg_stat_activityWHERE state IN ('idle in transaction','idle in transaction (aborted)')ORDER BY xact_start NULLS LAST;

Do not automatically terminate the oldest row. First identify ownership, business operation, locks, replication/migration tasks, and whether the session is performing a legitimate long transaction.

8. Cancel stops a current query; terminate ends the session

SELECT pg_cancel_backend(pid)FROM pg_stat_activityWHERE application_name = 'ch21_waiter';

pg_cancel_backend interrupts the current statement, leaving the session connected. This is appropriate when the statement itself is the unwanted work. It does not end an idle transaction because there is no current statement to cancel.

SELECT pg_terminate_backend(pid, 5000)FROM pg_stat_activityWHERE application_name = 'ch21_blocker';

pg_terminate_backend disconnects the session; its open transaction is rolled back and locks are released. Signaling another backend is privileged: superusers, suitable role members, or roles with pg_signal_backend may signal eligible non-superuser backends; only superusers can signal superuser backends. The optional timeout waits for termination confirmation.

9. Correlate with logs by application, PID, and query identifier

log_line_prefix = '%m [%p] %q%u@%d/%a Q=%Q '

%p is backend PID, %a is application name, and %Q can carry the current query identifier when query IDs are computed. This lets an incident timeline connect activity views, lock waits, pg_stat_statements, and server logs without depending on raw SQL text alone.

10. Resolve and verify

SELECT pid, application_name, state,       wait_event_type, wait_eventFROM pg_stat_activityWHERE application_name LIKE 'ch21_%'ORDER BY application_name;SELECT *FROM app.ch21_work_order_lockWHERE work_order_id = 21001;

After Session A commits, rolls back, or is terminated, Session B either proceeds or has already been canceled. Verify the final business row before declaring the incident resolved. Session-level remediation is not equivalent to application-state correctness.

Authoritative references

Statistics and logging fields evolve across PostgreSQL majors. These PostgreSQL 18 primary sources define the mechanisms used in this lesson.

• PostgreSQL 18 — Monitoring Database Activity
• PostgreSQL 18 — pg_locks
• PostgreSQL 18 — System Information / pg_blocking_pids
• PostgreSQL 18 — Server Signaling Functions
• PostgreSQL 18 — Predefined Roles