10个常见的 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 时会引起更高的延迟和更低的事务吞吐量。

 

另外，改进数据库架构还为了使数据更加结构化，以便对表或索引进行最佳的读写操作。另一种有用的方法是对数据库进行反规范化，因为这会降低 SQL 查询的复杂性，减少相关的表连接，进而可以从更少的表中获取数据。

 

简单来说最终驱动高性能的，是对具体环境中的应用程序和工作负载执行三步过程,即“定义、测量、优化”。

 

错误3：没有针对工作负载调整数据库

 

根据工作负载调整数据库，需要深入了解要存储的数据量、应用程序的性质，以及要执行的查询类型。可以随时修改配置并进行基准测试，直到对高负载下的资源使用满意为止。

 

例如，考虑是否可以将整个数据库放入计算机的可用内存中。如果是，那么显然希望增加数据库的 shared_buffers 值。类似地，了解工作负载是如何正确配置检查点和 autovacuum 进程的关键。例如，与满足事务处理性能委员会 C 类基准的混合在线事务处理工作负载相比，为 append-only 类型工作负载进行的这些配置将非常不同。

 

有很多有用的工具提供了查看查询性能的功能。关于更多查看查询性能的说明，可以浏览我的博客文章，其中讨论了一些可选的开源工具。还可以看下我在 YouTube 上的演示。

 

在 Percona，我们的两个工具可以极大地帮助理解查询性能状况：

 

lPMM - Percona监控和管理，是一个免费的、完全开源的项目，提供了一个带有详细系统统计和查询分析的图形界面。可随意试用适合MySQL、MongoDB或PostgreSQL的PMM演示程序。

 

lpg_stat_monitor - 这是pg_stat_statements的增强版本，可以借此更详细地了解查询性能状况、实际的查询计划和带有参数值的查询文本。可以从我们的下载页获得它在Linux上的可用包，也可以从PostgreSQL社区的yum存储库获得RPM包。

 

错误4：连接管理不当

 

乍一看连接配置似乎没问题，但是我见过太大的 max_connections 值导致内存不足错误的情况，所以配置 max_connection 还需要注意一下。

 

配置 max_connections 时必须考虑内核数、可用内存量和存储类型。谁都不希望让可能永远不会使用的连接致使服务器资源过载，况且还要为每个连接分配内核资源。PostgreSQL 内核文档有更多详细信息。

 

当客户端执行花费很少时间的查询时，连接池能显著提高性能，因为在这种类型的工作负载中，生成连接的开销相对变得很大。

 

错误5：Vacuum 工作异常

 

希望 autovacuum 没有被禁用。我们已经在许多生产环境中看到，用户完全禁用了 autovacuum，这通常是由于一些潜在的问题所导致。如果 autovacuum 在具体环境中不起作用，那么只可能有以下三个原因：

 

1.vacuum 过程没有被触发，或者至少没有像应该的那样频繁。

 

2.Vacuuming 太慢。

 

3.vacuum 没有清理没用的旧版本数据。

 

其中 1 和 2 都与配置选项直接相关。可以通过查询 pg_settings 系统视图来查看vacuum相关选项。

 

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 和并行工作线程的数量，可以潜在提高速度。vacuum 过程的触发可以通过配置比例因子或阈值来调节。

 

当 vacuum 过程没有清理没用的旧版本数据时，表明有某种东西阻碍了获取关键资源，罪魁祸首可能是以下一项或多项：

 

长时间运行的查询或事务；

 

复制环境中的备用服务器启用了 hot_Standby_feedback 选项；

 

大于所需的 vacuum_defer_cleanup_age 值；

 

保持 xmin 值的复制槽阻止了 vacuum 清理没用的旧版本数据。

 

如果想手动管理表的 vacuum 过程，那么请遵循帕累托定律（即80/20法则）。将集群调整到适合 80% 的表的最佳配置，然后专门针对剩下 20% 的表进行调整。记住，可以通过在 create 或 alter 语句中指定相关的存储选项，来为特定表禁用 autovacuum 或 toast.autovacuum。

 

错误6：恶意连接和长时间运行的事务

 

PostgreSQL 集群可能会受到很多因素的影响，而恶意连接就是其中之一。除了占用可能被其他应用程序使用的连接槽外，恶意连接和长时间运行的事务占用关键资源，这可能会对整个系统造成严重破坏。看一个较小程度的影响：在启用了 hot_standby_feedback 的复制环境中，备用服务器上的长时间事务可能会阻止主服务器上的 vacuum 完成其工作。

 

试想一个有问题的应用程序，它打开一个事务，然后停止响应。程序可能会持有锁，或者只是阻止 vacuum 清理旧版本数据，因为这些数据在此事务中仍然可见。如果该应用程序打开了大量此类事务怎么办？

 

通常情况下，可以通过将
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 分钟的准备事务可以创建为 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 树是最常用的索引类型，也用于主键。在过去的几个主要版本中，B树索引中出现了许多与性能相关（或剥离）的改进。这里是我的一篇博文，讨论了 PostgreSQL 14 中的重复版本变动。

 

当对表执行索引扫描时，对于每个匹配的行，索引会回表访问以获取数据和可见性信息，以便只选择当前事务可见的版本的数据。过度索引将导致更多的索引更新，因此会消耗更多资源而得不到预期的好处。

 

