`pg_stat_activity` and live locks

Capsule overview

pg_stat_statements, auto_explain, and the slow query log are post-mortem tools: they tell you what happened. Useful for analysis and prioritization, useless when something is breaking right now.

When it's 3am and your SRE team messages you "the API is down, p99 in timeout, what's happening in the database?", you need to know what's running right now. Which queries are active, for how long, what they're waiting on, what locks they hold, which transactions have been open for hours doing nothing. That's why pg_stat_activity exists: a real-time view of every active connection against the database.

This capsule teaches you:

• Reading pg_stat_activity and understanding each important column (state, wait_event, query, xact_start, etc.).
• Identifying hung transactions in idle in transaction and why they're so dangerous.
• Detecting locks blocking other queries using pg_locks and pg_blocking_pids().
• Canceling and terminating problematic queries with pg_cancel_backend() and pg_terminate_backend().
• Applying a live incident diagnosis workflow step by step.

By the end, you'll be able to open psql against a database under an incident and, in less than 5 minutes, identify what's blocking what and have a concrete action plan.

Mental model: the live control panel

pg_stat_activity is like the control tower panel at an airport. At each moment it shows:

• Every active flight (every connection to the database).
• Where it comes from (which app, which user).
• What it's doing (which query it's running, or whether it's idle).
• For how long (the start timestamp of the query, of the transaction).
• What it's waiting on (a lock, I/O, another process).

A "healthy" connection is like a flight on a normal route: query running, waiting on nothing, finishing fast. A "problematic" connection is a flight in trouble: query running for 2 hours, waiting on a lock held by another process, blocking three more passengers behind it.

Unlike the log (which records the past), pg_stat_activity is a snapshot of the exact moment you query it. Re-query it seconds later and you'll see a different picture. It's the tool for active incidents par excellence.

The canonical query

The most common way to query pg_stat_activity for diagnosis:

SELECT
    pid,
    usename,
    application_name,
    state,
    wait_event_type,
    wait_event,
    now() - xact_start AS xact_duration,
    now() - query_start AS query_duration,
    left(query, 150) AS query
FROM pg_stat_activity
WHERE state != 'idle'           -- ignore inactive connections in the pool
  AND pid != pg_backend_pid()   -- exclude your own query
ORDER BY query_start ASC;        -- the oldest first

Why this filter:

• state != 'idle': the pool has idle connections by design. You're not interested in them here; you're interested in the ones doing something or waiting on something.
• pid != pg_backend_pid(): your own query to pg_stat_activity appears in pg_stat_activity. Exclude it.
• ORDER BY query_start ASC: the oldest queries are the most suspicious. If one has been going 2 hours, it's the first you want to look at.

Example output

Under normal load:

 pid  | usename   | application_name | state  | wait_event_type | wait_event | xact_duration | query_duration |              query
------+-----------+-------------------+--------+-----------------+------------+---------------+----------------+--------------------------------
 1234 | bookstore | uvicorn           | active |                 |            | 00:00:00.020  | 00:00:00.020   | SELECT * FROM books WHERE id = $1
 1235 | bookstore | uvicorn           | active | Lock            | tuple      | 00:00:00.150  | 00:00:00.150   | UPDATE books SET stock = stock - 1 WHERE id = $1

Under an incident (hung query):

 pid  | usename   | application_name | state                | wait_event_type | wait_event       | xact_duration | query_duration |              query
------+-----------+-------------------+----------------------+-----------------+------------------+---------------+----------------+--------------------------------
 9876 | etl_user  | python_etl        | idle in transaction  |                 |                  | 02:15:34.123  | 02:15:30.000   | SELECT * FROM orders WHERE created_at > $1
 4321 | bookstore | uvicorn           | active               | Lock            | transactionid    | 00:00:45.000  | 00:00:45.000   | UPDATE orders SET status = 'shipped' WHERE id = $1
 4322 | bookstore | uvicorn           | active               | Lock            | transactionid    | 00:00:30.000  | 00:00:30.000   | UPDATE orders SET status = 'shipped' WHERE id = $1

Diagnosis of the second picture:

• pid 9876 (etl_user) has been in idle in transaction for 2 hours and 15 minutes. It has an open transaction, ran a query (SELECT FROM orders), and never closed the transaction. It's holding locks on rows of orders.
• pid 4321 and 4322 are app requests trying to UPDATE on orders. They're blocked (wait_event = transactionid) because the ETL's transaction holds locks.
• Every second that passes, more app requests pile up waiting.

Correct action: terminate the ETL's transaction (later in this capsule).

The important columns

The state values

The two forms of idle in transaction are the ones that most cause incidents. We explain them in detail.

The silent killer: idle in transaction

idle in transaction means: the client opened a transaction (BEGIN), ran some query, and then went quiet without closing the transaction (no COMMIT or ROLLBACK).

Meanwhile, PostgreSQL keeps:

• The locks that transaction took on the rows/tables it touched.
• The MVCC snapshot from the moment the transaction started, which:
  • Blocks autovacuum: it can't clean up old row versions because "someone" is looking at them.
  • Accumulates bloat in the tables that have many writes.

Why does idle in transaction happen?

Case 1: a bug in the app, no COMMIT/ROLLBACK on the error path.

# BAD
async def transfer(session, from_id, to_id, amount):
    async with session.begin():
        from_account = await session.get(Account, from_id)
        from_account.balance -= amount
        # external call to a validation API:
        await external_api.validate(from_id)  # if this fails, it raises an exception
        to_account = await session.get(Account, to_id)
        to_account.balance += amount
    # If the exception comes from external_api.validate, the `async with`
    # should roll back automatically, BUT if the code doesn't use the
    # context manager correctly or overrides it, it can end up idle in tx.

Case 2: an app that does a long transaction because there's a sleep or HTTP request in the middle.

# BAD
async def slow_endpoint(session):
    async with session.begin():
        users = await session.execute(select(User))
        # call an external API that takes 30 seconds:
        external_data = await external_api.fetch_data()  # 30s
        # more queries:
        await session.execute(...)
        # commit

During those 30 seconds, the transaction is open. If the endpoint is called a lot, you can accumulate many idle in transaction connections waiting for a response from the external API.

Case 3: an abandoned psql or interactive client.

You open psql, do BEGIN; SELECT ...;, go to lunch. The transaction stays open until you come back or until the server closes the connection by timeout (rarely configured).

Why is it dangerous?

Imagine:

T+0:    ETL does BEGIN; SELECT * FROM orders WHERE date > '2026-01-01';
        Acquires a ShareLock on orders.
T+1s:   ETL "hangs" (stays idle in tx — bug, sleep, whatever).
T+5s:   The bookstore API tries UPDATE orders SET ... WHERE id = 5.
        UPDATE needs a RowExclusiveLock on orders.
        ShareLock + RowExclusiveLock are not compatible → UPDATE waits.
T+10s:  Another API request tries an UPDATE on another orders row.
        It also waits.
T+30s:  There are already 50 requests waiting.
T+60s:  The app's pool filled up. New requests time out.
T+120s: The load balancer starts marking the app as unhealthy.
T+180s: Complete API outage.

All caused by a single idle in transaction connection that isn't even consuming CPU. That's why it's the silent killer.

Detecting it

SELECT
    pid,
    usename,
    application_name,
    now() - xact_start AS xact_duration,
    now() - state_change AS idle_for,
    state,
    left(query, 200) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND xact_start IS NOT NULL
ORDER BY xact_start ASC;

Example output:

 pid  | usename  | application_name | xact_duration | idle_for      | state                | last_query
------+----------+-------------------+---------------+----------------+----------------------+----------------
 9876 | etl_user | python_etl        | 02:15:34      | 02:15:30      | idle in transaction  | SELECT * FROM orders WHERE created_at > $1

Any idle in transaction with idle_for > 1 minute is highly suspicious. More than 5 minutes: act.

Detecting blocking: pg_locks and pg_blocking_pids()

pg_stat_activity tells you "this query is waiting on a lock". But it doesn't tell you who holds the lock that's blocking it. For that you need two more tools.

pg_blocking_pids() (PG 9.6+)

A function that, given a pid, returns an array of pids that are blocking it.

SELECT
    pid,
    pg_blocking_pids(pid) AS blocked_by,
    state,
    wait_event,
    now() - query_start AS query_duration,
    left(query, 100) AS query
