Who Is Doing What

See exactly who is running what inside a live PostgreSQL — learn pg_stat_activity, pg_locks, and pg_blocking_pids so you can diagnose a blocked or slow database in seconds.

When something is slow or stuck in PostgreSQL the first question is always the same: who is doing what right now? This course gives you the two views that answer it — pg_stat_activity for the live process list and pg_locks for the lock table — and teaches you to read them on a real stand with real contention. We start by building a transactional shop database, then launch a background workload to fill the activity view with interesting states. We walk every useful field in pg_stat_activity — state, wait_event_type, wait_event, query_start — and write focused queries to find active sessions, long-running queries, and sessions stuck in idle in transaction. Then we create a genuine row-level lock conflict between two sessions and watch pg_locks record it. We finish with pg_blocking_pids and a joined blocked-vs-blocker query that shows the full picture in one row: who is waiting, who is holding, what each is trying to do, and for how long.

What you'll build

• Read pg_stat_activity: find an active query, an idle connection, and a long-running one
• Interpret state, wait_event_type, wait_event, and query_start
• Tell active, idle, and idle in transaction apart
• Read pg_locks: understand locktype, mode, and granted
• Find blocked and blocking sessions with pg_blocking_pids()
• Write a joined blocked-vs-blocker query using pid and query text

Contents

1. A database to watch
2. Wait for it to be ready
3. One command to bring it up
4. Start the server
5. A short way into the database
6. The users table
7. The orders table
8. The payments table
9. The events table
10. Create the tables
11. Seed the users
12. Seed orders and payments
13. Seed the events
14. Load the data
15. Fresh statistics, single-threaded plans
16. The full process list
17. A background workload
18. Start the workload
19. Query the live process list
20. Filter for active sessions only
21. See who is running
22. The idle state
23. Spot idle sessions
24. Find long-running queries
25. Catch the slow query
26. What is each session waiting for
27. Read the wait events
28. Session A: open a transaction and hold it
29. Session B: try to write the same row
30. Start the blocking session
31. Start the waiting session
32. The full lock table
33. See the conflict in the lock table
34. Filter to waiting locks only
35. Isolate the waiting session
36. Ask who is blocking each session
37. Find the blocker
38. The blocked-vs-blocker query
39. Read the full picture
40. A clean slate when you need one
41. The complete diagnostic flow