Useful PostgreSQL Queries and Commands

postgres_queries_and_commands.sql

-- show granted locks
SELECT blocked_locks.pid         AS blocked_pid,
       blocked_activity.usename  AS blocked_user,
       blocking_locks.pid        AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query    AS blocked_statement,
       blocking_activity.query   AS current_statement_in_blocking_process
  FROM pg_catalog.pg_locks           AS blocked_locks
  JOIN pg_catalog.pg_stat_activity AS blocked_activity  ON blocked_activity.pid = blocked_locks.pid
  JOIN pg_catalog.pg_locks         AS blocking_locks 
       ON blocking_locks.locktype = blocked_locks.locktype
          AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
          AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
          AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
          AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
          AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
          AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
          AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
          AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
          AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
          AND blocking_locks.pid != blocked_locks.pid
  JOIN pg_catalog.pg_stat_activity AS blocking_activity ON blocking_activity.pid = blocking_locks.pid
 WHERE NOT blocked_locks.granted;

-- show pending locks
SELECT relation::regclass,
       *
  FROM pg_locks
 WHERE NOT granted;

-- show running queries (pre 9.2)
SELECT procpid,
       age(clock_timestamp(), query_start),
       usename,
       current_query 
  FROM pg_stat_activity 
 WHERE current_query != '<IDLE>'
   AND current_query NOT ILIKE '%pg_stat_activity%' 
 ORDER BY query_start desc;

-- show running queries (>9.2)
SELECT pid,
       age(clock_timestamp(), query_start),
       usename,
       query 
  FROM pg_stat_activity 
 WHERE query != '<IDLE>'
   AND query NOT ILIKE '%pg_stat_activity%' 
 ORDER BY query_start desc;

-- kill running query
SELECT pg_cancel_backend(procpid);

-- kill idle query
SELECT pg_terminate_backend(procpid);

-- vacuum command
VACUUM (VERBOSE, ANALYZE);

-- all database users
SELECT * FROM pg_stat_activity WHERE current_query NOT LIKE '<%';

-- all databases and their sizes
SELECT * FROM pg_user;

-- all tables and their size, with/without indexes
SELECT datname,
       pg_size_pretty(pg_database_size(datname))
  FROM pg_database
 ORDER BY pg_database_size(datname) DESC;

-- cache hit rates (should not be less than 0.99)
SELECT sum(heap_blks_read) AS heap_read,
       sum(heap_blks_hit)  AS heap_hit,
       (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) AS ratio
  FROM pg_statio_user_tables;

-- table index usage rates (should not be less than 0.99)
SELECT relname,
       100 * idx_scan / (seq_scan + idx_scan) AS percent_of_times_index_used,
       n_live_tup AS rows_in_table
  FROM pg_stat_user_tables 
 ORDER BY n_live_tup DESC;

-- how many indexes are in cache
SELECT sum(idx_blks_read) AS idx_read,
       sum(idx_blks_hit)  AS idx_hit,
       (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) AS ratio
  FROM pg_statio_user_indexes;