Find long-running queries in PostgreSQL

When a site is slow right now, the first question is which queries are running and for how long. PostgreSQL answers it from the pg_stat_activity view. You need a role that can see other sessions' queries: a superuser or a member of pg_read_all_stats. Stopping a query needs more: the same role as the session, membership of pg_signal_backendor a superuser. On a managed service, use the provider's equivalent.

SELECT pid,
       now() - query_start AS running_for,
       now() - xact_start  AS transaction_age,
       state,
       wait_event_type,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND pid <> pg_backend_pid()
ORDER BY running_for DESC
LIMIT 20;

Query text is cut off at track_activity_query_size (1024 bytes by default).

-- Ask the query to stop; the connection stays open
SELECT pg_cancel_backend(12345);

-- Last resort: close the whole connection
SELECT pg_terminate_backend(12345);

Cancel first. Terminating a session rolls back its open transaction and drops the connection, which an application may not handle well. Cancelling does nothing to an idle in transaction session, because it has no running query: terminate it, or set idle_in_transaction_session_timeout so PostgreSQL closes such sessions itself. To stop slow queries from piling up in future, set statement_timeout for the role or application that runs them.