PostgreSQL / 系统课时

治理索引与 TOAST 膨胀

治理索引与 TOAST 膨胀

删除大量到期历史数据后,表的逻辑行数可能已经下降,但索引文件和 TOAST 关系未必同步缩小。本课建立一套从尺寸取证、原因判断到重建或持续复用的治理路径,避免把“文件没有变小”误判成清理失败。

学习目标

  • 区分索引、主表和 TOAST 在大量删除后的空间表现
  • 使用 PostgreSQL 系统函数定位真正占用空间的关系
  • 根据后续写入模式选择复用、重建或表级重写策略

先确认空间到底留在哪里

普通 DELETE 只把旧行版本变成可回收空间。后续 VACUUM 可以让这些页内空间继续被 PostgreSQL 使用,但通常不会把关系文件立即缩小并归还给操作系统。索引页和 TOAST 表也以内部复用为主,因此清理后的首要任务不是马上重建,而是先测量空间构成。

先查看目标表、索引和 TOAST 的总体尺寸。以下示例假设待治理的大表为 event_history。

sql
SELECT
    pg_size_pretty(pg_relation_size('event_history')) AS heap_size,
    pg_size_pretty(pg_indexes_size('event_history')) AS indexes_size,
    pg_size_pretty(pg_total_relation_size('event_history')) AS total_size;

预期结果

text
 heap_size | indexes_size | total_size
-----------+--------------+-----------
 <VALUE>   | <VALUE>      | <VALUE>

实际尺寸取决于数据量,因此用 <VALUE> 表示动态值。pg_relation_size 观察主关系,pg_indexes_size 汇总该表索引,而 pg_total_relation_size 还会包含索引、TOAST 等附属关系。

如果需要单独观察 TOAST,可通过 reltoastrelid 找到该表关联的 TOAST 关系并测量其大小。

sql
SELECT
    c.relname AS table_name,
    t.relname AS toast_name,
    pg_size_pretty(pg_total_relation_size(t.oid)) AS toast_total_size
FROM pg_class AS c
JOIN pg_class AS t
  ON t.oid = c.reltoastrelid
WHERE c.oid = 'event_history'::regclass;

预期结果

text
  table_name   |      toast_name       | toast_total_size
---------------+-----------------------+-----------------
 event_history | pg_toast_<ID>         | <VALUE>

<ID> 与 <VALUE> 会因数据库对象和数据规模不同而变化。普通用户表通常会有关联的 TOAST 关系,即使当前没有行外存储的大值,该关系也可能存在且很小。因此,不能把“当前没有大量 TOAST 数据”理解为“没有 TOAST 关系”。如果查询意外没有返回记录,应检查目标对象是否确实是普通表、reltoastrelid 是否有效以及系统目录查询条件是否正确,而不能据此直接判断无需检查 TOAST。

理解删除后的空间为什么还在

大批量历史删除会同时影响三类物理结构:堆表中的旧 tuple 需要等待清理,索引中的失效条目需要被回收,超大字段对应的 TOAST 数据也会随着旧行版本失效。VACUUM 主要把这些空间标记为可再次利用,并不等价于压缩关系文件。

大量删除后堆表、索引与 TOAST 的空间去向

因此,仅凭磁盘文件尺寸没有下降,不能判断归档清理无效。更重要的问题是后续业务是否会再次写入相近规模的数据,以及剩余数据是否仍然需要现有索引结构。

判断索引是复用还是重建

如果表仍持续写入,新数据通常能逐步复用表页和索引中的可用空间。此时立即重建一个大型索引,可能只会制造额外 I/O、WAL、临时磁盘占用和更长的维护窗口。

当索引尺寸明显大于剩余数据所需规模,并且未来不会重新增长到原来的高水位时,才更适合考虑重建。生产环境通常优先评估并发重建,以减少对普通读写的阻塞。

sql
REINDEX INDEX CONCURRENTLY event_history_created_at_idx;

预期结果

text
REINDEX

并发重建仍需要额外磁盘空间,并且执行期间会产生显著 I/O 和 WAL。开始前应确认磁盘余量,完成后重新测量索引尺寸,并用实际业务查询验证执行计划与性能。

TOAST 很大时不要只盯索引

如果主表中包含大型 jsonb、text、bytea 等字段,删除历史行后,主要可回收空间可能来自 TOAST,而不是业务索引。单独执行 REINDEX 只能处理索引,不能压缩主表或 TOAST 的关系文件,因此必须先确认空间构成再选择手段。

当剩余数据未来会继续增长时,通常让 VACUUM 完成清理后继续复用空间即可;当数据永久性大幅缩减,并且确实需要把文件空间归还给操作系统时,才考虑表级重写操作,例如 VACUUM FULL。

sql
VACUUM (FULL, ANALYZE) event_history;

预期结果

text
VACUUM

VACUUM FULL 会重写表并取得 ACCESS EXCLUSIVE 锁,会阻塞该表上的并发访问,不适合在繁忙大表上未经评估直接执行。对于高可用生产系统,应安排维护窗口,或采用经过验证的在线重写、迁移方案降低业务影响。

用后续增长模式做最终决策

治理的核心不是追求“删除后文件必须立刻变小”,而是决定这些已释放空间还有没有复用价值。持续写入且容量会再次增长时,保留可复用空间往往成本最低;永久缩容且索引明显失衡时,可针对索引执行并发重建;主表和 TOAST 都需要真正缩小时,则需要评估表级重写及其锁、WAL、磁盘和恢复成本。

完成处理后再次执行尺寸查询,并结合真实业务 SQL 的 EXPLAIN (ANALYZE, BUFFERS) 验证性能。只有空间、查询性能和维护风险三方面都符合预期,才算完成一次有效的膨胀治理。

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

目录与阅读设置

阅读设置

17

记录一个想法

治理索引与 TOAST 膨胀

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

登录哪都通