表膨胀检测与处理

autovacuum 会持续回收死元组，但它只把死元组占用的空间登记到 FSM 供复用，不会把空间还给操作系统。于是长期运行的库会出现一种典型现象：表里的活数据只有 2 GB，文件却占了 20 GB。这就是表膨胀（table bloat）。

膨胀本身不是错误，而是 MVCC 的固有代价。危险的是「膨胀率长期失控」：顺序扫描要读的页数成倍增加、备份体积虚高、缓存命中率下降、甚至 VACUUM FULL 需要几倍于表大小的临时空间。

本节聚焦「怎么量、怎么判、怎么治」：用 pgstattuple 精确测量、用统计视图做全库巡检、判定膨胀多少才算问题、以及四种治理手段的取舍。

前置知识

阅读本节前，建议先了解：AutoVacuum 配置

基础概念

膨胀到底是什么

把一张表的物理文件想象成一本装订好的册子：每页 8 KB，页里放数据行。删掉一行，页里的槽位空出来了，但这一页仍然属于这本册子——文件大小不会变，页也不会自动消失。

text

一张表膨胀前后的对比（示意）

膨胀前（紧凑）：
┌────────┬────────┬────────┬────────┐
│ page 1 │ page 2 │ page 3 │ page 4 │
│ 满 100%│ 满 100%│ 满 100%│ 满  20%│
└────────┴────────┴────────┴────────┘
  ↑ 4 页全部承载有效数据

膨胀后（大量死元组被标记，空间可复用但未归还）：
┌────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┐
│   1    │   2    │   3    │   4    │   5    │   6    │   7    │   8    │
│ 活30%  │ 活10%  │ 空页   │ 活25%  │ 空页   │ 活 5%  │ 空页   │ 活 15% │
└────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┘
  ↑ 8 页，但有效数据只值 4 页 → 膨胀率 50%

关键在于：「可复用空间」和「已归还空间」是两回事。普通 VACUUM 产生的是前者，VACUUM FULL / pg_repack 产生的是后者。

两种空间要分清

概念	产生方式	文件大小变化	是否算膨胀
死元组空间	被标记但尚未清理	不变	是（严格意义的膨胀）
可复用空间	`VACUUM` 标记到 FSM	不变	是（最常被忽略的一种）
已归还空间	`VACUUM FULL` / `pg_repack`	变小	否

很多人只看 n_dead_tup 就下结论「没有膨胀」，这是错的。VACUUM 已经跑完、n_dead_tup 归零，表文件依然可能大得离谱——因为空间只是「待复用」。能否复用取决于后续 INSERT/UPDATE 是否恰好往这些页里塞数据。

膨胀从哪里来

来源	典型场景	特征
高频 `UPDATE`	计数器、状态机字段、`last_seen_at`	单行被反复改写，产生海量旧版本
长事务压制清理	报表事务、`idle in transaction`、逻辑复制槽	`n_dead_tup` 只涨不降
autovacuum 阈值过高	大表用默认 `scale_factor = 0.2`	清理太晚，单次清理量巨大
清理节流严重	`vacuum_cost_delay` 偏大、I/O 瓶颈	清理速度追不上产生速度
批量 `DELETE`	归档任务、清理历史数据	一次性留下大量空洞
分区/继承子表过多	单表设计缺陷	每个子表都有独立膨胀
`fillfactor` 设置不当	只读表设了低 `fillfactor`	白白预留空间

膨胀的代价

膨胀不只是「占磁盘」，它同时在四个维度上伤害系统：

text

表膨胀的四层代价

1. 扫描代价
   Seq Scan 要读的页数 ∝ 文件大小
   膨胀 3 倍 → 顺序扫描慢约 3 倍（在内存充足时）

2. 缓存代价
   shared_buffers / OS page cache 被无用的页挤占
   有效缓存容量按膨胀倍数缩小

3. 维护代价
   VACUUM 要扫完所有页，膨胀越多单次清理越慢
   备份/恢复时间线性增长

4. 治理代价
   膨胀到 10 倍时，VACUUM FULL 需要 2 倍磁盘空间
   且锁表时间与表大小成正比

膨胀会自我强化

膨胀的表 → VACUUM 更慢 → 清理更跟不上 → 膨胀更快。这个正反馈一旦形成，靠调参数很难扭转，必须做一次物理整理（VACUUM FULL 或 pg_repack）重置状态。

膨胀率怎么算

严格定义需要一个参照物：「同样这些活数据，紧凑排列需要多少页」。膨胀率就是实际页数与理论页数之差：

text

膨胀率 = 1 - (理论所需页数 / 实际页数)

其中：
  实际页数 = pg_class.relpages          （或 pg_relation_size / 8192）
  理论页数 = 由活元组的平均长度推算

这是所有膨胀检测工具（pgstattuple、pgstattuple_approx、各种监测系统的估算查询）背后的统一思路，区别只在于：

精确测量：逐页解析，算出真实的理论页数 → 慢，但准。
抽样估算：只读部分页，按比例外推 → 快，但与真实值有偏差。
统计估算：完全不读表，用 pg_stats 的 avg_width / null_frac 推算 → 最快，但偏差最大。

