Part-16 PostgreSQL: Find slow, long-running, and Blocked Queries

 pg_stat_activity: A table with one entry per server process, showing details of the running query for each.

select pid,user,query_start,now() - query_start as running_time,                           state,wait_event_type,wait_event,query  from pg_stat_activity

 where (now() - query_start) > interval '5 minutes';

A simple select * from pg_stat_activity will provide a snapshot of what is happening on your PostgreSQL database, with one line per current transaction, and the key columns:
datname: The database name that the query is running on.
pid: The Process ID of the backend for the running query. PID can be used with pg_terminate_backend() or pg_cancel_backend() separately.
usename: user name that is running/connected to this backend
client_addr: IP address of the connection to this backend. If this is empty/null, the row may refer to an internal connection or internal process.
backend_start: Time when the backend process was started, or when the client connected.
query_start: Time when the currently active query was started.
state: The current status of the backend, with options:
active: currently executing the query in the query column.

idle: not executing anything, and waiting for a new command.

idle in transaction: the backend is in a transaction, but not currently doing any work. This is the same as with “idle in transaction (error)” except one of the statements has an error.
fastpath function call: Executing a fast-path function.
disabled: track_activities has been disabled for this backend.

pg_locks: Information on current locks held within the database by open transactions, with one row per lockable object.

SELECT

    activity.pid,

    activity.usename,

    activity.query,

    blocking.pid AS blocking_id,

    blocking.query AS blocking_query

FROM pg_stat_activity AS activity

JOIN pg_stat_activity AS blocking 

ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

A simple select * from pg_locks will immediately provide you

1. locktype: The type of object that is locked – (see here) relation, page, object, tranasctionid, userlock etc.
2. relation: OID (object ID) of the relation targeted by the lock. Join to the pg_class OID to get the names of the tables here.
3. page: page number targeted by the lock within the relation.
4. transactionid xid: ID of the transaction targeted by the lock.
5. pid: Process ID of the server process holding/waiting for this lock. Joining this column onto the pg_stat_activity view can provide all of the information above on the query / user / time etc.
6. granted: True if lock is held, false if the process is waiting.

Viewing locks with table names and queries

select  relname as relation_name,  query, pg_locks.* 

from pg_locks  

join pg_class on pg_locks.relation = pg_class.oid

join pg_stat_activity

on pg_locks.pid = pg_stat_activity.pid

pg_blocking_pids(): A function that can find the process IDs (PIDs) of sessions that are blocking the PostgreSQL server process of a supplied PID.

pg_cancel_backend(): Function that cancels the currently running query by sending a SIGINT to a process ID.
pg_terminate_backend(): Terminate a backend process completely (the query and usually the connection) on the database (uses SIGTERM instead of SIGINT).

pg_cancel_backend(pid) will attempt to gracefully kill a running query process.
pg_terminate_backend(pid) will immediately kill the running query process, but potentially have side affects across additional queries running on your database server. The full connection may be reset when running pg_terminate_backend, so other running queries can be affected.