Postgres cheatsheet

pg_cheatsheet.sql

-- show number of connections
select count(*) from pg_stat_activity;

-- or
SELECT sum(numbackends) FROM pg_stat_database;

-- 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;

-- show long running queries (> 9.2)
SELECT pid,
       now() - pg_stat_activity.query_start AS duration,
       query,
       STATE
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

-- check autovacuum & analyze status
SELECT schemaname,
       relname,
       vacuum_count,
       last_vacuum,
       autovacuum_count
       last_autovacuum,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables;

-- check autovacuum & tup status (simple)
SELECT relname,
       n_tup_ins,
       n_tup_upd,
       n_tup_del,
       n_tup_hot_upd,
       n_live_tup,
       n_dead_tup,
       last_vacuum,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_all_tables
WHERE n_dead_tup > 0;

-- check autovacuum, autoanalyze, last_analyze
SELECT relname,last_autovacuum,last_autoanalyze, last_analyze 
FROM pg_stat_user_tables order by relname;

-- check autovacuum & tup status (advanced)
SELECT schemaname,
       relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup /(n_live_tup * current_setting('autovacuum_vacuum_scale_factor')::float8 + current_setting('autovacuum_vacuum_threshold')::float8) DESC

-- kill running query
SELECT pg_cancel_backend(procpid);

-- kill idle query
SELECT pg_terminate_backend(procpid);

-- Kill all queries that have been running for more than a minute
select pg_terminate_backend(pid)
from (
  SELECT
      pid
  FROM pg_stat_activity
     WHERE query <> '<insufficient privilege>'
     AND state <> 'idle'
     AND pid <> pg_backend_pid()
     AND query_start < now() - interval '1 minute'
     ORDER BY query_start DESC) t;

-- Detect which queries are blocked (waiting on a lock) and by which query they're blocked
SELECT pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- vacuum command
VACUUM (VERBOSE, ANALYZE);

-- show all database users
SELECT * FROM pg_user;

-- show all locks
SELECT t.relname,
       l.locktype,
       page,
       virtualtransaction,
       pid,
       MODE,
       GRANTED
FROM pg_locks l,
     pg_stat_all_tables t
WHERE l.relation = t.relid
ORDER BY relation ASC;

-- show all tables and their size
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
                                                
-- show how much space tables and indexes are taking up
SELECT relname AS TABLE_NAME,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS internal,
       pg_size_pretty(pg_table_size(relid) - pg_relation_size(relid)) AS EXTERNAL,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- show 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;

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

-- show 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;
                                                                                              
-- show tracking execution statistics of all SQL statements executed by a server
SELECT userid,
       dbid,
       queryid,
       query,
       calls,
       (total_time / 1000 / 60) AS total_minutes,
       (total_time/calls) AS average_time_ms,
       min_time,
       max_time,
       mean_time,
       stddev_time,
       ROWS,
       shared_blks_hit,
       shared_blks_read,
       shared_blks_dirtied,
       shared_blks_written,
       local_blks_hit,
       local_blks_read,
       local_blks_dirtied,
       local_blks_written,
       temp_blks_read,
       temp_blks_written,
       blk_read_time,
       blk_write_time
FROM pg_stat_statements
ORDER BY average_time_ms DESC LIMIT 100