四种治理手段对照

手段	锁级别	需要额外空间	索引是否重建	适用场景
`VACUUM`	`SHARE UPDATE EXCLUSIVE`	几乎不需要	否	预防，不缩文件
`VACUUM FULL`	`ACCESS EXCLUSIVE`	表大小 ×1 以上	是	小表、可停业务窗口
`CLUSTER`	`ACCESS EXCLUSIVE`	表大小 ×1 以上	是	顺带按索引聚簇
`pg_repack`	短暂 `ACCESS EXCLUSIVE`	表大小 ×1 以上	可选	大表在线整理

选哪个不取决于「哪个更快」，而取决于「你有多长的锁表窗口」。

语法与用法

安装 pgstattuple 扩展

pgstattuple 是官方附带扩展，随 PostgreSQL 发行（contrib），需要单独创建：

sql

-- 只需在使用的数据库里建一次
CREATE EXTENSION IF NOT EXISTS pgstattuple;

text

CREATE EXTENSION

检查是否可用：

sql

SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name = 'pgstattuple';

核心函数一览

函数	测量对象	是否读全表	用途
`pgstattuple(regclass)`	表 / 物化视图	是	精确的表膨胀数据
`pgstattuple_approx(regclass)`	表 / 物化视图	否（跳过 all-visible 页）	大表的快速估算
`pgstatindex(regclass)`	B-Tree 索引	是	索引膨胀数据
`pgstatginindex(regclass)`	GIN 索引	是	GIN 索引统计
`pgstathashindex(regclass)`	Hash 索引	是	Hash 索引统计
`pg_relpages(regclass)`	任意关系	否	返回页数（等价 `relpages`）

sql

-- 最常用的一条：精确测量某张表
SELECT * FROM pgstattuple('orders');

pgstattuple 的返回字段

字段	类型	含义
`table_len`	bigint	表文件总字节数
`tuple_count`	bigint	存活元组数
`tuple_len`	bigint	存活元组的字节数（含元组头）
`tuple_percent`	float8	`tuple_len / table_len` 百分比
`dead_tuple_count`	bigint	死元组数
`dead_tuple_len`	bigint	死元组字节数
`dead_tuple_percent`	float8	`dead_tuple_len / table_len` 百分比
`free_space`	bigint	可复用空闲字节数（已登记到 FSM 的）
`free_percent`	float8	`free_space / table_len` 百分比

关键读法：

text

膨胀率 ≈ 100 - tuple_percent      ← 粗看
更严格：100 - tuple_percent - free_percent   ← 只算「真正无法利用的」

free_percent 高不一定是坏事，但它与 dead_tuple_percent 一起说明了「文件里有多少空间目前不承载活数据」。

全库巡检的标准写法

sql

-- 对所有普通表逐一测量，按膨胀空间从大到小排序
SELECT
  n.nspname                      AS schema_name,
  c.relname                      AS table_name,
  pg_size_pretty(pg_relation_size(c.oid))            AS table_size,
  (pgstattuple(c.oid)).dead_tuple_percent::numeric(5,2) AS dead_pct,
  (pgstattuple(c.oid)).free_percent::numeric(5,2)       AS free_pct,
  (pgstattuple(c.oid)).tuple_percent::numeric(5,2)      AS live_pct
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND pg_relation_size(c.oid) > 10 * 1024 * 1024   -- 只看 10MB 以上的表
ORDER BY pg_relation_size(c.oid) DESC
LIMIT 30;

这样写会读两遍表

(pgstattuple(c.oid)).xxx 这种写法在同一行里出现三次，PostgreSQL 可能重复调用函数。正确做法是把它放进子查询或 LATERAL：

sql

SELECT
  c.relname AS table_name,
  pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
  round(t.dead_tuple_percent::numeric, 2) AS dead_pct,
  round(t.free_percent::numeric, 2)       AS free_pct,
  round(t.tuple_percent::numeric, 2)      AS live_pct,
  t.dead_tuple_count
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL pgstattuple(c.oid) AS t
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND pg_relation_size(c.oid) > 10 * 1024 * 1024
ORDER BY pg_relation_size(c.oid) DESC
LIMIT 30;

详细说明

权限：为什么报「permission denied」

pgstattuple() 系列函数默认只对超级用户和拥有 pg_stat_scan_tables 角色的用户开放。原因是它能读出表内的空间分布，属于偏敏感的运维信息。

sql

-- 授权给监控账号
GRANT pg_stat_scan_tables TO monitor_user;

text

GRANT ROLE

pg_stat_scan_tables 是一个预定义角色，只授予「扫描全表统计信息」这一项能力，比超级用户安全得多。生产环境的监控账号应该用它，而不是 SUPERUSER。

精确测量 vs 抽样估算

pgstattuple() 逐页读取，对大表代价很高（一张 500 GB 的表会读 500 GB）。pgstattuple_approx() 换了个思路：

text

pgstattuple_approx 的工作方式

1. 逐页读取每一页的「页头」（不做完整元组解析）
2. 遇到 Visibility Map 标记为 all-visible 的页：
   → 直接整页计入「活数据」，跳过细节
