PostgreSQL VACUUM 清理机制详解

-- 订单状态每次变更，都产生一个旧版本死元组
update orders set status = 'paid'      where order_id = 12345;
update orders set status = 'shipped'   where order_id = 12345;
update orders set status = 'delivered' where order_id = 12345;

-- 每天删除过期日志
delete from interface_execution_log where start_time < now() - interval '90 days';

-- step 1：导入 1000 万行临时数据
insert into odh_sell_in_inbound select * from external_source;

-- step 2：数据处理完毕，删除临时数据
delete from odh_sell_in_inbound where batch_id = 'xxx';

-- 结果：表大小维持在 1000 万行的体量，内部全是死元组空洞

-- 事务 a 开启但长时间未提交
begin;
select * from lorder_master_info where id = 1;
-- ⏰ 业务处理了很久，事务未提交...

-- 此期间，其他事务产生的所有死元组都无法被 vacuum 清理
-- 因为事务 a 可能还需要读到旧版本数据

-- 每秒上万次库存扣减
update inventory set stock = stock - 1 where product_id = 'hot001';

-- 查看死元组比例，找出需要清理的表
select
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    n_live_tup                                                          as live_tuples,
    n_dead_tup                                                          as dead_tuples,
    round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_ratio,
    last_autovacuum
from pg_stat_user_tables
where n_dead_tup > 10000
order by n_dead_tup desc
limit 20;

-- 清理单表
vacuum orders;

-- 清理 + 更新统计信息（最常用）
vacuum analyze orders;

-- 查看清理详情
vacuum verbose analyze orders;

vacuum full verbose analyze orders;

-- 数据大量变更后，一定要执行 analyze
vacuum analyze lorder_master_info;

-- 或只更新统计信息（不清理）
analyze lorder_master_info;

-- 查看各表的事务年龄（接近 2 亿时需警惕）
select
    relname,
    age(relfrozenxid) as xid_age,
    pg_size_pretty(pg_total_relation_size(oid)) as size
from pg_class
where relkind = 'r'
order by age(relfrozenxid) desc
limit 10;

-- 出现以下告警时，立即执行：
-- warning: database must be vacuumed within 1000000 transactions
vacuum freeze;

-- 统计信息过时 → 优化器估算行数偏差巨大 → 选错 join 方式 → 慢 30 倍
-- vacuum analyze 后 → 统计准确 → 选 hash join → 正常速度
vacuum analyze orders;

-- step 1：找出需要清理的表
select tablename, n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_ratio
from pg_stat_user_tables
where n_dead_tup > 100000
   or (n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0)) > 20
order by n_dead_tup desc;

-- step 2：执行清理（不锁表）
vacuum verbose analyze lorder_master_info;

-- step 3：验证效果
select pg_size_pretty(pg_total_relation_size('lorder_master_info')) as size,
       n_dead_tup, last_vacuum
from pg_stat_user_tables
where tablename = 'lorder_master_info';

-- step 1：确认磁盘空间（需要 2 倍表大小）
select pg_size_pretty(pg_total_relation_size('orders'))     as current_size,
       pg_size_pretty(pg_total_relation_size('orders') * 2) as required_space;

-- step 2：设置超时保护
set statement_timeout = '2h';

-- step 3：执行深度清理（⚠️ 会锁表）
vacuum full verbose analyze orders;

-- 逐个分区清理（推荐）
vacuum verbose analyze lorder_master_info_ap_st_fy2526_q1;
vacuum verbose analyze lorder_master_info_ap_st_fy2526_q2;
vacuum verbose analyze lorder_master_info_ap_st_fy2526_q3;
vacuum verbose analyze lorder_master_info_ap_st_fy2526_q4;

-- 批量清理所有子分区
do $$
declare r record;
begin
    for r in
        select tablename from pg_tables
        where schemaname = 'public'
          and tablename like 'lorder_master_info_%'
    loop
        raise notice '正在清理: %', r.tablename;
        execute 'vacuum verbose analyze ' || quote_ident(r.tablename);
    end loop;
end $$;

-- ✅ 大批量导入后，立即更新统计信息
insert into odh_sell_in_inbound select * from staging_table;
vacuum analyze odh_sell_in_inbound;

-- ✅ 大批量删除后，回收死元组空间
delete from interface_execution_log where start_time < now() - interval '90 days';
vacuum interface_execution_log;

-- ✅ 创建索引后，更新统计信息
create index concurrently idx_xxx on lorder_master_info (geo_type, fiscal_year);
analyze lorder_master_info;

# 安装（ubuntu）
sudo apt-get install postgresql-16-repack

# 在线整理表，不锁表，允许读写
pg_repack -d mydb -t orders
pg_repack -d mydb -t lorder_master_info_ap_st_fy2526_q4