postgres queries

postgres_queries.sql

-- show running queries
SELECT pid, age(clock_timestamp(), query_start) AS age, usename, state, query FROM pg_stat_activity WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY age DESC;

-- kill running query (cancel — graceful)
SELECT pg_cancel_backend(pid);

-- kill idle query (terminate — forceful)
SELECT pg_terminate_backend(pid);

-- show blocking + blocked queries
SELECT blocked.pid AS blocked_pid, blocked.usename AS blocked_user, blocking.pid AS blocking_pid, blocking.usename AS blocking_user, blocked.query AS blocked_query, blocking.query AS blocking_query, age(clock_timestamp(), blocked.query_start) AS blocked_for FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid)) WHERE NOT blocked.pid = ANY(pg_blocking_pids(blocked.pid));

-- current locks
SELECT l.locktype, l.mode, l.granted, l.pid, a.usename, a.query, age(clock_timestamp(), a.query_start) AS age, c.relname FROM pg_locks l LEFT JOIN pg_stat_activity a ON a.pid = l.pid LEFT JOIN pg_class c ON c.oid = l.relation WHERE NOT l.granted OR l.relation IS NOT NULL ORDER BY l.granted, age DESC;

-- long-running transactions (often the cause of bloat / replication lag)
SELECT pid, usename, state, xact_start, age(clock_timestamp(), xact_start) AS xact_age, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_age DESC;

-- connections by state and user
SELECT usename, state, count(*) FROM pg_stat_activity GROUP BY 1, 2 ORDER BY 1, 2;

-- vacuum command
VACUUM (VERBOSE, ANALYZE);

-- last vacuum / analyze per table (find tables overdue for maintenance)
SELECT schemaname, relname, n_live_tup, n_dead_tup, round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

-- autovacuum currently running
SELECT pid, datname, relid::regclass AS table, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, max_dead_tuples, num_dead_tuples FROM pg_stat_progress_vacuum;

-- all users
SELECT * FROM pg_user;

-- all databases and their sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database ORDER BY pg_database_size(datname) DESC;

-- all tables and their size (with and without indexes)
SELECT n.nspname AS schema, c.relname AS table, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size, pg_size_pretty(pg_relation_size(c.oid)) AS table_size, pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_size FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_total_relation_size(c.oid) DESC;

-- all indexes and their size
SELECT n.nspname AS schema, t.relname AS table, i.relname AS index, pg_size_pretty(pg_relation_size(i.oid)) AS index_size FROM pg_class i JOIN pg_index ix ON ix.indexrelid = i.oid JOIN pg_class t ON t.oid = ix.indrelid JOIN pg_namespace n ON n.oid = i.relnamespace WHERE i.relkind = 'i' AND n.nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_relation_size(i.oid) 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))::float / NULLIF(sum(heap_blks_hit), 0) AS ratio FROM pg_statio_user_tables;

-- table index usage rates (should not be less than 0.99)
SELECT relname, 100 * idx_scan / NULLIF(seq_scan + idx_scan, 0) 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))::float / NULLIF(sum(idx_blks_hit), 0) AS ratio FROM pg_statio_user_indexes;

-- find missing indexes
SELECT relname AS TableName, to_char(seq_scan, '999,999,999,999') AS TotalSeqScan, to_char(idx_scan, '999,999,999,999') AS TotalIndexScan, to_char(n_live_tup, '999,999,999,999') AS TableRows, pg_size_pretty(pg_relation_size(relname::regclass)) AS TableSize FROM pg_stat_all_tables WHERE schemaname = 'public' AND 50 * seq_scan > idx_scan AND n_live_tup > 10000 AND pg_relation_size(relname::regclass) > 5000000 ORDER BY relname ASC;

-- find invalid indexes
SELECT n.nspname AS schema, c.relname AS index FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid JOIN pg_namespace n ON n.oid = c.relnamespace WHERE i.indisvalid = false;

-- find duplicate indexes (same columns, same table)
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size, array_agg(idx ORDER BY idx) AS indexes FROM (SELECT indexrelid::regclass AS idx, (indrelid::text || E'\n' || indclass::text || E'\n' || indkey::text || E'\n' || coalesce(indexprs::text, '') || E'\n' || coalesce(indpred::text, '')) AS key FROM pg_index) sub GROUP BY key HAVING count(*) > 1 ORDER BY sum(pg_relation_size(idx)) DESC;