3. 遇到未标记的页：
   → 完整解析，统计活元组/死元组/空闲
4. 按「已解析页占全部页的比例」外推，估算整表

它的返回列多了一个 approx 标记：

字段	含义
`table_len`	表总字节数（精确，来自文件大小）
`scanned_percent`	实际完整解析的页占比
`approx_tuple_count`	估算的活元组数
`approx_tuple_len`	估算的活元组字节数
`approx_tuple_percent`	估算的活元组占比
`approx_dead_tuple_count`	估算的死元组数
`approx_dead_tuple_len`	估算的死元组字节数
`approx_dead_tuple_percent`	估算的死元组占比
`approx_free_space`	估算的空闲字节数
`approx_free_percent`	估算的空闲占比

sql

SELECT * FROM pgstattuple_approx('orders');

text

 table_len | scanned_percent | approx_tuple_count | approx_tuple_percent | approx_dead_tuple_percent | approx_free_percent
-----------+-----------------+--------------------+----------------------+---------------------------+--------------------
 412876800 |              12 |             892471 |                81.35 |                      2.11 |              16.54

scanned_percent 是关键：越低说明表越「干净」（大部分页都是 all-visible，说明 VACUUM 工作得很好），估算也越可靠。

场景	选哪个	原因
表 < 5 GB	`pgstattuple`	精确，耗时可控
表 5 GB ~ 1 TB	`pgstattuple_approx`	快一个数量级，误差可接受
只需要「有没有膨胀」	统计视图估算	零 I/O 成本
准备做维护决策	`pgstattuple`	需要精确数字算临时空间

不用扩展的估算方法

监控系统通常不能安装扩展、也不想扫全表，于是用 pg_stats 里的列宽统计做纯数学推算。原理是：

text

给定：relpages（实际页数）、reltuples（估算行数）
      pg_stats 里的 avg_width / null_frac

推算：
  1. 每行数据宽度 ≈ Σ(avg_width) × (1 - null_frac) + 24 字节元组头
  2. 每行占用 ≈ 对齐(行宽) + 4 字节行指针
  3. 每页可用 ≈ 8192 - 24 字节页头
  4. 理论页数 = ceil(reltuples / (每页可用 / 每行占用))
  5. 膨胀率 = 1 - 理论页数 / 实际页数

实现出来如下（这是估算，不是测量）：

sql

-- 纯统计估算的表膨胀巡检（不读表数据，零 I/O 压力）
WITH tbl AS (
  SELECT
    c.oid,
    c.relname,
    c.relpages,
    c.reltuples,
    pg_relation_size(c.oid) AS bytes
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind = 'r'
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
    AND c.relpages > 0
),
stats AS (
  SELECT
    s.schemaname,
    s.tablename,
    sum((1 - s.null_frac) * s.avg_width) AS data_width,
    count(*) AS n_cols
  FROM pg_stats s
  WHERE s.schemaname NOT IN ('pg_catalog', 'information_schema')
  GROUP BY s.schemaname, s.tablename
)
SELECT
  t.relname AS table_name,
  t.relpages,
  pg_size_pretty(t.bytes) AS real_size,
  round(t.reltuples::numeric) AS est_rows,
  -- 每行占用：元组头 23 字节按 8 字节对齐 + 数据 + 行指针 4 字节
  round((8 * ceil((23 + coalesce(st.data_width, 0)) / 8) + 4)::numeric, 1) AS row_bytes,
  -- 理论页数
  greatest(
    1,
    ceil(t.reltuples / floor((8192 - 24)::numeric
        / (8 * ceil((23 + coalesce(st.data_width, 0)) / 8) + 4)))
  ) AS expected_pages,
  round(
    (1 - greatest(1, ceil(t.reltuples / floor((8192 - 24)::numeric
        / (8 * ceil((23 + coalesce(st.data_width, 0)) / 8) + 4)))
        )::numeric / t.relpages) * 100, 1
  ) AS bloat_pct
FROM tbl t
LEFT JOIN stats st ON st.tablename = t.relname
WHERE t.relpages > 128
ORDER BY t.bytes DESC
LIMIT 20;

估算方法的已知偏差

avg_width 是采样得到的平均值，对有严重数据倾斜的列不准。
完全忽略 TOAST：宽列被压缩/外存后，实际占用远小于 avg_width。
忽略页内碎片与 fillfactor 预留空间。
死元组会被算进膨胀率，但 VACUUM 之后这部分应消失。

所以这个数字只能用来排序找出可疑的表，最终结论必须用 pgstattuple 复核。生产环境更推荐部署现成的膨胀检查工具，而不是自己维护 SQL。

只按体积排序的快速筛查

最省钱的一步：只看哪些表「大得不成比例」。一行 SQL 就能得到粗筛清单：

sql

SELECT
  c.relname,
  pg_size_pretty(pg_total_relation_size(c.oid))  AS total_size,
  pg_size_pretty(pg_relation_size(c.oid))        AS table_size,
  pg_size_pretty(pg_indexes_size(c.oid))         AS index_size,
  c.reltuples::bigint                            AS est_rows
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

