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

数据库膨胀是 PostgreSQL 中一种常见但通常难以察觉的性能杀手。作为一种多版本并发控制（MVCC）数据库，PostgreSQL 通过保持旧行版本可用，直到引用它们的事务完成，来实现并发。当行被更新或删除时，旧版本（死元组）会被标记为可重用，但物理上仍保留在磁盘上，这会导致存储使用量增加、索引扫描变慢以及查询性能下降。本指南将深入探讨如何检测这种膨胀，并提供使用 PostgreSQL 核心维护工具：VACUUM 的实用、可操作的策略。

理解和管理膨胀对于维护任何高吞吐量 PostgreSQL 实例的健康和效率至关重要。忽视膨胀可能导致不必要的存储消耗和随时间的查询延迟累积，需要主动监控和定期维护。

理解 PostgreSQL MVCC 和膨胀

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

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

数据库膨胀的后果

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

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

检测数据库膨胀

检测依赖于查询系统统计视图，以估算表占用的物理大小与有用数据量之间的关系。

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

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

关键监控指标：

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

虽然简单的计数很有用，但更精确的计算涉及估算大小差异。虽然没有一个通用的内置公式，但社区驱动的脚本可以显著估算膨胀。

示例查询（估算膨胀率）：

此示例估算死元组与总元组的比例，突出显示了需要积极清理的候选表。

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    pg_size_pretty(pg_relation_size(oid)) AS total_size,
    pg_size_pretty(pg_relation_size(oid) - (n_live_tup * (23 + avg_row_size))::bigint) AS estimated_bloat_size
FROM
    pg_stat_user_tables
WHERE
    n_dead_tup > 1000 -- 过滤掉可忽略的噪音
ORDER BY
    n_dead_tup DESC
LIMIT 10;

2. 评估膨胀的索引

膨胀经常显著影响索引。PostgreSQL 提供了 pg_stat_user_indexes 视图，但索引膨胀最好通过分析索引大小与其中包含的条目数量之间的关系来量化。膨胀的索引可能包含许多指向死元组的指针，从而增加了遍历时间。

管理膨胀：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. 重建索引（替代索引 `VACUUM FULL`）

可以重建单个索引，而不会完全长时间锁定主表，尽管在最终切换期间仍需要短暂的锁定。

REINDEX INDEX index_name;
-- 或在不进行完全表重写的情况下重建表上的所有索引：
REINDEX TABLE table_name;

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

pg_repack 工具是在线消除表膨胀且停机时间最小的首选方法。它通过创建与旧表相邻的表结构和数据的新、干净副本，同步应用更改，然后原子地交换表来工作。

pg_repack 的工作原理：

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

安装和使用（概念示例）：

首先，安装扩展（通常通过您的操作系统包管理器）。

-- 连接到您的 PostgreSQL 数据库
CREATE EXTENSION pg_repack;

-- 在线重建膨胀的表
SELECT pg_repack.repack('public', 'critical_table', 'ddl_concurrency=none');

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

总结和后续步骤

数据库膨胀是 PostgreSQL 中一个可控的问题。通过优化自动清理设置进行预防，始终优于事后清理。当膨胀发生时，请遵循以下层级：

监控：定期检查 pg_stat_user_tables 以获取较高的 n_dead_tup 计数。
调整自动清理：对于活动表，降低比例因子以确保标准 VACUUM 更频繁地运行。
修复：如果膨胀轻微，标准 VACUUM table_name 可能就足够了，前提是表活动减少。
积极修复（低停机时间）：使用 pg_repack 在线重写表结构。
紧急修复（高停机时间）：仅在停机时间可接受时，将 VACUUM FULL 作为最后手段，因为它会持有独占锁。

通过将这些检测和修复步骤集成到您的日常维护计划中，您可以确保您的 PostgreSQL 环境保持精简、快速和高效。