PostgreSQL sessions: pg_stat_activity on your iPhone
Activity is pg_stat_activity laid out for a phone: waiting sessions first, the longest ones next, states and wait events readable at a glance, and a mark on the session that blocks the others.
Updated · Vladimir Chemeris, the developer of HeapDeck
The query you would type, sorted for you
On a laptop you would start with something like this:
SELECT pid, usename, application_name, state, wait_event_type, wait_event,
now() - coalesce(xact_start, query_start) AS age,
left(query, 80) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY age DESC NULLS LAST;
HeapDeck’s Activity screen reads the same view every 5 seconds while it is open and sorts it for an incident: active sessions that wait on something first, then the rest by duration.
The row, field by field
- the pid and
user@application, with· youon HeapDeck’s own session; - the state:
active,idle in transaction,idleorwaiting; - the time in that state, counted from
query_startfor active sessions and from the start of the transaction foridle in transaction. The time turns amber at 10 seconds and red at 4 minutes; - the first line of its SQL;
- the wait, such as
waiting: Lock · relation, in red for a lock; blocking 4 sessionsin red on the session at the root of a lock chain.
Filters across the top narrow the list to Active, Idle in tx, > 10s or Waiting. Tap a row for the details: client address, the start of the current state, the wait event and the full SQL with a copy button. If that session waits on another one, the details say so and warn that cancelling it won’t unblock the chain.
Cancel or terminate
Swipe a row left for Cancel and Terminate:
- Cancel sends
pg_cancel_backend(pid): the statement stops, the session stays. - Terminate sends
pg_terminate_backend(pid): the session ends and its open transaction rolls back. On a connection you marked as Production, you type the pid to confirm.
Both are part of HeapDeck Pro. HeapDeck’s own session has no actions.
Without pg_monitor
Without pg_monitor, PostgreSQL shows other users’ sessions but replaces their SQL with <insufficient privilege>. HeapDeck keeps the list, says You can see sessions, but not their SQL and shows the grant that fixes it:
GRANT pg_monitor TO monitoring_role;
From a slow query to the sessions running it
On PostgreSQL 14 and later with compute_query_id on, a statement’s detail in Slow queries lists up to three sessions running it right now, next to its role, database, timings and I/O. Show all in Activity opens every such session, and Activity asks the server for them every 5 seconds, so you see the moment the last copy of a bad query finishes.
See all of HeapDeck’s screens.
Questions
How do I see other users' queries in pg_stat_activity without superuser?
Grant your role pg_monitor, or pg_read_all_stats. Without one of them, PostgreSQL replaces other users' query with <insufficient privilege>.
What does the state column mean?
active runs a statement. idle waits for a command with no transaction. idle in transaction waits inside an open transaction and keeps its locks. See what idle in transaction means.