FROM pg_stat_activity
WHERE pg_blocking_pids(pid)::text != '{}'
ORDER BY query_start ASC;

Example output:

  pid | blocked_by | state  | wait_event | query_duration | query
------+------------+--------+------------+----------------+---------------------------
 4321 | {9876}     | active | tuple      | 00:00:45       | UPDATE orders SET status = $1 WHERE id = $2
 4322 | {9876}     | active | tuple      | 00:00:30       | UPDATE orders SET status = $1 WHERE id = $2
 4323 | {4321}     | active | tuple      | 00:00:10       | UPDATE orders SET status = $1 WHERE id = $2

Reading:

• pid 4321 is blocked by 9876.
• pid 4322 is blocked by 9876.
• pid 4323 is blocked by 4321 (not by 9876 directly — the lock is transitive).

Action: attend to the "head of the queue" (pid 9876, generally the idle in transaction we saw earlier). Releasing it releases the rest.

Complete blocking chain

To see the complete chain with info about each blocker:

WITH blocked AS (
    SELECT
        pid AS blocked_pid,
        unnest(pg_blocking_pids(pid)) AS blocking_pid
    FROM pg_stat_activity
    WHERE pg_blocking_pids(pid)::text != '{}'
)
SELECT
    blocked.blocked_pid,
    blocked_act.usename AS blocked_user,
    blocked_act.wait_event,
    now() - blocked_act.query_start AS blocked_for,
    left(blocked_act.query, 80) AS blocked_query,
    '|',
    blocked.blocking_pid,
    blocking_act.usename AS blocking_user,
    blocking_act.state,
    now() - coalesce(blocking_act.xact_start, blocking_act.query_start) AS blocker_duration,
    left(blocking_act.query, 80) AS blocker_query
FROM blocked
JOIN pg_stat_activity blocked_act ON blocked_act.pid = blocked.blocked_pid
JOIN pg_stat_activity blocking_act ON blocking_act.pid = blocked.blocking_pid
ORDER BY blocked.blocked_pid;

This query shows you "who blocks whom" in a single picture. For incidents, this is the query to run.

pg_locks (more detail)

For a deep analysis of what type of lock and on what object:

SELECT
    locktype,
    relation::regclass AS table_name,
    mode,
    granted,
    pid,
    now() - query_start AS query_duration
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE NOT granted  -- queries waiting on a lock
ORDER BY query_start ASC;

granted = false means "this query requested this lock but hasn't been given it yet, it's waiting". Useful for knowing exactly what type of lock is being fought over.

Canceling and terminating connections

Once you've identified the problem, you have two ways to kill it: gentle (pg_cancel_backend) and forced (pg_terminate_backend).

pg_cancel_backend(pid): cancels the current query

SELECT pg_cancel_backend(9876);

Effect:

• If the connection is running a query (state = 'active'), the query is canceled. The transaction is NOT closed (it stays in an aborted state).
• If the connection is idle or idle in transaction, it does nothing visible.
• The connection stays open. The app can keep using it.

It's the correct option when you want to kill a slow query without dropping the whole connection.

pg_terminate_backend(pid): closes the connection

SELECT pg_terminate_backend(9876);

Effect:

• The connection is closed completely.
• The open transaction rolls back automatically.
• All locks are released.
• The client receives an error ("server closed the connection unexpectedly").

It's the nuclear option. Use it when:

• The connection is in idle in transaction and doesn't respond to cancellations (because there's no running query to cancel).
• You need to release locks now, without waiting.
• The client is hung and won't close the connection on its own.

Example incident workflow

You identified pid 9876 as the problematic transaction:

-- Step 1: confirm what it is and since when:
SELECT pid, usename, state, now() - xact_start AS duration, query
FROM pg_stat_activity WHERE pid = 9876;

-- Step 2: terminate (because it's idle in tx, cancel won't help):
SELECT pg_terminate_backend(9876);

-- Step 3: verify it closed:
SELECT pid FROM pg_stat_activity WHERE pid = 9876;
-- (should return 0 rows)

-- Step 4: verify that the blocked queries were released:
SELECT pid, state, wait_event, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';
-- (the ones that were blocked by 9876 should be progressing or finished)

