我们之前的博客文章“我们最讨厌的 PostgreSQL 部分”讨论了大家最喜欢的 DBMS 多版本并发控制 (MVCC) 实现所带来的问题。其中包括版本复制、表膨胀、索引维护和真空管理。本文将探讨针对每个问题优化 PostgreSQL 的方法。
尽管 PostgreSQL 的 MVCC 实现是 Oracle 和 MySQL 等其他广泛使用的数据库中最差的，但它仍然是我们最喜欢的 DBMS，而且我们仍然喜欢它！通过分享我们的见解，我们希望帮助用户释放这个强大的数据库系统的全部潜力。好消息是 OtterTune 会自动为您解决许多此类问题（但不是全部！）。
问题#1：版本复制
当查询修改元组时，无论更新其一列还是所有列，PostgreSQL 都会通过复制其所有列来创建新版本。这种复制可能会导致大量数据重复并增加存储需求，特别是对于具有许多列和较大行大小的表。
优化：不幸的是，如果不对 PostgreSQL 的内部结构进行重大重写（这会造成破坏），就没有解决此问题的解决方法。这不像在情景喜剧中替换了一个没有人注意到的角色。正如我们在上一篇文章中提到的，EnterpriseDB 在 2013 年通过 zheap 项目开始了这条道路，但该项目的最后一次更新是在 2021 年。其他人已经对 PostgreSQL 代码进行了硬分叉，以取代其 MVCC 实现。著名的例子包括 OrioleDB 和 YugabyteDB。但对这些系统的更改永远不会合并回主 PostgreSQL 代码库。所以我们暂时只能使用 PostgreSQL 的append-only MVCC。
问题#2：表膨胀
PostgreSQL 将过期版本（死元组）和活动元组存储在同一页面上。尽管 PostgreSQL 的 autovacuum 工作程序最终会删除这些死元组，但写入繁重的工作负载可能会导致它们累积的速度快于真空处理的速度。此外，自动清理仅删除死元组以供重用（例如，存储新版本），并且不会回收未使用的存储空间。在查询执行期间，PostgreSQL 将死元组加载到内存中（因为 DBMS 将它们与活元组混合在页面上），从而增加磁盘 IO 并损害性能，因为 DBMS 检索无用的数据。如果您正在运行 Amazon 的 PostgreSQL Aurora，这将增加 DBMS 的 IOPS，并导致您给 Jeff Bezos(amazon的老板) 更多的钱！
优化：我们建议监控 PostgreSQL 的表膨胀，然后定期回收未使用的空间。 内置pgstattuple模块可以准确计算数据库中的可用空间，但它需要全表扫描，这对于生产环境中的大表来说不实用。
$ psql -c "CREATE EXTENSION pgstattuple" -d $DB_NAME
$ psql -c "SELECT * FROM pgstattuple('$TABLE_NAME')" -d $DB_NAME
或者，可以使用一次性查询或脚本来估计表的未使用空间；它们比 pgstattuple 更快、更轻量，因为它们提供了表膨胀的粗略估计。如果未使用的空间量很大，则 pg_repack 扩展会从臃肿的表和索引中删除并回收页面。它在线工作，不需要在处理过程中对表进行独占锁定（与 VACUUM FULL 不同）。
以下命令将把 pg_repack 扩展安装到自我管理的 DBMS 中（请参阅 Amazon 的 PostgreSQL RDS 说明），然后压缩单个表。
$ psql -c "CREATE EXTENSION pg_repack" -d $DB_NAME
$ pg_repack -d $DB_NAME --table $TABLE_NAME
为了最大限度地减少对数据库性能的潜在影响，OtterTune 建议我们的客户在流量较低的非高峰时段启动此过程。
问题#3：二级索引维护
当应用程序对表执行 UPDATE 查询时，PostgreSQL 还必须更新该表的所有索引以将条目添加到新版本。这些索引更新增加了 DBMS 的内存压力和磁盘 I/O，特别是对于具有大量索引的表（一位 OtterTune 客户在单个表上有 90 个索引！）。随着表中索引数量的增加，更新元组时产生的开销也会增加。 PostgreSQL 避免更新仅堆元组 (HOT) 更新的索引，其中 DBMS 将新版本存储在与先前版本相同的页面上。但正如我们在上一篇文章中提到的，OtterTune 客户的 PostgreSQL 数据库仅对 46% 的更新操作使用 HOT 优化。
优化：减少 PostgreSQL 索引写入放大的明显解决方法是减少每个表的索引数量。但这说起来容易做起来难。我们建议从表中重复和未使用的索引开始。人们可以通过检查数据库的模式来识别重复索引，以查看两个索引是否以相同的顺序引用相同的列并使用相同的数据结构（例如，B+树与哈希表）。对于未使用的索引，PostgreSQL 维护索引级指标（例如，pg_stat_all_indexes.idx_scan），用于跟踪在索引上启动的索引扫描的数量。如果索引的该值为零，则所有应用程序的查询都不会使用该索引。确保忽略未使用的主键或唯一索引，因为 DBMS 使用它们对表强制执行完整性约束。
下面的屏幕截图显示了 OtterTune 的类似检查，用于自动查找不必要的索引。
OtterTune’s Unused and Duplicate index dashboard.
OtterTune 的未使用和重复索引仪表板。
一旦确定要删除的索引，下一步就是删除它们。但是，如果您的应用程序使用对象关系映射 (ORM) 框架来管理其数据库架构，那么您不希望手动删除索引，因为 ORM 可能会在将来的架构迁移期间重新创建索引。在这种情况下，有必要更新应用程序代码中的架构。如果应用程序未使用 ORM，则可以使用 DROP INDEX 命令。
问题#4：真空管理
PostgreSQL 的性能在很大程度上取决于其 autovacuum 清理过时数据和修剪 MVCC 方案中版本链的有效性。然而，由于其复杂性，配置自动清理以正确运行并及时删除这些数据具有挑战性。默认的全局自动清理设置不适合大型表（数百万到数十亿的元组），因为触发清理可能需要很长时间。此外，如果每个 autovacuum 调用需要很长时间才能完成或被长时间运行的事务阻塞，DBMS 将积累死元组并遭受陈旧统计数据的影响。将自动清理延迟太久会导致查询随着时间的推移逐渐变慢，需要手动干预来解决该问题。
优化：虽然在 PostgreSQL 中清理表很痛苦，但好消息是它是可以管理的。但正如我们现在所讨论的，这有很多步骤，并且需要跟踪很多信息。
控制 autovacuum 的第一步是监视每个表的死元组数量。 PostgreSQL 的 pg_stat_all_tables视图提供了监控表的基本指标，包括死元组 ( n_dead_tup ) 和活动元组 ( n_live_tup ) 数量的估计。通过此类表级指标，您可以确定每个表过期元组的百分比，并确定哪些表需要额外的清理工作。
对于具有大量死元组的表，您可以调整其设置以使 PostgreSQL 更频繁地触发 autovacuum。 PostgreSQL 允许您在表级别微调 autovacuum 参数，不同的表可能需要不同的最佳设置。最重要的旋钮是 autovacuum_vacuum_scale_factor：它指定在 PostgreSQL 调用 autovacuum 之前表中必须存在的死元组的最小百分比。该旋钮的默认值为 20%。如果应用程序的一个表有 10 亿个元组，PostgreSQL 不会在该表上运行清理，直到至少有 2 亿个死元组。如果该表中的平均元组大小为 1KB，则 2 亿个死元组将消耗 200GB 的磁盘存储空间。这甚至不包括指向这些表的索引指针的额外存储开销！为了避免此问题，您应该使用 ALTER TABLE SQL 命令将大型表的比例因子旋钮设置为小于 20%：
ALTER TABLE table_name SET (autovacuum_ vacuum_scale_factor = 0.05);
接下来，您应该检查 autovacuum 是否被长时间运行的事务阻塞。再次，我们可以依靠 PostgreSQL 的内部遥测来获取这些信息。 pg_stat_activity 视图提供每个 PostgreSQL 工作线程（即进程）当前执行状态的实时数据。它显示每个活动事务已运行多长时间。如果事务已经运行了几个小时，您应该考虑将其终止，以便 autovacuum 可以完成其操作。下面的示例查询查找所有运行时间超过五分钟的事务：
SELECT pid, NOW() - xact_start AS duration, query, state
FROM pg_stat_activity
WHERE (NOW() - xact_start) > INTERVAL '5 minutes';
然后，您可以使用 pg_cancel_backend 管理函数终止查询：
SELECT pg_cancel_backend($PID_TO_KILL);
当然，在街上删除查询可能会产生意想不到的后果，因此您必须确保杀死它们不会在您的应用程序中造成问题。为了避免将来出现同样的问题，请确保事务的查询不必要地运行更长的时间，因为它们使用的是低效的查询计划。请参阅我们之前关于优化查询性能的文章，例如欺骗 ORDER BY...LIMIT 和运行 ANALYZE ，了解如何使用 OtterTune 改善慢速查询。如果您不需要它们是原子的，您还可以重构您的应用程序，将大型事务分解为较小的工作单元（但不可否认，这并不总是容易做到）。
最后，您需要查看是否存在长时间运行的真空过程，然后调整其他旋钮。与 pg_stat_activity 显示 PostgreSQL 工作线程的状态类似，pg_stat_progress_vacuum 视图显示活动 autovacuum 操作的状态。通过此视图，您可以确定真空是否需要几个小时甚至几天才能完成。如果您的 PostgreSQL DBMS 确实有长时间运行的 Vacuum，那么 OtterTune 建议调整三个旋钮：
- autovacuum_work_mem 参数指定 DBMS 在每次 autovacuum 调用中可以使用的最大内存量。增加此参数可以加快清理速度，因为它可以在每次调用时修剪更多的死元组。
- autovacuum_vacuum_cost_limit 参数控制在 PostgreSQL 强制自动清理工作者暂时退出之前可以产生多少 I/O 活动自动清理工作者。该旋钮的值越高意味着自动清理将更加积极。
- 与这种基于成本的控制机制相关，autovacuum_vacuum_cost_delay 参数确定 autovacuum 工作线程在 DBMS 强制其退出后必须等待多长时间。较短的延迟意味着自动清理每次都会更快地恢复操作。
