防止膨胀：提升性能的高级PostgreSQL清理策略

PostgreSQL是一个强大且多功能的开源关系型数据库，它依赖多种内部机制来维护数据完整性和性能。其中，VACUUM操作在回收存储空间和防止因死元组导致的性能下降方面扮演着关键角色。虽然VACUUM通常以基础术语讨论，但理解和实施高级清理策略可以显著影响PostgreSQL数据库的健康和速度。

表膨胀是繁忙数据库中常见的问题，当删除或更新的行留下未立即移除的死元组时就会发生。这些死元组占用磁盘空间，并且由于数据库需要扫描更多数据，可能会减慢查询执行速度。自动清理（Autovacuum）是PostgreSQL的自动化后台进程，旨在管理这一问题，但其默认设置并不总是适用于所有工作负载。有用的工作是知道哪些表需要更积极的清理，哪些可以保持原样，以及何时手动维护窗口值得中断。

理解表膨胀及其影响

PostgreSQL使用多版本并发控制（MVCC）系统。当一行被更新时，会创建该行的新版本，旧版本被标记为死亡。类似地，当一行被删除时，它被标记为死亡但不会立即移除。这些死元组会保留在表中，直到VACUUM操作清理它们。如果VACUUM运行不够频繁或不够积极，死元组就会积累，导致表膨胀。

表膨胀的后果是显著的：

磁盘使用增加：膨胀的表消耗比必要更多的磁盘空间，可能导致存储问题和备份时间增加。
查询性能下降：扫描膨胀表的查询必须处理更多数据，包括死元组，导致执行时间延长。索引膨胀也有类似的负面影响。
缓存效率降低：膨胀的表和索引占用数据库缓存中更多空间，可能减少内存中可保留的活跃使用数据量。
自动清理开销：如果自动清理难以跟上元组更新和删除的速度，它本身可能成为性能瓶颈。

自动清理调优：第一道防线

自动清理是一个后台进程，旨在自动对发生显著变化的表执行VACUUM和ANALYZE操作。虽然默认启用，但其有效性很大程度上取决于正确的配置。调优自动清理参数对于在不造成过度系统负载的情况下防止膨胀至关重要。

在postgresql.conf中找到的关键自动清理配置参数：

autovacuum_vacuum_threshold：在对表运行VACUUM之前，更新或删除元组的最小数量。默认值为50。
autovacuum_vacuum_scale_factor：运行VACUUM之前表大小的一部分。默认值为0.2（20%）。
- 如果(死元组数量) > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * (活元组数量)，则触发VACUUM。
autovacuum_analyze_threshold：在运行ANALYZE之前，插入、更新或删除元组的最小数量。默认值为50。
autovacuum_analyze_scale_factor：运行ANALYZE之前表大小的一部分。默认值为0.1（10%）。
- 如果(更改元组数量) > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * (活元组数量)，则触发ANALYZE。
autovacuum_vacuum_cost_delay：如果超过成本限制，休眠的时间（以毫秒为单位）。默认值为20毫秒。
autovacuum_vacuum_cost_limit：清理进程在休眠前可以累积的最大成本量。默认值为-1（意味着如果设置了vacuum_cost_limit则使用它，否则实际上无限制，这并不理想）。
autovacuum_max_workers：可以同时运行的后台清理进程的最大数量。默认值为3。
autovacuum_nap_time：启动自动清理任务之间的最小延迟。默认值为1分钟。

实际自动清理调优场景：