看三个信号：

平均行宽异常：table_size / reltuples 远超实际数据宽度 → 有膨胀。
索引比表还大：index_size > table_size → 索引膨胀严重。
估算行数与实际差很多：说明统计信息过时。

判定膨胀多少才算问题

没有统一阈值，但可以用这套分档做决策依据（针对单表膨胀率）：

膨胀率	状态	建议动作
< 20%	健康	无需处理
20% ~ 30%	可接受	观察趋势，检查 autovacuum 参数
30% ~ 50%	需要关注	优化 autovacuum 配置，评估是否整理
> 50%	需要治理	排期 `pg_repack` 或窗口内 `VACUUM FULL`
> 100%	严重	优先处理，通常伴有 autovacuum 失效

还要看「绝对量」而不是只看比例

一张 40 MB 的查找表膨胀 60% 只浪费 24 MB，不值得为它锁表。判断优先级应该用绝对浪费空间：

text

浪费空间 = 表大小 × 膨胀率

先治「浪费 GB 级」的表，再谈比例。

死元组堆积 vs 可复用空间

两个指标指向完全不同的病因，必须分开看：

现象	`dead_tuple_percent`	`free_percent`	病因	处方
清理跟不上	高	低	autovacuum 阈值/节流/长事务	调参数、清长事务
清理够了但没复用	低	高	表以 `UPDATE` 为主，页内空间难复用	降 `fillfactor`、整理一次
两者都低	低	低	健康	无需动作
两者都高	高	高	长期失控	整理 + 修配置

第二种情况最容易被误诊。表每天都被 VACUUM、n_dead_tup 一直是 0，但文件就是不缩——因为死元组腾出的空间是「零散的」，新插入的行不一定放得进去。

fillfactor：从源头减少膨胀

fillfactor 决定插入时每页最多填多少。默认 100 意味着一页塞满，之后任何 UPDATE 都只能把新版本放到别的页，留下一个无法复用的空洞。

sql

-- 高频更新的表，留 20% 空间给原地更新
ALTER TABLE orders SET (fillfactor = 80);
-- 注意：只对之后新写入的页生效
VACUUM orders;   -- 触发一次清理，但不重写已满的页

要让 fillfactor 对已有数据生效，必须做物理重写：

sql

-- 重写后新页会按 fillfactor=80 填充
VACUUM FULL orders;      -- 或 pg_repack

text

fillfactor 的效果对比（页内 8KB）

fillfactor = 100（默认）
┌──────────────────────────┐
│ 行A 行B 行C ... 行Z  ████ │  ← 塞满
└──────────────────────────┘
  UPDATE 行C → 新版本放不进本页，写到 page 2，本页留洞

fillfactor = 80
┌──────────────────────────┐
│ 行A 行B ... 行P  ░░░░░░░░ │  ← 留 20%
└──────────────────────────┘
  UPDATE 行C → 新版本可以放回本页同一区域

表类型	建议 `fillfactor`
只读 / 追加写（日志、事实表）	100（默认）
中等更新频率	90
高频 `UPDATE` 小行（计数器）	70 ~ 80
有 HOT 更新的索引列不变场景	80

HOT 更新：索引不膨胀的关键

UPDATE 时如果没有任何索引列被修改，且新版本能放进同一页，PostgreSQL 会做 HOT 更新（Heap-Only Tuple）：索引项指向原位置，由页内链表找到新版本，不产生新的索引项。

sql

-- 反例：更新索引列 → 无法 HOT，索引必然膨胀
UPDATE orders SET created_at = now() WHERE id = 1;   -- 不推荐（created_at 有索引）

-- 正例：只更新非索引列 → 可 HOT
UPDATE orders SET status = 'paid' WHERE id = 1;

设计表时的一条硬规则：

不要把高频变化的列放进索引。

例如「最后登录时间」如果建了索引，每次登录都会写一个新索引项，索引膨胀速度与登录次数成正比。

监控膨胀趋势而不是快照

单次测量只能说明「现在」，趋势才能说明「会不会失控」。把膨胀率按天存档：

sql

-- 建议落库为一张监控表，每天采集一次
CREATE TABLE IF NOT EXISTS monitor.bloat_history (
  captured_at   timestamptz NOT NULL DEFAULT now(),
  schema_name   text        NOT NULL,
  table_name    text        NOT NULL,
  table_bytes   bigint      NOT NULL,
  dead_pct      numeric(5,2),
  free_pct      numeric(5,2),
  PRIMARY KEY (captured_at, schema_name, table_name)
);

INSERT INTO monitor.bloat_history
  (schema_name, table_name, table_bytes, dead_pct, free_pct)
SELECT
  n.nspname,
  c.relname,
  pg_relation_size(c.oid),
  round(t.dead_tuple_percent::numeric, 2),
  round(t.free_percent::numeric, 2)
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL pgstattuple_approx(c.oid) AS t
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND pg_relation_size(c.oid) > 100 * 1024 * 1024;

趋势查询：

sql

