1
-- 安装扩展
2
CREATE EXTENSION IF NOT EXISTS pageinspect;
3

4
-- 假设表 test 的数据在 0 号数据块
5
SELECT lp_off, t_xmin, t_xmax, t_ctid
6
FROM heap_page_items(get_raw_page('test', 0));
7

8
-- 示例输出：
9
--  lp_off | t_xmin | t_xmax | t_ctid
10
-- --------+--------+--------+--------
11
--     816 |    100 |      0 | (0,1)    -- 由事务100插入，未被修改
12
--     800 |    100 |    102 | (0,3)    -- 由事务100插入，被事务102更新，新版本在(0,3)
13
--     784 |    102 |      0 | (0,3)    -- 由事务102插入（更新的新版本）

1
// 简化的快照结构
2
typedef struct SnapshotData {
3
    TransactionId xmin;   // 当前所有活跃事务中最小的事务ID
4
    TransactionId xmax;   // 下一个将分配的事务ID
5
    TransactionId *xip;   // 活跃事务ID列表（xmin ~ xmax 之间）
6
    uint32      xcnt;     // 活跃事务数量
7
} SnapshotData;

1
-- 事务 T1：插入一行
2
INSERT INTO accounts (id, balance) VALUES (1, 1000);
3
-- 堆表中产生元组：(t_xmin=100, t_xmax=0, balance=1000)
4

5
-- 事务 T2：更新该行
6
UPDATE accounts SET balance = 2000 WHERE id = 1;
7
-- 堆表中现在有两个元组：
8
--   旧版本：(t_xmin=100, t_xmax=102, t_ctid=(0,2))  <- Dead Tuple
9
--   新版本：(t_xmin=102, t_xmax=0, balance=2000)     <- 当前可见版本
10

11
-- 事务 T3：删除该行
12
DELETE FROM accounts WHERE id = 1;
13
-- 新版本也变成 Dead Tuple：
14
--   (t_xmin=102, t_xmax=103, t_ctid=(0,2))  <- Dead Tuple

1
-- 查看 AutoVacuum 相关参数
2
SHOW autovacuum_vacuum_threshold;    -- 默认 50
3
SHOW autovacuum_vacuum_scale_factor; -- 默认 0.2（20%）
4
SHOW autovacuum_analyze_threshold;   -- 默认 50
5
SHOW autovacuum_analyze_scale_factor;-- 默认 0.1（10%）
6

7
-- VACUUM 触发条件：
8
-- dead_tuples > autovacuum_vacuum_threshold +
9
--               autovacuum_vacuum_scale_factor * reltuples
10
-- 即：死元组数 > 50 + 20% × 表行数
11

12
-- ANALYZE 触发条件：
13
-- changed_tuples > autovacuum_analyze_threshold +
14
--                  autovacuum_analyze_scale_factor * reltuples
15
-- 即：变更行数 > 50 + 10% × 表行数

1
-- 对频繁更新的大表设置更激进的 VACUUM 策略
2
ALTER TABLE hot_table SET (
3
    autovacuum_vacuum_scale_factor = 0.05,   -- 5% 死元组即触发
4
    autovacuum_analyze_scale_factor = 0.02    -- 2% 变更即更新统计信息
5
);

1
-- 日常维护：使用普通 VACUUM（不阻塞）
2
VACUUM accounts;
3

4
-- 紧急修复：使用 VACUUM FULL（阻塞 + 重写）
5
VACUUM FULL accounts;
6

7
-- 更好的替代方案：pg_repack（在线重建，不阻塞）
8
-- 需要安装扩展：CREATE EXTENSION pg_repack;
9
-- pg_repack -d mydb -t accounts;

1
-- 核心 VACUUM 调优参数
2
autovacuum_max_workers = 4           -- AutoVacuum 工作进程数（默认 3）
3
autovacuum_naptime = 30s             -- 检查间隔（默认 1min）
4
autovacuum_vacuum_cost_limit = 2000  -- 每轮 I/O 限额（默认 -1，表示继承 vacuum_cost_limit 的值）
5
autovacuum_vacuum_cost_delay = 2ms   -- 达到限额后的休眠时间（默认 2ms，PG11 及更早为 20ms）
6

