PostgreSQL 性能调优 · 全景深度教程

让两个自动系统的"估算"贴近现实

PostgreSQL 的慢和膨胀,几乎都来自两个判断:查询优化器估算"哪个执行计划最便宜",autovacuum 估算"何时该清理死元组"。这份教程围绕这条主线,把诊断、优化器、索引、MVCC/VACUUM、配置五块连成一条因果链。

基于 PostgreSQL 13–18(默认值标注到 18) 阅读 ≈ 半天面向有 SQL 经验的后端工程师代码:示例 SQL 未在本机统一验证,输出形态以官方语义为准

A适合谁

这份教程为以下读者写,三条都满足时收益最大:

能写中等复杂度的 SQL(多表 JOIN、子查询、聚合、窗口函数),理解索引大致是什么,但没系统调过 PostgreSQL。
用过关系型数据库(尤其是 MySQL),知道"加索引能加速查询",但说不清 PostgreSQL 为什么有时不用你建的索引。
能在本机或测试库执行 EXPLAIN、改 postgresql.conf、重启实例——教程里的每个结论都建议你拿真实库验证一次。

B不适合谁

完全没写过 SQL:先补 SQL 基础(SELECT / JOIN / GROUP BY),再回来。推荐 PostgreSQL 官方 Tutorial。
找的是部署/高可用/复制方案:本教程聚焦单实例性能,不覆盖流复制、failover、分片中间件。那是另一套主题。
资深 PostgreSQL DBA:你对 planner 内部和 VACUUM 细节已有体系认知,这里的铺垫会偏慢。直接读 release notes 和源码 README 更划算。

C读完之后你能做到什么

这份教程想给你的、文档不会直接给的一句话:

这份教程的核心收获

慢和膨胀都是"某个自动系统的估算偏离了现实"——优化器估错了代价,或 autovacuum 估错了清理时机。学会把任何症状先归位到"哪个估算错了、它的输入是什么",再决定动哪个旋钮,而不是背一张参数表。

具体的、可验证的能力:

读懂 EXPLAIN (ANALYZE, BUFFERS) 的每一行,指出估算行数与真实行数偏差最大的节点。
解释 PostgreSQL 为什么对某条查询选了顺序扫描而不是你建的索引,并说出至少两种让它改主意的办法。
判断一张表是否膨胀、autovacuum 是否跟得上,并算出它的触发阈值。
为一台已知内存的机器,给出 shared_buffers / work_mem / maintenance_work_mem 的初始值,并说清每个值对应前面哪个机制。
面对"查询变慢了"的线上工单,按"测量 → 归因 → 改一个变量 → 复测"的流程定位,而不是凭直觉乱调。

一句话本质

PostgreSQL 调优 = 校准两个自动系统的估算,让它贴近现实。查询优化器靠统计信息 + 代价模型估算"哪个执行计划最便宜";autovacuum 靠阈值公式估算"何时该清理死元组"。你的工作不是接管方向盘,而是喂给它们准确的输入(统计、索引、代价参数、内存、vacuum 阈值)和足够的资源。几乎每个调优动作都能挂到这条因果链上。

由此引出两个核心观念——理解了它们,其余内容会自然归位:

① 优化器(第 02 章):PostgreSQL 社区版默认不支持 query hint——你无法命令优化器走某个计划。计划完全由代价模型自动选。所以查询调优 = 修正"代价估算与现实脱节",而不是 FORCE INDEX。这是从 MySQL 过来最大的思维切换。

② MVCC(第 04 章):UPDATE 不原地改,而是写一个新行版本并留下死元组;DELETE 只是打标记。死元组靠 VACUUM 回收。表膨胀、长事务的危害、freeze/wraparound、autovacuum——全是这一条事实派生出来的。

现状速览 · 截至 2026-06

核心机制(代价模型、MVCC、B-tree、autovacuum 公式)多年稳定,本教程主体适用于 PostgreSQL 13 及以上。但近几个版本改写了几条流传很广的经典建议,正文会在对应位置标注:

