PostgreSQL使用中的九个拿石头砸自己脚的“坑”

前言

本文整理来自postgresweekly推荐的技术文章。感觉有点意思。虽然我本人并不完全赞同其中的观点，但是里边的思想值得借鉴。所谓仁者见仁。以下是这篇文章的主体内容，分享给广大PGer们，有不同观点，欢迎文后留言。

连接这些陷阱的共同点是可伸缩性。当您的数据库很小时，它们不会影响您。但是，如果有一天您希望您的数据库不要太小，那么提前考虑它们是值得的。否则他们会在你最不方便的时候反咬你一口。此外，在许多情况下，从一开始就做正确的事情比改变一个工作系统来做正确的事情要少得多。

九大坑

1、work_mem使用缺省值

我第一次在生产环境当中部署Postgres时犯的最大错误是没有更新work_mem的默认值。此设置控制每个查询操作在必须开始将数据写入临时之前可用的内存量。

如果您没有意识到这一点，这是一个很容易落入的陷阱，因为您在本地开发中的所有查询通常都会完美运行。也许在生产环境中，一开始也没有问题。但是随着应用程序的增长，数据量和查询的复杂性都会增加。只有到那时，您才会开始遇到问题，就像教科书上所说的“但它在我的机器上工作”那样。

当work_mem被过度使用时，您将看到数据被调入和调出时的延迟峰值，导致哈希表和排序操作运行得慢得多。性能下降是极端的，甚至可能导致全面中断，这取决于应用程序基础结构的组成。

一个好的值取决于多个因素:Postgres实例的大小，查询的频率和复杂性，并发连接的数量。所以这确实是你应该时刻关注的事情。

通过pgbadger运行日志是寻找警告信号的一种方法。另一种方法是使用自动化的第三方系统，它可以在问题出现之前提醒您，例如pganalyze(说明:我与pganalyze没有关联，但我是一个非常满意的客户)。

此时，您可能会问是否有一个神奇的公式可以帮助您为work_mem选择正确的值。这不是我的发明，但这是PG的老人们传给我的:

work_mem = ($YOUR_INSTANCE_MEMORY * 0.8 - shared_buffers) / $YOUR_ACTIVE_CONNECTION_COUNT

注意：

在实际使用中，我们也可以指定事务级别的work_mem，使用SET LOCAL work_mem来指定。

2、将应用逻辑全部放进存储过程和函数当中

Postgres对过程代码有一些很好的抽象，将大量甚至全部应用程序逻辑推入db层是很诱人的。毕竟，这样做消除了代码和数据之间的延迟，这对用户来说应该意味着更低的延迟，对吧?嗯,不。

Postgres中的函数和过程不是零成本抽象，它们会从你的性能预算中扣除。当您花费内存和CPU来管理调用堆栈时，可用于实际运行查询的内存和CPU就会减少。在严重的情况下，可能会以一些令人惊讶的方式表现出来，比如无法解释的延迟峰值和复制延迟。

简单的函数是可以的，特别是如果你可以将它们标记为IMMUTABLE或STABLE。但是，每当您在内存中组装数据结构或使用嵌套函数或递归时，都应该仔细考虑是否可以将该逻辑移回应用程序层。Postgres中没有TCO !

当然，扩展应用程序节点要比扩展数据库容易得多。您可能希望尽可能推迟考虑数据库扩展，这意味着对资源使用要保守。

3、大量使用触发器

触发器是另一个可能被滥用的功能。

首先，它们的效率低于一些替代方案。可以使用生成列或物化视图实现的需求应该使用这些抽象，因为它们在Postgres内部得到了更好的优化。

其次，在触发器如何鼓励面向事件的思维方面存在一个隐藏的问题。如您所知，在SQL中，将相关的INSERT或UPDATE查询批处理在一起是很好的实践，这样您就可以一次锁定表并一次写入所有数据。您可能会在应用程序代码中自动执行此操作，甚至不需要考虑它。但触发因素可能是一个盲点。