高事务率数据库：对于频繁更新和删除的表，您可能需要降低autovacuum_vacuum_threshold和autovacuum_vacuum_scale_factor以更频繁地触发清理。例如，在繁忙的表上，您可以设置：
```
ALTER TABLE your_table SET (autovacuum_vacuum_threshold = 500, autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE your_table SET (autovacuum_analyze_threshold = 200, autovacuum_analyze_scale_factor = 0.02);
```
这使得对该特定表的清理更加积极。
偶尔更新的大型静态表：对于主要是读取且很少更新的表，默认设置可能就足够了，或者您甚至可以增加scale_factor以减少不必要的清理开销。
控制自动清理影响：为了防止自动清理消耗过多资源，您可以调整autovacuum_vacuum_cost_delay和autovacuum_vacuum_cost_limit。正确的值取决于存储速度和工作负载，因此请在正常流量期间进行测试，而不是盲目复制数字。
```
ALTER TABLE your_table SET (
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_cost_delay = 5
);
```
session_replication_role不是自动清理调优控制。它影响触发器和规则行为，不应作为膨胀管理的快捷方式。

手动VACUUM最佳实践

虽然自动清理至关重要，但在某些情况下，手动VACUUM操作是必要或有益的：

大量数据加载/删除后：在大量批量操作后执行手动VACUUM可以立即回收空间并防止膨胀积累。
当自动清理落后时：如果您观察到尽管自动清理在运行，但仍有显著膨胀，手动VACUUM可以提供即时清理。
VACUUM FULL用于极端膨胀：在严重膨胀的情况下，即使常规VACUUM也不足够，可以使用VACUUM FULL。然而，VACUUM FULL会将整个表重写到一个新文件中，这是一个阻塞操作（需要排他锁），并且在大表上可能需要很长时间。应极其谨慎地使用，最好在维护窗口期间进行。
VACUUM (FREEZE)：此选项强制VACUUM冻结任何足够旧的元组，这些元组被视为对所有未来事务永久可见。这有助于防止VACUUM警告并减少事务ID回卷问题的可能性。

手动VACUUM命令：

标准VACUUM：回收空间并使其可重用。除非使用TRUNCATE，否则不会显著缩小磁盘上的文件大小。
```
VACUUM your_table;
VACUUM VERBOSE your_table; -- 提供更多输出
```
VACUUM ANALYZE：执行VACUUM，然后更新表统计信息。这对查询规划器至关重要。
```
VACUUM ANALYZE your_table;
```
VACUUM FULL：重写表，回收所有未使用空间并缩小文件。需要排他锁。
```
VACUUM FULL your_table;
```
VACUUM (FREEZE)：强制冻结旧元组。
```
VACUUM (FREEZE) your_table;
```
VACUUM (TRUNCATE)：在PostgreSQL 13+中可用，此选项可以回收表文件末尾的空间，类似于TRUNCATE但不需要整个操作的排他锁。它仍然需要在结束时短暂获取排他锁。
```
VACUUM (TRUNCATE) your_table;
```

高级策略和考虑因素

除了基本的自动清理调优和手动VACUUM命令外，还有几种高级技术可以进一步优化清理：

监控膨胀：定期监控表的膨胀情况。您可以使用SQL查询来估计膨胀或使用监控工具。

-- 查询以估计膨胀（需要pgstattuple扩展）
-- CREATE EXTENSION pgstattuple;
SELECT
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
    pg_size_pretty(pg_table_size(oid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(oid) - pg_table_size(oid)) AS index_size,
    CASE WHEN dead_tuples > 0 THEN round(100.0 * dead_tuples / (live_tuples + dead_tuples), 2) ELSE 0 END AS percent_bloat
FROM (
    SELECT
        schemaname,
        relname,
        n_live_tup AS live_tuples,
        n_dead_tup AS dead_tuples,
        c.oid
    FROM pg_stat_user_tables s JOIN pg_class c ON s.relid = c.oid
) AS stats
WHERE live_tuples + dead_tuples > 0
ORDER BY percent_bloat DESC;

-- 无需扩展的替代查询以估计膨胀
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    CASE WHEN n_live_tup > 0 THEN round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) ELSE 0 END AS percent_bloat
FROM pg_stat_user_tables
ORDER BY percent_bloat DESC;