同样，索引不足将导致更多的表扫描，这可能导致更多的 I/O 操作，从而导致性能下降。

 

创建索引时不仅要考虑表上的索引数量，还要考虑这些索引在所针对的查询上如何进行优化。理想情况下，希望每次查询只扫描一个索引，但有一些限制。尽管 B 树索引支持所有运算符的索引扫描，但 GiST 和 SP GiST 索引仅支持某些运算符。有关详细信息，请参阅文档。

 

以下简单的检查项，可以帮助验证是否为系统进行了最佳索引设置：

 

确保配置正确（例如，为相关硬件调整了随机页访问成本）。

 

检查统计数据是否最新，或者至少检查运行analyze或vacuum命令的表上是否有索引。确保统计数据最新或接近最新，以便查询计划更有可能选择索引扫描。

 

创建正确类型的索引（B树、哈希或其他类型）。

 

在正确的列上使用索引。不要忘记，在索引中包含查询所需列可以避免回表访问，即所谓索引覆盖。并非所有索引类型都允许索引覆盖，因此使用时请检查文档。

 

去除不必要的索引。请参阅pg_statio_user_indexes，了解有关索引和块命中的更多信息。

 

了解索引覆盖对重复数据消除、重复版本变动和仅索引扫描等功能的影响。

 

错误8：备份和 HA 不足

 

HA 的作用不仅是保持服务的正常运行，还要确保服务在定义的验收标准内进行响应，并满足 RPO（恢复点目标）和 RTO（恢复时间目标）目标。要达到系统正常运行时间要求的9的个数（正常使用时间与总时间之比），请参阅此wiki页面以计算百分比。

 

为了满足 RPO 和 RTO，必须考虑许多因素，包括计划内停机时间、任何自动或手动操作及其频率和持续时间，当然还有与计划外停机相关的成本。

 

拥有准确和及时的备份，以及有效恢复备份的能力，在定义 RPO 和 RTO 这两个参数方面起着关键作用,其中涉及数据备份的频率是多少，怎样管理 WAL 文件，如何验证备份和 WAL 文件等诸多问题。

 

根据工作负载和可用的维护时间窗口，通常应至少每七天进行一次备份。除此之外，还应该定期测试恢复过程，以便确认这些备份是有效的。事实上，只有应用程序能够恢复并进行处理，备份才算成功。不应信任未经测试的备份。

 

错误9：错误管理扩展模块

 

PostgreSQL 自带 50 多个扩展模块，而后还有个人或组织提供的第三方扩展模块。PostgreSQL 内核提供了一些常用的扩展模块，如 pg_stat_statements，此外还有一些著名的扩展模块，例如 PostGIS，它们不是内核的一部分。

 

首先应该确保所部署的任何一组扩展模块都能够一起工作，而不会相互影响。此外还有性能方面的考虑。有些扩展模块只是简单的 SQL 扩展，而另一些扩展模块带有共享对象或 DLL，这会消耗更多资源并影响整体性能，一定要了解这些扩展模块将消耗哪些资源。

 

更重要的是，任何预加载的扩展模块都会成为服务器的一部分。无论是否通过发出 CREATEEXTENSION… 语句创建了 SQL 接口，这些预加载扩展模块都将在后台工作。例如，无论是否创建了 SQL 接口，将 pg_stat_statements 添加到共享预加载库中都会导致性能下降。这里的总体经验是仔细考虑是否真的需要这些扩展模块。

 

下面是一些很有用的关于对扩展模块的查询。

 

可以查询系统视图pg_extension以获取有关已安装扩展模块的信息。

 

SELECT * FROM pg_extension;

 

类似地，可以找出系统上所有可用的扩展模块。

 

SELECT * FROM pg_available_extensions();

 

还可以查询所拥有的扩展模块的可用版本。

 

SELECT * FROM pg_available_extension_versions();

 

错误10：忽略支持工具

 

除 PostgreSQL 集群本身以外，还应该考虑需要哪些其他支持工具以改善 PostgreSQL 的使用体验，因此有必要了解可用的工具。由于旧版本存在严重问题，人们对某些工具存有误解，所以应看下新版本、各个社区的活跃性以及发布的频率。

 

例如，让我们回顾一下 PostgreSQL 生态系统中用于连接池和负载均衡的几个工具：PgBouncer、HAProxy 和 Pgpoo II。

 

HAProxy 是一个负载均衡器。请注意，与各种操作系统发行版一起打包的 HAProxy 版本很旧。如 CentOS 7 中版本为 1.5，CentOS 8 中版本为 1.8，而 HAProxy 的最新版本是 2.6。作为参考，HAProxy 2.4 有 1687 个新提交代码。虽然使用操作系统发行版提供的包更容易，但这些包可能太旧了。

 

PgBouncer 是一款轻量级连接池。虽然它是单线程的，但如果运行多个 PgBouncer 实例并监听同一端口，则支持 SO_REUSEPORT 选项的内核可能会允许负载均衡。需要检查内核文档，看看它是否支持负载均衡或轮询，也许根本不支持。使用 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 成为了一个非常流行的开源数据库。它被设计为易于使用和可扩展，以满足广泛的用户需求。然而，这种灵活性同时也意味着在使用它时必须审视用法，并在安装时就考虑有针对性地进行相应的配置。这样会使应用程序的性能更好，更能使用户怡然自得，而且从长远看，还可以节省大量成本。