从零开始学PostgreSQL (五)： 日常数据库维护任务

数据库维护任务概述

PostgreSQL 数据库的维护是确保数据库高效、可靠运行的关键。维护任务可以分为几个关键的方面，包括常规吸尘（VACUUM）、例行重索引（REINDEX）、日志文件维护等。下面我们将详细探讨这些维护任务：

常规VACUUM

吸尘基础

PostgreSQL 的 VACUUM 命令是数据库维护的关键部分，主要用于以下几个方面：

1. 空间回收：VACUUM 清除已被更新或删除的行所占用的磁盘空间，使得这些空间可以被重用，这对于提高存储效率至关重要。
2. 统计信息更新：VACUUM 更新用于查询优化的统计信息，帮助查询规划器做出更准确的决策，从而提升查询性能。
3. 加速索引扫描：VACUUM 更新可见性映射（visibility map），这有助于加速仅索引扫描（index-only scans），通过标记页面是否包含可见行来减少不必要的数据访问。
4. 防止事务ID环绕：VACUUM 还有助于防止因事务ID（XID）环绕导致的数据丢失。当数据库执行大量事务时，32位事务ID可能回绕，VACUUM 可以通过冻结旧行的事务ID来避免这一问题。

VACUUM 命令有两种主要形式：

• Standard VACUUM：这是默认的 VACUUM 类型，它可以与数据库的日常操作并行运行，因此对在线事务处理的影响较小。
• VACUUM FULL：这种形式可以更彻底地回收磁盘空间，但它需要更多的I/O操作和时间，且会锁定整个表，阻止其他会话对表进行修改，因此通常不建议在繁忙的生产环境中频繁使用。

VACUUM 在运行时会产生大量的I/O负载，可能影响其他会话的性能。为了减轻这种影响，可以通过调整配置参数来优化VACUUM的执行

恢复磁盘空间

在 PostgreSQL 中，数据行的更新 (UPDATE) 或删除 (DELETE) 操作不会立即清除旧的行版本。这是 MVCC（多版本并发控制）机制的必要组成部分，确保即使在并发事务环境下，每个事务也能看到一致的数据视图。然而，随着事务的累积，数据库中会积累大量的过时或删除的行版本，这会导致表膨胀和磁盘空间的无效利用。

为了处理这些问题，PostgreSQL 提供了 VACUUM 命令，它有几种不同的形式，用于不同的清理需求：

1. 标准 VACUUM：这种形式的 VACUUM 清除表和索引中的死行版本，并标记空间供将来重用。它不会显著压缩表的物理大小，也不会释放磁盘空间给操作系统，除非在特殊情况下，表的末尾有连续的空闲页面。
2. VACUUM FULL：这是一种更彻底的表压缩方法，通过重新写入整个表来消除死行版本，从而最小化表的物理大小。然而，它需要额外的磁盘空间来存储新表的副本，并且可能需要较长的时间来完成。

VACUUM 的目标通常是保持磁盘空间的稳态使用，而不是追求最小化的表大小。频繁的、适度的 VACUUM 运行比不频繁的 VACUUM FULL 更适合维护高更新率的表。

对于那些不使用 autovacuum 的环境，通常的做法是在低负载期间安排定期的 VACUUM 操作，对于高更新率的表，可能需要更频繁的清理。对于有多个数据库的集群，使用 vacuumdb 工具可以更方便地执行跨数据库的 VACUUM。

当表经历大量更新或删除活动，且需要回收大量磁盘空间时，VACUUM FULL、CLUSTER 或 ALTER TABLE 的重写变体可能更合适，尽管它们需要表锁和额外的磁盘空间。

最后，对于定期清空全部内容的表，TRUNCATE 命令是一个更快、更高效的选择，因为它立即删除表的所有内容，无需后续的 VACUUM 操作。然而，TRUNCATE 不遵循 MVCC 语义，因此在某些情况下可能不适用

更新 Planner 统计信息

PostgreSQL 的查询规划器依赖于准确的统计信息来生成高效的查询计划。这些统计信息由 ANALYZE 命令收集，该命令可以独立调用或作为 VACUUM 的一部分调用。统计信息对于优化查询计划至关重要，不准确的信息可能导致效率低下。

