每个死元组的行指针（ItemPointer）占 6 字节
最大死元组数 = maintenance_work_mem / 6

示例：
  maintenance_work_mem = 64MB = 67108864 字节
  最大死元组数 = 67108864 / 6 ≈ 11,184,810 (约1100万)

  如果表有 5000 万死元组，则需要 5 轮索引清理
  每轮索引清理都需要完整扫描所有索引

-- 对于大型表，增大 maintenance_work_mem
-- 注意：此值是每个 VACUUM/autovacuum worker 独立分配的
-- 总内存 = maintenance_work_mem * autovacuum_max_workers
ALTER SYSTEM SET maintenance_work_mem = '512MB';

-- 对于超大表（亿级行），可设为 1GB-2GB
-- 但需确保系统总内存充足
ALTER SYSTEM SET maintenance_work_mem = '1GB';

-- autovacuum 专用内存（PG14+）
-- 如果设置，autovacuum worker 使用此值而非 maintenance_work_mem
ALTER SYSTEM SET autovacuum_work_mem = '1GB';

事务 ID 空间（32位）：

0                    2^31                    2^32-1
|--------|--------|--------|--------|--------|
0       1B       2B       3B       4B       ~4.29B

特殊值：
  0  = InvalidTransactionId (无效)
  1  = BootstrapTransactionId (引导)
  2  = FrozenTransactionId (冻结)
  3  = FirstNormalTransactionId (第一个正常事务)

XID 比较使用模 2^31 的环形空间：

将 32 位 XID 空间视为一个环：
                    2^31 (约21亿)
                       |
              已过去  |  未来
          (对当前可见)| (对当前不可见)
                       |
   当前XID ----->------|
                       |
          (对当前不可见)|  已过去
                       |  (对当前可见)
                       |
                    2^32-1 (约42亿)

对于"当前 XID" C 和"比较 XID" X：
  如果 (X - C) mod 2^32 < 2^31，则 X 在"过去"（已发生）
  如果 (X - C) mod 2^32 >= 2^31，则 X 在"未来"（未发生）

回卷问题图示：

假设当前 XID = 2^31 + 100 (约21亿+100)
某元组的 t_xmin = 100 (很久以前插入)

模运算比较：
  (100 - (2^31 + 100)) mod 2^32
  = (-2^31) mod 2^32
  = 2^31
  >= 2^31

判断结果：t_xmin 在"未来" -> 元组不可见！
实际：t_xmin 在很久以前的"过去" -> 元组应可见

后果：数据"消失"（逻辑上被误判为未来事务插入）

FREEZE 操作前后对比：

冻结前：
  元组: t_xmin=5000000, t_xmax=0, t_infomask=(XMIN_COMMITTED)
  可见性判断: 需要用 5000000 与当前 XID 做模运算比较
  风险: 如果当前 XID - 5000000 > 2^31，元组"消失"

冻结后（PostgreSQL 9.4+，标志位方式）：
  元组: t_xmin=5000000(保留), t_xmax=0, t_infomask=(HEAP_XMIN_FROZEN)
  可见性判断: 检测到冻结标志位 -> 直接返回"可见"
  效果: 永久可见，免疫 XID 回卷

-- 方式1: 手动执行 VACUUM FREEZE
-- 强制冻结所有可冻结的元组（vacuum_freeze_min_age 被视为 0）
VACUUM FREEZE orders;

-- 方式2: autovacuum 自动冻结
-- 当表的 relfrozenxid 年龄超过 vacuum_freeze_table_age 时，
-- autovacuum 执行全表扫描的 VACUUM 并冻结符合条件的元组

-- 方式3: 指定选项的 VACUUM
VACUUM (FREEZE, VERBOSE) orders;

-- 查看当前设置
SHOW vacuum_freeze_min_age;

-- 全局设置
ALTER SYSTEM SET vacuum_freeze_min_age = 50000000;
SELECT pg_reload_conf();

-- 按表设置
ALTER TABLE orders SET (vacuum_freeze_min_age = 10000000);

relfrozenxid 的含义：
  表级"冻结水位线"，所有 t_xmin < relfrozenxid 的元组已被冻结。
  age(relfrozenxid) = 当前 XID - relfrozenxid

