Monitoring
Session monitor
SELECT
pid
,datname
,usename
,application_name
,client_hostname
,state
,client_port
,backend_start
,query_start
,query
FROM pg_stat_activity// Monitors Postgres sessions (SQL)
Few important parameters to know
Pid - Backend process ID
Datname - Database name
Username - 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);
// 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;// Top 20 big tables/indexes (excluding catalog tables) (SQL)