PostgreSQL / 系统课时

表与索引膨胀

表与索引膨胀

表文件变大并不等于发生了膨胀。真正要判断的是:页面里有多少空间已失去有效数据承载能力、索引页密度是否持续下降,以及这些空间能否被后续写入复用。本篇给出测量方法,并把普通 VACUUM、重建和在线整理的边界分开。

学习目标

  • 区分表膨胀、索引膨胀与正常预留空间
  • 使用 PostgreSQL 统计与 pgstattuple 测量浪费
  • 根据停机窗口、磁盘余量和回收目标选择治理方式

膨胀到底是什么

PostgreSQL 使用 MVCC。UPDATE 通常会留下旧行版本,DELETE 也不会立刻从数据文件中挖掉对应字节;VACUUM 在确认旧版本不再可见后,把这些位置标记为可复用。只要后续写入能复用这些页面,文件较大并不一定是问题。

索引也会保留指向旧行版本的条目,并在页分裂、删除和更新中出现低密度页面。因此“表很大”和“索引很大”只能说明占用空间,不能单独证明膨胀。更有意义的是同时看死元组比例、空闲空间、索引叶子页密度和长期增长趋势。

表与索引膨胀及治理路径

先测量表与索引

安装 pgstattuple 扩展后,可以直接查看表中有效元组、死元组和空闲空间;对 B-tree 索引可用 pgstatindex 查看叶子页密度。大型生产表执行精确扫描前,应先在低峰期评估 I/O 成本。

sql
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    pg_size_pretty(table_len) AS table_size,
    tuple_count,
    dead_tuple_count,
    round(dead_tuple_percent::numeric, 2) AS dead_pct,
    round(free_percent::numeric, 2) AS free_pct
FROM pgstattuple('public.orders');

SELECT
    pg_size_pretty(index_size) AS index_size,
    leaf_pages,
    deleted_pages,
    round(avg_leaf_density::numeric, 2) AS avg_leaf_density,
    round(leaf_fragmentation::numeric, 2) AS leaf_fragmentation
FROM pgstatindex('public.orders_pkey');

预期结果

text
CREATE EXTENSION
 table_size | tuple_count | dead_tuple_count | dead_pct | free_pct
 <VALUE>    | <VALUE>     | <VALUE>          | <VALUE>  | <VALUE>

 index_size | leaf_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
 <VALUE>    | <VALUE>    | <VALUE>       | <VALUE>          | <VALUE>

实际数值取决于数据量和写入历史。不要用单次百分比直接触发重建;更稳妥的判断是观察同一对象在相似业务负载下是否持续变大,同时确认可复用空间并没有被后续写入消化。

按回收目标选择治理动作

普通 VACUUM 适合“让表继续健康工作”:它清理不再需要的旧版本并让空间可复用,但通常不会缩小数据文件。如果业务只是持续写入同一张表,这往往已经足够。

当索引自身明显膨胀,而表文件无需重写时,可以重建索引。对需要维持读写的生产系统,优先评估并发重建:

sql
REINDEX INDEX CONCURRENTLY public.orders_pkey;

预期输出

text
REINDEX

REINDEX CONCURRENTLY 会使用额外磁盘空间,并且不能放在显式事务块中执行。它降低长时间阻塞普通读写的风险,但仍会在若干短阶段获取必要锁,因此要在真实负载下验证。

需要把表文件空间真正归还给操作系统时,才考虑 VACUUM FULL 或其他表重写方案。VACUUM FULL 会重写表并持有较强锁,适合有明确维护窗口的场景;如果希望降低停机影响,可评估 pg_repack 等外部扩展,但它不是 PostgreSQL 内置能力,必须先验证版本兼容、额外磁盘与运维流程。

重建前先检查代价

治理膨胀时,最容易犯的错误是“看到大文件就重写”。真正应先确认四件事:是否存在长事务阻止旧版本回收、是否有足够的临时磁盘空间、业务能否承受锁等待,以及空间回收后是否很快又会被同类更新重新占满。

如果表长期高频更新,可以进一步检查更新模式、索引数量与 fillfactor,减少无意义的索引维护和页分裂。膨胀治理的目标不是追求最小文件,而是在可接受的锁和 I/O 成本下,让页面空间能够稳定复用,并把真正异常的表或索引重建掉。

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

目录与阅读设置

阅读设置

17

记录一个想法

表与索引膨胀

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

登录哪都通