pg_stat_activity

pg_stat_activity is one of the system views of PostgreSQL. This view is very helpful to know what’s going on. With pg_stat_activity, you can know:

=# \d pg_stat_activity
                      View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
 datid            | oid                      |           |          |
 datname          | name                     |           |          |
 pid              | integer                  |           |          |
 leader_pid       | integer                  |           |          |
 usesysid         | oid                      |           |          |
 usename          | name                     |           |          |
 application_name | text                     |           |          |
 client_addr      | inet                     |           |          |
 client_hostname  | text                     |           |          |
 client_port      | integer                  |           |          |
 backend_start    | timestamp with time zone |           |          |
 xact_start       | timestamp with time zone |           |          |
 query_start      | timestamp with time zone |           |          |
 state_change     | timestamp with time zone |           |          |
 wait_event_type  | text                     |           |          |
 wait_event       | text                     |           |          |
 state            | text                     |           |          |
 backend_xid      | xid                      |           |          |
 backend_xmin     | xid                      |           |          |
 query            | text                     |           |          |
 backend_type     | text                     |           |          |

You can get process id and see what application it is and what query it is running, and the event it is waiting, and the state of connections.

Number of Connections

-- Total Connections
SELECT COUNT(1) FROM pg_stat_activity
;

-- idle
SELECT COUNT(1) FROM pg_stat_activity WHERE state = 'idle'
;

SELECT Connections out

-- order by when the connection started
SELECT * FROM pg_stat_activity ORDER BY backend_start
;

-- order by when the transaction started
SELECT * FROM pg_stat_activity ORDER BY xact_start
;

-- order by when the query started
SELECT * FROM pg_stat_activity ORDER BY query_start
;

Group by wait event counts

SELECT state, wait_event, wait_event_type, COUNT(*)
FROM pg_stat_activity
GROUP BY 1, 2, 3
ORDER BY wait_event
;

 state  |     wait_event      | wait_event_type | count
--------+---------------------+-----------------+-------
        | AutoVacuumMain      | Activity        |     1
        | BgWriterHibernate   | Activity        |     1
        | CheckpointerMain    | Activity        |     1
 idle   | ClientRead          | Client          |     2
 idle   | Extension           | Extension       |     5
        | Extension           | Extension       |     1
        | LogicalLauncherMain | Activity        |     1
        | WalWriterMain       | Activity        |     1
 active |                     |                 |     1

Query by Connection State

SELECT datname,
       COUNT(*) AS open_connections,
       COUNT(*) FILTER (WHERE state = 'idle') AS idle_connections,
       COUNT(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_transaction,
       COUNT(*) FILTER (WHERE state = 'active') AS active
FROM pg_stat_activity
GROUP BY ROLLUP(1)
;

and state could be these values:

from postgresql.org

from postgresql.org

Find Query running > 15s

SELECT datname,
       pid,
       usename,
       application_name,
       xact_start,
       query,
       (now() - xact_start)::interval(3) AS duration
FROM pg_stat_activity
WHERE (now() - xact_start)::interval > '15 sec'::interval
;

Find query waits lock but not granted

pg_locks
pg_class

SELECT pg_locks.locktype,
       pg_class.relname,
       pg_locks.pid,
       pg_locks.mode,
       pg_stat_activity.query
FROM pg_locks
LEFT OUTER JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
LEFT OUTER JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE NOT pg_locks.granted
ORDER BY pg_locks.pid DESC
;

and here are all attributes from relevant documentations:

pg_locks.locktype text
Type of the lockable object: relation, extend, frozenid, page, tuple, transactionid, virtualxid, spectoken, object, userlock, or advisory.

pg_class.relname name
Name of the table, index, view, etc.

pg_locks.pid int4
Process ID of the server process holding or awaiting this lock, or null if the lock is held by a prepared transaction

pg_locks.mode text
Name of the lock mode held or desired by this process

pg_stat_activity.query text
Text of this backend's most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query that was executed. By default the query text is truncated at 1024 bytes; this value can be changed via the parameter track_activity_query_size.

pg_stat_activity.pid integer
Process ID of this backend

pg_locks.relation oid (references pg_class.oid)
OID of the relation targeted by the lock, or null if the target is not a relation or part of a relation

pg_class.oid oid
Row identifier

pg_locks.granted bool
True if lock is held, false if lock is awaited