VACUUM 的冻结策略：
  age(relfrozenxid) < vacuum_freeze_table_age:
    -> 惰性扫描（利用可见性映射跳过全冻结页）
    -> 不主动推进 relfrozenxid

  age(relfrozenxid) >= vacuum_freeze_table_age:
    -> 急切扫描（扫描所有页面，包括全可见页）
    -> 主动冻结所有年龄 >= vacuum_freeze_min_age 的元组
    -> 推进 relfrozenxid 到当前 OldestXmin

-- 查看各表的 relfrozenxid 年龄
SELECT
    relname,                          -- 表名
    age(relfrozenxid) AS xid_age,     -- XID 年龄
    relfrozenxid::text AS frozen_xid  -- 冻结水位线
FROM pg_class
WHERE relkind = 'r'                   -- 普通表
  AND relfrozenxid IS NOT NULL
ORDER BY xid_age DESC
LIMIT 20;

-- 监控接近回卷风险的表
SELECT
    relname,
    age(relfrozenxid) AS xid_age,
    round(100.0 * age(relfrozenxid) / 200000000, 2) AS pct_to_warning,
    round(100.0 * age(relfrozenxid) / 2147483647, 2) AS pct_to_wraparound
FROM pg_class
WHERE relkind = 'r'
  AND relfrozenxid IS NOT NULL
  AND age(relfrozenxid) > 150000000  -- 超过 1.5 亿的表
ORDER BY xid_age DESC;

-- 查看多事务 ID 年龄
SELECT
    relname,
    age(relminmxid) AS mxid_age,        -- 多事务年龄
    relminmxid::text AS min_mxid        -- 表级最小多事务 ID
FROM pg_class
WHERE relkind = 'r'
  AND relminmxid IS NOT NULL
ORDER BY mxid_age DESC
LIMIT 20;

WARNING:  database "mydb" must be vacuumed within 177013 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.

-- 设置告警：relfrozenxid 年龄超过 1.5 亿时告警
-- 监控脚本
SELECT
    datname,
    age(datfrozenxid) AS db_age,
    round(100.0 * age(datfrozenxid) / 200000000, 2) AS pct_to_force
FROM pg_database
WHERE age(datfrozenxid) > 150000000;

-- 确保无长事务
SET statement_timeout = '300s';          -- 语句超时5分钟
SET idle_in_transaction_session_timeout = '600s';  -- 空闲事务超时10分钟

-- 清理废弃复制槽
SELECT pg_drop_replication_slot(slot_name)
FROM pg_replication_slots
WHERE active = false
  AND xmin IS NOT NULL
  AND age(xmin) > 100000000;

-- 简单膨胀率估算（基于统计信息，速度快但精度低）
SELECT
    schemaname,
    relname,
    n_live_tup,                              -- 活元组数
    n_dead_tup,                              -- 死元组数
    round(
        100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0),
        2
    ) AS dead_pct,                           -- 死元组百分比
    pg_size_pretty(pg_relation_size(relid)) AS table_size,  -- 表大小
    last_autovacuum,                         -- 上次自动清理时间
    last_vacuum                              -- 上次手动清理时间
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_pct DESC
LIMIT 20;

-- 精确膨胀率估算（基于实际页面采样，精度高但耗时）
-- 使用 pgstattuple 扩展
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- 查看单表膨胀详情
SELECT * FROM pgstattuple('orders');
-- 返回字段：
--   table_len:        表总字节数
--   tuple_count:      活元组数
--   tuple_len:        活元组总字节数
--   tuple_percent:    活元组占比
--   dead_tuple_count: 死元组数
--   dead_tuple_len:   死元组总字节数
--   dead_tuple_percent: 死元组占比
--   free_space:       空闲空间字节数
--   free_percent:     空闲空间占比

-- 批量查看所有表膨胀
SELECT
    schemaname,
    relname,
    table_len,
    tuple_percent,
    dead_tuple_percent,
    free_percent,
    table_len - tuple_len AS bloat_bytes,    -- 膨胀字节数
    round(100.0 * (table_len - tuple_len) / table_len, 2) AS bloat_pct