人们倾向于将每个触发函数视为一个离散的、可组合的单元。作为程序员，我们重视关注点的分离，并且通过模型级联进行独立更新的想法非常有吸引力。如果您觉得自己被拉向了那个方向，请记住查看整个图，并查找可以通过批处理查询进行优化的部分。

这里有一个有用的原则，就是在每个表上只使用一个BEFORE触发器和一个AFTER触发器。给你的触发器函数起一个通用的名字，比如before_foo和after_foo，然后把所有的逻辑都内联在一个函数里。使用TG_OP来区分触发操作。如果函数很长，用一些注释将其分解，但不要试图重构成更小的函数。这样可以更容易地确保有效地实现写操作，而且还可以限制在Postgres中管理扩展调用堆栈的开销。

4、重度使用NOTIFY

使用NOTIFY，您可以将触发器的范围扩展到应用程序层。如果您没有时间或兴趣来管理专用的消息队列，那么这很方便，但是这也不是一个没有成本的抽象。

如果生成大量事件，则用于通知侦听器的资源将无法在其他地方使用。如果侦听器需要读取更多数据来处理事件有效负载，这个问题可能会加剧。然后，您将为每个NOTIFY事件以及处理程序逻辑中的每个相应读取支付费用。就像使用触发器一样，这可能是一个盲点，隐藏了将这些读取批处理在一起并减少数据库负载的机会。

代替NOTIFY，考虑将事件写入FIFO表，然后以常规节奏批量使用它们。正确的节奏取决于您的应用程序，可能是几秒钟，也可能是几分钟。无论哪种方式，它都会减少负载，为其他事情留下更多的CPU和内存。

事件队列表的可能模式可能如下所示:

CREATE TABLE event_queue (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type text NOT NULL,
  data jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  occurred_at timestamptz NOT NULL,
  acquired_at timestamptz,
  failed_at timestamptz
);

有了这个，你可以像这样从队列中获取事件:

UPDATE event_queue
SET acquired_at = now()
WHERE id IN (
  SELECT id
  FROM event_queue
  WHERE acquired_at IS NULL
  ORDER BY occurred_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1000 -- Set this limit according to your usage
)
RETURNING *;

在read上设置acquired_at并使用FOR UPDATE SKIP LOCKED保证每个事件只处理一次。在它们被处理之后，您也可以批量删除获取的事件(对于永久存储无限大小的历史事件数据，有比Postgres更好的选择)。

注意：

我来提示一下， 这里FOR UPDATE SKIP LOCKED 是上边处理逻辑的关键。有不清楚的，可以自查官方文档。非常有用的一种技巧。

5、不要在真实数据上执行EXPLAIN ANALYZE

EXPLAIN是每个后端工程师工具包中的核心工具。我相信你已经勤奋地检查了可怕的Seq扫描的查询计划。但是如果使用EXPLAIN ANALYZE, Postgres可以返回更准确的计划数据，因为它实际上执行了查询。当然，您不希望在生产环境中这样做。因此，要很好地使用EXPLAIN ANALYZE，首先应该采取几个步骤。

任何查询计划都只与运行它的数据一样好。在每个表中只有几行的本地数据库上运行EXPLAIN是没有意义的。也许您很幸运，拥有一个全面的种子脚本，可以用实际数据填充本地实例，但即使这样，也有更好的选择。

在生产基础设施旁边设置一个专用的沙箱实例非常有帮助，定期使用prod最近的备份进行恢复，特别是为了在开发中的任何新查询上运行EXPLAIN ANALYZE。让沙盒实例比生产实例小，这样它就比prod更受约束。现在EXPLAIN ANALYZE可以让您对部署后的查询预期执行情况有信心。如果它们在沙箱中看起来不错，那么当它们进入生产环境时就不会有什么惊喜了。

6、首选CTE而不是子查询

如果你经常使用EXPLAIN，这个可能不会让你感到困惑，但它之前让我感到困惑，所以我想明确地提一下。