5 minutes to resolve an incident that without this can be hours.

Why this matters in real work

1. It's the tool for live incidents. When your system is down and you need the root cause NOW, there's no substitute for pg_stat_activity. Datadog, New Relic, and APMs show aggregate latency, not per-connection activity. Logs are historical. Only pg_stat_activity gives you the snapshot of the moment.

2. It makes you useful on-call. Backend devs who only know EXPLAIN are useless when there's an active incident and nobody knows what's running. Knowing how to read pg_stat_activity and resolve blocking makes you the person the team looks for at 3am.

3. It detects app bugs that don't appear in testing. idle in transaction almost never appears in unit tests or staging because it requires sustained concurrent load. In production, a badly closed transaction can blow up the system. Knowing how to detect it fast is what makes the difference between diagnosis in minutes vs in hours.

4. It enables postmortems with a precise timeline. "At 14:32 we saw an ETL transaction idle since 14:30. At 14:35 there were 50 blocked requests. We terminated the transaction at 14:36." That timeline is only given by pg_stat_activity correlated with log timestamps.

5. It's basic in any senior backend or SRE interview. "Your API is down, what queries do you run against the DB?" The correct answer starts with SELECT FROM pg_stat_activity WHERE state != 'idle'. If your answer is "I'd look at the logs", the interviewer knows you never operated production.

Traps and common mistakes

Mistake 1 (conceptual): assuming idle is a problem

Symptom: "I see 50 idle connections in pg_stat_activity. Is there a memory leak?"

Why it confuses: "idle" sounds negative. But in a setup with a pool, idle is normal: connections open in the pool waiting to be assigned to a request.

How to distinguish: idle (without a transaction) is benign. idle in transaction (with an open transaction without commit) is a problem.

How to fix it: filter idle out of the analysis. The canonical query at the start of the capsule already does it (WHERE state != 'idle').

Mistake 2 (interpretation): canceling the wrong query

Symptom: "I saw a slow query and canceled it. The API is still down."

Why it happens: the slow query was a victim, not the cause. It was waiting on a lock held by ANOTHER pid. You canceled the victim, left the killer alive, and another victim will appear in its place.

How to distinguish: before canceling, run pg_blocking_pids(pid). If it returns pids, that query is NOT the problem — the problem is whoever blocks it.

How to fix it: always point at the "head of the queue" (the connection that is NOT blocked by anyone but IS blocking others). That's the root cause.

-- Find the head of the queue among the blockers:
SELECT pid, state, query
FROM pg_stat_activity
WHERE pid IN (
    SELECT unnest(pg_blocking_pids(pid))
    FROM pg_stat_activity
    WHERE pg_blocking_pids(pid)::text != '{}'
)
AND pg_blocking_pids(pid)::text = '{}';  -- not blocked by anyone

Mistake 3 (operational): using pg_terminate_backend when pg_cancel_backend is enough

Symptom: "I terminated an active connection to cancel the query. Now the app reports a lost connection error."

Why it happens: pg_terminate_backend closes the connection. The app that was using it receives an abrupt error and, depending on its pool, may take a while to recover it.

How to distinguish:

• If you want to cancel the query and keep the connection alive: pg_cancel_backend.
• If the connection is idle in transaction (no active query): only pg_terminate_backend works.
• If the app is well designed with retry, pg_terminate_backend is safe. If not, prefer cancel.

How to fix it: try pg_cancel_backend first. If in 5-10 seconds it doesn't work (because there was no active query), move to pg_terminate_backend.

Mistake 4 (security): allowing cancel/terminate without restrictions

Symptom: "A junior dev canceled a query that was important because it looked slow. We caused another incident."

Why it happens: pg_cancel_backend and pg_terminate_backend are functions that any superuser can run. If all devs have superuser access to production, accidents happen.

How to distinguish: review who has superuser in production. If it's more than 2-3 people, you're missing role separation.

How to fix it: create a specific "DBA on-call" role with permissions to cancel/terminate (PG 13+ has pg_signal_backend exactly for this):

GRANT pg_signal_backend TO oncall_user;

pg_signal_backend allows canceling/terminating but does NOT grant other superuser powers.

Mistake 5 (interpretation): not understanding wait_event or wait_event_type

