PostgreSQL定时索引维护：如何批量重建表中碎片率超60%的索引？

批量重建脚本 #

以下PL/pgSQL脚本会自动遍历指定表中碎片率超过60%的索引，并执行重建操作：

DO $$
DECLARE
    rec record;
BEGIN
    -- 遍历符合条件的索引
    FOR rec IN
        SELECT i.indexrelid::regclass AS index_name
        FROM pg_index AS i
        CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s
        WHERE i.indrelid = 'my_schema."my_table"'::regclass
          AND s.leaf_fragmentation > 60
    LOOP
        -- 使用quote_ident处理带特殊字符的标识符，避免语法错误
        EXECUTE 'REINDEX INDEX ' || quote_ident(split_part(rec.index_name::text, '.', 1)) || '.' || quote_ident(split_part(rec.index_name::text, '.', 2));
        -- 输出重建日志，用于验证执行结果
        RAISE NOTICE '已重建索引: %', rec.index_name;
    END LOOP;
END $$;

脚本说明 #

• 用FOR ... IN循环遍历查询结果，批量处理所有符合条件的索引
• quote_ident函数用于处理包含特殊字符（如空格、大写字母）的模式名或索引名，避免SQL语法错误
• 仅保留重建所需的索引名字段，简化查询逻辑
• RAISE NOTICE会在执行时输出重建的索引名称，方便排查执行情况

适配并发重建（减少锁表影响） #

如果需要避免重建时锁表影响业务，可以使用REINDEX INDEX CONCURRENTLY，但该命令不能在事务块中执行，因此需要改为函数形式：

-- 创建重建函数
CREATE OR REPLACE FUNCTION reindex_high_frag_indexes()
RETURNS void AS $$
DECLARE
    rec record;
BEGIN
    FOR rec IN
        SELECT i.indexrelid::regclass AS index_name
        FROM pg_index AS i
        CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s
        WHERE i.indrelid = 'my_schema."my_table"'::regclass
          AND s.leaf_fragmentation > 60
    LOOP
        EXECUTE 'REINDEX INDEX CONCURRENTLY ' || quote_ident(split_part(rec.index_name::text, '.', 1)) || '.' || quote_ident(split_part(rec.index_name::text, '.', 2));
        RAISE NOTICE '已并发重建索引: %', rec.index_name;
    END LOOP;
END $$ LANGUAGE plpgsql;

设置定时任务 #

借助pg_cron扩展可以实现定时执行重建操作：

1. 先安装pg_cron扩展（需超级用户权限）：

CREATE EXTENSION IF NOT EXISTS pg_cron;

2. 创建定时任务（示例为每周日凌晨2点执行）：

-- 普通重建定时任务
SELECT cron.schedule(
    'reindex-high-frag-indexes',
    '0 2 * * 0', -- cron表达式：周日凌晨2点
    $$
    DO $$
    DECLARE
        rec record;
    BEGIN
        FOR rec IN
            SELECT i.indexrelid::regclass AS index_name
            FROM pg_index AS i
            CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s
            WHERE i.indrelid = 'my_schema."my_table"'::regclass
              AND s.leaf_fragmentation > 60
        LOOP
            EXECUTE 'REINDEX INDEX ' || quote_ident(split_part(rec.index_name::text, '.', 1)) || '.' || quote_ident(split_part(rec.index_name::text, '.', 2));
            RAISE NOTICE '已重建索引: %', rec.index_name;
        END LOOP;
    END $$;
    $$
);

-- 并发重建定时任务（调用上述函数）
SELECT cron.schedule(
    'reindex-high-frag-indexes-concurrently',
    '0 2 * * 0',
    'SELECT reindex_high_frag_indexes();'
);

前置要求 #

• 确保执行用户拥有REINDEX权限，以及访问系统表的权限
• pgstatindex函数依赖pgstattuple扩展，若未安装需先执行：CREATE EXTENSION IF NOT EXISTS pgstattuple;

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