PostgreSQL / 系统课时
建立高更新表的观测基线
建立高更新表的观测基线
高更新表出现膨胀、autovacuum 跟不上或查询变慢时,单看一次 n_dead_tup 很容易误判。本篇建立一套可重复采样的基线,把死元组、表大小、更新速率和 VACUUM 历史放到同一时间轴上。
学习目标
- 从系统视图采集高更新表的核心 MVCC 与 VACUUM 指标。
- 用相邻两次快照计算更新速率、死元组增长和 HOT 更新比例。
- 正确理解
n_dead_tup与 VACUUM 历史字段的边界,避免把估算值当成精确事实。
先固定四类基线信号
一张高更新表至少要同时看四类数据,否则很难区分“写入本来就高”“垃圾版本累积”与“VACUUM 确实失速”。
| 信号 | 推荐来源 | 用途 |
|---|---|---|
| 活元组、死元组 | pg_stat_user_tables |
观察版本累积趋势 |
| 更新与 HOT 更新计数 | pg_stat_user_tables |
计算更新速率与 HOT 比例 |
| 堆、索引、总大小 | 大小函数 | 判断空间增长发生在哪一层 |
| 最近 VACUUM 时间与次数 | pg_stat_user_tables |
判断人工和自动 VACUUM 是否持续发生 |
n_live_tup 与 n_dead_tup 都是统计估算,不是逐行扫描后的精确计数。基线的价值在于同一张表、相同采样方式下的趋势,而不是某一个瞬时数字。
采集一次可重复快照
下面先建立一个轻量快照表。运行前把 public.orders 替换为真实高更新表;快照表建议放在运维 schema,并由监控账号定期写入。
CREATE TABLE IF NOT EXISTS mvcc_table_baseline (
captured_at timestamptz NOT NULL,
relid oid NOT NULL,
schemaname name NOT NULL,
relname name NOT NULL,
n_live_tup bigint NOT NULL,
n_dead_tup bigint NOT NULL,
n_tup_upd bigint NOT NULL,
n_tup_hot_upd bigint NOT NULL,
heap_bytes bigint NOT NULL,
index_bytes bigint NOT NULL,
total_bytes bigint NOT NULL,
last_vacuum timestamptz,
last_autovacuum timestamptz,
vacuum_count bigint NOT NULL,
autovacuum_count bigint NOT NULL
);
SELECT pg_stat_clear_snapshot();
INSERT INTO mvcc_table_baseline
SELECT
clock_timestamp(),
s.relid,
s.schemaname,
s.relname,
s.n_live_tup,
s.n_dead_tup,
s.n_tup_upd,
s.n_tup_hot_upd,
pg_relation_size(s.relid),
pg_indexes_size(s.relid),
pg_total_relation_size(s.relid),
s.last_vacuum,
s.last_autovacuum,
s.vacuum_count,
s.autovacuum_count
FROM pg_stat_user_tables AS s
WHERE s.relid = 'public.orders'::regclass;
SELECT
captured_at,
relname,
n_live_tup,
n_dead_tup,
n_tup_upd,
pg_size_pretty(total_bytes) AS total_size,
last_autovacuum,
autovacuum_count
FROM mvcc_table_baseline
WHERE relid = 'public.orders'::regclass
ORDER BY captured_at DESC
LIMIT 1;预期输出:
captured_at | relname | n_live_tup | n_dead_tup | n_tup_upd | total_size | last_autovacuum | autovacuum_count
-------------+---------+------------+------------+-----------+------------+-----------------+-----------------
<TIME> | orders | <VALUE> | <VALUE> | <VALUE> | <VALUE> | <TIME_OR_NULL> | <VALUE>pg_stat_clear_snapshot() 只清除当前会话缓存的统计快照,不会重置累计计数。这样在长事务或连续查询里,可以尽量避免反复读取同一份已缓存统计。
从两个快照计算更新速率
至少采集两次快照后,再计算相邻样本的增量。采样间隔要覆盖真实业务波动:高峰期可以更密,低峰期可以更疏,但同一张表最好保持固定周期,便于比较。
WITH samples AS (
SELECT
b.*,
lag(captured_at) OVER w AS prev_at,
lag(n_tup_upd) OVER w AS prev_upd,
lag(n_tup_hot_upd) OVER w AS prev_hot_upd,
lag(n_dead_tup) OVER w AS prev_dead,
lag(total_bytes) OVER w AS prev_total_bytes
FROM mvcc_table_baseline AS b
WHERE relid = 'public.orders'::regclass
WINDOW w AS (PARTITION BY relid ORDER BY captured_at)
),
latest AS (
SELECT *
FROM samples
WHERE prev_at IS NOT NULL
ORDER BY captured_at DESC
LIMIT 1
)
SELECT
round(
(n_tup_upd - prev_upd)::numeric
/ NULLIF(EXTRACT(EPOCH FROM captured_at - prev_at), 0),
2
) AS updates_per_sec,
n_dead_tup,
n_dead_tup - prev_dead AS dead_tup_delta,
round(
100.0 * (n_tup_hot_upd - prev_hot_upd)
/ NULLIF(n_tup_upd - prev_upd, 0),
2
) AS hot_update_pct,
pg_size_pretty(total_bytes - prev_total_bytes) AS size_delta
FROM latest;预期输出:
updates_per_sec | n_dead_tup | dead_tup_delta | hot_update_pct | size_delta
-----------------+------------+----------------+----------------+-----------
<VALUE> | <VALUE> | <VALUE> | <VALUE> | <VALUE>如果 n_tup_upd - prev_upd 出现负数,不要继续计算速率,应先检查统计是否被重置;累计计数被清零后,新旧快照已经不在同一个基线区间。size_delta 也可能为负值,例如发生表重写、截断或其他空间回收操作时。
正确读取 VACUUM 历史
last_vacuum 和 vacuum_count 反映手工 VACUUM,last_autovacuum 和 autovacuum_count 反映 autovacuum 的最近发生时间与累计次数。它们能回答“最近有没有跑过”,但不能单独回答“这次跑了多久、处理了多少页、为什么没清干净”。
正在执行的 VACUUM 可以通过 pg_stat_progress_vacuum 观察进度,但它不是历史表;任务结束后记录就消失。如果需要保留每次 VACUUM 的持续时间、触发原因和扫描规模,应把 PostgreSQL 日志或监控系统纳入长期基线。
一条真正可用的高更新表基线,应至少保留“时间戳 + 累计更新计数 + 死元组估算 + 堆/索引大小 + 最近 VACUUM 状态”。有了连续样本,后续才能把“更新变快”“垃圾增长”“VACUUM 频率不足”和“空间异常增长”拆成不同问题,而不是靠一次截图猜原因。