FROM pgstattuple_approx('public.orders'::regclass);

-- 使用 pgstatindex 扩展检测索引膨胀
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- 查看单个索引的膨胀情况
SELECT * FROM pgstatindex('idx_orders_status');
-- 返回字段：
--   version:          版本
--   tree_level:       B-Tree 层级
--   index_size:       索引大小(字节)
--   root_block_no:    根节点块号
--   internal_pages:   内部页数
--   leaf_pages:       叶子页数
--   empty_pages:      空页数
--   deleted_pages:    已删除页数
--   avg_leaf_density: 平均叶子密度(越高越好)
--   leaf_fragmentation: 叶子碎片率(越低越好)

-- 批量检测所有索引膨胀
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,                                -- 索引扫描次数
    idx_tup_read,                            -- 读取的元组数
    idx_tup_fetch                            -- 获取的元组数
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- 完整的表膨胀检测脚本
-- 结合 pgstattuple 与统计信息

-- 步骤1: 基于统计信息的快速筛查
WITH stat_bloat AS (
    SELECT
        schemaname,
        relname,
        n_live_tup,
        n_dead_tup,
        round(
            100.0 * n_dead_tup /
            NULLIF(n_live_tup + n_dead_tup, 0), 2
        ) AS dead_pct,
        pg_relation_size(relid) AS table_bytes,
        relid
    FROM pg_stat_user_tables
    WHERE n_live_tup > 10000  -- 仅检查大于1万行的表
)
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    dead_pct,
    pg_size_pretty(table_bytes) AS table_size,
    last_autovacuum
FROM stat_bloat
WHERE dead_pct > 10  -- 死元组占比超过10%
ORDER BY dead_pct DESC;

-- 步骤2: 对高膨胀表执行精确测量
-- 需要安装 pgstattuple 扩展
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    table_name,
    table_len,
    tuple_len,
    tuple_percent,
    dead_tuple_len,
    dead_tuple_percent,
    free_space,
    free_percent,
    table_len - tuple_len AS bloat_bytes,
    round(100.0 * (table_len - tuple_len) / table_len, 2) AS bloat_pct
FROM (
    SELECT
        relname AS table_name,
        table_len,
        tuple_len,
        tuple_percent,
        dead_tuple_count,
        dead_tuple_len,
        dead_tuple_percent,
        free_space,
        free_percent
    FROM pgstattuple('orders')  -- 替换为实际表名
) t;

-- 步骤3: 索引膨胀检测
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS index_scans,
    idx_tup_read,
    idx_tup_fetch,
    round(
        100.0 * idx_tup_fetch / NULLIF(idx_tup_read, 0), 2
    ) AS fetch_pct  -- 获取率，越低说明索引效率越差
FROM pg_stat_user_indexes
WHERE idx_scan > 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 使用 pgstatindex 检测索引膨胀
SELECT * FROM pgstatindex('idx_orders_status');

-- 关键指标：
--   avg_leaf_density < 50%  -> 严重膨胀
--   leaf_fragmentation > 50% -> 需要重建

-- 方案1: REINDEX（PG12+ 支持并发）
REINDEX INDEX CONCURRENTLY idx_orders_status;

-- 方案2: REINDEX TABLE（重建所有索引）
REINDEX TABLE CONCURRENTLY orders;

-- 方案3: 使用 pg_repack 重建表+索引
SELECT repack_table('orders');

-- 安装扩展
CREATE EXTENSION pg_repack;

-- 对单表执行在线重建
-- 命令行执行（非 SQL）
-- pg_repack -d mydb -t orders -j 2
-- -d: 数据库名
-- -t: 表名
-- -j: 并行 worker 数

-- 对所有表执行在线重建
-- pg_repack -d mydb

-- 仅重建索引
-- pg_repack -d mydb -t orders --index-only

-- 仅重建指定索引
-- pg_repack -d mydb -t orders --index=idx_orders_status

-- pg_squeeze 使用示例
CREATE EXTENSION pg_squeeze;

-- 对表执行 squeeze
SELECT squeeze.table('orders');

-- 查看 squeeze 任务状态
SELECT * FROM squeeze.tasks;