ANALYZE 可以被 autovacuum 守护程序自动触发，当表的内容发生变化时，守护程序会根据更改的行数自动运行 ANALYZE。然而，如果更新不影响统计上重要的列，管理员可能选择手动运行 ANALYZE，特别是在继承结构中，因为 autovacuum 只会在父表自身发生变化时分析父表，而不会考虑子表的更改。因此，可能需要手动在父表上运行 ANALYZE 以更新整个继承树的统计信息。

对于频繁更新的表，定期更新统计信息更为重要，但对于数据分布变化不大的表，则可能不需要频繁更新。例如，一个包含时间戳的列可能需要更频繁的统计信息更新，而一个包含 URL 的列，尽管更新频繁，但其值的分布可能变化较慢。

ANALYZE 可以针对整个表或特定列运行，允许根据应用需求灵活更新统计信息。在实践中，通常只需要分析整个数据库，因为这是个快速操作，而且 ANALYZE 使用统计抽样而非全表扫描。

对于在 WHERE 子句中使用且数据分布不规则的列，可能需要更详细的直方图统计信息，这可以通过 ALTER TABLE SET STATISTICS 设置。对于涉及函数调用的表达式，创建统计信息对象或表达式索引可以收集更多关于函数选择性的信息，从而改进查询计划。

需要注意的是，autovacuum 不会自动为外部表或分区表运行 ANALYZE，因此对于这些表，管理员需要定期手动运行 ANALYZE 以保持统计信息的最新状态，确保查询规划器能够生成最佳的执行计划。

总之，维护准确的统计信息对于 PostgreSQL 的性能至关重要，而适时和适当地使用 ANALYZE 命令是实现这一目标的关键。

更新可见性地图

在 PostgreSQL 中，VACUUM 命令不仅用于回收存储空间和重新组织数据，还负责维护一种称为“可见性地图”的数据结构。这个地图对于每个表都是独立的，它的作用是跟踪哪些数据页上的所有元组对当前所有活动事务以及未来的事务（直到数据页再次被修改）都是可见的。可见性地图的存在服务于两个主要目标：

1、优化 VACUUM 运行：

• 当 VACUUM 下次运行时，它可以跳过那些标记为完全可见的页面，因为这些页面不需要任何清理工作。这显著减少了 VACUUM 的运行时间和资源消耗。

2、提高索引扫描效率：

• PostgreSQL 的索引不保存元组的可见性信息。在常规索引扫描中，每当找到一个匹配的索引条目时，系统都需要回查到主表（堆）中的实际元组，以确认该元组对当前事务是否可见。
• 通过使用可见性地图，系统在进行仅索引扫描时，可以先检查地图以判断页面上的所有元组是否都对当前事务可见。如果确认所有元组都可见，那么就可以避免回查主表，直接使用索引完成查询。这对于大型数据集尤其有效，因为可见性地图相对较小，更容易被缓存在内存中，从而避免了对磁盘的额外访问，提高了查询速度。

综上所述，可见性地图是一种关键的优化机制，它帮助 PostgreSQL 减少不必要的磁盘 I/O 操作，提升查询效率和 VACUUM 的性能。

防止事务 ID 环绕失败

VACUUM 可以防止事务 ID 环绕失败，这是一个潜在的问题，当所有事务 ID 被使用后，新的事务将无法被分配事务 ID。

PostgreSQL 使用多版本并发控制 (MVCC) 来管理并发事务和数据版本。在 MVCC 下，事务可以看到其开始时的数据快照，这意味着事务无法看到在其开始之后插入或修改的数据行。事务 ID (XID) 用来标识这些事务，而每个事务都有一个唯一的 XID。

然而，由于 XID 是 32 位的，因此它们在理论上有一个上限，即 40 亿个事务。当达到这个上限时，XID 计数器将回绕至零，这会导致一个问题：先前的事务 ID 突然看起来像是未来的事务，从而导致那些事务的数据变得对当前事务不可见，这可能会引起数据丢失。

为了解决这个问题，PostgreSQL 实现了 XID 冻结机制。当一行被标记为冻结时，它被赋予一个特殊的事务 ID (FrozenTransactionId)，这个 ID 比所有常规 XID 都小，这意味着这些行将永远对任何未来的事务可见，无论 XID 是否回绕。

为了确保数据的一致性和避免 XID 回绕问题，PostgreSQL 提供了以下机制：

