如何找出数据库中需要执行VACUUM FULL的所有表？

前提条件 #

确保已安装pgstattuple扩展（该扩展提供表级统计函数）：

CREATE EXTENSION IF NOT EXISTS pgstattuple;

方法一：用psql客户端直接批量执行（无需创建函数） #

在psql中执行以下语句，利用\gexec自动执行生成的查询，先将结果存入临时表再按死元组比例排序：

-- 创建临时表存储统计结果
CREATE TEMP TABLE table_stats (
    table_name text,
    tuple_percent numeric,
    dead_tuple_count bigint,
    dead_tuple_percent numeric,
    free_space bigint,
    free_percent numeric
);

-- 生成插入语句并自动执行
SELECT format(
    'INSERT INTO table_stats SELECT %L, tuple_percent, dead_tuple_count, dead_tuple_percent, free_space, free_percent FROM pgstattuple(%L)',
    schemaname || '.' || tablename,
    schemaname || '.' || tablename
)
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') -- 排除系统表
\gexec

-- 查询最终结果，按死元组比例降序排序
SELECT * FROM table_stats ORDER BY dead_tuple_percent DESC;

方法二：创建PL/pgSQL函数复用查询逻辑 #

如果需要多次执行统计，可创建一个复用函数：

CREATE OR REPLACE FUNCTION get_all_table_stats()
RETURNS TABLE (
    table_name text,
    tuple_percent numeric,
    dead_tuple_count bigint,
    dead_tuple_percent numeric,
    free_space bigint,
    free_percent numeric
) AS $$
DECLARE
    table_rec record;
BEGIN
    -- 遍历所有用户自定义表
    FOR table_rec IN 
        SELECT schemaname || '.' || tablename AS full_table_name 
        FROM pg_tables 
        WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
    LOOP
        -- 动态执行pgstattuple并返回统计数据
        RETURN QUERY EXECUTE format(
            'SELECT %L AS table_name, tuple_percent, dead_tuple_count, dead_tuple_percent, free_space, free_percent FROM pgstattuple(%L)',
            table_rec.full_table_name,
            table_rec.full_table_name
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

调用函数并排序：

SELECT * FROM get_all_table_stats() ORDER BY dead_tuple_percent DESC;

关键注意事项 #

• pgstattuple会扫描目标表的全量数据，大表执行时会消耗较多IO和CPU，建议在业务低峰期运行
• 死元组比例（dead_tuple_percent）仅为参考指标，是否需要执行VACUUM FULL需结合以下因素判断：
  • 表的空闲空间占比（free_percent）
  • 表的总大小（可结合pg_total_relation_size补充查询）
  • 业务读写模式：若表后续仍有大量写入，普通VACUUM即可回收空间；仅当空间浪费严重且短期内无大量写入时，VACUUM FULL才更有意义
• VACUUM FULL会获取表的排他锁，执行期间表无法进行读写操作，务必谨慎操作

内容的提问来源于stack exchange，提问作者fujidaon