Diagnosing Live Activity with pg_stat_activity
Learn to use the pg_stat_activity view to see what every connection is doing right now, find long-running and blocked queries, and safely cancel or terminate problem sessions.
Diagnosing Live Activity with pg_stat_activity is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Your Window into Live Sessions
The pg_stat_activity view has one row per server connection. It is the first place to look when the database feels slow or stuck, showing what each session is doing this instant.
The Key Columns
The most useful columns are:
pid: the backend process idstate: active, idle, idle in transactionquery: the current or last SQL textwait_event: what the session is waiting on
A Basic Look
Select the essentials for all active sessions.
SELECT pid, usename, state, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle';Finding Long-Running Queries
Compute how long each active query has been running by subtracting query_start from now.
SELECT pid, now() - query_start AS runtime, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY runtime DESC;Idle in Transaction Danger
A session in idle in transaction holds locks and pins the oldest XID without doing work. These can block vacuum and other sessions. Hunt them down.
SELECT pid, now() - xact_start AS tx_age, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY tx_age DESC;Seeing What a Session Waits On
The wait_event_type and wait_event columns reveal whether a session is waiting on a lock, on I/O, or on a client. This pinpoints the bottleneck.
SELECT pid, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;Finding Who Blocks Whom
Combine activity with pg_blocking_pids to see which sessions are blocking others.
SELECT pid, pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;Cancelling a Query
pg_cancel_backend stops the current query in a session but leaves the connection open. Try this first — it is the gentler option.
SELECT pg_cancel_backend(12345);Terminating a Connection
If cancelling is not enough, pg_terminate_backend closes the whole connection, rolling back its transaction. Use it for stuck idle-in-transaction sessions.
SELECT pg_terminate_backend(12345);Building a Monitoring Habit
Good practice:
- Set
idle_in_transaction_session_timeoutto auto-kill stragglers - Set
statement_timeoutto bound runaway queries - Watch
pg_stat_activityduring incidents before reaching for the kill switch
Counting Connections by State
To gauge overall pressure, summarize how many connections sit in each state. A pile of idle-in-transaction or active sessions hints at pooling or query problems.
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;Quick Check
Test your live-monitoring knowledge.
Recap
You learned live diagnosis with pg_stat_activity:
- One row per connection with state, query, and wait info
- Find long queries via
now() - query_start - Hunt idle-in-transaction sessions that block vacuum
pg_blocking_pidsreveals who blocks whom- Cancel a query or terminate a backend when needed
Frequently asked questions
Is the “Diagnosing Live Activity with pg_stat_activity” lesson free?
Yes — the full text of “Diagnosing Live Activity with pg_stat_activity” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.
What will I learn in “Diagnosing Live Activity with pg_stat_activity”?
Learn to use the pg_stat_activity view to see what every connection is doing right now, find long-running and blocked queries, and safely cancel or terminate problem sessions. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start PostgreSQL Performance & Query Optimization?
No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Diagnosing Live Activity with pg_stat_activity” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?
Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Using pg_stat_statements and pg_buffercache
- Logging Configuration for Analysis
- External Monitoring Tools Integration
- Diagnosing Live Activity with pg_stat_activity