Symptom: "I see wait_event = ClientRead on many connections. Is that a problem?"

Why it confuses: PostgreSQL reports any wait in wait_event. Some are a problem (Locks, IO), others are normal (ClientRead means "waiting for the client to send the next query", which is normal on idle connections).

How to distinguish: the wait_event_type that matter for incidents are:

• Lock → blocked by another process. Investigate.
• LWLock → short internal lock. Generally nothing to do.
• IO → reading or writing disk. Normal in heavy queries.
• BufferPin → another process has the buffer pinned. Rare, generally normal.
• Activity → background workers doing their job (autovacuum, walwriter, etc.). Normal.
• Client → waiting for the client (ClientRead, ClientWrite). Normal on pool connections.

How to fix it: filter by wait_event_type IN ('Lock', 'IO') to focus on the actionable:

SELECT pid, state, wait_event_type, wait_event, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE wait_event_type IN ('Lock', 'IO')
ORDER BY query_start ASC;

Mistake 6 (operational): not using application_name

Symptom: "I see 200 connections from user bookstore. I don't know which are the app, which are the workers, which are the cron jobs."

Why it happens: without application_name configured, all connections from the same user are indistinguishable in pg_stat_activity.

How to distinguish: review application_name in the view. If it's empty or they all say the same thing, you're not taking advantage of this column.

How to fix it: configure application_name in each of the app's clients:

# SQLAlchemy with asyncpg
engine = create_async_engine(
    "postgresql+asyncpg://...",
    connect_args={"server_settings": {"application_name": "bookstore-api"}},
)

# Another process (worker, ETL):
engine = create_async_engine(
    "postgresql+asyncpg://...",
    connect_args={"server_settings": {"application_name": "bookstore-etl-daily"}},
)

Then in pg_stat_activity you can filter by application_name = 'bookstore-etl-daily' and separate workloads.

Exercises

Exercise 1: simulate idle in transaction and detect it

Open two psql sessions. In the first, open a transaction and run a query without committing. In the second, run the canonical pg_stat_activity query and verify that you detect the first session as idle in transaction.

docker exec -it bookstore-pg psql -U bookstore -d bookstore

BEGIN;
SELECT count(*) FROM books;
-- Do NOT do COMMIT or ROLLBACK. Leave this session open.

docker exec -it bookstore-pg psql -U bookstore -d bookstore

SELECT
    pid,
    usename,
    application_name,
    state,
    now() - xact_start AS xact_duration,
    now() - state_change AS idle_for,
    left(query, 100) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY xact_start ASC;

 pid  | usename   | application_name | state                | xact_duration | idle_for      | last_query
------+-----------+-------------------+----------------------+---------------+----------------+-----------------------
 4567 | bookstore | psql              | idle in transaction  | 00:00:45      | 00:00:42      | SELECT count(*) FROM books

Exercise 2: simulate blocking and a wait chain

With three psql sessions, simulate this scenario:

• Session 1 does BEGIN; UPDATE books SET stock = 100 WHERE id = 1; (without committing).
• Session 2 tries UPDATE books SET stock = 200 WHERE id = 1; (gets blocked).
• Session 3 tries UPDATE books SET stock = 300 WHERE id = 1; (also blocked).

Identify the complete wait chain with pg_blocking_pids().

BEGIN;
UPDATE books SET stock = 100 WHERE id = 1;
-- NO commit. Leave the session open.

UPDATE books SET stock = 200 WHERE id = 1;
-- This query hangs (blocked by session 1)

UPDATE books SET stock = 300 WHERE id = 1;
-- This one also hangs

SELECT
    pid,
    pg_blocking_pids(pid) AS blocked_by,
    state,
    wait_event,
    now() - query_start AS duration,
    left(query, 80) AS query
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
  AND (state = 'active' OR pg_blocking_pids(pid)::text != '{}')
ORDER BY query_start ASC;

 pid  | blocked_by | state               | wait_event | duration | query
------+------------+---------------------+------------+----------+----------------------------------
 1234 | {}         | idle in transaction |            | 00:00:50 | UPDATE books SET stock = 100 WHERE id = 1
 1235 | {1234}     | active              | tuple      | 00:00:35 | UPDATE books SET stock = 200 WHERE id = 1
 1236 | {1235}     | active              | tuple      | 00:00:20 | UPDATE books SET stock = 300 WHERE id = 1

