---
author: Peter Pang
date: '2026-03-06'
guest: ''
layout: post.njk
source: https://www.youtube.com/watch?v=wmGikV_393Y
speaker: Peter Pang
tags:
  - postgres-optimization
  - performance-tuning
  - configuration-parameters
  - database-performance
  - know-why
title: 揭秘Postgres性能瓶颈：参数调优的威力与实践
summary: 本期视频深入探讨Postgres性能优化的核心——配置参数。演讲者首先指出仅依赖SQL语句优化（如索引）的局限性，随后详细剖析了影响性能的关键参数及其与操作系统的相互作用。通过严谨的性能测试，展示了从默认配置到手动优化再到自动化调参，性能可实现30%至343%的惊人飞跃。视频强调了理解底层原理（Know-Why）的重要性，尤其在AI时代，这关乎核心竞争力。
insight: ''
draft: true
series: ''
category: software-development
area: tech-engineering
project: []
people: []
companies_orgs:
  - Huawei
products_models:
  - Postgres
media_books: []
status: evergreen
---
这是 **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** 正在以摧枯拉朽之势接管各种“操作型”的任务。你觉得一个只会操作数据库的人，和一个了解数据库底层原理的人，哪个更有机会保住饭碗呢？