-- 哪些表的膨胀率一周内涨了 15 个百分点
SELECT table_name,
       max(dead_pct + free_pct) AS pct_now,
       min(dead_pct + free_pct) AS pct_week_ago,
       max(table_bytes) - min(table_bytes) AS bytes_growth
FROM monitor.bloat_history
WHERE captured_at > now() - interval '7 days'
GROUP BY table_name
HAVING max(dead_pct + free_pct) - min(dead_pct + free_pct) > 15
ORDER BY bytes_growth DESC;

治理前的空间与时间预算

决定做物理整理前，先算两笔账。

空间账：VACUUM FULL 与 pg_repack 都需要写一份新数据。

text

需要空闲空间 ≈ 表大小 + 最大索引大小 + 安全余量
（不是「表大小 × 2」那么绝对：同一时刻旧文件仍在）

sql

-- 一次算出该表整理需要预留的空间与当前可用空间
SELECT
  c.relname,
  pg_size_pretty(pg_relation_size(c.oid))         AS table_size,
  pg_size_pretty(pg_indexes_size(c.oid))          AS index_size,
  pg_size_pretty(pg_total_relation_size(c.oid))   AS total_size,
  pg_size_pretty(pg_tablespace_size(t.spcname))   AS tablespace_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relname = 'orders';
-- 注意：reltablespace = 0 表示使用默认表空间，上面的 JOIN 会漏掉

sql

-- 更稳妥：直接看数据目录所在文件系统的剩余空间（需要在宿主机执行 df）

bash

df -h /var/lib/postgresql/18/main

时间账：VACUUM FULL 的耗时大致与「表大小 + 索引大小」成正比。实测一次再外推：

text

先拿最小的分区试一次，记录 每 GB 耗时，
再乘以目标表大小，得到粗略的时间窗口需求。

索引膨胀要单独处理

表膨胀和索引膨胀是两个独立问题。VACUUM / VACUUM FULL 会重建索引（VACUUM FULL），但普通 VACUUM 只清理索引里的死项条目，不整理索引页结构。

sql

-- 索引膨胀检测用 pgstatindex，而不是 pgstattuple
SELECT * FROM pgstatindex('orders_pkey');

text

 version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------
       4 |          2 |    1048576 |             3 |              8 |        120 |           0 |             6 |            71.24 |             18.33

判定要点：

avg_leaf_density 越低说明叶子页越空，索引越膨胀。
deleted_pages > 0 说明有完全清空但仍挂在树上的页——这是索引膨胀的典型特征。
leaf_fragmentation 高说明叶子页物理顺序与逻辑顺序脱节。

索引膨胀的完整治理方案见索引膨胀检测。

实战示例

场景一：从「表变大」到确诊膨胀

背景：orders 表只有 300 万行、平均行宽约 200 字节，理论大小约 700 MB，但 pg_relation_size 显示 4.2 GB。

第一步：量化

sql

SELECT
  pg_size_pretty(pg_relation_size('orders'))  AS table_size,
  pg_size_pretty(pg_indexes_size('orders'))   AS index_size,
  pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

text

 table_size | index_size | total_size
------------+------------+------------
 4128 MB    | 812 MB     | 4928 MB

第二步：精确测量

sql

SELECT * FROM pgstattuple('orders');

text

 table_len  | tuple_count | tuple_len  | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
------------+-------------+------------+---------------+------------------+----------------+--------------------+------------+--------------
 4328521728 |     3004271 |  681574400 |         15.75 |            18244 |        4194304 |               0.10 | 3634655232 |        83.97

读结论：

text

活数据只占 15.75%，空闲空间占 83.97%，死元组几乎为零。

→ 不是「清理跟不上」，而是「清理过但空间没被复用」
→ 说明这张表的写入模式以 UPDATE 为主，且 fillfactor 是默认的 100

第三步：验证假设

sql

SELECT
  n_tup_upd, n_tup_ins, n_tup_del, n_tup_hot_upd,
  round(n_tup_hot_upd * 100.0 / greatest(n_tup_upd, 1), 1) AS hot_ratio
FROM pg_stat_user_tables
WHERE relname = 'orders';

text

 n_tup_upd | n_tup_ins | n_tup_del | n_tup_hot_upd | hot_ratio
-----------+-----------+-----------+---------------+-----------
   9827431 |   3012886 |     10000 |         41022 |       0.4

hot_ratio 只有 0.4% —— 说明绝大多数 UPDATE 都产生了新索引项并写到新页，典型的「无法复用空间」。

第四步：治理

sql

-- 1. 先调低 fillfactor，让后续写入不再制造新洞
ALTER TABLE orders SET (fillfactor = 80);

-- 2. 在线整理（推荐）或窗口内 VACUUM FULL
--    pg_repack 会按新 fillfactor 重写
-- $ pg_repack --table=orders --no-superuser-check dbname

text

INFO: repacking table "public.orders"

第五步：复验

sql

SELECT
  pg_size_pretty(pg_total_relation_size('orders')) AS total_size,
  count(*) FILTER (WHERE oid IS NOT NULL) AS dummy_check
FROM pg_class WHERE oid = 'orders'::regclass;