是否需要消除膨胀?
  |
  +-- 否 -> 日常维护: 标准 VACUUM (autovacuum 自动执行)
  |
  +-- 是 -> 膨胀程度如何?
            |
            +-- 轻度 (<30%) -> 调优 autovacuum 参数，等待自动回收
            |
            +-- 中度 (30%-60%) -> 手动 VACUUM + 调优参数
            |
            +-- 重度 (>60%)
                |
                +-- 是否可以停机?
                    |
                    +-- 是 -> VACUUM FULL (低峰期执行)
                    |
                    +-- 否 -> 是否有主键?
                        |
                        +-- 是 -> pg_repack 或 pg_squeeze
                        |
                        +-- 否 -> 评估添加主键 / 接受膨胀
                                  或计划维护窗口执行 VACUUM FULL

-- 错误：业务高峰期执行 VACUUM FULL 导致长时间锁表
VACUUM FULL orders;  -- 阻塞业务数小时

-- 正确：低峰期执行或使用 pg_repack
-- 低峰期
VACUUM FULL orders;

-- 或在线重建
SELECT repack_table('orders');

-- 错误：只关注主表膨胀，忽视 TOAST 表
-- 大文本/字节数据存储在 TOAST 表中，同样会膨胀

-- 正确：检查 TOAST 表的膨胀
SELECT
    c.relname AS main_table,
    t.relname AS toast_table,
    pg_size_pretty(pg_relation_size(c.oid)) AS main_size,
    pg_size_pretty(pg_relation_size(t.oid)) AS toast_size
FROM pg_class c
JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind = 'r'
ORDER BY pg_relation_size(t.oid) DESC;

# 监控磁盘 I/O（每 5 秒刷新）
iostat -x 5

# 关注指标：
#   %util   : 磁盘利用率（VACUUM 期间可能接近 100%）
#   await   : I/O 等待时间（VACUUM 期间可能升高）
#   r/s w/s : 每秒读写次数

-- 查看当前 VACUUM 的 I/O 统计（PG17+）
SELECT
    pid,
    relid::regclass AS table_name,
    command,
    phase,
    buffer_usage_limit,                     -- 缓冲使用限制
    heap_blks_total,                        -- 堆总块数
    heap_blks_scanned,                      -- 已扫描块数
    heap_blks_vacuumed,                     -- 已清理块数
    index_vacuum_count                      -- 索引清理轮数
FROM pg_stat_progress_vacuum;

-- 事务ID回卷风险监控（推荐每5分钟执行一次）
SELECT
    c.relname AS table_name,
    c.relnamespace::regnamespace AS schema_name,
    age(c.relfrozenxid) AS xid_age,                  -- XID年龄
    c.relfrozenxid::text AS frozen_xid,              -- 冻结XID
    round(
        100.0 * age(c.relfrozenxid) / 200000000, 2
    ) AS pct_to_autovacuum,                          -- 距强制autovacuum百分比
    round(
        100.0 * age(c.relfrozenxid) / 2147483647, 2
    ) AS pct_to_wraparound,                          -- 距回卷百分比
    pg_size_pretty(pg_relation_size(c.oid)) AS size  -- 表大小
FROM pg_class c
WHERE c.relkind IN ('r', 't', 'm')                   -- 普通表/TOAST/物化视图
  AND c.relfrozenxid IS NOT NULL
  AND age(c.relfrozenxid) > 100000000                -- 年龄 > 1亿
ORDER BY xid_age DESC;

-- 数据库级 XID 消耗速率监控
SELECT
    datname,
    age(datfrozenxid) AS db_xid_age,                 -- 数据库XID年龄
    datfrozenxid::text AS db_frozen_xid,
    round(
        age(datfrozenxid) / 3600.0, 2
    ) AS xids_per_hour_estimate                      -- 估算每小时XID消耗(需多次采样)
FROM pg_database
ORDER BY db_xid_age DESC;

-- 长事务监控（可能阻止死元组清理）
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    backend_xmin,                                    -- 持有的xmin
    backend_xid,                                     -- 当前事务XID
    xact_start,                                      -- 事务开始时间
    now() - xact_start AS transaction_duration,      -- 事务持续时间
    query_start,
    now() - query_start AS query_duration,
    query,
    state_change