-- find unused indexes
WITH table_scans AS (SELECT relid, tables.idx_scan + tables.seq_scan AS all_scans, (tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del) AS writes, pg_relation_size(relid) AS table_size FROM pg_stat_user_tables AS tables), all_writes AS (SELECT sum(writes) AS total_writes FROM table_scans), indexes AS (SELECT idx_stat.relid, idx_stat.indexrelid, idx_stat.schemaname, idx_stat.relname AS tablename, idx_stat.indexrelname AS indexname, idx_stat.idx_scan, pg_relation_size(idx_stat.indexrelid) AS index_bytes, indexdef ~* 'USING btree' AS idx_is_btree FROM pg_stat_user_indexes AS idx_stat JOIN pg_index USING (indexrelid) JOIN pg_indexes AS indexes ON idx_stat.schemaname = indexes.schemaname AND idx_stat.relname = indexes.tablename AND idx_stat.indexrelname = indexes.indexname WHERE pg_index.indisunique = FALSE), index_ratios AS (SELECT schemaname, tablename, indexname, idx_scan, all_scans, round((CASE WHEN all_scans = 0 THEN 0.0::numeric ELSE idx_scan::numeric / all_scans * 100 END), 2) AS index_scan_pct, writes, round((CASE WHEN writes = 0 THEN idx_scan::numeric ELSE idx_scan::numeric / writes END), 2) AS scans_per_write, pg_size_pretty(index_bytes) AS index_size, pg_size_pretty(table_size) AS table_size, idx_is_btree, index_bytes FROM indexes JOIN table_scans USING (relid)), index_groups AS (SELECT 'Never Used Indexes' AS reason, *, 1 AS grp FROM index_ratios WHERE idx_scan = 0 AND idx_is_btree UNION ALL SELECT 'Low Scans, High Writes' AS reason, *, 2 AS grp FROM index_ratios WHERE scans_per_write <= 1 AND index_scan_pct < 10 AND idx_scan > 0 AND writes > 100 AND idx_is_btree UNION ALL SELECT 'Seldom Used Large Indexes' AS reason, *, 3 AS grp FROM index_ratios WHERE index_scan_pct < 5 AND scans_per_write > 1 AND idx_scan > 0 AND idx_is_btree AND index_bytes > 100000000 UNION ALL SELECT 'High-Write Large Non-Btree' AS reason, index_ratios.*, 4 AS grp FROM index_ratios, all_writes WHERE (writes::numeric / (total_writes + 1)) > 0.02 AND NOT idx_is_btree AND index_bytes > 100000000 ORDER BY grp, index_bytes DESC) SELECT reason, schemaname, tablename, indexname, index_scan_pct, scans_per_write, index_size, table_size FROM index_groups;

-- get progress of indexing
SELECT clock_timestamp() - a.xact_start AS duration_so_far, coalesce(a.wait_event_type ||'.'|| a.wait_event, 'false') AS waiting, a.state, p.phase, CASE p.phase WHEN 'initializing' THEN '1 of 12' WHEN 'waiting for writers before build' THEN '2 of 12' WHEN 'building index: scanning table' THEN '3 of 12' WHEN 'building index: sorting live tuples' THEN '4 of 12' WHEN 'building index: loading tuples in tree' THEN '5 of 12' WHEN 'waiting for writers before validation' THEN '6 of 12' WHEN 'index validation: scanning index' THEN '7 of 12' WHEN 'index validation: sorting tuples' THEN '8 of 12' WHEN 'index validation: scanning table' THEN '9 of 12' WHEN 'waiting for old snapshots' THEN '10 of 12' WHEN 'waiting for readers before marking dead' THEN '11 of 12' WHEN 'waiting for readers before dropping' THEN '12 of 12' END AS phase_progress, format('%s(%s of %s)', coalesce(round(100.0 * p.blocks_done / nullif(p.blocks_total, 0), 2)::text || '%', 'not applicable'), p.blocks_done::text, p.blocks_total::text) AS scan_progress, format('%s (%s of %s)', coalesce(round(100.0 * p.tuples_done / nullif(p.tuples_total, 0), 2)::text || '%', 'not applicable'), p.tuples_done::text, p.tuples_total::text) AS tuples_loading_progress, format('%s (%s of %s)', coalesce((100 * p.lockers_done / nullif(p.lockers_total, 0))::text || '%', 'not applicable'), p.lockers_done::text, p.lockers_total::text) AS lockers_progress, format('%s (%s of %s)', coalesce((100 * p.partitions_done / nullif(p.partitions_total, 0))::text || '%', 'not applicable'), p.partitions_done::text, p.partitions_total::text) AS partitions_progress, p.current_locker_pid, trim(trailing ';' from l.query) AS current_locker_query FROM pg_stat_progress_create_index AS p JOIN pg_stat_activity AS a ON a.pid = p.pid LEFT JOIN pg_stat_activity AS l ON l.pid = p.current_locker_pid ORDER BY clock_timestamp() - a.xact_start DESC;

