-- 常规VACUUM（推荐每日执行）
VACUUM ANALYZE;

-- 针对特定表的VACUUM
VACUUM ANALYZE your_busiest_table;

-- 彻底的VACUUM（建议每周或每月）
VACUUM FULL VERBOSE;

-- 更新统计信息（查询优化器需要）
ANALYZE VERBOSE;

-- 针对特定表
ANALYZE VERBOSE your_busiest_table;

-- 重建碎片化严重的索引
REINDEX INDEX your_index_name;
REINDEX TABLE your_table_name;

-- 并发重建（PostgreSQL 12+）
REINDEX INDEX CONCURRENTLY your_index_name;

-- 自动VACUUM通常足够，但可以补充
VACUUM ANALYZE;

-- 更彻底的维护
VACUUM FULL ANALYZE;
CHECKPOINT;

-- 全面维护
REINDEX DATABASE your_database;
VACUUM FULL VERBOSE;
ANALYZE VERBOSE;

# 针对频繁读写数据库的优化设置
autovacuum = on
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

-- 安装pg_cron
CREATE EXTENSION pg_cron;

-- 每天凌晨3点执行VACUUM
SELECT cron.schedule('0 3 * * *', 'VACUUM ANALYZE');

-- 每周日凌晨2点执行维护
SELECT cron.schedule('0 2 * * 0', 'VACUUM FULL ANALYZE');

# 每天执行维护
0 2 * * * psql -d your_db -c "VACUUM ANALYZE"

# 每周执行全面维护
0 3 * * 0 psql -d your_db -c "VACUUM FULL ANALYZE; REINDEX DATABASE your_db"

-- 检查表膨胀情况
SELECT schemaname, tablename, 
       n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric/n_live_tup::numeric*100, 2) as dead_ratio
FROM pg_stat_user_tables
ORDER BY dead_ratio DESC;

-- 检查索引状态
SELECT schemaname, tablename, indexname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes;

-- 查看autovacuum统计信息
SELECT relname, last_vacuum, last_autovacuum, 
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables;

-- 针对热点表增加维护频率
ALTER TABLE your_busy_table SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02
);

-- 监控长事务和锁
SELECT * FROM pg_locks WHERE granted = false;
SELECT * FROM pg_stat_activity WHERE state <> 'idle';