1. VACUUM 命令：用于清理过时的行版本和更新统计信息。当 VACUUM 发现行的 XID 超过了 vacuum_freeze_min_age 设置的阈值时，它会冻结这些行，使它们对所有未来事务永久可见。
2. autovacuum：这是一个后台进程，自动执行 VACUUM 操作。通过配置 autovacuum_freeze_max_age 参数，可以确保在 XID 达到回绕点前有足够的安全边界，从而防止数据丢失。
3. 系统表监控：系统表如 pg_class 和 pg_database 包含了关于表和数据库中最早未冻结 XID 的信息，这有助于管理员监控 XID 的年龄并采取预防措施。
4. XID 管理参数：vacuum_freeze_min_age, vacuum_freeze_table_age, 和 autovacuum_freeze_max_age 等参数用于控制冻结和自动真空的频率和行为，以平衡性能和安全性。
5. 警告和错误：当系统检测到 XID 快要回绕时，会发出警告，提示管理员执行数据库范围的 VACUUM。如果剩余事务数低于 300 万，系统将拒绝分配新的 XID，只允许只读事务，直到问题解决。
6. 解决方法：解决长时间运行的事务，清理准备好的事务，删除旧的复制槽，并执行必要的 VACUUM 操作以推进冻结点。
7. 单用户模式：虽然不推荐，但在某些极端情况下，可能需要在单用户模式下执行 VACUUM 或其他操作，以避免更大的问题。

这两个 SQL 查询分别用于监控 PostgreSQL 数据库中表和数据库级别的事务 ID (XID) 年龄，这对于理解数据库的健康状况和维护需求至关重要。

SELECT c.oid::regclass as table_name,
      greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm');

这个查询的作用是列出所有普通表（'r' 表示常规表，'m' 表示 TOAST 表）及其对应的最老未冻结事务 ID 的年龄。relfrozenxid 是 pg_class 系统目录表中的一个字段，它
记录了表中最早的未冻结事务 ID。age() 函数计算当前事务 ID 与 relfrozenxid 之间的差值，即事务的年龄。通过 greatest() 函数，查询返回表本身和其关联的 TOAST 表
中更老的事务 ID 年龄。
SELECT datname, age(datfrozenxid) FROM pg_database;
这个查询则展示了每个数据库中最早的未冻结事务 ID 的年龄。pg_database 系统目录表中的 datfrozenxid 字段存储了数据库中最老的未冻结事务 ID。通过 age() 函数，查询
计算当前事务 ID 与 datfrozenxid 的差值，以了解数据库层面的事务年龄。

Autovacuum 守护程序

PostgreSQL 的 autovacuum 功能是一项强大的自动化工具，用于在后台自动执行 VACUUM 和 ANALYZE 命令，以维护数据库的健康状态。以下是 autovacuum 的关键要素和功能：

1、架构：

• Autovacuum 启动器：这是一个持久运行的后台进程，负责启动所有数据库的 autovacuum worker 进程。
• Autovacuum Worker 进程：这些进程由启动器按需启动，每个进程检查其所属数据库中的表，决定是否需要执行 VACUUM 或 ANALYZE。

2、工作流程：

• Autovacuum 会根据表的活动水平决定是否执行维护操作，使用统计信息来判断是否达到设定的阈值。
• 工作进程会检查表中自上次 VACUUM 或 ANALYZE 以来的插入、更新或删除操作的数量，以及事务 ID 的年龄。

3、配置参数：

• track_counts 必须设置为 true 才能使用 autovacuum，以便收集必要的统计信息。
• autovacuum_naptime 控制工作进程启动的频率。
• autovacuum_max_workers 限制同时运行的 worker 进程数量。
• autovacuum_freeze_max_age 设置事务 ID 年龄的上限，超过这个值的表会被强制 VACUUM。
• autovacuum_vacuum_threshold 和 autovacuum_vacuum_scale_factor 用于计算 VACUUM 的阈值。
• autovacuum_analyze_threshold 和 autovacuum_analyze_scale_factor 用于决定何时执行 ANALYZE。

4、性能控制：

• Autovacuum 试图平衡运行中的 worker 进程，以避免过度的 I/O 负载，但单个表的参数可以覆盖全局设置。
• Autovacuum worker 进程通常不会阻止其他命令，但如果其他进程请求的锁与 autovacuum 持有的锁冲突，autovacuum 可能会被中断。

5、例外情况：

