这是 Postgres（Postgres: 世界上最好的数据库），以及原子能，全网最尊重Postgres的程序员。在这个视频系列里，我将结合自己接近10年的Postgres实战经验，带你深入了解它的技术原理和应用场景，让你彻底搞懂Postgres，能在日常工作中发挥它的性能和功能优势。

深入理解Postgres性能优化

今天的第一期，我们就来聊大家最关心的性能优化问题。因为数据库通常是系统架构中的性能瓶颈，对它的优化会直接决定整个系统的性能上限。绝大部分程序员对于数据库优化的理解，仅限于 SQL语句（SQL: 结构化查询语言）的层面。常用的招数就是 Query（Query: 查询）、Index（Index: 索引）、Explain（Explain: 解释）这个“大三角”。

先通过 explain analyze 分析 Query 的执行方案，然后找到那些会出现 Sequential Scan（Sequential Scan: 顺序扫描）的地方，最后补上相对应的 Index，提高扫描速度。这招平时都挺好用，直到某一天，你遇到了一个解释不清的结果。比如，明明已经有 Index，为什么它还是选择了 Sequential Scan，而不是 Index Scan（Index Scan: 索引扫描）呢？

这是因为 Query Planner（Query Planner: 查询规划器）的规划算法比你想象的要复杂不少。它的决策不是一个简单的 Decision Tree（Decision Tree: 决策树），而是会考虑到诸多因素。比如，对于某个 Query，它会先估算最后会匹配到的数据量。当它发现这个总量很少的时候，就会觉得：如果我先扫 Index，再跳到 Table（Table: 表）去读取数据，来回跳转的成本说不定比直接扫 Table 读取数据还要高。那不如干脆就跳过 Index，直接做 Sequential Scan 了。

你可以把 Query Planner 看作是 SQL 的 Compiler（Compiler: 编译器）。就像我在【让编程再次伟大#28】里面提到过的，别跟 Compiler 耍小聪明，它的心思比你多太多了。

而另一个不要太纠结 SQL 层面的原因是：explain 只会告诉你一个在真空中的理论值。但在现实环境里，你的 Query 不是一个独立事件。你的数据库时刻都在处理各种 Query，还有各种后台任务在执行。它们都需要排队分享各种资源：如何分配 CPU 和内存？要开几个并行的线程？缓存多少数据？什么时候写入硬盘？这些都会在极大程度上影响数据库整体以及每一个 Query 的运行性能。

配置参数：性能上限的真正幕后“黑手”

掌控这一切的，是 Postgres 底下的数百个配置参数。可以说它们才是决定 Postgres 性能上限的真正幕后“黑手”，也是我们这期视频的真正主角。配置参数看似只是一些简单数字，但是要把它们配好并不是简单的事情，因为你不能够无脑地直接拉到最大值。不是什么东西都是越大越好的，尺寸合适才是最重要的。

就拿缓存来说，shared_buffers 可以说是整个 Postgres 里最重要的参数之一。因为内存比硬盘有量级上的速度优势，所以我们肯定是希望能够尽量多地缓存数据，让查询更多地从内存拿数据，而不是从硬盘上拿。但 Postgres 官方文档却不建议你把 shared_buffers 设置太高，最好是系统内存的 25%。这是为什么呢？

因为 Postgres 是把数据以文件形式存在很多个文件夹里的，所以每次读取数据的时候，都要经过文件系统。这样就会触发系统内核里的缓存，这也会提供一个强有力的加速。但操作系统上的缓存是动态管理的，如果当前的内存所剩空间不多了，它就会主动舍弃一些缓存，这样就会影响到数据库读文件的性能。所以我们要兼顾 Postgres 和系统缓存，找到一个合适的平衡点。这也是从【让编程再次伟大#01】开始，我就反复提到过的“取舍的艺术”。

而有些时候，Postgres 的参数甚至会直接受到系统参数的影响。比如 max_files_per_Postgres，可以同时打开的文件数量。这个默认是 1,000，一般情况下已经够用了，因为 Postgres 会尽量把同一个表的数据存在同一个文件里，所以一般的查询都不会需要打开多少个文件。但有一个特殊情况是：如果你用到了 Partitioned Table（Partitioned Table: 分区表），每一个 Partition（Partition: 分区）都会被分开存储。查询它（这个 Table）的时候就会可能需要打开很多个文件，那这个参数就会变成性能瓶颈。