SELECT
  pg_size_pretty(pg_total_relation_size('orders')) AS total_size,
  round(t.tuple_percent::numeric, 2) AS live_pct,
  round(t.free_percent::numeric, 2)  AS free_pct
FROM pgstattuple('orders') t;

text

 total_size | live_pct | free_pct
------------+----------+----------
 748 MB     |    84.21 |    15.44

体积从 4.9 GB 降到 748 MB，free_pct 约 15%，正好对应 fillfactor = 80 预留的 20%。

场景二：用估算巡检全库，再定点精测

背景：库里有 400 张表，不可能每张都跑 pgstattuple。需要先用零成本的估算缩小范围。

第一步：估算巡检

sql

SELECT relname, relpages, reltuples
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
  AND c.relpages > 1000
ORDER BY c.relpages DESC
LIMIT 10;

第二步：挑出「行数少但页数多」的可疑表

sql

-- 平均每行占用字节数 = 表大小 / 行数，显著高于实际行宽的即为可疑
SELECT
  c.relname,
  c.reltuples::bigint AS est_rows,
  c.relpages,
  pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
  round(pg_relation_size(c.oid) / greatest(c.reltuples, 1), 1) AS bytes_per_row
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
  AND c.reltuples > 10000
ORDER BY pg_relation_size(c.oid) / greatest(c.reltuples, 1) DESC
LIMIT 15;

text

 relname       | est_rows | relpages | table_size | bytes_per_row
---------------+----------+----------+------------+---------------
 sessions      |  1200342 |   384120 | 3001 MB    |        2621.4
 counters      |   502331 |    98420 | 769 MB     |        1605.2
 orders        |  3004271 |   528420 | 4128 MB    |        1441.6
 products      |   120000 |     1420 | 11 MB      |          96.4

前三个的 bytes_per_row 都在 1 KB 以上，而实际行宽只有几十到两百字节 —— 高度可疑。

第三步：对可疑表精测

sql

SELECT
  c.relname,
  pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
  round(t.dead_tuple_percent::numeric, 2) AS dead_pct,
  round(t.free_percent::numeric, 2)       AS free_pct,
  round(t.tuple_percent::numeric, 2)      AS live_pct,
  round(t.scanned_percent::numeric, 1)    AS scanned_pct
FROM (VALUES ('sessions'::regclass), ('counters'::regclass), ('orders'::regclass)) v(oid)
JOIN pg_class c ON c.oid = v.oid
CROSS JOIN LATERAL pgstattuple_approx(c.oid) AS t
ORDER BY pg_relation_size(c.oid) DESC;

text

 relname  | table_size | dead_pct | free_pct | live_pct | scanned_pct
----------+------------+----------+----------+----------+-------------
 sessions | 3001 MB    |     0.02 |    91.20 |     8.78 |         4.1
 counters | 769 MB     |     0.01 |    88.44 |    11.55 |         3.7
 orders   | 4128 MB   |     0.10 |    83.97 |    15.75 |         6.2

三张表都是同一个病：空间充分可复用但从未被复用。scanned_pct 都很低，说明 Visibility Map 状态良好（VACUUM 一直在正常工作），问题不在清理频率上。

第四步：对症下药

sql

-- sessions 表：典型的高频 UPDATE（每次心跳都更新 last_seen）
ALTER TABLE sessions SET (fillfactor = 70);
-- counters 表：行很短但更新极频繁
ALTER TABLE counters SET (fillfactor = 70);
-- orders 表：更新频率中等
ALTER TABLE orders SET (fillfactor = 85);

bash

# 依次在线整理（注意逐个执行，不要并行，避免 I/O 争抢）
pg_repack --table=sessions --no-superuser-check mydb
pg_repack --table=counters --no-superuser-check mydb
pg_repack --table=orders   --no-superuser-check mydb

text

INFO: repacking table "public.sessions"
INFO: repacking table "public.counters"
INFO: repacking table "public.orders"

第五步：长期观测

整理完成后把膨胀率纳入每日采集，观察 free_pct 是否稳定在 1 - fillfactor/100 附近。如果又开始向上漂，说明 fillfactor 还设高了。

注意事项

1. pgstattuple 会长时间持有 AccessShareLock 并全表读取

在生产库的大表上直接跑它，可能引发大量磁盘 I/O 与缓存抖动，甚至拖慢并发查询。

sql

-- 不推荐：直接对 1TB 的表跑精确测量
SELECT * FROM pgstattuple('huge_events');

-- 推荐：先估算，再用 approx 复核
SELECT * FROM pgstattuple_approx('huge_events');

2. 别把 free_percent 高直接等同于「要 VACUUM FULL」

free_percent 高只说明「空间没被复用」。如果表的写入模式就是以 INSERT 为主，空间迟早会被用上，此时整理反而是浪费。

sql

-- 先看写入模式再决定
SELECT n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd
FROM pg_stat_user_tables WHERE relname = 'orders';

3. VACUUM FULL 在业务高峰期执行会完全阻塞该表

它取 ACCESS EXCLUSIVE 锁，期间该表的一切读写全部排队。对核心交易表执行可能直接导致应用超时雪崩。

sql

