Use pg_stat_activity for Live Checks

Your PostgreSQL server is silently juggling dozens of connections, running background workers, and holding locks—and you have no idea what's happening. When a query suddenly hangs or an application reports a connection timeout, you need live visibility into the database's inner workings. This lesson shows you how to use pg_stat_activity for live checks—the system view that reveals active queries, idle connections, and blocking locks in real time. By the end, you'll be able to diagnose performance issues and answer the question every PostgreSQL DBA asks: "What is my database doing right now?"

The problem this lesson solves

Imagine you're running a pre‑production test and a query that usually completes in milliseconds now takes minutes. Your application logs show a connection timeout, but you have no stack trace from the database side. You could restart the database, but that kills all connections—and possibly the offending query that you wanted to inspect. Or you could comb through logs, but they're verbose and often miss the exact moment.

This is where pg_stat_activity comes to the rescue. It's a live, in‑memory system view that shows one row per backend process, including the current SQL query, its state, wait events, and metadata like the connected user and database. With this view, you can run a single SELECT and instantly see:

• Which queries are currently running
• Which are idle waiting for the next command
• Which are stuck in a long transaction
• Which sessions are holding locks that block others

In short, it answers the classic question: What is my database doing right now?—without restarting or installing additional tools.

Core concept / mental model

Think of PostgreSQL as a busy restaurant. Each table is a dining table, and every backend process is a waiter. pg_stat_activity is the floor manager's clipboard that lists every waiter, what they're doing (taking an order, delivering food, standing idly), and which table they're serving. If a customer complains about delays, the manager checks the clipboard to see which waiter is stuck, where, and why.

In PostgreSQL terms:

• Backend process: A server process handling one client connection. pg_stat_activity has one row per backend.
• State: The current activity—active (running a query), idle (waiting for new commands), idle in transaction (inside a BEGIN block but not executing), or fastpath function call.
• Wait event: What the backend is waiting on (e.g., ClientRead, Lock, IO). wait_event_type tells the category.
• Query: The last query executed by that backend. For active queries, it's the current one.
• xact_start, query_start, state_change: Timestamps showing how long a transaction or query has been running.

Pro tip: pg_stat_activity is refreshed in near‑real‑time—each backend updates its row when its state changes. It's not a historical record; for that, you'd turn to pg_stat_statements or logging.

How it works step by step

To use pg_stat_activity effectively, follow this logical progression:

1. Connect to your database as a superuser (or a role with pg_monitor privileges). Without these, you'll only see your own sessions.
2. Run a query against pg_stat_activity to list sessions. Start broad—show all columns—then narrow down with WHERE clauses.
3. Filter by state to find running queries (state = 'active'), idle sessions, or idle transactions.
4. Identify long‑running queries by ordering on query_start (the time the current query started).
5. Check for blocking using the blocking_pids column (or by joining the view to itself).
6. Take action: cancel a query with pg_cancel_backend(pid) or terminate a session with pg_terminate_backend(pid).
7. Re‑check after your action to confirm the situation resolved.

This order helps you move from global awareness to targeted intervention.

Hands-on walkthrough

Let's put this into practice. We'll create a table, open two psql sessions, and simulate a blocking scenario.

Step 1: Create test data

-- In terminal 1 (as a superuser)
CREATE TABLE IF NOT EXISTS orders (
    id SERIAL PRIMARY KEY,
    customer TEXT NOT NULL,
    total NUMERIC(10,2) NOT NULL
);

INSERT INTO orders (customer, total) VALUES ('Alice', 100.00), ('Bob', 50.00);

Step 2: View all sessions

In the same terminal, run:

SELECT pid, usename, datname, state, query_start, query
FROM pg_stat_activity;

You'll see your current session plus any others. Expect output like:

 pid | usename | datname | state  |           query_start           |          query
-----+---------+---------+--------+--------------------------------+------------------------
 123 | alice   | mydb    | active | 2025-01-10 10:15:00.123456+00 | SELECT ... FROM pg_stat_activity...
 456 | bob     | mydb    | idle   |                                |

Step 3: Simulate a long query

Open a second psql terminal and run:

BEGIN;
UPDATE orders SET total = total * 2 WHERE customer = 'Alice';
-- Do not commit yet!

Now in the first terminal, query pg_stat_activity again:

SELECT pid, state, query, xact_start, query_start
FROM pg_stat_activity
WHERE state <> 'idle';

The output will show both sessions—one idle in transaction, the other still active.

Step 4: Cancel the idle transaction

If an idle transaction holds locks and blocks others, terminate it:

SELECT pg_terminate_backend(456); -- Replace with the actual pid

This forcibly disconnects that session. In psql, the client will show a connection reset message.

Pro tip: Use pg_cancel_backend() to cancel a running query while keeping the connection open. Use pg_terminate_backend() as a last resort—it kills the entire session, releasing connection pools and locks.

Step 5: Detect blocking sessions

Run this query to see which sessions are blocking others while our transaction is open:

SELECT pid, usename, state, wait_event_type, wait_event, query,
       (SELECT array_agg(pg_blocking_pids()) ) AS blocking_pids
FROM pg_stat_activity
WHERE state = 'active';

Actually, the correct way is to use the blocking_pids column directly—available since PostgreSQL 9.6. Here's a cleaner version:

SELECT a.pid, a.state, a.query,
       b.pid AS blocking_pid,
       b.query AS blocking_query
FROM pg_stat_activity a
JOIN pg_stat_activity b ON a.pid = ANY (b.blocking_pids)
WHERE a.state = 'active';

This will show which session is blocked (a) and which one is blocking (b).

Compare options / when to choose what

pg_stat_activity is not the only monitoring tool. Here's a quick comparison:

For a quick live check, pg_stat_activity wins. For trend analysis, use pg_stat_statements or logs. You'll often combine them: use pg_stat_activity to spot a problem, then dive into pg_stat_statements to see if it's a recurring pattern.

Troubleshooting & edge cases

1. permission denied for view pg_stat_activity — You need superuser or the pg_monitor role. Non‑privileged users see only their own rows. Fix: grant pg_monitor to the monitoring role.

2. Query showing idle in transaction for hours — That's a sneaky cause of lock contention. The session began a transaction but never committed. Use pg_terminate_backend to kill it, but be aware it rolls back any uncommitted work.

3. Queries stuck in active state with wait_event_type = 'Lock' — This means the query is trying to acquire a lock held by another session. Use the blocking_pids query to find the culprit.

4. pg_stat_activity output huge — Many connections from a pool. Filter by datname or usename to narrow down, or use query LIKE to find specific queries.

5. Can't see queries from other users — Again, permissions. If you're not a superuser, you can't see others' queries. Work with your DBA to grant pg_monitor.

6. pg_terminate_backend doesn't kill the query? — If the backend is in a state that ignores signals (rare), you may need to wait or restart the whole database as a last resort.

Pro tip: When diagnosing, always capture a pg_stat_activity snapshot before taking any action. That way, you preserve the evidence for later analysis.

What you learned & what's next

You've learned how to use pg_stat_activity for live checks—querying the view to see active sessions, identifying long‑running queries, understanding active, idle, and idle in transaction states, and using pg_cancel_backend and pg_terminate_backend to intervene. You also saw how to detect blocking sessions with blocking_pids. You can now confidently answer "What is my database doing right now?" and take action when something goes wrong.

Next in this track, you'll learn about query performance analysis using EXPLAIN to understand how PostgreSQL executes a query—an essential skill for optimizing slow queries that you might spot with pg_stat_activity. You'll turn the question "What is it doing?" into "Why is it slow?"