但你也不能够随便改它，因为在操作系统上也有一个类似的参数，Postgres 是无法突破系统的上限的。比如说 Ubuntu 和 Fedora 这些家用的 Linux 系统，默认的单进程就最多只可以同时打开 1,024 个文件。这主要是因为系统要顾全大局，要确保每个程序的需求，不能够让一个程序吃光所有的资源。不过我们在生产环境里面，通常都会给数据库准备一个独立的服务器，本来就是要把所有资源都给数据库用的，自然就可以大胆地把系统的上限和 Postgres 上限都往上改了。

版本演进与参数的生命周期

而其实上面这个 Partition 的性能问题，在 v11 之后的 Postgres 已经不是问题了。因为 v11 有一个新参数 enable_partition_pruning，能够给 Query Planner 加上一个更智能的 Partition 检测逻辑。比如说你有个储存日志的表，通过月份做了 Partition。那当你搜索今年 1 月到 3 月的数据的时候，Planner 就会判断有几个 Partition 可以用得上，而那些用不上的 Partition，它们所在的文件都不需要被打开，数据也不会被读取，那整个数据库的压力就会小很多。

那如果我的 Postgres 是 v11 之前的版本怎么办？也不用着急，因为有一个已经被淘汰的老参数 constraint_exclusion，可以提供到类似的功能。但它的功能只作用于 Inherit Table（Inherit Table: 继承表）这种过时老语法。而这也反映出这套知识体系的学习难度所在，因为 Postgres 每年都会稳定地发布大版本更新，每年都会有新的参数出现，旧的参数被淘汰。你就得一直学，一直跟上。截止到最新的 v18，Postgres 总共有不多不少刚好 400 个配置参数。我不建议你一口气把它们全部都啃下来，因为这是不可能的（吗？）。所以我推荐你从一些重要的基础参数开始。

这里我分享一个很好用的小网站：postgresqlco.nf。这是一个西班牙的小公司做的，他们收集整理了所有的配置参数，包括它们的加入版本、移除版本、默认值、可以调整的范围、官方的说明文档，以及如何优化的建议。所有的信息都一目了然。这个小网站有一个调参教程，分为基础、进阶、高级三种。基础教程里面列了 15 个参数，如果你没什么头绪的话，就可以从这 15 个下手，一个个熟悉。

性能测试：参数调优的真实威力

那么问题来了，如果把所有的参数都弄懂了、都优化了，能够达到什么样的性能效果呢？3%？30%？还是 300%？买定离手！废话不多说，我们直接上机测试一下。

这里我准备了三台配置一样的服务器，都是 8 核 16G 内存，都安装了 Postgres v13。其中第一台保留了默认的配置参数，没有做任何的修改。而第二台，我手动优化了刚才提到的 15 个基础参数，当然这个改动只是我的个人做法，不代表最优解。而第三台，则是把所有的 Postgres 配置参数以及相关的操作系统参数都优化了。不过事先声明，这里用的是华为云的 X 实例（x2e 型号），它有自动调参和自动优化功能，所以不是我手动调的 400 个参数，我暂时还没有那么强。

开始测试之前，先介绍一下这次的测试场景。首先框架用的是 Postgres 自带的 pgbench。它不是 MySQL 那个 mysqlslap 那种垃圾，你就看它文档上这一大堆参数，基本上什么场景都可以模拟，除了没有炫酷的 Dashboard（Dashboard: 仪表盘），不亚于那些专门用来做性能测试的第三方付费软件。你也可以设计自己的测试脚本。而 pgbench 也自带了 3 个脚本：一个是只模拟读取的 select-only，一个是以更新为主的 simple-update，以及混合了 SELECT、INSERT、UPDATE 命令的 tpcb-like。最后这个是基于数据库行业最权威的 TPC 测试标准设计出来的，我寻思这参考价值应该比自己手搓的脚本更好，所以这次就决定用它了。

而为了让测试更接近生产环境，我参照自家产品（的数据）设置了 20 个并行的 Connection（Connection: 连接）。因为一般情况下，我们的后端系统都会通过 Connection Pool（Connection Pool: 连接池）来获取 Connection，而不是直连数据库。所以就算整个服务的流量有 1,000 TPS（TPS: Transactions Per Second，每秒事务数），实际上也只会有 20 个并行的 Connection。

好了，环境搭建好，我们就可以开始测试了。pgbench 实时反馈的统计报告包括三个指标：体现吞吐能力的 TPS（每秒事务数），计算端对端平均用时的 Latency Average（Latency Average: 平均延迟），以及用时波动的 Standard Deviation（Standard Deviation: 标准差）。