许多工程师都是自下而上的思考者，而CTE(即WITH查询)是表达自下而上思维的自然方式。但它们可能不是最有效的方法。

相反，我发现子查询的执行速度要快得多。当然，这完全取决于特定的查询，所以我不会一概而论，只是建议您应该为自己的复杂查询解释这两种方法。

文档的“CTE物化”^[1]部分讨论了潜在的原因，其中更明确地描述了性能权衡。这是一个很好的总结，所以我就不浪费你的时间在这里解释了。如果你想知道更多，就去读一读吧。

注意：

这一观点， 目前也显得过时了。从12版开始，Postgres在优化CTE方面做得更好了，而且通常只是用子查询代替CTE。这里保留了这一节，因为关于与EXPLAIN方法比较的更广泛的观点仍然存在，而且“CTE物化”文档仍然值得一读。但是请记住上面链接的评论!

7、对时间要求严格的查询使用递归CTE

如果您的数据模型是一个图，那么您的第一反应自然是递归地遍历它。Postgres为此提供了递归CTE，它们工作得很好，甚至允许您优雅地处理自引用/无限递归循环。虽然它们很优雅，但速度并不快。随着图形的增长，性能会下降。

这里有一个有用的技巧，就是考虑应用程序流量在读和写方面是如何堆积的。读比写多的情况很常见，在这种情况下，您应该考虑将您的图形非规范化为一个物化视图或表，以便更好地优化读取。如果您可以将每个可查询子图存储在自己的行上，包括查询所需的所有相关列，那么读取就变成了一个简单(且快速)的SELECT。这样做的代价当然是写性能，但通常是值得的。

8、外键上不加索引

Postgres不会自动为外键创建索引。如果您更熟悉MySQL，这可能会让您感到惊讶，因此请注意其含义，因为它可能会在几个方面伤害您。

它最明显的影响是使用外键的连接的性能。但是这些很容易用EXPLAIN发现，所以不太可能抓住你。

也许不太明显的是ON DELETE和ON UPDATE行为的性能。如果您的模式依赖于级联删除，那么通过在外键上添加索引可能会获得很大的性能提升。

9、使用IS NOT DISTINCT FROM与索引列比较

当您对NULL使用常规比较运算符时，结果也是NULL，而不是您可能期望的布尔值。解决这个问题的一种方法是替换<>with IS DISTINCT FROM和replace = with IS NOT DISTINCT FROM。这些运算符将NULL视为常规值，并且总是返回布尔值。

然而，如果有索引的话，=通常会导致查询计划器使用索引，而is NOT DISTINCT FROM则会绕过索引，而可能会执行Seq扫描。当您第一次在EXPLAIN的输出中注意到它时，可能会感到困惑。

如果发生这种情况，并且您希望强制查询使用索引，则可以显式设置null检查，然后对非空情况使用=。

换句话说，如果你有一个像这样的查询:

SELECT * FROM foo
WHERE bar IS NOT DISTINCT FROM baz;

你可以这样做:

SELECT * FROM foo
WHERE (bar IS NULL AND baz IS NULL)
OR bar = baz;

参考资料

往期导读: 
1. PostgreSQL中配置单双向SSL连接详解
2. 提升PSQL使用技巧：PostgreSQL中PSQL使用技巧汇集(1)
3. 提升PSQL使用技巧：PostgreSQL中PSQL使用技巧汇集(2)
4. PostgreSQL SQL的基础使用及技巧
5. PostgreSQL开发技术基础:过程与函数
6. PostgreSQL中vacuum 物理文件truncate发生的条件
7. PostgreSQL中表的年龄与Vacuum的实验探索：Vacuum有大用
8. PostgreSQL利用分区表来弥补AutoVacuum的不足
9. 也聊聊PostgreSQL中的空间膨胀与AutoVacuum
10. 正确理解SAP BTP中hyperscaler PG中的IOPS (AWS篇)