很多问题会在 PostgreSQL 安装过程中发生，并且不少问题潜伏在不易察觉的地方，在一段时间内逐渐积累，最终引起POSTGRESQL的故障。无论是明显的性能下降，还是资源消耗和使用成本的急剧增加，尽早识别这些问题非常重要——更好的做法是，通过知道更多于POSTGRESQL的知识，避免出现此类问题。
凭借 Percona 多年来为无数 PostgreSQL 用户提供帮助的经验，我们撰写了一个最常见错误的清单。即使您认为已经正确配置了 PostgreSQL 安装，您仍然可能会发现这个清单对于验证您的设置非常有用。
错误 1：使用默认配置运行
PostgreSQL 可以不配置优化直接使用。，默认配置非常基础，没有针对任何特定工作负载进行优化。这种过于保守的配置允许 PostgreSQL 在任何环境下运行，并期望用户会根据自己的需求进行配置。pgtune 工具提供了一组基于硬件资源和工作负载类型的配置子集。这是根据您的工作负载需求配置 PostgreSQL 集群的一个好的开始。此外，您可能还需要配置 autovacuum、log、checkpoint 和 WAL等配置部分。
确保服务器针对即将到来的需求进行最佳配置非常重要，以避免不必要的重新启动。因此，请查看 pg_settings 目录视图中带有 “postmaster” 上下文的是需要服务器重启生效的配置。
SELECT name, setting, boot_val
FROM pg_settings
WHERE context = 'postmaster';
在设置高可用性（HA）集群时，这一点尤为关键，因为主服务器的任何停机时间都会降低集群性能，并导致备用服务器升级为主服务器角色。
错误 2：未优化的数据库设计和架构
这一点至关重要。仅仅因为未优化的数据库设计和架构，有些公司的成本是实际所需成本的五倍以上。一个最好的建议是关注您当前以及近期内的工作负载需求，而不是六个月或一年后可能需要的东西。过于长远的考虑意味着您的表格是为可能永远无法实现的未来需求而设计的。这仅仅是其中一个方面。
与此同时，过度依赖对象关系映射（ORM）也是导致性能较差的主要原因之一。ORM用于使用面向对象编程语言将应用程序连接到数据库，并且它们应该随着时间的推移简化开发人员的工作负担。然而，了解 ORM 提供的内容以及它引入的性能影响至关重要。在底层，ORM 可能会执行多个查询，无论是合并多个关系，还是执行聚合，甚至分割查询数据。总体而言，当使用 ORM 时，您的事务的延迟会更高，吞吐量会更低。
超越对象关系映射（ORMs），改进数据库架构是关于如何组织数据以便你的读写操作对索引和关系都是最优的。一个可以提供帮助的方法是去规范化数据库，因为这减少了SQL查询的复杂性和相关联接，使得你可以从较少的关系中获取数据。
最后，性能受到简单三步过程“定义、测量和优化”的驱动，在您的环境中针对您的应用程序和负载进行调整。
错误 3：未根据负载调整数据库
针对负载进行调优需要了解您计划存储的数据量、应用程序的性质以及要执行的查询类型。在严重负载下，您可以始终调整并对设置进行基准测试，直到您对资源消耗满意为止。
例如，您的整个数据库是否可以适应您计算机上的可用RAM？如果是这样，那么您显然会希望建立更大的 shared_buffers 值。同样，了解负载对于配置检查点和自动清理进程至关重要。例如，对于仅追加工作负载与符合交易处理性能委员会C类基准的混合在线事务处理工作负载，您将非常不同地配置它们。
有许多有用的工具可以提供查询性能见解。您可以查看我的关于查询性能见解的博客文章，其中讨论了一些可用的开源选项，或在YouTube上观看我的演示。
在Percona，我们有两个工具将非常有助于您了解查询性能模式：
PMM - Percona Monitoring and Management 是一个免费的完全开源项目，提供了一个图形界面，包含详细的系统统计信息和查询分析。欢迎尝试适用于MySQL、MongoDB和PostgreSQL的PMM演示。
pg_stat_monitor - 这是 pg_stat_statements 的增强版本，提供更详细的查询性能模式、实际查询计划以及带有参数值的查询文本的见解。您可以从我们的下载页面获取Linux版本，或者从PostgreSQL社区yum仓库获取RPM软件包。
这些工具可以帮助您深入了解并优化数据库查询性能，确保应用程序运行得更加高效。
错误 4：不当的连接管理
连接配置乍一看似乎无害。然而，我见过 max_connections 设置为非常大的值导致内存耗尽错误的情况。因此，在配置 max_connection 时需要注意。
在配置 max_connections 时，必须考虑核心数量、可用内存量和存储类型。您不希望让可能永远不会使用的连接占用服务器资源。此外，每个连接还分配了内核资源。有关更多详细信息，请参阅 PostgreSQL 内核文档。
当客户端执行耗时非常短的查询时，连接池可以显著提高性能，因为在这种负载类型下，创建连接的开销变得很重要。在实际场景中，合理配置连接数并使用连接池器对于提高数据库性能至关重要。
错误 5：Vacuum（清理）没有正常工作
希望您没有禁用 autovacuum。我们在许多生产环境中看到用户完全禁用了 autovacuum，通常是由于某些潜在问题。如果 autovacuum 在您的环境中确实无法正常工作，可能有以下三个原因：
清理程序没有被触发，或者至少没有像应该那样经常触发。
清理速度太慢。
清理程序没有清除死亡行。
1和2都直接与配置选项相关。您可以通过查询 pg_settings 视图查看与清理相关的选项。
sql
SELECT name
, short_desc
, setting
, unit
, CASE
WHEN context = 'postmaster' THEN 'restart'
WHEN context = 'sighup' THEN 'reload'
ELSE context
END "server requires"
FROM pg_settings
WHERE name LIKE '%vacuum%';
通过调整 autovacuum_work_mem 和并行工作线程数，可能会提高速度。可以通过配置比例因子或阈值来调整触发清理进程的设置。
当真空处理过程无法清除死亡元组时，这表明某些事物正在阻碍关键资源。罪魁祸首可能是以下一个或多个：
长时间运行的查询或事务。
在复制环境中的备用服务器，其中hot_standby_feedback选项已打开。
vacuum_defer_cleanup_age 的值大于实际所需。
复制槽位使xmin值保持低位，从而阻止了清除死亡元组的真空操作。
如果你想手动管理某个关系的真空操作，请遵循帕累托法则（又称80/20法则）。调整群集至最佳配置，然后针对那几个表进行特定调优。请记住，在创建或修改语句期间，通过指定相关的存储选项，可以为特定关系禁用autovacuum或toast.autovacuum。
错误 6：恶意连接和长时间运行的事务
许多事物可能使你的PostgreSQL集群陷入困境，恶意连接就是其中之一。除了占用可供其他应用程序使用的连接槽外，恶意连接和长时间运行的事务还会占用关键资源，从而对整个系统造成严重破坏。在较小程度上，在打开hot_standby_feedback选项的复制环境中，备用服务器上长时间运行的事务可能会阻止主服务器上的真空操作执行其任务。
想象一个有问题的应用程序：它打开一个事务后就不再响应。这样的事务可能持有锁或简单地阻止真空处理过程清除死亡元组，因为这些元组在这些事务中仍然可见。如果该应用程序打开大量这样的事务会怎么样？
通常情况下，通过将idle_in_transaction_session_timeout配置为针对查询调优的值，可以消除这类事务。当然，在开始调整参数时，始终要考虑到你的应用程序的行为。
在调整idle_in_transaction_session_timeout之外，监控pg_stat_activity以获取任何长时间运行的查询或等待客户端相关事件的时间超过预期的会话。关注时间戳、等待事件和状态列。图片
除了这些之外，预处理事务（特别是孤立的预处理事务）也会占用关键系统资源（锁或xmin值）。我建议为预处理事务设置一个命名规则以定义它们的存在时间。例如，可以创建一个最长5分钟的预处理事务，如PREPARE TRANSACTION ‘foo_prepared 5m’。
SELECT gid
, prepared
, REGEXP_REPLACE(gid, '.* ', '') AS age
FROM pg_prepared_xacts
WHERE prepared + CAST(regexp_replace(gid, '.* ', '') AS INTERVAL) < NOW();
这提供了一种方案让应用程序定义它们预处理事务的存在时间。然后，可以使用cronjob或计划任务来监视并回滚任何超出预期存在时间的活动预处理事务。
错误 7：过度索引或低度索引
当然，对关系进行过度索引没有什么问题。或者真的有问题？要从PostgreSQL实例中获得最佳性能，了解PostgreSQL如何管理索引至关重要。
PostgreSQL中有多种类型的索引。每种索引都有不同的用途，并且每种索引都有自己的开销。B-tree 是最常用的索引类型，也用于主键。过去的几个主要版本已经看到很多与性能相关的（以及去除冗余）改进在B-tree索引中。这是我的一篇博客文章，讨论了PostgreSQL 14中的重复版本变动。
当在关系上执行索引扫描时，对于每个匹配的元组，它会访问堆来获取数据和可见性信息，以便选择当前事务可见的版本。过度索引将导致更多的索引更新，因此消耗更多资源而无法获得期望的收益。
类似地，低度索引将导致更多的堆扫描，可能会导致更多的I/O操作，从而降低性能。
索引并不仅仅是关于你在一个关系上拥有多少个索引。关键是这些索引针对预期用例有多优化。理想情况下，您希望每次都执行索引仅扫描，但是存在限制。尽管B-tree索引支持所有操作符的索引仅扫描，但GiST和SP-GiST索引只支持部分操作符。有关更多详细信息，请参阅文档。
遵循一个简单的检查表可以帮助您验证系统是否已针对索引进行了最佳设置：
确保正确设置配置（例如，调整随机页面成本以适应您的硬件）。
检查统计数据是否为最新状态，或至少在具有索引的关系上运行分析或清理命令。这将确保统计数据基本上是最新的，使得计划器更有可能选择索引扫描。
创建正确类型的索引（B树、哈希或其他类型）。
在合适的列上使用索引。不要忘记包括未索引的列以避免访问堆。并非所有索引类型都允许覆盖索引，请务必查阅相关文档。
移除不必要的索引。查看 pg_statio_user_indexes 以获取关于索引和块命中次数的更多信息。
了解覆盖索引对诸如去重、重复版本变化和仅索引扫描等功能的影响。
有关更多实用查询，请参阅此 索引维护的wiki页面。
错误 8：备份和高可用性（HA）不足
高可用性不仅关注服务的持续运行，还要确保服务在预定义的接受标准内响应，并满足 RPO（恢复点目标）和 RTO（恢复时间目标）指标。为了满足正常运行时间需求以及所针对的 nines 数量，请参阅此 wiki 页面以获取百分比计算。
许多因素需要考虑以满足 RPO 和 RTO，包括计划内停机时间、任何自动或手动活动、它们的频率和持续时间，当然还有与非计划停机相关的成本。
准确且及时地备份数据并有效恢复它们在定义这两个参数中起着关键作用。数据备份的频率是多少？WAL 文件如何管理？如何验证备份和 WAL 文件？
根据工作负载和可用的维护窗口，至少每七天应进行一次备份。与此同时，您应定期测试恢复过程，以便了解这些备份是否有用。事实上，只有在您能够使应用程序重新启动并处理时，围绕这方面的计划才能成功。未经测试的备份不能被信任。
错误 9：扩展管理不当
PostgreSQL 自带50多个扩展。此外还有许多由个人和组织提供的第三方扩展。有一些常用的扩展，如核心提供的 pg_stat_statements；还有其他一些著名的扩展，如不属于核心部分的 PostGIS。
首先，您应确保已部署的任何一组扩展可以在不互相干扰的情况下共同工作。除此之外，还有性能方面的考虑。有些扩展只是 SQL 扩展，但也有其他扩展会携带共享对象或 DLL，这将消耗更多资源并影响整体性能。请确保了解这些扩展将消耗的资源。
更重要的是，预加载的任何扩展都会成为服务器的一部分。无论您是否通过发出 CREATE EXTENSION… 语句创建了 SQL 接口，这些扩展都将在后台运行。例如，将 pg_stat_statements 添加到共享预加载库将导致性能下降，无论您是否创建了 SQL 接口。这里的总体教训是要仔细考虑您是否真的需要这些扩展。
以下是关于扩展的一些实用查询。
您可以查询目录 pg_extension 以获取有关已安装扩展的信息。
SELECT * FROM pg_extension;
同样，您可以找出系统上可用的扩展。
SELECT * FROM pg_available
错误 10：忽视辅助工具
建立你的 PostgreSQL 集群不应该孤立地看待。你需要考虑其他辅助工具，这些工具可以带来更好的 PostgreSQL 体验。因此，分析可用工具是非常重要的。一些工具存在误解，这归因于早期版本存在严重问题。所以要关注新版本、相应社区的积极性以及发布频率。
例如，让我们回顾一下在 PostgreSQL 生态系统中用于连接池和负载均衡的几个工具：PgBouncer, HAProxy 和 Pgpoo-II。
HAProxy 是一个负载均衡器。注意，与各种发行版打包的版本相比较老。CentOS 7有1.5版本，CentOS 8有1.8版本。最新的是2.6。仅供参考，HAProxy 2.4新增了1687个提交。尽管使用发行版提供的软件包更容易，但它们可能太旧了。
PgBouncer 是一个轻量级的连接池器。尽管它是单线程的，但支持 SO_REUSEPORT 选项的内核可能允许通过运行多个监听同一端口的 PgBouncer 实例来实现负载均衡。请查阅您的内核文档，了解是否支持负载均衡、轮询调度或完全不支持。使用 systemd 模板，您可以非常简单且优雅地运行多个 PgBouncer 实例。只需创建文件 /etc/systemd/system/pgbouncer@.service，然后使用诸如 systemctl start pgbouncer@ 1，systemctl start pgbouncer@ 2 等命令运行您喜欢的 PgBouncer 实例。
过去几年，Pgpool-II 的应用越来越广泛。新增了许多功能，包括监控和法定人数存在。因此，它提供的功能远不止连接池。
您会选择使用哪一个？PgBouncer？HAProxy？Pgpool-II？还是 PgBouncer 和 HAProxy？您配置了流式复制吗？使用 HAProxy，您可能需要为读写分别设置独立的端口。最终，选择将取决于您的用例（在某些情况下，可能是滥用情况！）。
PostgreSQL 是一款非常受欢迎的开源数据库，有很多原因。它的设计目标是易于使用并能扩展以满足广泛用户的需求。然而，这种灵活性确实意味着您需要审视自己的方法，并考虑相应地调整安装。这样不仅可以使您的应用程序性能更好，从而使用户更满意，还可能随着时间的推移节省大量成本。
