安装PostgreSQL会出现很多问题。更糟糕的是，许多问题可能潜伏在未被发现的情况下，随着问题在一段时间内的积累，然后突然发生重大影响，使它成为每个人关注的焦点。无论是性能的显著下降，还是资源消耗和计费成本的急剧上升，尽早发现这类问题都很重要——或者更好的做法是，通过配置实现以适应所需的工作负载来避免这些问题。
根据Percona多年来帮助无数PostgreSQL商店的经验，我们编制了一份最常见的错误列表。即使您认为您已经正确配置了PostgreSQL安装，您可能仍然会发现这个列表在验证您的安装时很有用。
错误#1:运行默认配置
PostgreSQL可以开箱即用，但是它并没有很好地配置来满足你的需求。默认配置非常基本，没有针对任何特定的工作负载进行调优。这种过于保守的配置允许PostgreSQL运行任何环境，并期望用户根据自己的需要进行配置。
的pgtune工具提供了基于硬件资源和工作负载类型的配置子集。这是基于工作负载需求配置PostgreSQL集群的一个很好的起点。此外，您可能必须配置autovacuum、日志、检查点和WAL(预写日志)保留变量。
为了避免任何不必要的重启，您的服务器必须针对近期的未来需求进行优化配置，这一点非常重要。因此，请在pg_settings目录视图中查看所有带有“postmaster”上下文的guc。
SELECT name, setting, boot_val FROM pg_settings WHERE context = 'postmaster';这在设置高可用性(HA)集群时尤其重要，因为主服务器的任何停机都将降低集群的性能，并导致备用服务器提升到主服务器角色。
错误#2:未优化的数据库设计和架构
这一点怎么强调都不为过。我个人曾见过一些组织仅仅因为未优化的数据库设计和架构而支付超过所需成本五倍的费用。
这里最好的建议之一是看看你现在和不久的将来的工作量需要什么，而不是六个月到一年的时间里可能需要什么。看得太长远意味着您的表是为可能永远不会实现的未来需求而设计的。这只是其中的一个方面。
除此之外，过度依赖对象-关系映射(ORM)也是性能不佳的主要原因。orm用于使用面向对象的编程语言将应用程序连接到数据库，随着时间的推移，它们应该简化开发人员的工作。但是，理解ORM提供了什么以及它带来了什么样的性能影响是至关重要的。在底层，ORM可以执行多个查询，无论是组合多个关系、执行聚合，还是分割查询数据。总的来说，在使用ORM时，您将体验到更高的延迟和更低的事务吞吐量。
除了orm之外，改进数据库体系结构还涉及结构化数据，以便您的读写操作对索引和关系都是最优的。一种有帮助的方法是对数据库进行反规范化，因为这降低了SQL查询的复杂性和相关的连接，从而可以从更少的关系中获取数据。
最后，在应用程序和工作负载的环境中，性能由简单的“定义、度量和优化”三步过程驱动。
错误#3:没有针对工作负载调优数据库
针对工作负载进行调优需要深入了解您打算存储的数据量、应用程序的性质以及要执行的查询类型。您可以随时对设置进行调优和基准测试，直到对严重负载下的资源消耗感到满意为止。
例如，您的整个数据库是否可以放入机器的可用RAM中?如果是，那么您显然希望为它增加shared_buffers值。类似地，理解工作负载是如何配置检查点和autovacuum流程的关键。例如，与满足事务处理性能委员会Type C基准测试的混合在线事务处理工作负载相比，仅附加工作负载的配置将非常不同。
有很多有用的工具可以提供查询性能分析。你可能会去看看我的博客关于查询性能洞察，其中讨论了一些可用的开源选项，或参见我在YouTube上的演讲．
在Percona，我们有两个工具可以极大地帮助你理解查询性能模式:
- PMMPercona Monitoring and Management是一个免费的、完全开源的项目，它提供了一个带有详细系统统计和查询分析的图形界面。请随意尝试PMM演示它支持MySQL、MongoDB和PostgreSQL。
- pg_stat_monitor—这是pg_stat_statements的增强版本，可以更详细地洞察查询性能模式、实际查询计划和带参数值的查询文本。它在Linux上可以从我们的下载页面获得，也可以作为RPM包从PostgreSQL社区yum存储库中获得。
错误#4:连接管理不当
连接配置乍一看没有什么问题。但是，我曾经见过max_connections的值太大导致内存不足错误的实例。因此，配置max_connection需要一些注意。
在配置max_connections时，必须考虑到核数、可用内存量和存储类型。您不希望使用可能永远不会使用的连接使服务器资源过载。此外，还有为每个连接分配的内核资源。PostgreSQL的内核文件请听详细报道。
当客户端执行花费很少时间的查询时，连接池可以显著提高性能，因为在这种类型的工作负载中生成连接的开销变得非常大。
错误5:吸尘器不能正常工作
希望您没有禁用autovacuum。在许多生产环境中，我们已经看到用户完全禁用了autovacuum，通常是由于一些潜在的问题。如果自动吸尘器在你的环境中不起作用，可能只有三个原因:
- 真空过程没有被触发，或者至少没有像它应该的那样频繁。
- 吸尘太慢了。
- 吸尘器没有清理死排。
1和2都与配置选项直接相关。您可以通过查询pg_settings视图查看与真空相关的选项。
SELECT name, short_desc, setting, unit, CASE WHEN context = 'postmaster' THEN 'restart' WHEN context = 'sighup' THEN 'reload' ELSE context END "server required " FROM pg_settings WHERE name LIKE '%vacuum%';可以通过调优autovacuum_work_mem和并行工作者的数量来潜在地提高速度。真空过程的触发可以通过配置比例因子或阈值进行调整。
当真空过程没有清理死元组时，这表明有什么东西阻碍了关键资源。罪魁祸首可能是以下一种或多种:
- 长时间运行的查询或事务。
- 复制环境中的备用服务器，并打开hot_standby_feedback选项。
- 大于所需值的vacuum_defer_cleanup_age。
- 保留xmin值并防止真空清理死元组的复制槽。
如果你想手动处理一段关系的真空，那么遵循帕累托定律(又名80/20规则)。将集群调优到最佳配置，然后专门针对那几个表进行调优。记住自动真空或烤面包。通过在create或alter语句中指定关联的存储选项，可以为特定关系禁用Autovacuum。
错误#6:错误连接和长时间运行的事务
有很多事情可以劫持你的PostgreSQL集群，恶意连接就是其中之一。除了占用可能被其他应用程序使用的连接插槽外，恶意连接和长时间运行的事务还占用可能在整个系统中造成严重破坏的关键资源。在较小程度上，在打开hot_standby_feedback的复制环境中，备用服务器上长时间运行的事务可能会阻止主服务器上的真空完成其工作。
设想一个有bug的应用程序打开一个事务，然后停止响应。它可能持有锁，或者只是阻止真空清理死元组，因为这些元组在这样的事务中仍然是可见的。如果该应用程序要打开大量这样的事务呢?
通常情况下，您可以通过将idle_in_transaction_session_timeout配置为为查询调优的值来摆脱此类事务。当然，无论何时开始调优参数，都要始终牢记应用程序的行为。
除了调优idle_in_transaction_session_timeout之外，还要监视pg_stat_activity，查看是否有长时间运行的查询或等待客户端相关事件的时间超过预期的会话。请注意时间戳、等待事件和状态列。
backend_start | 2022-10-25 09:25:07.934633+00 xact_start | 2022-10-25 09:25:11.238065+00 query_start | 2022-10-25 09:25:11.238065+00 state_change | 2022-10-25 09:25:11.238381+00 wait_event_type | Client wait_event | ClientRead state | idle in transaction除此之外，预备事务(特别是孤立的预备事务)还可以保留关键的系统资源(锁或xmin值)。我建议为准备好的事务设置一个命名法来定义它们的年龄。例如，一个最大5分钟的已准备事务可以创建为准备事务'foo_prepared ' 5m．
SELECT gid, prepared, REGEXP_REPLACE(gid， '。* '， ") AS age FROM pg_prepared_xacts WHERE prepared + CAST(regexp_replace(gid， ')。* '， ")作为interval) < now ();这为应用程序提供了一种方案，用于定义其准备事务的年龄。然后，cronjob或计划作业可以监视和回滚任何已准备好的事务，这些事务在超出预定时间后仍处于活动状态。
错误7:索引过多或索引不足
当然，过度索引一种关系并没有什么错。真的有吗?为了让你的PostgreSQL实例获得最佳性能，你必须了解PostgreSQL是如何管理索引的。
PostgreSQL中有多种类型的索引。每个都有不同的用例，并且每个都有自己的开销。B-tree是最常用的索引类型。它也用于主键。在过去的几个主要发行版中，b -树索引已经有了很多与性能相关的改进。这是其中之一我的博客文章讨论了PostgreSQL 14中的重复版本。
当对关系执行索引扫描时，对于每个匹配的元组，它访问堆以获取数据和可见性信息，因此只选择当前事务可见的版本。过度索引将导致对更多索引的更新，从而消耗更多资源，而没有获得预期的好处。
类似地，索引不足会导致更多的堆扫描，这可能会导致更多的I/O操作，从而导致性能下降。
索引不仅仅是关系上的索引数量。重要的是如何针对所需的用例优化这些索引。理想情况下，您希望每次都只进行索引扫描，但有一些限制。虽然b -树索引支持对所有操作符进行索引扫描，但GiST和SP-GiST索引只支持对某些操作符进行索引扫描。看到文档欲知详情。
以下简单的检查表可以帮助您验证您的系统是否为索引设置了最佳设置:
- 确保正确设置了配置(例如，针对硬件调优了随机页面成本)。
- 检查统计信息是否是最新的，或者至少在与索引的关系上运行了analyze或vacuum命令。这将确保统计数据或多或少是最新的，以便规划器有更好的概率选择索引扫描。
- 创建正确的索引类型(B-tree、散列或其他类型)。
- 在正确的列上使用索引。不要忘记包含非索引列以避免堆访问。并不是所有的索引类型都允许覆盖索引，所以一定要检查文档。
- 去掉不必要的索引。看到pg_statio_user_indexes有关索引和块命中的更多见解。
- 了解覆盖索引对重复数据删除、重复版本搅动和仅索引扫描等特性的影响。
看到这个关于索引维护的Wiki页面获取更多有用的查询。
错误#8:备份和HA不足
HA不仅仅是保持服务正常运行。它还涉及确保服务在定义的验收标准内响应，并满足RPO(恢复点目标)和RTO(恢复时间目标)目标。要匹配正常运行时间要求和您的目标数字9，请参考此wiki页面用于百分比计算。