-- delete records in batches
DO $$ DECLARE batch_size INT := 10000; rows_deleted INT := 0; total_rows_deleted INT := 0; BEGIN LOOP WITH del AS (DELETE FROM <tbl> WHERE id IN (SELECT id FROM <tbl> LIMIT batch_size) RETURNING id) SELECT COUNT(*) INTO rows_deleted FROM del; total_rows_deleted := total_rows_deleted + rows_deleted; RAISE NOTICE 'Deleted: % (Total: %)', rows_deleted, total_rows_deleted; EXIT WHEN rows_deleted = 0; COMMIT; END LOOP; END $$;

-- per-table status of logical replication data copy
SELECT s.subname AS subscription_name, c.relnamespace::regnamespace::text AS table_schema, c.relname AS table_name, CASE rel.srsubstate WHEN 'i' THEN 'initialized' WHEN 'd' THEN 'copying' WHEN 's' THEN 'synchronized' WHEN 'r' THEN 'ready' END AS state, rel.srsublsn FROM pg_catalog.pg_subscription s JOIN pg_catalog.pg_subscription_rel rel ON rel.srsubid = s.oid JOIN pg_catalog.pg_class c ON c.oid = rel.srrelid WHERE subdbid = (SELECT oid FROM pg_catalog.pg_database WHERE datname = pg_catalog.current_database());

-- total percent status of logical replication data copy
WITH t AS (SELECT CASE rel.srsubstate WHEN 'i' THEN 'initialized' WHEN 'd' THEN 'copying' WHEN 's' THEN 'synchronized' WHEN 'r' THEN 'ready' END AS state FROM pg_catalog.pg_subscription s JOIN pg_catalog.pg_subscription_rel rel ON rel.srsubid = s.oid WHERE subdbid = (SELECT oid FROM pg_catalog.pg_database WHERE datname = pg_catalog.current_database())) SELECT count(*) FILTER (WHERE state = 'ready') AS num_ready, count(*) FILTER (WHERE state != 'ready') AS num_not_ready, round((count(*) FILTER (WHERE state = 'ready'))::float8 / count(*)::float8 * 100) AS percent_ready FROM t;

-- physical replication lag (run on primary)
SELECT client_addr, application_name, state, sync_state, pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS sent_lag_bytes, pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn) AS write_lag_bytes, pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS flush_lag_bytes, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes, write_lag, flush_lag, replay_lag FROM pg_stat_replication;

-- replication slot status (catch slots holding WAL)
SELECT slot_name, slot_type, active, database, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

-- row count estimates for all tables in a schema
SELECT n.nspname AS schema, c.relname AS table, CASE WHEN c.reltuples >= 1000000 THEN to_char(c.reltuples / 1000000, 'FM999999999.00') || 'M' WHEN c.reltuples >= 1000 THEN to_char(c.reltuples / 1000, 'FM999999999.00') || 'K' ELSE to_char(c.reltuples, 'FM999999999') END AS estimate FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname = 'schema' ORDER BY c.reltuples DESC;

-- sequences approaching their max value (integer overflow risk)
SELECT schemaname, sequencename, last_value, max_value, round(100.0 * last_value / max_value, 2) AS pct_used FROM pg_sequences WHERE last_value IS NOT NULL ORDER BY pct_used DESC NULLS LAST;

