PostgreSQL / 系统课时
建立容量与增长基线
建立容量与增长基线
归档之前先回答两个问题:大表当前占了多少空间,以及它正在以多快的速度增长。本课用 PostgreSQL 自带统计函数拆分表、索引和 TOAST 占用,并把周期性快照转成增长基线,为后续保留周期、归档窗口和容量预测提供数据依据。
学习目标
- 使用 PostgreSQL 函数拆分测量表、索引与 TOAST 空间
- 建立可重复采样的容量快照并计算增长速度
- 区分业务数据增长与索引、TOAST 等附加空间成本
拆开看大表的空间组成
pg_total_relation_size() 适合快速查看一个表及其相关对象的总占用,但容量治理不能只看总数。实际至少要区分表本体、索引和 TOAST,因为三者的增长原因与治理方式不同。
下面以 public.event_history 为目标表。pg_relation_size() 返回主关系本体大小,pg_indexes_size() 返回该表全部索引大小;若存在 TOAST 关系,则通过 reltoastrelid 找到它,并统计 TOAST 表及其索引的总占用。
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;预期结果
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(*) 那样扫描整张大表。
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;预期结果
CREATE TABLE
INSERT 0 1如果历史表已经存在,客户端可能额外显示“relation already exists”之类的提示;关键验证点是本次 INSERT 成功写入 1 行。实际值和提示格式可能因客户端而异。
若近期发生大量写入而统计信息明显陈旧,应先检查自动分析是否正常,必要时在合适窗口执行 ANALYZE。采样频率则应与增长速度匹配:缓慢增长的大表按天采样通常足够,快速写入的大表可以缩短到小时级。
折算单位时间增长速度
有了连续样本后,可以用窗口函数比较相邻采样点,再根据实际时间间隔折算近似日增长量。这样即使采样没有严格间隔 24 小时,也能得到可比较的趋势指标。
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;预期结果
sampled_at | growth_bytes | estimated_bytes_per_day
-------------------------------+--------------+------------------------
<TIME> | <VALUE> | <VALUE>
... | ... | ...这里得到的是两个采样点之间的净空间变化折算值,不应直接理解为稳定的业务写入速率。批量导入、删除、VACUUM、索引创建或重建都可能造成短期突变,因此容量预测应观察多个周期,而不是只取最近一次差值。
从基线判断增长来自哪里
总空间上涨并不等于业务数据按同样速度增长。判断异常时应把总字节、表本体、索引以及估算行数放在一起看。
如果 total_bytes 与 estimated_rows 基本同比增长,通常说明主要来自新增数据;如果索引增长明显快于表本体,应检查是否新增索引、索引结构是否膨胀或数据分布是否发生变化;如果表本体快速扩大而行数变化不大,则应继续结合平均行宽、UPDATE 频率、TOAST 使用情况和 VACUUM 状态排查。
建立基线后的关键产物不是某一次“当前大小”,而是一条持续更新的时间序列:表有多大、索引占多少、TOAST 是否显著、行数怎样变化,以及单位时间内净增多少。后续制定在线保留周期和归档窗口时,应以这条趋势线而不是单次快照作为依据。