使用 VACUUM 检测和消除 PostgreSQL 中的数据库膨胀

PostgreSQL 数据库膨胀发生在旧行版本和过大索引占用的空间超过实时数据所需时。您可能会注意到备份变大、扫描变慢、存储成本上升，或者自动清理持续工作但表在磁盘上并未缩小。

修复方法取决于您需要回收哪种空间。标准 VACUUM 使死元组空间在 PostgreSQL 内部可重用。VACUUM FULL、REINDEX 以及 pg_repack 等工具可以物理缩小对象，但它们具有不同的锁定和操作成本。

理解 PostgreSQL MVCC 和膨胀

要有效对抗膨胀，我们必须首先了解其根本原因。PostgreSQL 的 MVCC 架构确保读取者永远不会阻塞写入者，反之亦然。当更新一行时，PostgreSQL 不会覆盖旧行；它会插入一个新版本，并将旧版本标记为死。类似地，删除的行会留下死元组。

当这些死元组积累的速度快于维护进程（自动清理或手动 VACUUM）清理或重用空间的速度时，就会发生膨胀。

数据库膨胀的后果

膨胀在几个关键领域影响性能：

增加磁盘空间使用： 死元组占用物理空间，迫使表和索引消耗比必要更多的存储。
顺序扫描变慢： 数据库引擎在表扫描期间必须读取死元组，增加 I/O 负载。
索引效率低下： 膨胀的索引更大，导致遍历索引结构时需要更多磁盘读取。
浪费自动清理工作： 自动清理必须更努力、更长时间地清理表，可能延迟其他表的关键维护。

检测数据库膨胀

检测从目录统计信息和对象大小开始。将简单查询视为分类，而不是精确的膨胀测量，因为表布局、填充因子、TOAST 数据和索引都会影响实际数字。

1. 使用 `pg_stat_user_tables` 识别膨胀的表

pg_stat_user_tables 视图提供用户定义表的统计信息。我们可以通过比较分配给表的总大小与实时数据的大小来计算近似膨胀。

要监控的关键指标：

n_dead_tup：死元组数量。
last_autovacuum、last_vacuum：上次运行维护的时间。

简单计数很有用，因为它们显示清理压力正在积累的地方。与 n_live_tup 相比，大量的 n_dead_tup 是更仔细检查表的好理由。

示例查询（查找清理候选）：

此查询突出显示具有许多死元组的表，并显示上次清理运行的时间：

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tuple_pct,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    last_autovacuum,
    last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY
    n_dead_tup DESC
LIMIT 10;

2. 评估膨胀的索引

膨胀通常严重影响索引。首先查找高变更率表上异常大的索引：

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

仅凭大尺寸并不能证明膨胀，但它告诉您哪些索引值得使用更深入的工具（如 pgstattuple 扩展或监控平台的膨胀查询）进行检查。

管理膨胀：VACUUM 的作用

VACUUM 是 PostgreSQL 回收死元组空间和更新可见性图的主要工具。

自动清理：第一道防线

默认情况下，PostgreSQL 自动运行 autovacuum 进程。当达到阈值时，自动清理会执行标准 VACUUM（将空间标记为内部可重用，但不会释放回操作系统）。该阈值由 autovacuum_vacuum_scale_factor（默认 0.2 或表大小的 20%）加上 autovacuum_vacuum_threshold（默认 50 个元组）定义。

配置提示： 对于高变更率表，考虑降低 scale_factor 以更早触发维护，防止大量膨胀积累。

-- 示例：为关键表 'orders' 设置激进的自动清理参数
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 100);

标准 VACUUM 与 VACUUM FULL

有两种主要的清理模式：

标准 `VACUUM`

标准 VACUUM 将死元组标记为在现有物理文件内可重用。它不会缩小磁盘上的表文件大小。这是非阻塞的，对于高流量表是安全的。

VACUUM table_name;
VACUUM (VERBOSE) table_name; -- 显示有关清理元组的统计信息

`VACUUM FULL`（空间回收工具）

VACUUM FULL 重写整个表文件，以物理方式移除死元组并将空间回收给操作系统。

警告： VACUUM FULL 在其持续时间内需要对表进行ACCESS EXCLUSIVE 锁。这意味着对该表的所有读取和写入操作都将被阻塞，直到 VACUUM FULL 完成。请谨慎对大型、使用频繁的表使用此命令。

VACUUM FULL table_name;

最佳实践： 仅在膨胀严重且您可以承受停机时间，或在计划维护窗口期间使用 VACUUM FULL。

高级抗膨胀策略

当 VACUUM FULL 过于干扰时，存在其他方法以更少的停机时间回收空间。

1. 重建索引

当索引膨胀是主要问题时，可以单独重建索引。对于繁忙系统，首选并发形式，以便在大部分操作期间继续读取和写入：

REINDEX INDEX CONCURRENTLY index_name;

普通 REINDEX INDEX index_name; 更快但需要更强的锁，因此在维护窗口期间使用它。

2. 使用 `pg_repack` 进行在线表重写

pg_repack 实用程序是消除表膨胀且停机时间最短的首选方法。它通过创建原始表结构和数据的新干净副本，同步应用更改，然后原子地交换表来工作。

pg_repack 的工作原理：

它创建一个镜像原始表的临时表（_new）。
它使用触发器持续监控原始表上的更改。
它执行最终的同步复制和交换。

安装和使用（典型流程）：

为您的 PostgreSQL 版本安装扩展和 CLI 包，在数据库中启用扩展，然后从 shell 运行 pg_repack 命令：

CREATE EXTENSION pg_repack;

pg_repack --table=public.critical_table --dbname=mydb

关于 pg_repack 的说明： 虽然与 VACUUM FULL 相比，它显著减少了锁定，但它仍然需要创建触发器并复制数据，这会暂时消耗额外的 I/O 和存储。

要点

当您在膨胀导致故障之前进行监控时，数据库膨胀是可管理的。通过调整自动清理进行预防优于紧急重写。当膨胀发生时，遵循此层次结构：

监控： 定期检查 pg_stat_user_tables 中较高的 n_dead_tup 计数。
调整自动清理： 对于活动表，降低 scale factor 以确保标准 VACUUM 更频繁地运行。
修复： 如果膨胀较小，如果表活动下降，标准 VACUUM table_name 可能就足够了。
激进修复（低停机时间）： 使用 pg_repack 在线重写表结构。
紧急修复（高停机时间）： 仅当停机时间可接受时，才将 VACUUM FULL 作为最后手段使用，因为它持有排他锁。

在任何重写之前，检查长时间运行的事务，并确保您有足够的可用磁盘空间用于操作。