那么最后的测试结果出来： 首先看第一台服务器，我们的默认基准组的最终平均数据，它跑出了 6,400 TPS，Latency 是 3ms，Standard Deviation 是 8ms。 而第二台服务器，经过我精心打磨了 15 个参数的人工组，最后跑出来 8300 TPS，Latency 是 2.4ms，Standard Deviation 是 7ms，和基准对比提升了大约 30%。效果已经非常显著了。你要知道，这个测试脚本里都是一些很简单的 SQL 语句，没有什么 Join（Join: 表连接）、Partition（Partition: 分区）之类的复杂语法。你要是只在 SQL 层面上面优化，你再加 100 个 Index，也做不出来 30% 的性能提升。

最后我们看第三台服务器，所有参数都自动优化、自动调整之后的机器组，它跑出了惊人的 22,000 个 TPS，对比基准组整整 343% 的提升。不是 3%，不是 30%，是 300%！一个简单的 SELECT 或者 INSERT 能够获得 3 倍以上的性能提升，是不是很反直觉？但事实就是如此，很多时候我们其实就是守着金山，却不知道怎么把这个金子挖出来而已。

而除了 TPS，Latency 的 Standard Deviation 同样也很重要。有参与过系统维护工作的观众应该都懂，最美的线条就是 Dashboard 上面的平滑直线，那些忽上忽下的心电图则是最让人心脏骤停的。而很多这种波动情况都是资源分配的不合理造成的，比如说缓存太容易满，或者 Vacuum（Vacuum: 垃圾回收）经常和查询任务撞上之类的。

从基准组的分阶段数据就能看到，平均 Standard Deviation 8ms，好的时候 3ms，差的时候 16-17ms，甚至能够到 21ms 上下，会有 7 倍的波动幅度。而在全员优化的机器组，平均 Standard Deviation 在 5ms 左右，分阶段录得最好是 3ms，最差是 9ms，大多数情况下都在 4-6ms 之间。所以配置参数的优化，不只是提到提速的作用，它代表着资源的分配更丝滑，所以更少出现 Edge Cases（Edge Cases: 边缘案例）。

最后，如果你想要获得更精细的数据，你可以在 pgbench 上用到 --log 参数，它会保存一份详细的测试日志，每一个 Transaction（Transaction: 事务）都会单独记录，你就可以用它手动统计 p95、p99 之类的指标了。

专家经验与AI时代的“Know-Why”

我前年发过一个讨论 CentOS 系统迁移的视频，里面提到过一个观点：“在底层系统的维护上，我更相信经过多年经验积累下来的专家知识和规则库”。这个 Postgres 性能测试再次验证了这个观点。为什么这么说呢？因为在测试过程中，我就一直很好奇，机器组的这个华为云 X 实例怎么做到给这么多参数做优化的？是不是搞了什么时髦的大模型在后面进行操盘？

所以我动用小小的人脉，拿到了一些信息，得知了它背后的研发过程。他们首先是把各个版本的操作系统参数、内核参数、Postgres 参数都收集起来，收了大概有 7,000 多个。然后他们的数据库专家入场，结合以前服务过的客户、调优过的数据库，找到一些最佳案例，然后把这些定为 Global Minima（Global Minima: 最优解）。之后用上经典优化算法 Bayesian Optimization（Bayesian Optimization: 贝叶斯优化），对 7,000 个参数进行优化、推导，直到落入到这个 Global Minima 为止。这就非常的朴实无华，都是实打实的经验库产物，没有大模型那种解释不清的黑箱推演，确实很能让人安心。

这也让我想起了上个月采访开源大佬朱彬彬时，彬彬提到一个云服务行业的潜规则：“大家都有的东西，就没有办法做出差异化”，“真正决定胜负的，都是那些专家藏起来的经验和独特的功能”。那也不怪华为会在这里藏一手了，这差异化不就打出来了嘛。只不过我觉得他们也没必要藏，因为我大胆地预言：以后动态调优 Postgres 参数会成为主流的做法，也会成为每个架构师的标配设计方案。

不过华为的这个操作，也刚好解释了本期视频最重要的一个问题：“我为什么要关心这些参数？”“我为什么要费那么多功夫去研究它们？”因为很多程序员会有一个错误的观念：就觉得这些配置参数这种东西是 DBA（DBA: Database Administrator，数据库管理员）在管的，不是自己的职责范围，自己没必要学习。对这些人，我建议他们重温【让编程再次伟大#20】，领悟一下里面（著名程序员）Atomic Energy的名言：“the secret is in the know-why”。这句话在 AI 时代尤为宝贵。因为 AI 正在以摧枯拉朽之势接管各种“操作型”的任务。你觉得一个只会操作数据库的人，和一个了解数据库底层原理的人，哪个更有机会保住饭碗呢？