-- Table and index detailed stats
WITH table_stats AS
  (SELECT psut.relname,
          psut.n_live_tup,
          1.0 * psut.idx_scan / greatest(1, psut.seq_scan + psut.idx_scan) AS index_use_ratio
   FROM pg_stat_user_tables psut
   ORDER BY psut.n_live_tup DESC),
     table_io AS
  (SELECT psiut.relname,
          sum(psiut.heap_blks_read) AS table_page_read,
          sum(psiut.heap_blks_hit) AS table_page_hit,
          sum(psiut.heap_blks_hit) / greatest(1, sum(psiut.heap_blks_hit) + sum(psiut.heap_blks_read)) AS table_hit_ratio
   FROM pg_statio_user_tables psiut
   GROUP BY psiut.relname
   ORDER BY table_page_read DESC),
     index_io AS
  (SELECT psiui.relname,
          psiui.indexrelname,
          sum(psiui.idx_blks_read) AS idx_page_read,
          sum(psiui.idx_blks_hit) AS idx_page_hit,
          1.0 * sum(psiui.idx_blks_hit) / greatest(1.0, sum(psiui.idx_blks_hit) + sum(psiui.idx_blks_read)) AS idx_hit_ratio
   FROM pg_statio_user_indexes psiui
   GROUP BY psiui.relname,
            psiui.indexrelname
   ORDER BY sum(psiui.idx_blks_read) DESC)
SELECT ts.relname,
       ts.n_live_tup,
       ts.index_use_ratio,
       ti.table_page_read,
       ti.table_page_hit,
       ti.table_hit_ratio,
       ii.indexrelname,
       ii.idx_page_read,
       ii.idx_page_hit,
       ii.idx_hit_ratio
FROM table_stats ts
LEFT OUTER JOIN table_io ti ON ti.relname = ts.relname
LEFT OUTER JOIN index_io ii ON ii.relname = ts.relname
ORDER BY ti.table_page_read DESC,
         ii.idx_page_read DESC;
                                                   
-- Number of table rows per table
SELECT 
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;

-- Locks: https://jaketrent.com/post/find-kill-locks-postgres
-- Listing locks
SELECT pid
FROM pg_locks l
JOIN pg_class t ON l.relation = t.oid
WHERE t.relkind = 'r'
  AND t.relname = 'search_hit';
  
-- Matching queries with locks
SELECT pid,
       state,
       usename,
       query,
       query_start
FROM pg_stat_activity
WHERE pid in
    (SELECT pid
     FROM pg_locks l
     JOIN pg_class t ON l.relation = t.oid
     AND t.relkind = 'r'
     WHERE t.relname = 'search_hit' );

-- Killing locks
SELECT pg_cancel_backend(11929);

-- or
SELECT pg_terminate_backend(11929);

select pid 
from pg_locks l 
join pg_class t on l.relation = t.oid 
where t.relkind = 'r'
and t.relname = 'search_hit';
                                                   
-- Permissions management                                              
REVOKE SELECT ON "table_name" FROM user_name;
\z table_name
GRANT SELECT (id, name, ...) ON "table_name" TO user_name;

-- Change table, sequence and view owner;
ALTER TABLE tab_name OWNER TO new_owner_name;

-- Grant all priviledges to a user;
GRANT USAGE ON SCHEMA public to USER user_foo;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user_foo;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO user_foo;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO user_foo;
GRANT ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA public TO user_foo;
GRANT ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA public TO user_foo;

-- Grant access to all future tables owned by master since this account performs the migrations
ALTER DEFAULT PRIVILEGES FOR USER master IN SCHEMA public GRANT ALL ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR USER master IN SCHEMA public GRANT ALL ON FUNCTIONS TO app_user;
ALTER DEFAULT PRIVILEGES FOR USER master IN SCHEMA public GRANT ALL ON SEQUENCES TO app_user;
ALTER DEFAULT PRIVILEGES FOR USER master IN SCHEMA public GRANT ALL ON ROUTINES TO app_user;

-- Copy priviledges from user A to user B
GRANT A TO B

-- Show current user
SELECT SESSION_USER, CURRENT_USER;

-- Show all users with their configuration
SELECT usename, usesysid, useconfig FROM pg_catalog.pg_user;

-- Change user configuration
ALTER USER app_user SET lock_timeout='20s';

-- Replication management

-- On primary
select * from pg_stat_replication;

-- On replica
select * from pg_stat_wal_receiver;

-- References
-- https://levelup.gitconnected.com/the-magic-of-vacuum-keeping-your-postgresql-database-in-top-shape-991a40ae101