Something is slow. Maybe a page takes forever to load, maybe a migration is hanging, maybe your Supabase dashboard just spins. You suspect a query is stuck somewhere in your database, but you can't see what's happening — Postgres doesn't exactly surface this on its own.

Turns out it does. You just need to ask.

Seeing what's running

Postgres keeps track of every active connection and what it's doing in a system view called pg_stat_activity. You can query it like any table:

SELECT pid, state, query, age(clock_timestamp(), query_start) AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

That gives you every non-idle process — its process ID, current state, the SQL it's running, and how long it's been at it. If something has been running for minutes when it should take milliseconds, you've found your problem.

A few things worth knowing about the columns:

• pid — the process ID, which you'll need if you want to kill it
• state — usually active (running right now), idle in transaction (sitting inside an open transaction doing nothing), or idle (waiting for work)
• query — the actual SQL text
• query_start — when the current query began

If you want to include the user and database to narrow things down:

SELECT pid, usename, datname, state, query, age(clock_timestamp(), query_start) AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

The dangerous one — idle in transaction

An active query that's been running for a while is usually just slow. An idle in transaction connection is a different kind of problem — it means someone (or some code) opened a transaction and never committed or rolled it back. The connection is doing nothing, but it's still holding locks, which can block other queries from running.

These are the ones that tend to cause cascading slowdowns. If you see one that's been sitting there for longer than expected, it's almost certainly a bug in application code — a missing COMMIT, an unhandled exception that skipped the cleanup, or a connection pool that didn't reclaim the session properly.

Killing a process

Once you've identified the offending pid, you have two options.

The gentle approach — ask the query to cancel:

SELECT pg_cancel_backend(12345);

This sends a cancel signal to the running query. If the process is active, the query stops and the connection goes back to idle. It's the equivalent of hitting Ctrl+C — the session stays alive, no harm done.

The forceful approach — terminate the connection entirely:

SELECT pg_terminate_backend(12345);

This kills the entire backend process. The connection is dropped, any open transaction is rolled back, and the client gets disconnected. Use this when pg_cancel_backend doesn't work — which tends to happen with idle in transaction sessions, since there's no active query to cancel.

Replace 12345 with the actual pid from your pg_stat_activity query.

Killing in bulk

If you've got several stuck connections and want to clear them all at once:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND query_start < now() - interval '5 minutes';

That terminates every connection that's been idle in a transaction for more than five minutes. Adjust the interval to taste.

On Supabase specifically

If you're on Supabase, you can run all of this through the SQL Editor in the dashboard. The same pg_stat_activity view is available, and pg_cancel_backend / pg_terminate_backend both work. No extra permissions needed — the default postgres role has access.

One thing to keep in mind: Supabase runs background processes for Realtime, Auth, and PostgREST. You'll see these in pg_stat_activity too. Don't kill them — they'll usually show up with usernames like supabase_admin or authenticator. Stick to terminating connections from your own application's role.