PostgreSQL 分区表大量更新删除场景下的 VACUUM 优化策略：实战案例与参数调优

你好，我是你的数据库老伙计，这次咱们来聊聊 PostgreSQL 分区表在频繁更新和删除操作下的 VACUUM 优化。对于有经验的 PostgreSQL DBA 来说，VACUUM 的重要性不言而喻，尤其是在分区表环境下，如何高效地进行垃圾回收，直接关系到数据库的性能和稳定性。别担心，我会结合实际案例，一步步带你深入理解并掌握这块硬骨头。

为什么分区表需要特别关注 VACUUM？

先来明确一个问题：为什么分区表需要特别关注 VACUUM？

咱们都知道，PostgreSQL 采用 MVCC（多版本并发控制）机制，更新和删除操作并不会立即物理删除旧数据，而是将其标记为“死亡元组”。这些死亡元组会占用存储空间，并影响查询性能。VACUUM 的作用就是清理这些死亡元组，回收空间，并更新表的统计信息，以便查询优化器做出正确的决策。

对于普通表，VACUUM 的影响可能还不太明显。但对于分区表，情况就大不一样了：

• 数据量大： 分区表通常用于存储海量数据，这意味着死亡元组的数量也会非常庞大。
• 更新删除频繁： 很多业务场景下，分区表会频繁进行更新和删除操作，例如按时间分区的日志表，每天都会产生大量新数据，同时也会删除旧数据。
• 分区数量多： 分区表可能包含成百上千个分区，每个分区都需要进行 VACUUM。

如果 VACUUM 策略不当，会导致以下问题：

• 性能下降： 大量死亡元组会导致查询变慢，甚至阻塞正常的业务操作。
• 空间膨胀： 死亡元组占用大量存储空间，导致磁盘空间不足。
• 统计信息过时： 查询优化器无法做出正确的决策，导致查询计划不优。

因此，针对分区表的 VACUUM 优化至关重要。

常规 VACUUM 操作

在深入探讨分区表的 VACUUM 优化之前，咱们先回顾一下 PostgreSQL 中常规的 VACUUM 操作。

PostgreSQL 提供了两种 VACUUM 命令：

• VACUUM (FULL)： 这是最彻底的 VACUUM 方式，它会锁定整个表，并重新组织数据，完全回收空间。这种方式效率低，会阻塞所有读写操作，通常只在特殊情况下使用（例如，表结构发生重大变化）。
• VACUUM： 这是常用的 VACUUM 方式，它不会锁定整个表，可以在线执行。它会扫描表中的死亡元组，并将其标记为可重用空间，但不会立即回收空间。这种方式对业务影响较小，但回收空间的效果不如 VACUUM FULL。

另外，PostgreSQL 还提供了 ANALYZE 命令，用于更新表的统计信息。通常，我们会将 VACUUM 和 ANALYZE 一起使用，例如：

VACUUM ANALYZE your_table;

Autovacuum：自动 VACUUM

手动执行 VACUUM 显然不现实，PostgreSQL 提供了 Autovacuum 守护进程来自动执行 VACUUM 和 ANALYZE 操作。

Autovacuum 的行为由一系列参数控制，这些参数可以在 postgresql.conf 文件中配置，也可以通过 ALTER TABLE 命令针对特定表进行配置。

关键 Autovacuum 参数

以下是一些关键的 Autovacuum 参数：

• autovacuum (boolean)： 是否启用 Autovacuum。
• autovacuum_vacuum_threshold (integer)： 触发 VACUUM 操作的最小死亡元组数量。默认值是 50。
• autovacuum_vacuum_scale_factor (floating point)： 触发 VACUUM 操作的死亡元组比例。默认值是 0.2，表示当死亡元组数量超过表总行数的 20% 时触发 VACUUM。
• autovacuum_analyze_threshold (integer)： 触发 ANALYZE 操作的最小变更行数。默认值是 50。
• autovacuum_analyze_scale_factor (floating point)： 触发 ANALYZE 操作的变更行数比例。默认值是 0.1，表示当变更行数超过表总行数的 10% 时触发 ANALYZE。
• autovacuum_vacuum_cost_delay (integer)： VACUUM 操作的成本延迟，用于限制 VACUUM 的 I/O 影响。默认值是 20 毫秒。
• autovacuum_vacuum_cost_limit (integer)： VACUUM 操作的成本限制。默认值是 200。
• autovacuum_naptime (integer)： Autovacuum 守护进程的休眠时间。默认值是 1 分钟。
• autovacuum_max_workers (integer)： Autovacuum 工作进程的最大数量。默认值是 3。

Autovacuum 工作原理

Autovacuum 守护进程会定期检查数据库中的所有表，根据 autovacuum_vacuum_threshold 和 autovacuum_vacuum_scale_factor 计算每个表的 VACUUM 阈值，如果表的死亡元组数量超过阈值，则触发 VACUUM 操作。

同样，Autovacuum 会根据 autovacuum_analyze_threshold 和 autovacuum_analyze_scale_factor 计算每个表的 ANALYZE 阈值，如果表的变更行数超过阈值，则触发 ANALYZE 操作。

Autovacuum 使用多个工作进程并行执行 VACUUM 和 ANALYZE 操作，工作进程的数量由 autovacuum_max_workers 参数控制。