-- common queries ordered by total cpu time (requires pg_stat_statements)
SELECT round((pss.total_exec_time + pss.total_plan_time)::numeric, 2) AS total_time, pss.calls, round((pss.mean_exec_time + pss.mean_plan_time)::numeric, 2) AS mean, round((100 * (pss.total_exec_time + pss.total_plan_time) / sum((pss.total_exec_time + pss.total_plan_time)::numeric) OVER ())::numeric, 2) AS cpu_portion_pctg, pss.query FROM pg_stat_statements pss JOIN pg_database pd ON pd.oid = pss.dbid ORDER BY (pss.total_exec_time + pss.total_plan_time) DESC LIMIT 100;

-- common queries ordered by mean execution time
SELECT round(pss.mean_exec_time::numeric, 2) AS mean_ms, pss.calls, round(pss.total_exec_time::numeric, 2) AS total_ms, pss.query FROM pg_stat_statements pss WHERE pss.calls > 50 ORDER BY pss.mean_exec_time DESC LIMIT 50;

-- reset pg_stat_statements (useful before benchmarking)
SELECT pg_stat_statements_reset();

-- table bloat estimate (approximation, no extension required)
WITH constants AS (SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 8 AS ma), bloat_info AS (SELECT ma, bs, schemaname, tablename, (datawidth + (hdr + ma - (CASE WHEN hdr % ma = 0 THEN ma ELSE hdr % ma END)))::numeric AS datahdr, (maxfracsum * (nullhdr + ma - (CASE WHEN nullhdr % ma = 0 THEN ma ELSE nullhdr % ma END))) AS nullhdr2 FROM (SELECT schemaname, tablename, hdr, ma, bs, SUM((1 - null_frac) * avg_width) AS datawidth, MAX(null_frac) AS maxfracsum, hdr + (SELECT 1 + count(*) / 8 FROM pg_stats s2 WHERE null_frac <> 0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename) AS nullhdr FROM pg_stats s, constants GROUP BY 1, 2, 3, 4, 5) AS foo), table_bloat AS (SELECT schemaname, tablename, cc.relpages, bs, CEIL((cc.reltuples * ((datahdr + ma - (CASE WHEN datahdr % ma = 0 THEN ma ELSE datahdr % ma END)) + nullhdr2 + 4)) / (bs - 20::float)) AS otta FROM bloat_info JOIN pg_class cc ON cc.relname = bloat_info.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema' WHERE cc.relkind = 'r') SELECT schemaname, tablename, pg_size_pretty((bs * relpages)::bigint) AS actual_size, pg_size_pretty((bs * otta)::bigint) AS expected_size, CASE WHEN otta = 0 THEN 0.0 ELSE round((relpages / otta::numeric)::numeric, 1) END AS bloat_ratio, pg_size_pretty(((bs * (relpages - otta))::bigint)) AS wasted FROM table_bloat WHERE relpages > 100 ORDER BY (relpages - otta) DESC LIMIT 25;

-- what's hot in shared buffers (requires pg_buffercache extension)
SELECT n.nspname AS schema, c.relname AS relation, c.relkind, count(*) AS buffers, pg_size_pretty(count(*) * 8192) AS buffered, round(100.0 * count(*) / (SELECT setting::int FROM pg_settings WHERE name = 'shared_buffers'), 1) AS pct_of_buffers, round(100.0 * count(*) * 8192 / NULLIF(pg_relation_size(c.oid), 0), 1) AS pct_of_relation FROM pg_buffercache b JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') GROUP BY n.nspname, c.relname, c.relkind, c.oid ORDER BY buffers DESC LIMIT 25;

-- triage: what's going on + what's burning CPU
WITH activity AS ( SELECT count(*) FILTER (WHERE state = 'active') AS active, count(*) FILTER (WHERE state = 'idle') AS idle, count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx, count(*) FILTER (WHERE wait_event IS NOT NULL AND state = 'active') AS waiting, count(*) AS total, (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_conn, max(EXTRACT(epoch FROM clock_timestamp() - query_start)) FILTER (WHERE state = 'active') AS longest_query_s, max(EXTRACT(epoch FROM clock_timestamp() - xact_start)) AS longest_tx_s FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND backend_type = 'client backend' ), blocking AS ( SELECT count(DISTINCT blocked.pid) AS blocked_count FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid)) ), locks AS ( SELECT count(*) FILTER (WHERE NOT granted) AS waiting_locks FROM pg_locks ), cache AS ( SELECT round((sum(heap_blks_hit)::numeric / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0)) * 100, 2) AS heap_hit_pct, (SELECT round((sum(idx_blks_hit)::numeric / NULLIF(sum(idx_blks_hit) + sum(idx_blks_read), 0)) * 100, 2) FROM pg_statio_user_indexes) AS idx_hit_pct FROM pg_statio_user_tables ), db AS (SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size), top_table AS ( SELECT n.nspname || '.' || c.relname || ' (' || pg_size_pretty(pg_total_relation_size(c.oid)) || ')' AS biggest FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 1 ), top_query AS ( SELECT pid || ' (' || round(EXTRACT(epoch FROM clock_timestamp() - query_start))::text || 's): ' || left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS q FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid() AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start ASC NULLS LAST LIMIT 1 ), pgss_window AS ( SELECT GREATEST(EXTRACT(epoch FROM (now() - pg_postmaster_start_time())), 1) AS secs ), pgss_total AS ( SELECT sum(total_exec_time + total_plan_time) AS total_t FROM pg_stat_statements ), top_cpu AS ( SELECT row_number() OVER (ORDER BY total_exec_time + total_plan_time DESC) AS rn, round((100 * (total_exec_time + total_plan_time) / NULLIF((SELECT total_t FROM pgss_total), 0))::numeric, 1) AS pct, calls, round(mean_exec_time::numeric, 2) AS mean_ms, round((calls / (SELECT secs FROM pgss_window))::numeric, 1) AS cps, left(regexp_replace(query, '\s+', ' ', 'g'), 140) AS q FROM pg_stat_statements WHERE query NOT ILIKE '%pg_stat_statements%' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY total_exec_time + total_plan_time DESC LIMIT 5 ), top_seq AS ( SELECT row_number() OVER (ORDER BY seq_tup_read DESC) AS rn, schemaname || '.' || relname AS tbl, seq_scan, seq_tup_read, idx_scan, round(seq_tup_read::numeric / NULLIF(seq_scan, 0)) AS avg_rows, pg_size_pretty(pg_relation_size(relid)) AS size FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_tup_read DESC LIMIT 5 ) SELECT * FROM ( SELECT 1 AS sort, 'activity'::text AS category, 'connections (active/idle/iit/total/max)'::text AS metric, (active||'/'||idle||'/'||idle_in_tx||'/'||total||'/'||max_conn)::text AS value FROM activity UNION ALL SELECT 2, 'activity', 'active waiting on event', waiting::text FROM activity UNION ALL SELECT 3, 'activity', 'longest active query (s)', coalesce(round(longest_query_s)::text, '-') FROM activity UNION ALL SELECT 4, 'activity', 'longest transaction (s)', coalesce(round(longest_tx_s)::text, '-') FROM activity UNION ALL SELECT 5, 'activity', 'blocked sessions', blocked_count::text FROM blocking UNION ALL SELECT 6, 'activity', 'lock requests waiting', waiting_locks::text FROM locks UNION ALL SELECT 7, 'activity', 'oldest active query', coalesce(q, '-') FROM top_query UNION ALL SELECT 8, 'resource', 'database size', db_size FROM db UNION ALL SELECT 9, 'resource', 'heap cache hit %', coalesce(heap_hit_pct::text, '-') FROM cache UNION ALL SELECT 10,'resource', 'index cache hit %', coalesce(idx_hit_pct::text, '-') FROM cache UNION ALL SELECT 11,'resource', 'biggest table', biggest FROM top_table UNION ALL SELECT 11 + rn, 'top_cpu', 'pgss #'||rn||' ('||pct||'% cpu, '||calls||' calls, '||cps||'/s, '||mean_ms||'ms avg)', q FROM top_cpu UNION ALL SELECT 16 + rn, 'seq_scans', 'seq #'||rn||' ('||seq_scan||' scans, '||seq_tup_read||' rows, idx '||idx_scan||', avg '||coalesce(avg_rows::text,'-')||', '||size||')', tbl FROM top_seq ) s ORDER BY sort;