FROM pg_stat_activity
WHERE state != 'idle'
  AND xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'      -- 超过5分钟的事务
ORDER BY xact_start;

-- 复制槽监控（废弃的复制槽会阻止清理）
SELECT
    slot_name,
    plugin,
    slot_type,
    datname,
    temporary,
    active,                                          -- 是否活跃
    active_pid,                                      -- 活跃进程PID
    xmin,                                            -- 持有的xmin
    catalog_xmin,                                    -- 目录xmin
    restart_lsn,                                     -- 重启LSN
    confirmed_flush_lsn,                             -- 确认刷新LSN
    wal_status,                                      -- WAL状态
    safe_wal_size,                                   -- 安全WAL大小
    now() - pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_duration
FROM pg_replication_slots
ORDER BY xmin;

-- 3. XID 回卷风险
SELECT
    count(*) AS tables_at_risk,
    max(age(relfrozenxid)) AS max_xid_age,
    round(100.0 * max(age(relfrozenxid)) / 200000000, 2) AS max_pct_to_force
FROM pg_class
WHERE relkind = 'r'
  AND age(relfrozenxid) > 150000000;

-- 4. 阻塞源
SELECT
    count(*) AS blocking_sessions
FROM pg_stat_activity
WHERE state != 'idle'
  AND xact_start IS NOT NULL
  AND now() - xact_start > interval '10 minutes';

-- 5. 废弃复制槽
SELECT
    count(*) AS inactive_slots
FROM pg_replication_slots
WHERE active = false;

# postgresql.conf 推荐配置
autovacuum = on
autovacuum_max_workers = 3                    # 保持默认
autovacuum_naptime = '1min'                   # 保持默认
autovacuum_vacuum_cost_delay = '2ms'          # 保持默认
autovacuum_vacuum_cost_limit = 200            # 保持默认
maintenance_work_mem = '128MB'                # 适度提升
log_autovacuum_min_duration = 0               # 记录所有autovacuum

# postgresql.conf 推荐配置
autovacuum = on
autovacuum_max_workers = 5                    # 适当增加
autovacuum_naptime = '45s'                    # 略微缩短
autovacuum_vacuum_cost_delay = '2ms'
autovacuum_vacuum_cost_limit = 500            # 提高配额
maintenance_work_mem = '512MB'                # 显著提升
log_autovacuum_min_duration = '1s'            # 仅记录耗时>1s的操作

# 大表按表调优（在SQL中设置）
# ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.05);

# postgresql.conf 推荐配置
autovacuum = on
autovacuum_max_workers = 8                    # 大幅增加
autovacuum_naptime = '30s'                    # 缩短检查间隔
autovacuum_vacuum_cost_delay = '1ms'          # 降低延迟
autovacuum_vacuum_cost_limit = 1000           # 大幅提高配额
autovacuum_work_mem = '1GB'                   # autovacuum专用内存
maintenance_work_mem = '1GB'                  # 手动VACUUM内存
log_autovacuum_min_duration = '5s'            # 仅记录耗时>5s的操作

# 关键大表必须按表调优

ERROR:  database is not accepting commands to avoid wraparound data loss in database "ecommerce"
HINT:  Stop the postmaster and vacuum that database in single-user mode.

-- 步骤1: 检查数据库 XID 年龄（在只读状态下仍可查询）
SELECT
    datname,
    age(datfrozenxid) AS xid_age,
    round(100.0 * age(datfrozenxid) / 2147483647, 2) AS pct_to_wraparound
FROM pg_database
ORDER BY xid_age DESC;

-- 结果：ecommerce 数据库 XID 年龄 = 2,147,400,000，距回卷仅剩 483,647

-- 步骤2: 检查哪张表导致回卷风险
SELECT
    relname,
    age(relfrozenxid) AS xid_age,
    last_autovacuum,
    autovacuum_count
FROM pg_class
WHERE relkind = 'r'
  AND age(relfrozenxid) > 2000000000
ORDER BY xid_age DESC;