分区表 Autovacuum 优化策略

了解了 Autovacuum 的基本原理后，咱们来看看如何针对分区表进行优化。

1. 针对分区表调整 Autovacuum 参数

对于分区表，我们可以通过 ALTER TABLE 命令针对每个分区表单独设置 Autovacuum 参数，这比全局设置更灵活。

例如，对于一个按时间分区的日志表，我们可以根据每个分区的数据量和更新频率，设置不同的 VACUUM 阈值：

-- 对最近一个月的分区设置更低的 VACUUM 阈值
ALTER TABLE your_partitioned_table SET (autovacuum_vacuum_scale_factor = 0.1, autovacuum_vacuum_threshold = 1000) WHERE partition_condition = '...';
 
-- 对历史分区设置更高的 VACUUM 阈值
ALTER TABLE your_partitioned_table SET (autovacuum_vacuum_scale_factor = 0.3, autovacuum_vacuum_threshold = 10000) WHERE partition_condition = '...';

2. 使用更激进的 VACUUM 策略

对于更新删除非常频繁的分区表，我们可以考虑使用更激进的 VACUUM 策略，例如：

• 降低 autovacuum_vacuum_scale_factor： 例如设置为 0.05，表示当死亡元组数量超过表总行数的 5% 时就触发 VACUUM。
• 降低 autovacuum_vacuum_threshold： 例如设置为 100，表示当死亡元组数量超过 100 个时就触发 VACUUM。
• 增加 autovacuum_max_workers： 如果服务器资源充足，可以增加 Autovacuum 工作进程的数量，加快 VACUUM 速度。
• 降低autovacuum_vacuum_cost_delay: 适当降低延迟，增加vacuum执行频率。

3. 定期手动 VACUUM

即使启用了 Autovacuum，也建议定期手动执行 VACUUM，特别是在业务低峰期。手动 VACUUM 可以更彻底地清理死亡元组，并更新统计信息。

4. 监控 VACUUM 状态

我们需要密切监控 VACUUM 的状态，及时发现问题并进行调整。PostgreSQL 提供了一些视图和函数来查看 VACUUM 相关信息：

• pg_stat_progress_vacuum： 查看当前正在进行的 VACUUM 操作的进度。
• pg_stat_all_tables： 查看每个表的最后一次 VACUUM 和 ANALYZE 时间，以及死亡元组数量等信息。
• pg_stat_user_tables: 只查看用户表。

5. 考虑使用 pg_repack

如果 VACUUM 无法有效回收空间，或者需要执行 VACUUM FULL，可以考虑使用 pg_repack 扩展。pg_repack 可以在线重新组织表，效果类似于 VACUUM FULL，但不会阻塞读写操作。

实战案例：日志分区表的 VACUUM 优化

假设我们有一个按天分区的日志表 logs，每天产生 1000 万条数据，我们需要保留最近 30 天的数据，每天凌晨 1 点删除过期数据。

初始配置

CREATE TABLE logs (
    id SERIAL,
    log_time TIMESTAMP,
    message TEXT
) PARTITION BY RANGE (log_time);
 
-- 创建分区
CREATE TABLE logs_20240101 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-01-02');
-- ... 创建其他分区 ...
 
-- 使用默认的 Autovacuum 参数

问题分析

运行一段时间后，我们发现以下问题：

• 查询性能下降，特别是查询最近几天的数据时。
• 磁盘空间占用率持续增长。
• 查看 pg_stat_all_tables，发现 logs 表的 n_dead_tup (死亡元组数量) 非常高。

优化步骤

1. 调整 Autovacuum 参数：

   -- 对最近 7 天的分区设置更激进的 VACUUM 策略
   ALTER TABLE logs SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 1000) WHERE log_time >= NOW() - INTERVAL '7 days';
    
   -- 对其他分区设置稍宽松的 VACUUM 策略
   ALTER TABLE logs SET (autovacuum_vacuum_scale_factor = 0.1, autovacuum_vacuum_threshold = 5000) WHERE log_time < NOW() - INTERVAL '7 days';

2. 增加 autovacuum_max_workers：

   # postgresql.conf
   autovacuum_max_workers = 5

   根据机器的资源情况，可以适当增大。

3. 手动 VACUUM:

    -- 每天凌晨 2 点执行手动 VACUUM
    VACUUM (VERBOSE, ANALYZE) logs;

4. 监控 VACUUM 状态：

   -- 查看 VACUUM 进度
   SELECT * FROM pg_stat_progress_vacuum;
    
   -- 查看表状态
   SELECT * FROM pg_stat_all_tables WHERE relname = 'logs';

优化效果

经过以上优化，查询性能明显提升，磁盘空间占用率得到控制，n_dead_tup 数量显著下降。

总结

PostgreSQL 分区表的 VACUUM 优化是一个持续的过程，需要根据实际业务场景和数据特点进行调整。没有一劳永逸的方案，只有不断地监控、分析、调整，才能找到最适合自己的 VACUUM 策略。

希望这篇文章能帮助你更好地理解和掌握 PostgreSQL 分区表的 VACUUM 优化。记住，实践出真知，多动手，多思考，你也能成为 PostgreSQL 高手！如果你还有其他问题，欢迎随时来找我交流。