-- 错误！业务高峰期直接执行
VACUUM FULL orders;

-- 正确：在线整理，或放到维护窗口
-- pg_repack --table=orders mydb

4. 磁盘空间不足时 VACUUM FULL 会失败并留下垃圾

VACUUM FULL 先把数据写进新文件，写满磁盘会失败。更糟的是 PG 12 以前它会先尝试重建索引，可能把表搞得更膨胀。

sql

-- 执行前务必确认剩余空间
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS need_at_least;

5. fillfactor 修改不会自动重写已有数据

ALTER TABLE ... SET (fillfactor = ...) 只影响之后分配/写入的页。已经写满的页不会因为改了参数就变空。

sql

ALTER TABLE orders SET (fillfactor = 80);
-- 还需要一次物理重写才能让参数对存量数据生效
-- pg_repack --table=orders mydb

6. 高频变化的列建索引是膨胀的主要来源之一

索引列被更新时无法 HOT 更新，必然产生新索引项。这是「表不算大但索引巨大」的典型原因。

sql

-- 不推荐：last_seen_at 变化极频繁，每次更新都写新索引项
CREATE INDEX idx_sessions_last_seen ON sessions (last_seen_at);

-- 推荐：如果只是为了「查最近活跃」的少量场景，用部分索引
CREATE INDEX idx_sessions_active
  ON sessions (last_seen_at)
  WHERE is_active;                       -- 只索引活跃行，大幅减少条目

7. reltuples 与 relpages 是估算值，不是精确值

它们由 VACUUM / ANALYZE 更新。如果表刚批量写入还没清理，这两个数字会严重失真，导致估算公式得出荒谬结果。

sql

-- 估算前先确保统计信息是新的
ANALYZE orders;

8. 分区表的膨胀要按分区看，不能看父表

父表本身没有数据，pg_relation_size 对父表返回 0 或很小。必须逐分区测量。

sql

-- 只看某个父表的分区
SELECT
  c.relname AS partition,
  pg_size_pretty(pg_relation_size(c.oid)) AS size,
  round(t.dead_tuple_percent::numeric, 2) AS dead_pct
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
CROSS JOIN LATERAL pgstattuple_approx(c.oid) AS t
WHERE i.inhparent = 'orders'::regclass
ORDER BY pg_relation_size(c.oid) DESC;

9. TOAST 表也要看

宽字段（text、jsonb、bytea）会被外存到 TOAST 表。TOAST 表同样会膨胀，而且它的膨胀往往被忽略——因为主表的 pg_relation_size 里不包含它。

sql

-- TOAST 表膨胀检测
SELECT
  c.relname AS main_table,
  t.relname AS toast_table,
  pg_size_pretty(pg_relation_size(t.oid)) AS toast_size,
  round(pt.dead_tuple_percent::numeric, 2) AS dead_pct
FROM pg_class c
JOIN pg_class t ON t.oid = c.reltoastrelid
CROSS JOIN LATERAL pgstattuple_approx(t.oid) AS pt
WHERE c.relname = 'orders';

10. pg_repack 需要额外的临时空间且会短暂锁表

它建一张影子表、同步增量、最后切换——切换瞬间取 ACCESS EXCLUSIVE。虽然只有毫秒级，但仍会短暂阻塞。

bash

# 关键：确认临时表的存放位置有足够空间
pg_repack --table=orders --tablespace=fast_ssd --no-superuser-check mydb

11. 别为了「缩文件」而频繁 VACUUM FULL

物理重写会把所有页重新写一遍，产生大量 WAL 与 I/O。频繁做等于把数据库当成「每天重装一次」的工具。

text

反模式：每周对所有大表 VACUUM FULL 一次
后果：WAL 暴增、复制延迟、I/O 长期饱和、反而拖慢业务
正解：管好 autovacuum + 设对 fillfactor，让膨胀自然不发生

最佳实践

1. 先建立「膨胀基线」，再谈治理

不知道历史值就无法判断「涨得快不快」。建议把膨胀率按天采集，形成基线。

sql

-- 每天采集一次，落进监控表（见前文 monitor.bloat_history）
-- 关键指标：dead_pct、free_pct、table_bytes

2. 巡检按「浪费的绝对空间」排序，而不是比例

sql

-- 优先处理浪费最多的表
SELECT
  c.relname,
  pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
  round(t.free_percent::numeric, 1) AS free_pct,
  pg_size_pretty((pg_relation_size(c.oid) * t.free_percent / 100)::bigint) AS wasted
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL pgstattuple_approx(c.oid) AS t
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND pg_relation_size(c.oid) > 100 * 1024 * 1024
ORDER BY (pg_relation_size(c.oid) * t.free_percent / 100) DESC
LIMIT 20;

3. 大表用 pgstattuple_approx，只用精确版复核候选

环节	工具	频率
每日巡检	统计估算（零 I/O）	每天
每周深度巡检	`pgstattuple_approx`	每周
治理前确认	`pgstattuple`	按需

4. 给高频更新的表设 fillfactor，而不是靠事后整理

sql