-- 结果：表 user_sessions 的 XID 年龄 = 2,147,390,000
--       last_autovacuum = NULL（从未被 autovacuum 处理）
--       autovacuum_count = 0

-- 步骤3: 检查为何 autovacuum 未处理该表
SELECT reloptions FROM pg_class WHERE relname = 'user_sessions';
-- 结果: {autovacuum_enabled=false}  -- autovacuum 被禁用!

-- 步骤4: 检查是否有长事务阻止冻结
SELECT
    pid,
    backend_xmin,
    now() - xact_start AS duration,
    query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY backend_xmin ASC;

-- 结果：发现一个 ETL 进程持有 xmin=100000000 的旧快照，已运行 72 小时

-- 紧急步骤1: 终止长事务
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = <ETL进程PID>;

-- 紧急步骤2: 启用表的 autovacuum
ALTER TABLE user_sessions SET (autovacuum_enabled = true);

-- 紧急步骤3: 手动执行 FREEZE
-- 注意：只读状态下无法执行 VACUUM，需要先解除只读

# 步骤 3（续）：单用户模式执行 FREEZE
# 1) 停止正常实例
pg_ctl stop -D $PGDATA

# 2) 以单用户模式进入问题数据库
postgres --single -D $PGDATA ecommerce

# 3) 在单用户模式提示符中执行（回车两次确认执行）：
VACUUM FREEZE user_sessions;

# 4) 按 Ctrl+D 退出单用户模式，恢复正常启动
pg_ctl start -D $PGDATA

-- 措施1: 全局禁止关闭 autovacuum（通过监控告警）
-- 定期检查是否有表禁用了 autovacuum
SELECT relname, reloptions
FROM pg_class
WHERE relkind = 'r'
  AND reloptions::text LIKE '%autovacuum_enabled=false%';

-- 措施2: 设置 idle_in_transaction_session_timeout
ALTER SYSTEM SET idle_in_transaction_session_timeout = '600s';
-- 自动终止超过10分钟的空闲事务

-- 措施3: 设置 statement_timeout 防止超长查询
ALTER SYSTEM SET statement_timeout = '300s';

-- 措施4: 监控 XID 年龄并设置告警
-- 当任何表 XID 年龄 > 1.5亿时告警
-- 当任何数据库 XID 年龄 > 1.8亿时紧急告警

-- 步骤1: 检查死元组无法清理的原因
VACUUM (VERBOSE) tenant_data;
-- 输出: "897623 row versions cannot be removed yet"
-- 说明存在持有旧 xmin 的对象

-- 步骤2: 检查长事务（无发现）
SELECT pid, backend_xmin, now() - xact_start AS duration
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
  AND now() - xact_start > interval '10 minutes';
-- 结果: 0 行（无长事务）

-- 步骤3: 检查复制槽
SELECT
    slot_name,
    active,
    active_pid,
    xmin,
    catalog_xmin,
    restart_lsn,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots
ORDER BY xmin ASC;

-- 结果：
-- slot_name      | active | xmin      | lag_bytes
-- etl_slot       | f      | 12345678  | 45 GB
-- replica_slot   | t      | 56789012  | 2 MB

-- 步骤1: 确认复制槽确实废弃
-- 检查 active_pid 是否存在（如果存在说明消费者仍连接）
SELECT slot_name, active, active_pid
FROM pg_replication_slots
WHERE slot_name = 'etl_slot';
-- active=f, active_pid=NULL -> 确实废弃

-- 步骤2: 删除废弃的复制槽
SELECT pg_drop_replication_slot('etl_slot');

-- 步骤3: 等待 autovacuum 自动清理（或手动触发）
VACUUM (VERBOSE) tenant_data;
-- 此时 OldestXmin 前进，死元组可被清理

-- 步骤4: 监控磁盘空间回收
SELECT pg_size_pretty(pg_database_size('saas_db'));

-- 措施1: 定期检查废弃复制槽
SELECT slot_name, active, xmin,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag
FROM pg_replication_slots
WHERE active = false;

-- 措施2: 设置复制槽超时（PG13+ max_slot_wal_keep_size）
ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
-- 限制复制槽可保留的 WAL 大小，防止无限堆积

