PostgreSQL 一键巡检 SQL 合集

一、筛查数据表膨胀SELECT schemaname, relname AS table_name, n_live_tup AS live_rows, n_dead_tup AS dead_rows, ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup,0)*100,2) AS dead_tuple_pct, pg_size_pretty(pg_total_relation_size(relid)) AS table_total_size FROM pg_stat_user_tables WHERE n_dead_tup 10000 ORDER BY dead_tuple_pct DESC;使用说明： 重点关注死元组占比较高的表，这类表极易持续膨胀，需要及时执行VACUUM ANALYZE。 如需精准计算膨胀率，可以安装pgstattuple扩展精细化检测。二、查找阻塞数据库的长事务长空闲事务是Autovacuum失效、表疯狂膨胀的头号诱因，这条SQL可以快速捕获风险会话。SELECT pid, usename, datname, now() - xact_start AS transaction_duration, state, query FROM pg_stat_activity WHERE now() - xact_start '3min'::interval AND