-- 訂單狀態(tài)每次變更，都產(chǎn)生一個(gè)舊版本死元組
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;

-- 每天刪除過(guò)期日志
DELETE FROM interface_execution_log WHERE start_time < now() - interval '90 days';

-- Step 1：導(dǎo)入 1000 萬(wàn)行臨時(shí)數(shù)據(jù)
INSERT INTO odh_sell_in_inbound SELECT * FROM external_source;

-- Step 2：數(shù)據(jù)處理完畢，刪除臨時(shí)數(shù)據(jù)
DELETE FROM odh_sell_in_inbound WHERE batch_id = 'xxx';

-- 結(jié)果：表大小維持在 1000 萬(wàn)行的體量，內(nèi)部全是死元組空洞

-- 事務(wù) A 開(kāi)啟但長(zhǎng)時(shí)間未提交
BEGIN;
SELECT * FROM lorder_master_info WHERE id = 1;
-- ? 業(yè)務(wù)處理了很久，事務(wù)未提交...

-- 此期間，其他事務(wù)產(chǎn)生的所有死元組都無(wú)法被 VACUUM 清理
-- 因?yàn)槭聞?wù) A 可能還需要讀到舊版本數(shù)據(jù)

-- 每秒上萬(wàn)次庫(kù)存扣減
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;

-- 清理 + 更新統(tǒng)計(jì)信息（最常用）
VACUUM ANALYZE orders;

-- 查看清理詳情
VACUUM VERBOSE ANALYZE orders;

VACUUM FULL VERBOSE ANALYZE orders;

-- 數(shù)據(jù)大量變更后，一定要執(zhí)行 ANALYZE
VACUUM ANALYZE lorder_master_info;

-- 或只更新統(tǒng)計(jì)信息（不清理）
ANALYZE lorder_master_info;

-- 查看各表的事務(wù)年齡（接近 2 億時(shí)需警惕）
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;

-- 出現(xiàn)以下告警時(shí)，立即執(zhí)行：
-- WARNING: database must be vacuumed within 1000000 transactions
VACUUM FREEZE;

-- 統(tǒng)計(jì)信息過(guò)時(shí) → 優(yōu)化器估算行數(shù)偏差巨大 → 選錯(cuò) Join 方式 → 慢 30 倍
-- VACUUM ANALYZE 后 → 統(tǒng)計(jì)準(zhǔn)確 → 選 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：執(zhí)行清理（不鎖表）
VACUUM VERBOSE ANALYZE lorder_master_info;

-- Step 3：驗(yàn)證效果
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：確認(rèn)磁盤(pán)空間（需要 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：設(shè)置超時(shí)保護(hù)
SET statement_timeout = '2h';

-- Step 3：執(zhí)行深度清理（?? 會(huì)鎖表）
VACUUM FULL VERBOSE ANALYZE orders;

-- 逐個(gè)分區(qū)清理（推薦）
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;

-- 批量清理所有子分區(qū)
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 $$;

-- ? 大批量導(dǎo)入后，立即更新統(tǒng)計(jì)信息
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;

-- ? 創(chuàng)建索引后，更新統(tǒng)計(jì)信息
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

# 在線整理表，不鎖表，允許讀寫(xiě)
pg_repack -d mydb -t orders
pg_repack -d mydb -t lorder_master_info_ap_st_fy2526_q4