Monitoring Database Activity

SELECT pid, usename, application_name, state, wait_event_type, wait_event,
       now() - query_start AS runtime, query
  FROM pg_stat_activity
 WHERE state <> 'idle' ORDER BY runtime DESC;

SELECT datname, xact_commit, xact_rollback, blks_read, blks_hit,
       tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted
  FROM pg_stat_database;

SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup,
       n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd
  FROM pg_stat_user_tables ORDER BY n_live_tup DESC;

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
  FROM pg_stat_user_indexes ORDER BY idx_scan DESC;

SELECT relname, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit
  FROM pg_statio_user_tables;

SELECT relname, n_mod_since_analyze, last_analyze, last_autoanalyze
  FROM pg_stat_user_tables;

SELECT relname, indexrelname, idx_scan
  FROM pg_stat_user_indexes WHERE idx_scan = 0;

SELECT pid, locktype, mode, granted, relation::regclass
  FROM pg_locks WHERE NOT granted;

SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query,
       blocking.pid AS blocking_pid, blocking.query AS blocking_query
  FROM pg_stat_activity blocked
  JOIN pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

SELECT pg_cancel_backend(12345);     -- gentle, only cancels current query

SELECT pg_terminate_backend(12345);  -- force-close session

SELECT pg_size_pretty(pg_database_size(current_database()));

SELECT relname,
       pg_size_pretty(pg_total_relation_size(oid))   AS total,
       pg_size_pretty(pg_relation_size(oid))         AS heap,
       pg_size_pretty(pg_indexes_size(oid))          AS indexes
  FROM pg_class WHERE relkind IN ('r','p') ORDER BY pg_total_relation_size(oid) DESC LIMIT 20;

SELECT indexrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid))
  FROM pg_index ORDER BY pg_relation_size(indexrelid) DESC LIMIT 20;