-- In Session 4:
SELECT pg_terminate_backend(1234);

WITH blocked AS (
    SELECT pid, unnest(pg_blocking_pids(pid)) AS blocking_pid
    FROM pg_stat_activity
    WHERE pg_blocking_pids(pid)::text != '{}'
)
SELECT 'pid ' || pid || ' -> blocked by -> pid ' || blocking_pid AS chain
FROM blocked;

 chain
-----------------------------------
 pid 1235 -> blocked by -> pid 1234
 pid 1236 -> blocked by -> pid 1235

Exercise 3: simulated incident workflow

Simulate a real incident: an ETL transaction blocks 5 app requests. Diagnose and implement the complete resolution.

SET application_name = 'etl_daily_job';
BEGIN;
UPDATE orders SET processed = true WHERE created_at > '2026-04-01';
-- Does work but no commit.
SELECT 1;  -- stays in idle in transaction

SET application_name = 'bookstore_api';
UPDATE orders SET status = 'shipped' WHERE id = <unique-id-per-session>;
-- These queries get blocked

SELECT
    pid,
    application_name,
    state,
    wait_event,
    now() - query_start AS duration,
    left(query, 80) AS query
FROM pg_stat_activity
WHERE state != 'idle'
  AND pid != pg_backend_pid()
ORDER BY query_start ASC;

SELECT
    pid,
    application_name,
    pg_blocking_pids(pid) AS blocked_by,
    left(query, 60) AS query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
  AND pid != pg_backend_pid();

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    now() - xact_start AS xact_duration,
    left(query, 200) AS last_query
FROM pg_stat_activity
WHERE pid = <etl_pid>;

SELECT pg_terminate_backend(<etl_pid>);

SELECT pid, application_name, state, wait_event,
       now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle' AND pid != pg_backend_pid();

Exercise 4: alert query to detect idle in tx > 5 min

Design a query that fires an alert if there's any connection in idle in transaction for more than 5 minutes. This query could run every minute from a monitoring system (Prometheus, Datadog, etc.).

-- Alert query: idle in tx for more than 5 minutes
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    now() - xact_start AS idle_in_tx_for,
    state,
    left(query, 200) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start ASC;

#!/bin/bash
# alert-idle-tx.sh
COUNT=$(docker exec bookstore-pg psql -U bookstore -d bookstore -tA -c "
SELECT count(*)
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - xact_start > interval '5 minutes';
")

if [ "$COUNT" -gt 0 ]; then
    # Send alert (Slack, email, PagerDuty)
    curl -X POST $SLACK_WEBHOOK -d "{\"text\": \"ALERT: $COUNT idle in tx connections > 5 min in bookstore DB\"}"
fi

* * * * * /path/to/alert-idle-tx.sh

pg_stat_activity_count{state="idle in transaction"} > 0

queries:
  - name: "idle_in_tx_long"
    query: |
      SELECT count(*) AS count
      FROM pg_stat_activity
      WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
        AND now() - xact_start > interval '5 minutes';
    metrics:
      - count:
          usage: "GAUGE"
          description: "Number of long-running idle in transaction connections"

postgres_idle_in_tx_long_count > 0

Exercise 5: distinguish the real blocker from the victim

You're shown this snapshot:

 pid  | blocked_by | state  | wait_event | duration | query
------+------------+--------+------------+----------+--------
 1001 | {}         | active |            | 00:02:15 | UPDATE big_table SET ...
 1002 | {1001}     | active | tuple      | 00:01:50 | UPDATE big_table SET ... WHERE id = 5
 1003 | {1001}     | active | tuple      | 00:01:30 | UPDATE big_table SET ... WHERE id = 8
 1004 | {1003}     | active | tuple      | 00:00:45 | UPDATE big_table SET ... WHERE id = 8

Answer:

1. Which is the root blocker?
2. Which are victims?
3. What would your primary action be?
4. Is the action to kill (pg_terminate_backend) or just cancel the query (pg_cancel_backend)? Why?

-- What table and how many rows does it affect?
SELECT pid, query FROM pg_stat_activity WHERE pid = 1001;

-- Is it waiting on something?
SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE pid = 1001;

-- 1. Confirm before acting:
SELECT pid, application_name, query FROM pg_stat_activity WHERE pid = 1001;

-- 2. Cancel:
SELECT pg_cancel_backend(1001);

-- 3. Verify:
SELECT pid, state FROM pg_stat_activity WHERE pid = 1001;
-- If it returns to state = 'idle' or disappears, the query was canceled fine.

-- 4. Verify that the victims progressed:
SELECT pid, state, wait_event FROM pg_stat_activity WHERE pid IN (1002, 1003, 1004);

Exercise 6: configure application_name and filter by workload

Configure your bookstore FastAPI app to report application_name = "bookstore-api" and, if you have a worker, another process with application_name = "bookstore-worker". Verify in pg_stat_activity that you can differentiate workloads.

# app/database.py
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://bookstore:bookstore@localhost:5432/bookstore",
    connect_args={
        "server_settings": {
            "application_name": "bookstore-api",
        }
    },
)