B-tree skip scan(PG 18,2025-09):多列索引 (a, b) 在 WHERE 不含前导列 a 时也能被用上——但仅当 a 是低基数列。"前导列必须出现在 WHERE"不再是绝对铁律。
VACUUM TidStore(PG 17,2024-09):VACUUM 的死元组内存不再有 1GB 上限,maintenance_work_mem 可放心调大。
异步 I/O(PG 18):effective_io_concurrency 默认从 1 改为 16,且通过 io_uring/worker 真正生效。
EXPLAIN BUFFERS(PG 18):EXPLAIN (ANALYZE) 默认就带 BUFFERS,不必再手动加。
JIT 默认关(PG 19,2026-06 进入 Beta):"分析型负载就开 JIT"的旧建议被官方默认值反转。

版本节奏:PG 18 是最新稳定版(2025-09 GA),16/17 仍是主流在用,PG 19 已 feature-freeze(Beta 1 约 2026-06-04,GA 目标 2026-09)。本教程默认值以 PG 18 文档为准,旧版差异随文标注。

读之前 · 关于"我已经懂了"的错觉

调优主题特别容易制造"读懂了"的假象,因为每条规则单独看都顺理成章。出现下面三种感觉时,先停一下——它们往往是没真学进去的信号,不是学会了的信号:

"我读得很顺":顺,通常是因为内容和你已有的印象一致,没碰到需要重建的认知。真正的调优反直觉点(顺序扫描有时比索引快、加索引会拖慢写入)读起来应该有点"咯噔"。
"这些参数我都见过":见过参数名 ≠ 知道它对应哪个机制、调大调小各自的代价。本教程的每个参数都要求你能说出它影响的是优化器、内存、还是 VACUUM。
"我没卡壳":每章末尾有 self-check 和"刚好够不着"的挑战。不动手做、直接看答案,等于把这一章当小说读了一遍。

D概念地图

先把整张地图装进脑子,再下钻细节。后面五章的每个知识点,都挂在这张图的某个节点上。

图 0PostgreSQL 调优全景:症状从顶部进入,经第 01 章的测量定位到四类根因。注意:02 优化器与 04 MVCC/VACUUM 标红——它们是两个"核心观念";底部统一节点说明四类根因最终都归到同一句话:某个自动系统的估算偏离了现实。

E三条阅读路径

按目的选路径,不必从头读到尾:

线上救火(最快定位):01 诊断 → 02 优化器(只看扫描类型与统计) → 04 的"膨胀诊断"一节。先能定位,再回头补全。
系统建立心智模型(推荐,顺序读):01 → 02 → 03 → 04 → 05 → 06。每章开头有上一章的回顾句,前后衔接。
面试冲刺(讲清机制 + 权衡):02 优化器 → 03 索引 → 04 MVCC,重点读每章的"备选方案对比"和"带来的代价",最后用 06 的判别场景自测。

F目录

诊断与测量

EXPLAIN 读法、pg_stat_statements、wait events、关键指标。建立"先测量再动手"的纪律。

02 · 核心①

查询优化器与执行计划

代价模型、统计信息、四种扫描、三种 JOIN。计划是被代价估算选出来的。

索引

B-tree 机制、GIN/GiST/BRIN、部分/表达式/覆盖索引、skip scan、索引为何不被用。

04 · 核心②

MVCC、VACUUM 与表膨胀

可见性、死元组、HOT、autovacuum 公式、膨胀诊断、freeze/wraparound。

配置与资源

内存参数、WAL/checkpoint、代价参数、连接池。每个参数对应前面某个机制。

自测题库 + 判别场景

三层梯度题 + 跨章场景:给症状,选该动哪一块。含亲手画图。

G学完之后

分区表(partitioning):在你的"表与索引"心智上加一层"按范围/列表切分,让 planner 做分区裁剪"。
并行查询(parallel query):在第 02 章的扫描/JOIN 上加一层"多 worker 协作",理解 max_parallel_workers_per_gather 何时生效。
复制与只读副本:把单实例调优扩展到"读写分离下,长查询如何拖慢主库 VACUUM"(hot_standby_feedback)。
连接池深入(PgBouncer):在第 05 章"连接成本"上加一层"事务级池化 vs 会话级池化"的取舍。
扩展生态:pg_stat_statements 之外的 auto_explain、pg_buffercache、pgvector 等,按需深入。