PostgreSQL / 系统课时

建立容量与增长基线

建立容量与增长基线

归档之前先回答两个问题:大表当前占了多少空间,以及它正在以多快的速度增长。本课用 PostgreSQL 自带统计函数拆分表、索引和 TOAST 占用,并把周期性快照转成增长基线,为后续保留周期、归档窗口和容量预测提供数据依据。

学习目标

  • 使用 PostgreSQL 函数拆分测量表、索引与 TOAST 空间
  • 建立可重复采样的容量快照并计算增长速度
  • 区分业务数据增长与索引、TOAST 等附加空间成本

拆开看大表的空间组成

pg_total_relation_size() 适合快速查看一个表及其相关对象的总占用,但容量治理不能只看总数。实际至少要区分表本体、索引和 TOAST,因为三者的增长原因与治理方式不同。

下面以 public.event_history 为目标表。pg_relation_size() 返回主关系本体大小,pg_indexes_size() 返回该表全部索引大小;若存在 TOAST 关系,则通过 reltoastrelid 找到它,并统计 TOAST 表及其索引的总占用。

sql
SELECT
    c.oid::regclass AS relation_name,
    pg_relation_size(c.oid) AS table_bytes,
    pg_indexes_size(c.oid) AS index_bytes,
    COALESCE(pg_total_relation_size(t.oid), 0) AS toast_total_bytes,
    pg_total_relation_size(c.oid) AS total_bytes
FROM pg_class AS c
LEFT JOIN pg_class AS t
  ON t.oid = NULLIF(c.reltoastrelid, 0)
WHERE c.oid = 'public.event_history'::regclass;

预期结果

text
 relation_name | table_bytes | index_bytes | toast_total_bytes | total_bytes
---------------+-------------+-------------+-------------------+------------
 event_history | <VALUE>     | <VALUE>     | <VALUE>           | <VALUE>
(1 row)

这里必须调用 PostgreSQL 的 pg_indexes_size() 计算该表全部索引占用。toast_total_bytes 使用对应 TOAST 关系的 pg_total_relation_size(),因此包含 TOAST 表及其索引;没有 TOAST 关系时返回 0。

不要要求 total_bytes 与前三项简单相加后严格相等。pg_total_relation_size() 统计的是目标表全部磁盘空间,包括主 fork 之外的其他 fork;而单独统计的 TOAST 总大小又属于目标表总大小的一部分。容量基线的重点是稳定观察各指标随时间的变化,而不是手工拼出一个等式。

建立可持续采样的容量快照

单次查询只能回答“现在多大”,增长速度至少需要两个时间点。容量快照还应保存估算行数和原始字节值,而不是只保存 pg_size_pretty() 生成的展示文本。

下面建立轻量历史表,并完成一次采样。reltuples 是规划器维护的估算行数,适合趋势分析,不会像精确 COUNT(*) 那样扫描整张大表。

sql
CREATE TABLE IF NOT EXISTS ops_relation_size_history (
    sampled_at timestamptz NOT NULL DEFAULT now(),
    relation_name text NOT NULL,
    estimated_rows bigint,
    table_bytes bigint NOT NULL,
    index_bytes bigint NOT NULL,
    total_bytes bigint NOT NULL
);

INSERT INTO ops_relation_size_history (
    relation_name,
    estimated_rows,
    table_bytes,
    index_bytes,
    total_bytes
)
SELECT
    c.oid::regclass::text,
    c.reltuples::bigint,
    pg_relation_size(c.oid),
    pg_indexes_size(c.oid),
    pg_total_relation_size(c.oid)
FROM pg_class AS c
WHERE c.oid = 'public.event_history'::regclass;

预期结果

text
CREATE TABLE
INSERT 0 1

如果历史表已经存在,客户端可能额外显示“relation already exists”之类的提示;关键验证点是本次 INSERT 成功写入 1 行。实际值和提示格式可能因客户端而异。

若近期发生大量写入而统计信息明显陈旧,应先检查自动分析是否正常,必要时在合适窗口执行 ANALYZE。采样频率则应与增长速度匹配:缓慢增长的大表按天采样通常足够,快速写入的大表可以缩短到小时级。

折算单位时间增长速度

有了连续样本后,可以用窗口函数比较相邻采样点,再根据实际时间间隔折算近似日增长量。这样即使采样没有严格间隔 24 小时,也能得到可比较的趋势指标。

sql
WITH samples AS (
    SELECT
        sampled_at,
        total_bytes,
        lag(sampled_at) OVER (ORDER BY sampled_at) AS previous_at,
        lag(total_bytes) OVER (ORDER BY sampled_at) AS previous_bytes
    FROM ops_relation_size_history
    WHERE relation_name = 'event_history'
)
SELECT
    sampled_at,
    total_bytes - previous_bytes AS growth_bytes,
    round(
        (total_bytes - previous_bytes)::numeric
        / NULLIF(EXTRACT(EPOCH FROM sampled_at - previous_at), 0)
        * 86400
    ) AS estimated_bytes_per_day
FROM samples
WHERE previous_at IS NOT NULL
ORDER BY sampled_at;

预期结果

text
          sampled_at           | growth_bytes | estimated_bytes_per_day
-------------------------------+--------------+------------------------
 <TIME>                        | <VALUE>      | <VALUE>
 ...                           | ...          | ...

这里得到的是两个采样点之间的净空间变化折算值,不应直接理解为稳定的业务写入速率。批量导入、删除、VACUUM、索引创建或重建都可能造成短期突变,因此容量预测应观察多个周期,而不是只取最近一次差值。

从基线判断增长来自哪里

总空间上涨并不等于业务数据按同样速度增长。判断异常时应把总字节、表本体、索引以及估算行数放在一起看。

如果 total_bytes 与 estimated_rows 基本同比增长,通常说明主要来自新增数据;如果索引增长明显快于表本体,应检查是否新增索引、索引结构是否膨胀或数据分布是否发生变化;如果表本体快速扩大而行数变化不大,则应继续结合平均行宽、UPDATE 频率、TOAST 使用情况和 VACUUM 状态排查。

建立基线后的关键产物不是某一次“当前大小”,而是一条持续更新的时间序列:表有多大、索引占多少、TOAST 是否显著、行数怎样变化,以及单位时间内净增多少。后续制定在线保留周期和归档窗口时,应以这条趋势线而不是单次快照作为依据。

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

目录与阅读设置

阅读设置

17

记录一个想法

建立容量与增长基线

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

登录哪都通