-- 措施3: 监控脚本（集成到告警系统）
-- 每小时检查一次，lag > 1GB 的非活跃槽告警

-- 步骤1: 检查表膨胀情况
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / n_live_tup, 2) AS dead_ratio,
    last_autovacuum,
    autovacuum_count,
    pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE relname = 'leaderboard';

-- 结果：
-- n_live_tup = 1,000,000
-- n_dead_tup = 8,500,000  (死元组是活元组的8.5倍!)
-- dead_ratio = 850%
-- last_autovacuum = 2小时前
-- autovacuum_count = 3 (整天只触发3次)

-- 步骤2: 检查 autovacuum 配置
SHOW autovacuum_vacuum_scale_factor;  -- 0.2 (默认)
SHOW autovacuum_vacuum_cost_delay;    -- 2ms (默认)
SHOW autovacuum_vacuum_cost_limit;    -- 200 (默认)

-- 步骤3: 计算当前触发阈值
-- 阈值 = 50 + 0.2 * 1,000,000 = 200,050
-- 需要累积 20 万死元组才触发，对高频表太迟钝

-- 步骤4: 检查 autovacuum worker 状态
SELECT count(*) FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker';
-- 结果: 3 (全部被其他表占用)

-- 步骤1: 紧急手动清理
VACUUM (VERBOSE, ANALYZE) leaderboard;

-- 步骤2: 调整排行榜表的 autovacuum 参数（激进）
ALTER TABLE leaderboard SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- 1% 即触发
    autovacuum_vacuum_threshold = 5000,       -- 至少5000死元组
    autovacuum_vacuum_cost_delay = '0.1ms',  -- 几乎不休眠
    autovacuum_analyze_scale_factor = 0.02   -- 频繁更新统计
);

-- 步骤3: 增加 autovacuum worker 数量
ALTER SYSTEM SET autovacuum_max_workers = 6;
-- 需重启

-- 步骤4: 增大 maintenance_work_mem 加速索引清理
ALTER SYSTEM SET autovacuum_work_mem = '512MB';

-- 步骤5: 监控效果
-- 观察 24 小时后:
SELECT relname, n_dead_tup, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'leaderboard';
-- n_dead_tup 降至 50000 以下
-- autovacuum 频率提升到每 15 分钟一次

-- 1. 检查 autovacuum 是否启用
SHOW autovacuum;  -- 应为 on

-- 2. 检查 track_counts 是否启用（autovacuum 依赖统计收集）
SHOW track_counts;  -- 应为 on

-- 3. 检查表是否禁用了 autovacuum
SELECT
    relname,
    reloptions
FROM pg_class
WHERE relname = 'orders'
  AND reloptions::text LIKE '%autovacuum_enabled=false%';

-- 4. 检查触发阈值是否设置过高
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    -- 计算当前阈值
    50 + 0.2 * n_live_tup AS default_threshold,
    reloptions
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- 5. 检查 autovacuum worker 是否已耗尽
SELECT count(*) FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker';

-- 6. 检查是否有锁阻塞 autovacuum
SELECT
    pid,
    virtualxid,
    transactionid,
    granted,
    mode,
    query
FROM pg_locks
WHERE virtualxid = 'autovacuum';

-- 7. 检查表是否被其他 VACUUM 占用
SELECT
    pid,
    relid::regclass,
    mode,
    granted
FROM pg_locks
WHERE relation = 'orders'::regclass;

-- 查找持有最旧 xmin 的会话
SELECT
    pid,
    usename,
    application_name,
    state,
    backend_xmin,
    backend_xid,
    xact_start,
    now() - xact_start AS duration,
    query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY backend_xmin ASC
LIMIT 5;

-- 终止长事务
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '30 minutes';

-- 设置 idle_in_transaction_session_timeout 防止未来发生
ALTER SYSTEM SET idle_in_transaction_session_timeout = '600000';  -- 10分钟
SELECT pg_reload_conf();

-- 错误：VACUUM 不能在事务块中执行
BEGIN;
VACUUM orders;  -- ERROR: VACUUM cannot run inside a transaction block
COMMIT;

-- 正确：VACUUM 自动提交执行
VACUUM orders;