Monitoring

Session Monitor

SELECT 
    pid
    ,datname
    ,usename
    ,application_name
    ,client_hostname
    ,state
    ,client_port
    ,backend_start
    ,query_start
    ,query  
FROM pg_stat_activityCopy

// Monitors Postgres sessions (SQL)

Pid - Backend Process ID

Datname - Database Name

Usename - User running the query

Application_name - Client Application Name

State - State of Session (e.g. Active, Waiting, Idle ..)

Query - Query executed

Cancel Running Query

SELECT pg_cancel_backend(pid);
Copy

// To cancel a running query with pid provided. This is useful in case of killing long running queries (SQL)

Biggest Postgres Table/Indexes by their sizes

SELECT
  nspname || '.' || relname AS "Object Name", relkind As "Object Type",
  pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;Copy

// Top 20 Big tables/Indexes (Excluding catalog tables) (SQL)