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)