PostgreSQL VACUUM INDEX_CLEANUP参数使用疑问及测试求助

1. 参数作用级别 #

• 表级：执行VACUUM命令时通过INDEX_CLEANUP=ON/OFF显式指定，作用于当前操作的表及其所有关联索引，这是最常用的方式。
• 数据库/会话级：可设置配置参数vacuum_index_cleanup（默认值为on），该参数会影响当前会话或整个数据库中所有默认的VACUUM操作。
• 索引级：不支持单独针对某一个索引启用该参数，因为VACUUM是表级操作，会统一处理表关联的所有索引。

2. 运行前是否需要禁用其他设置 #

不需要专门禁用其他设置，但需注意两点：

• 如果当前会话已通过SET vacuum_index_cleanup = off修改了默认行为，执行VACUUM (INDEX_CLEANUP=ON)会覆盖会话级配置，优先使用命令显式指定的参数。
• 确保目标表未被排他锁（如LOCK TABLE ... IN EXCLUSIVE MODE）占用，否则VACUUM无法执行，但这不属于“禁用设置”范畴，只是操作前的状态检查。

3. 测试无预期效果的原因、参数收益及使用场景 #

你的测试无效原因 #

你的测试流程中，第一次执行VACUUM (VERBOSE, ANALYZE)时，默认INDEX_CLEANUP=ON，已经完成了索引死元组的清理工作。后续再执行VACUUM (INDEX_CLEANUP=ON)时，索引中已无待清理的死条目，自然看不到索引大小变化。

正确的测试流程应该是：

1. 完成UPDATE操作后，先执行VACUUM (VERBOSE, ANALYZE, INDEX_CLEANUP=OFF)，跳过索引清理；
2. 此时查看索引大小，会发现明显膨胀；
3. 再执行VACUUM (VERBOSE, ANALYZE, INDEX_CLEANUP=ON)，就能看到索引大小下降的效果。

参数作用与收益 #

INDEX_CLEANUP控制VACUUM是否清理索引中的死元组条目（由UPDATE/DELETE操作产生，原索引条目不再指向有效数据）：

• 启用时：彻底清理索引死条目，减小索引体积，提升索引扫描效率，降低磁盘IO和内存占用。
• 禁用时：跳过索引清理，加快VACUUM执行速度，但会导致索引持续膨胀，长期会显著降低查询性能。

显式使用场景 #

• 临时应急：当数据库负载极高，需要快速完成VACUUM释放表空间时，临时用INDEX_CLEANUP=OFF加速操作，后续在负载低谷期再用INDEX_CLEANUP=ON补做索引清理。
• 索引膨胀修复：当监控发现某张表的索引膨胀率过高（可通过pg_stat_user_indexes、pg_indexes_size等工具排查），显式执行带INDEX_CLEANUP=ON的VACUUM来修复膨胀。
• 覆盖默认配置：如果数据库级设置了vacuum_index_cleanup=off，针对特定需要清理索引的表，显式指定INDEX_CLEANUP=ON执行VACUUM。

附修正后的测试代码示例：

CREATE TABLE demo_table (
    id SERIAL PRIMARY KEY,
    data TEXT
);

INSERT INTO demo_table (data)
SELECT 'Some test data ' || generate_series(1, 10000000);

CREATE INDEX idx_demo_table_data ON demo_table (data);

UPDATE demo_table SET data = 'Updated test data' WHERE id % 2 = 0;

-- 第一步：跳过索引清理，快速执行VACUUM
VACUUM (VERBOSE, ANALYZE, INDEX_CLEANUP=OFF) demo_table;

-- 查看此时的索引状态（可见明显膨胀的索引大小）
SELECT 
    indexrelname AS index_name, 
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, 
    idx_scan AS index_scans, 
    idx_tup_read AS tuples_read, 
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes WHERE indexrelname = 'idx_demo_table_data';

-- 第二步：执行带索引清理的VACUUM
VACUUM (VERBOSE, ANALYZE, INDEX_CLEANUP=ON) demo_table;

-- 再次查看索引状态（索引大小应明显减小）
SELECT 
    indexrelname AS index_name, 
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, 
    idx_scan AS index_scans, 
    idx_tup_read AS tuples_read, 
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes WHERE indexrelname = 'idx_demo_table_data';

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