# worker/database.py
engine = create_async_engine(
    "postgresql+asyncpg://bookstore:bookstore@localhost:5432/bookstore",
    connect_args={
        "server_settings": {
            "application_name": "bookstore-worker",
        }
    },
)

uvicorn main:app --reload &
# And if there's a worker:
python worker.py &

for i in {1..50}; do
    curl -s http://localhost:8000/books > /dev/null &
done

SELECT
    application_name,
    count(*) AS connections,
    count(*) FILTER (WHERE state = 'active') AS active,
    count(*) FILTER (WHERE state = 'idle') AS idle,
    count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx
FROM pg_stat_activity
WHERE application_name LIKE 'bookstore%'
GROUP BY application_name
ORDER BY application_name;

 application_name  | connections | active | idle | idle_in_tx
-------------------+-------------+--------+------+------------
 bookstore-api     |          15 |      8 |    7 |          0
 bookstore-worker  |           3 |      2 |    1 |          0

log_line_prefix = '%m [%p] %q%u@%d (app=%a) '

Summary and next step

In this capsule you:

• Learned to read pg_stat_activity and understand each important column (state, wait_event, xact_start, query, application_name).
• Identified hung transactions in idle in transaction and understood why they're the #1 silent killer of production APIs.
• Detected blocking chains using pg_blocking_pids() and learned to find the "head of the queue".
• Differentiated pg_cancel_backend() (gentle, cancels the query) vs pg_terminate_backend() (nuclear, closes the connection) and when to use each.
• Applied a real incident workflow: detect → confirm the blocker → investigate → act → verify.

Before moving on, you should be able to:

• Write the canonical pg_stat_activity query from memory.
• Detect idle in transaction > 5 min with a single query.
• Identify the root blocker in a wait chain.
• Decide between cancel and terminate based on the connection's state.
• Configure application_name to separate workloads.

Next capsule — External tools: pganalyze, pgwatch2, and others. The built-in tools (pg_stat_statements, auto_explain, pg_stat_activity) are the foundation. But at a certain scale, parsing logs by hand and running ad-hoc SQL queries stops scaling. The industry built a SaaS and open-source layer on top: pganalyze (SaaS), pgwatch2 + Grafana (open-source), Datadog DBM, AWS Performance Insights, etc. Capsule 07 gives you a decision matrix: when it's worth investing in one of these, when a home-made Grafana is enough, when bash scripts are sufficient. It's not a SaaS pitch — it's understanding the tooling spectrum and choosing what's appropriate for your team and your scale.

Resources

1. PostgreSQL 16 — pg_stat_activity view — the complete official reference for columns.
2. PostgreSQL 16 — Wait events documentation — the complete table of wait_event and wait_event_type.
3. PostgreSQL 16 — Locks monitoring — the pg_locks view and how to combine it with pg_stat_activity.
4. Hubert "depesz" Lubaczewski — "Idle in transaction" — a classic on why idle in tx is dangerous.
5. PostgreSQL wiki — Lock Monitoring — useful queries for lock diagnosis.
6. Citus Data — "PostgreSQL connection states" — an explanation of connection states and troubleshooting.

Module 5 — Database Performance & Query Tuning Guide