Find what is stuck

A query that never finishes, a migration that waits forever, an app that times out: most often one session holds a lock and others queue behind it. Activity shows who is waiting on whom, and lets you stop the session at the root.

Open Activity

Open the command palette with ⌘⇧P on macOS or Ctrl+Shift+P on Windows and Linux, and choose Show activity. Home has the same link, and the Performance view on the activity bar has a short Activity summary that opens it.

The head says how many sessions are in your database and on the server, for example 4 sessions in billing · 19 on the server. Under it, four counts: sessions on the server, running a query, idle in a transaction, and waiting on a lock.

Activity reads again every 2 seconds while its window is in front. Choose another rate in Live, every: 1, 2, 5, 10 or 30 s, or click Pause to hold the view still while you read it, and Resume to carry on.

Sessions belong to the server, not to one database. Activity shows your database’s sessions and hides the rest until you turn off Hide other databases. Server and database explains which views read which.

Read the Sessions table

Each row is one client session, from pg_stat_activity:

Column	What it shows
PID	The server process ID, the number Cancel and Terminate act on
User	The role the session logged in as
Application	Its application_name. A session of one of your query tabs says Your tab
Database	Where it is connected. Another database’s name is muted
Client	The client address
State	active, idle, idle in transaction and so on
Waiting on	Its wait event, such as Lock: transactionid
Query	How long its current statement has run
Transaction	How long its transaction has been open
Text	The statement, on one line. Hover it for the whole text

Three switches above the table filter it. Hide idle is on at first. Hide Alcuin’s own is off, so you see the app’s own sessions too. Hide other databases is on.

A row tinted as a warning is waiting on another session.

If your role is not a member of pg_read_all_stats, Postgres hides other roles’ statements. Activity says Other roles' queries are hidden: this role is not a member of pg_read_all_stats., and those rows read hidden from this role.

Follow the blocking tree

When any session waits on a lock, a Blocking section appears above Sessions, with how many are waiting.

Each tree starts at a root blocker: a session that holds a lock others need and waits on nobody itself. Its row says how long it has been holding. Under it, indented, is every session waiting on it, then every session waiting on those.

A blocking tree: the root blocker, and the sessions waiting on it indented under it.

For a waiting session, Waits for names the lock mode and what it is on, and For says how long it has waited. A session that waits on more than one other is drawn under the first and says also waits on the rest.

The root blocker is usually the one to act on. Often it is idle in transaction: an open transaction that holds its locks while nothing runs. Ending it frees everything under it.

Locks span the whole server, so a session in another database can block one in yours. The tree shows it with its Database.

Cancel a query or terminate a session

1. Click the Actions button (⋯) at the end of the session’s row, in Blocking or Sessions.
2. Choose Cancel query to stop the statement it is running, or Terminate session to end the session.
3. Read the dialog. It names the session, its user, application and client, and shows its statement.
4. Confirm with Cancel query or Terminate session.

Alcuin then sends one of these, with the PID:

SELECT pg_catalog.pg_cancel_backend(4121);
SELECT pg_catalog.pg_terminate_backend(4121);

The two do different things:

• Cancel query stops the running statement. The session stays connected. If it was in a transaction, that transaction is now failed and must be rolled back. Cancel query is offered only for an active session.
• Terminate session ends the connection. Postgres rolls back its open transaction and releases its locks. Use it for a session that is idle in a transaction, since there is no statement to cancel.

When it works, a message says Canceled the query in session 4121 or Terminated session 4121, and the blocking tree shrinks at the next refresh. If the session ended on its own first, the message is Session 4121 had already ended., or Session 4121 has already ended. when it was gone before Alcuin sent anything.

For a session in another database, the actions say which one, for example Terminate session in analytics.

The dialog appears when your Safety rules ask before destructive statements, which every environment does at first.

What you need

• An unlocked connection. While a connection is read-only, the Actions menu leaves out Cancel query and Terminate session. Unlock it first: see Work on production without breaking it.
• Permission on the server. Postgres lets a role signal its own role’s sessions. To signal other roles’ sessions, the role needs membership in pg_signal_backend or must be a superuser, and only a superuser may signal a superuser’s session. Alcuin offers the actions only where your role may use them.

Alcuin never offers them for the session Activity itself reads through.

Start from Health

Health flags a session idle in a transaction for over a minute, or running one transaction for over five. The finding offers Terminate session… and Show activity. See Find out why something is slow.

Explain what a session runs

A session’s Actions menu also has Explain, which plans its statement in the bottom panel, and Open query, which opens it in a new query tab. A statement that still holds $1 parameters is explained as a generic plan, which needs Postgres 16 or later. A session of one of your own tabs has Go to tab instead.

If it did not work

• This role may not cancel session 4821. or This role may not terminate session 4821.: your role lacks the permission above. See ALC-3005.
• This connection is read-only. Unlock it to act on sessions.: the connection locked again before you confirmed. See ALC-3001.
• The tree comes back after a cancel: the session kept its transaction open and still holds the locks. Terminate it, or ask whoever owns it to commit or roll back.
• Statements show as hidden from this role: ask an administrator to grant pg_read_all_stats.