• 分区表不会直接被 autovacuum 处理，但可以手动运行 ANALYZE 来保持统计信息的准确性。
• 临时表也无法被 autovacuum 访问，需要通过会话 SQL 命令进行维护。

7、监控与日志：

• log_autovacuum_min_duration 参数可以设置，以记录 autovacuum worker 进程的活动，便于监控和调试。

警告：

如果有命令定期获取与 autovacuum 锁冲突的锁，比如 ANALYZE，这可能会干扰 autovacuum 的正常运行，应予以注意。

总之，autovacuum 是 PostgreSQL 数据库维护的重要组成部分，它通过自动化的 VACUUM 和 ANALYZE 操作，确保了数据库性能和数据一致性，同时降低了数据库管理员的负担。正确配置和监控 autovacuum 参数对于保持数据库的健康运行至关重要。

例行重索引

PostgreSQL 的索引维护是确保数据库性能的关键部分，尤其是在数据模式或使用模式随时间发生变化的情况下。定期执行索引重编（reindexing）可以帮助优化索引的结构和性能，以下是进行例行重索引的一些原因和实践：

索引膨胀和效率问题

• B-Tree 索引：当大量索引键被删除，但仍有少量键保留时，索引页面可能仍然分配，导致空间效率低下。这在数据模式中常见于大量插入和删除操作的情况，尤其是当多数键在一个范围内被删除时。
• 非 B-Tree 索引：对于哈希、GiST、SP-GiST、GIN 和 BRIN 等非 B-Tree 索引类型，索引膨胀的可能性尚未得到充分研究，因此建议定期监控这些索引的物理大小。

索引性能考量

• 新旧索引的性能差异：新构建的 B-Tree 索引通常比经过多次更新的索引访问速度略快，因为逻辑上相邻的页面在新索引中也物理上相邻，这可以减少磁盘 I/O，从而提高性能。

REINDEX 命令的使用

• 安全性和便利性：REINDEX 命令是重建索引的安全且简单的方法。它将重建索引，消除膨胀，并重新排序页面以提高效率。
• 锁的使用：默认情况下，REINDEX 需要 ACCESS EXCLUSIVE 锁，这会阻止对表的任何读写操作。为了避免长时间的锁定，可以使用 REINDEX CONCURRENTLY 选项，它只需要 SHARE UPDATE EXCLUSIVE 锁，允许其他会话在索引重建过程中继续读取数据。

实践建议

• 对于表现出高膨胀率的 B-Tree 索引，建议定期进行重索引，特别是在数据模式涉及大量删除操作的情况下。
• 对于非 B-Tree 索引，定期监控其物理大小，并根据需要进行重索引。
• 使用 REINDEX CONCURRENTLY 选项来最小化对数据库操作的影响，特别是在高负载的生产环境中。

总之，定期的索引重编是维护 PostgreSQL 数据库性能的关键实践。通过识别和解决索引膨胀问题，以及利用 REINDEX 命令的选项来最小化对运行中的数据库的影响，可以确保索引持续高效地支持查询性能。

日志文件维护

日志文件记录了数据库服务器的活动，这对于故障诊断和审计非常重要。定期清理和归档日志文件是必要的，以避免日志文件过大而导致磁盘空间不足或性能下降。

使用场景

• 常规吸尘：在频繁更新或删除数据的表上定期运行，特别是在 OLTP（联机事务处理）系统中。
• 例行重索引：在高写入负载下或当查询性能下降时进行。
• 日志文件维护：在生产环境中，日志文件应定期归档和清理，特别是在高流量或合规性要求严格的场景中。

注意事项

• 性能影响：VACUUM 和 REINDEX 可能会影响在线事务的性能，应尽量安排在低峰时段执行。
• 资源消耗：这些维护操作可能需要大量的 CPU 和 I/O 资源，确保服务器有足够的资源可用。
• 备份和恢复：在执行重大维护操作前，建议先做好数据备份。
• 监控和调整：持续监控数据库性能和资源使用情况，适时调整维护策略和参数。

总结

定期执行 PostgreSQL 数据库的维护任务是保持数据库健康和高性能的关键。通过执行 VACUUM、REINDEX 和适当的日志文件管理，可以确保数据的完整性、提高查询性能，并降低系统故障的风险。维护策略应根据数据库的实际工作负载和业务需求进行定制，以达到最佳效果。