索引维护：索引也可能变得膨胀。如果需要，使用REINDEX重建它们。普通的REINDEX可能会阻塞正常工作；REINDEX CONCURRENTLY减少了中断，但需要更长时间，并且仍然需要规划。
```
REINDEX INDEX CONCURRENTLY your_index_name;
```
事务ID回卷预防：PostgreSQL重用事务ID。当一个ID达到其最大值时，它会回卷。为了防止数据损坏，PostgreSQL会冻结旧元组。VACUUM（特别是使用FREEZE）起着关键作用。自动清理的freeze_max_age参数决定了事务ID在自动清理被迫运行之前可以有多旧，即使其他阈值未达到。
```
-- 监控事务ID年龄
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC LIMIT 10;
```
如果您看到非常大的年龄，表明清理可能跟不上。
分区策略：对于非常大的表，考虑分区。清理较小的分区比清理一个巨大的单表快得多且资源消耗更少。
连接池：虽然不直接是清理策略，但高效的连接池（例如，使用PgBouncer）可以减少建立数据库连接的开销，从而间接有益于整体数据库性能，并允许后台维护任务（如自动清理）更顺畅地运行。
长事务控制：单个旧事务可能会阻止清理。检查长时间打开的会话，特别是idle in transaction会话，因为它们可以保持旧行版本可见并强制膨胀增长。
```
SELECT pid, state, now() - xact_start AS transaction_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;
```

实际清理调优工作流程

从有问题的表开始，而不是整个服务器。如果一个900 GB的订单表膨胀而一个20 MB的查找表是干净的，全局更改可能会制造噪音而不解决实际问题。首先查看pg_stat_user_tables：

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

然后将其与工作负载进行比较。一个类似队列的表不断更新状态可能需要较低的autovacuum_vacuum_scale_factor，因为等待一个巨大表的20%变成死亡太晚了。一个月度归档分区可能根本不需要积极的设置。按表设置允许您区别对待这些情况。

对于高更新表，一个常见的模式是：

ALTER TABLE job_events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 1000
);

这些数字是起点，不是普遍真理。观察死元组是否在自动清理运行之间停止增长，查询延迟是否改善，以及自动清理是否在高峰时段造成不可接受的I/O。

当膨胀已经严重时，常规VACUUM可能阻止恶化但不会缩小关系文件。这让许多团队感到惊讶。常规VACUUM使空间在表内可重用；它通常不会将大部分空间返回给操作系统。要物理缩小一个大表，您需要在破坏性选项之间做出选择，例如VACUUM FULL、表重建、分区轮换或允许时使用pg_repack等工具。每个选项都有锁定、磁盘空间和操作上的权衡。

选择最不痛苦的修复方法

如果表只是中度膨胀但仍然接收稳定的写入，从自动清理调优和旧事务清理开始。您希望PostgreSQL自然地重用空间，而不是在业务时间内重写一个大表。

如果表进行了一次性清理并且现在小得多，常规VACUUM将使空空间可重用于未来的插入和更新。如果您需要将该空间返回给操作系统，计划一个重写选项。VACUUM FULL简单但阻塞。pg_repack可能破坏性较小，但它是一个额外的扩展，并且仍然需要足够的空闲磁盘空间来构建替换结构。分区表为您提供了另一个选项：删除或分离旧分区，而不是从一个巨大表中删除数百万行。

如果索引是问题，不要习惯性地重建每个索引。检查哪些索引大、未使用或重复。pg_stat_user_indexes可以显示索引扫描计数，模式审查可以揭示重叠索引，例如(user_id)和(user_id, created_at)，其中可能只需要一个。移除一个真正未使用的索引可以提高写入性能并减少未来的清理工作。

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

在重启或统计重置后，对“未使用”索引要小心，因为计数器会重新开始。在删除任何内容之前，查看足够的历史记录。

好的清理策略在有效时是平淡无奇的。自动清理运行足够频繁，死元组不会堆积，手动维护保留给已知事件，旧事务被视为生产问题而不是无害的空闲会话。目标不是尽可能多地清理。目标是在不窃取应用程序所需的I/O的情况下，保持清理领先于变更。