7
-- 手动 VACUUM 的 I/O 节流
8
vacuum_cost_limit = 200              -- 每轮 I/O 限额
9
vacuum_cost_delay = 0                -- 默认不休眠（手动执行优先级高）

1
-- 查看表的 HOT 更新统计
2
SELECT n_tup_ins, n_tup_upd, n_tup_hot_upd,
3
       round(n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0) * 100, 2) AS hot_ratio
4
FROM pg_stat_user_tables
5
WHERE relname = 'accounts';
6

7
-- 示例输出：
8
--  n_tup_ins | n_tup_upd | n_tup_hot_upd | hot_ratio
9
-- -----------+-----------+---------------+-----------
10
--      10000 |      8000 |          7200 |     90.00

1
-- 设置 fillfactor 为 80%，预留 20% 页空间给 HOT 更新
2
ALTER TABLE accounts SET (fillfactor = 80);
3
VACUUM FULL accounts;  -- 需要重建表使 fillfactor 生效

1
-- 查看各表的死元组情况
2
SELECT relname,
3
       n_live_tup,
4
       n_dead_tup,
5
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_ratio
6
FROM pg_stat_user_tables
7
ORDER BY dead_ratio DESC;
8

9
-- 示例输出：
10
--  relname   | n_live_tup | n_dead_tup | dead_ratio
11
-- -----------+------------+------------+------------
12
--  orders    |    1000000 |     350000 |      35.00  <- 膨胀严重
13
--  users     |     500000 |       2000 |       0.40

1
-- 查找运行时间最长的事务
2
SELECT pid, state, xact_start,
3
       now() - xact_start AS duration,
4
       query
5
FROM pg_stat_activity
6
WHERE state != 'idle'
7
ORDER BY duration DESC;
8

9
-- 查找阻止 VACUUM 的最小事务 ID（horizon）
10
-- 如果这个值很旧，说明有长事务卡住了 VACUUM

1
-- 对高频更新的表，设置更激进的 AutoVacuum 参数
2
ALTER TABLE orders SET (
3
    autovacuum_vacuum_scale_factor = 0.05,   -- 5% 死元组即触发
4
    autovacuum_vacuum_threshold = 1000,       -- 基础阈值提高到 1000
5
    autovacuum_vacuum_cost_limit = 1000,      -- 提高该表的 I/O 限额
6
    autovacuum_vacuum_cost_delay = 1          -- 降低该表的休眠延迟
7
);

1
-- 安装扩展
2
CREATE EXTENSION pg_repack;
3

4
-- 命令行执行
5
-- pg_repack -d mydb -t orders

1
-- 查看各索引的大小和膨胀情况
2
SELECT schemaname, relname, indexrelname,
3
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
4
       idx_scan AS index_scans
5
FROM pg_stat_user_indexes
6
ORDER BY pg_relation_size(indexrelid) DESC;

1
-- 并发重建索引（推荐）
2
REINDEX INDEX CONCURRENTLY idx_orders_status;
3

4
-- 重建某表所有索引
5
REINDEX TABLE CONCURRENTLY orders;

1
CREATE TABLE hot_test (
2
    id SERIAL PRIMARY KEY,
3
    status TEXT,
4
    updated_at TIMESTAMP DEFAULT NOW()
5
);
6

7
INSERT INTO hot_test (status) SELECT 'pending' FROM generate_series(1, 100);
8

9
-- 更新所有行
10
UPDATE hot_test SET status = 'shipped', updated_at = NOW();
11

12
-- 查看 HOT 更新统计
13
SELECT relname, n_dead_tup, n_live_tup, n_tup_upd, n_tup_hot_upd
14
FROM pg_stat_user_tables WHERE relname = 'hot_test';

1
 relname  | n_dead_tup | n_live_tup | n_tup_upd | n_tup_hot_upd
2
----------+------------+------------+-----------+---------------
3
 hot_test |          0 |        100 |       100 |            57

1
VACUUM hot_test;
2

3
SELECT relname, n_dead_tup, n_live_tup
4
FROM pg_stat_user_tables WHERE relname = 'hot_test';

1
 relname  | n_dead_tup | n_live_tup
2
----------+------------+------------
3
 hot_test |          0 |        100

1
SELECT pg_size_pretty(pg_relation_size('hot_test')) as size;

1
 size
2
-------
3
 16 kB