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

Download on the App Store
Scan to open it on your iPhone

Free · Pro $19.99 one-time · PostgreSQL 13–19 · iOS 17+ · iPhone and iPad

Activity screen: sessions sorted with waiting ones first, each with pid, user@app, state, duration, SQL and wait event; one marked blocking 4 sessions

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 · you on HeapDeck’s own session;
  • the state: active, idle in transaction, idle or waiting;
  • the time in that state, counted from query_start for active sessions and from the start of the transaction for idle 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 sessions in 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.