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,并由监控账号定期写入。

sql
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;

预期输出:

text
 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() 只清除当前会话缓存的统计快照,不会重置累计计数。这样在长事务或连续查询里,可以尽量避免反复读取同一份已缓存统计。

从两个快照计算更新速率

至少采集两次快照后,再计算相邻样本的增量。采样间隔要覆盖真实业务波动:高峰期可以更密,低峰期可以更疏,但同一张表最好保持固定周期,便于比较。

sql
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;

预期输出:

text
 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 频率不足”和“空间异常增长”拆成不同问题,而不是靠一次截图猜原因。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

建立高更新表的观测基线

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通