-- 建表时就设好（建表后改的只对新页生效）
CREATE TABLE sessions (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  token       text NOT NULL,
  last_seen   timestamptz NOT NULL DEFAULT now(),
  payload     jsonb
) WITH (fillfactor = 70);        -- 高频更新，预留 30%

5. 用 HOT 更新友好型设计，减少索引膨胀

sql

-- 把「高频变化的列」与「索引列」分开：
CREATE TABLE documents (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  int NOT NULL,          -- 索引列，几乎不变
  status     text NOT NULL,         -- 常用筛选，但变化不快
  updated_at timestamptz NOT NULL,  -- 高频变化，绝不建索引
  body       text
);
CREATE INDEX idx_documents_tenant ON documents (tenant_id, status);

6. 优先用 pg_repack 而不是 VACUUM FULL

只要该表不能接受秒级以上的写入阻塞，就应该用在线整理。

bash

# 只重写表，不重建索引（索引单独用 REINDEX CONCURRENTLY）
pg_repack --table=orders --no-superuser-check mydb

# 索引膨胀单独处理，可以并发重建

sql

REINDEX INDEX CONCURRENTLY orders_pkey;

7. 治理顺序：先修配置，再重写数据

先做物理重写而不改配置，等于「清了一次垃圾但不改掉乱扔的习惯」，两个月后膨胀会重现。

text

正确顺序：
1. 查病因（写入模式 / autovacuum / 长事务 / fillfactor）
2. 改配置（fillfactor、autovacuum 按表覆盖、清理长事务源头）
3. 观察 1~2 周，确认新数据不再制造膨胀
4. 再做一次物理整理，把历史遗留空间收回

8. 长事务源头必须一起解决

只要存在长期打开的事务，VACUUM 无论如何都清不掉它开始之后产生的死元组。

sql

-- 找出最老的事务
SELECT pid, usename, state,
       now() - xact_start AS xact_age,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

-- 应用侧要设置：idle_in_transaction_session_timeout

sql

-- 数据库级兜底
ALTER DATABASE appdb SET idle_in_transaction_session_timeout = '5min';
ALTER DATABASE appdb SET statement_timeout = '30s';

9. 整理要在低峰期做，并监控 I/O

即使 pg_repack 不锁表，它也会读全表 + 写全表，I/O 压力极大。

bash

# 用 ionice / nice 降低对在线业务的影响
ionice -c3 nice -n19 pg_repack --table=orders --no-superuser-check mydb

sql

-- 同时观察复制延迟（如果是从库/有副本）
SELECT client_addr, state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag
FROM pg_stat_replication;

10. 把膨胀率写进告警，但设合理阈值

告警太灵敏会被噪声淹没，太迟钝会错过治理窗口。

text

建议阈值（针对 > 100MB 的表）：
├── 警告：浪费空间 > 10 GB 且 膨胀率 > 30%
├── 严重：浪费空间 > 50 GB 或 膨胀率 > 50%
└── 趋势：7 天内膨胀率上涨 > 20 个百分点

11. 归档与分区是控制膨胀的结构性手段

对只增不改的时序数据，用分区 + DETACH 归档，比任何清理手段都高效——直接扔掉整张分区文件，不产生死元组。

sql

-- 归档一个月的数据：秒级完成，零膨胀
ALTER TABLE events DETACH PARTITION events_2025_08;
-- 然后按需导出后 DROP

12. 不要指望一次整理一劳永逸

膨胀是 MVCC 的常态。目标是让它缓慢、可控地增长，而不是追求「永远 0%」。

text

健康状态：膨胀率长期在 10% ~ 25% 之间小幅波动
说明：清理与产生达到了动态平衡，此时不需要任何干预

继续学习：冻结事务 ID（FREEZE）

表膨胀检测与处理 ​

基础概念 ​

膨胀到底是什么 ​

两种空间要分清 ​

膨胀从哪里来 ​

膨胀的代价 ​

膨胀率怎么算 ​

四种治理手段对照 ​

语法与用法 ​

安装 pgstattuple 扩展 ​

核心函数一览 ​

pgstattuple 的返回字段 ​

全库巡检的标准写法 ​

详细说明 ​

权限：为什么报「permission denied」 ​

精确测量 vs 抽样估算 ​

不用扩展的估算方法 ​

只按体积排序的快速筛查 ​

判定膨胀多少才算问题 ​

死元组堆积 vs 可复用空间 ​

fillfactor：从源头减少膨胀 ​

HOT 更新：索引不膨胀的关键 ​

监控膨胀趋势而不是快照 ​

表膨胀检测与处理

基础概念

膨胀到底是什么

两种空间要分清

膨胀从哪里来

膨胀的代价

膨胀率怎么算

四种治理手段对照

语法与用法

安装 pgstattuple 扩展

核心函数一览

pgstattuple 的返回字段

全库巡检的标准写法

详细说明

权限：为什么报「permission denied」

精确测量 vs 抽样估算

不用扩展的估算方法

只按体积排序的快速筛查

判定膨胀多少才算问题

死元组堆积 vs 可复用空间

fillfactor：从源头减少膨胀

HOT 更新：索引不膨胀的关键

监控膨胀趋势而不是快照