36 先优化工作，再升级机器

我们拿了一个小应用的工作负载，在一台笔记本上对本书的示例数据运行，每次只改一件事。同一台机器，同样的数据，同样的请求。最初每个事务读取 91,448 页，每秒完成 13 个事务。八次改动之后，每个事务读取 38 页，每秒运行一万多个事务。

这些改动没有一项是换更大的机器，也没有一项是加副本、做分区或换数据库。它们是三个索引、两次改写、一个表设置、把二十一条查询换成一条、一个连接池、几项配置，以及一张汇总表。

机器从来就不是问题所在。

这就是本章的主张：让 Postgres 变快，主要在于顺序。找出昂贵的工作，从成本最低的地方修起，每改一项都测量。硬件排在接近末尾的位置，横向扩展在那之后。

顺序就是方法

下面各节是可以采取的优化手段，按每投入一小时通常能获得的收益排序：便宜、收益大、容易撤销的在前；昂贵、收益小、难以撤销的在后。测量证明没有问题的项目可以跳过，但不要越级去做后面的事。

这份排名不是自然定律：一个有 3,000 个未使用连接池的客户端的系统，应先加连接池，再动索引。因此，第 1 项是测量。

整张清单都遵循两条规则。每次只改一件事，改动前后测量相同的工作负载（只改一件事，并端到端测量）。数页面，不数毫秒：每次调用访问的页面数在不同运行间几乎不变，因此即使机器上有干扰，它也能告诉你改动起了什么作用（缓冲区才是证据）。书末的检查清单也采用这个顺序，每个习惯一行。

1. 找出昂贵的工作

你正在想的那条慢查询，往往不是代价最高的那条。按总时间和每次调用的块数对 pg_stat_statements 排序，采样等待事件，判断会话是在工作、读取还是排队。然后在动手修改之前写下一句话：“我们的瓶颈是 X，因为 Y 显示了 Z。”

**相关内容：**用四种方式给同一视图排序，先看总时间、采样等待，看看时间花在哪里、慢查询的方法。

2. 修查询和索引

大多数收益都在这里。为了返回 20 行而读取 35,000 页的查询，问题是缺少索引，或谓词无法利用索引；修复只需一行 DDL 或改一下 where：本书的第一个索引就把一条查询的读取量从 35,249 页降到 91 页。深度 OFFSET 分页也属于这里：它会读取跳过的每一行，而键集分页（keyset pagination）只读取要返回的那一页。信号是：每次调用的块数很高，Rows Removed by Filter 远大于返回行数。

**相关内容：**从查询出发设计索引，而不是从列出发、保持索引列裸露、OFFSET 会读取它跳过的每一行、错误计划有四种原因。

3. 修模式

模式会长久地决定每行数据要花多少页面。可用的手段包括：窄类型、按对齐要求排列列顺序，把 8 字节类型放在前面（某张表上节省了 27%）、顺序键或 UUIDv7 键，以及适合 HOT 更新的表。它排在查询之后，只是因为重塑一张大表需要做迁移，不能只发一次版。信号是：HOT 占比低、行宽、每次插入产生的 WAL 量。

**相关内容：**每个字节都要按行付费、慎选键，少后悔、HOT 是一次性做出的设计决定。

4. 减少往返

一个请求发出 21 条很快的查询，就要付出 21 次往返的代价，而 pg_stat_statements 永远不会为它亮红灯，因为没有哪一条语句很慢。修复方式都是“更少、更大的语句”：用连接或 lateral 代替 N+1 循环，用批量写入代替每行一次插入。信号是：每个请求的查询数。

**相关内容：**N+1 是一个循环，不是慢查询、快速加载数据。

5. 保持清理和统计信息健康

这属于日常维护，不是调优。一旦落后，前面所有手段都会失效：死行会增加每次扫描的工作量，陈旧统计信息则按一张已经不存在的表来规划。信号是：n_dead_tup、最老事务的年龄、Heap Fetches。

**相关内容：**对大表来说，默认值不对、清理无法移除旧快照可能需要的内容、自动清理的分析会漏掉三类变化。

6. 使用连接池

每个连接都是一个进程。每个请求都新建连接，就要每次承担 fork、认证和冷缓存的代价；让几千个连接保持打开，就要付出内存和争用的代价。使用连接池，按核心数而不是用户数来确定池大小。信号是：连接速率，以及按状态统计的连接数。

**相关内容：**每个连接都是一个进程、根据核心数而不是用户数确定连接池大小。

7. 调整配置

配置为工作分配资源预算。它不能让查询少读页面：一次扫描要读 35,000 页，无论 shared_buffers 设为多少，都还是 35,000 页。它能做的是解除上限，因此要在你能说清上限在哪里时才调：临时文件、请求触发的检查点、未命中缓存的工作集。

**相关内容：**内存：先做预算，再调优、从这份 4 vCPU、16 GB 配置开始，然后测量。

8. 预计算与缓存

当每条查询都只读取所需数据之后，就别再把同一个昂贵的问题问两遍了。这一步排得这么靠后，是因为汇总表（rollup）或缓存都是同一份事实的第二份副本，需要持续维护它的新鲜度。信号是：每次调用的块数随租户规模增长。

**相关内容：**预先算好要展示的内容、把工作移出请求路径。

9. 购买耗尽的资源

现在，而且只有到现在，才轮到机器：购买测量表明已经耗尽的资源，而不是直接升到下一档。

**相关内容：**用数据争取更大的机器、迁移之前，先证明问题出在磁盘。

10. 只有相应信号出现时，才横向扩展

副本、分区和分片各自都带有永远不会消失的代价，因此每一种都要等到自己的信号出现。

**相关内容：**证据说到头了，纵向扩容才算到头。

实证：一个应用，八次改动

写一张清单不费什么事。所以我们拿本书的示例数据实际跑了一遍：同一份工作负载，按照这份操作指南的顺序，每次只调整一项，每次改完都测量。下面的每个结论，在实验中都有对应检查。

这个应用有四个端点，写法都是第一版常见的样子。仪表盘按天统计某个账户 30 天内的事件，筛选条件里有类型转换（created_at::date >= ...）。动态列表使用 OFFSET 分页。项目页加载 20 个项目，再分别为每个项目发送一条查询，获取其最新事件：这就是 ORM 替你写出的 N+1 循环。摄入端点插入一条部署事件，并递增项目计数器。而且应用每个请求都会新建连接，就像一个无服务器函数。events 最初只有主键。

-- dashboard: an account's events per day, filter on a cast
select created_at::date as day,
       count(*) as events,
       count(*) filter (where kind = 'deploy'
                          and payload->>'status' = 'failed') as failed_deploys
from events
where account_id = :a
  and created_at::date >= date '2026-09-06'
group by 1
order by 1;

-- feed: OFFSET pagination, pages 1 to 25
select id, project_id, kind, created_at
from events
where account_id = :a
order by created_at desc, id desc
limit 20 offset :skip;

-- projects page: 20 of these, one per project
select kind, created_at from events
where project_id = :p1 order by created_at desc limit 1;

-- ingest: a deploy plus the project's counter
begin;
insert into events (account_id, project_id, user_id, kind, payload, created_at)
select account_id, id, owner_id, 'deploy', '{"status": "ok"}',
       timestamptz '2026-10-06 12:00'
from projects where id = :p;
update projects
   set event_count = event_count + 1,
       last_event_at = timestamptz '2026-10-06 12:00'
 where id = :p;
commit;

基线：每个事务 91,448 页

没有索引时，仪表盘和动态列表各自读取整张 events 表的 35,173 页，分别返回 30 行和 20 行。项目页把整张表读了 20 遍：一次页面访问执行 21 条语句，读取约 703,000 页。混合负载平均每个事务读取 91,448 页，每秒运行 13 个事务。

select lab.prove('with no index, the dashboard and the feed each read the whole events table: over 35,000 pages a call',
  :s0_dash > 35000 and :s0_feed > 35000);
select lab.prove('the N+1 projects page sends 21 statements and reads over 700,000 pages',
  :s0_stmts = 21 and :s0_proj > 700000);
select lab.prove('the baseline mix averages over 80,000 pages per transaction',
  :s0_mix > 80000);

先调配置，重要的东西并没有改变

我们先做了那件看起来顺理成章的事：调优服务器。work_mem = 64MB、random_page_cost = 1.1、effective_cache_size = 8GB、jit = off，每条查询四个并行工作进程。

每个事务的页面数为 91,510，与基线的差距在 0.1% 以内。吞吐量一次为 8，另一次为 17，基线是 13。只是噪声。配置改变工作如何运行，却不改变有多少工作。

select lab.prove('tuning the configuration first leaves pages per transaction where they were (within 2%)',
  abs(:s_tuned_mix - :s0_mix) < 0.02 * :s0_mix);
select lab.prove('the indexes alone beat the tuned baseline''s throughput by more than 3x',
  :s1_tps > 3 * :s_tuned_tps);

按操作指南的顺序，做八次改动

每一步都保留前面所有步骤的改动。两列吞吐量来自实验的两次完整执行；每个数字都是该步骤各次运行的中位数。

页面数来自第一次完整执行；第二次与之相比，差异不超过 1%。

select lab.prove('pages per transaction fell more than 1,000x from the baseline',
  :s8_mix * 1000 < :s0_mix);
select lab.prove('throughput rose more than 100x on the same machine',
  :s8_tps > 100 * :s0_tps);

create index events_account_created_idx on events (account_id, created_at, id);
create index events_project_created_idx on events (project_id, created_at);
create index projects_account_idx on projects (account_id);

select id, project_id, kind, created_at
from events
where account_id = :a
  and (created_at, id) < (:last_created_at, :last_id)
order by created_at desc, id desc
limit 20;

select lab.prove('step 1, indexes: pages per transaction fall more than 100x',
  :s1_mix * 100 < :s0_mix);
select lab.prove('step 1: throughput rises more than 5x',
  :s1_tps > 5 * :s0_tps);
select lab.prove('step 1: the dashboard still reads every event of the account (1,500 to 2,500 pages), because of the ::date cast',
  :s1_dash between 1500 and 2500);
select lab.prove('step 2, a bare-column range: the dashboard reads under 250 pages, 8x fewer',
  :s2_dash < 250 and :s2_dash * 8 < :s1_dash);
select lab.prove('step 2: throughput rises more than 2x',
  :s2_tps > 2 * :s1_tps);
select lab.prove('step 1 to 2: OFFSET pagination reads 200 to 300 pages for 20 rows',
  :s2_feed between 200 and 300);
select lab.prove('step 3, keyset: the feed reads under 30 pages, 8x fewer',
  :s3_feed < 30 and :s3_feed * 8 < :s2_feed);

alter table projects set (fillfactor = 90);
vacuum full projects;   -- rewrites at the new fillfactor; pg_repack in production

select lab.prove('steps 0 to 3: over 100 counter updates went cold on packed pages',
  :s0_cold + :s1_cold + :s2_cold + :s3_cold > 100);
select lab.prove('step 4, fillfactor 90: over 99.5% of the counter updates are HOT',
  :s4_hot > 99.5);
select lab.prove('step 5, one lateral query: 1 statement instead of 21, the same pages within 10%',
  :s5_stmts = 1 and abs(:s5_proj - :s4_proj) < 0.1 * :s4_proj);
select lab.prove('step 5: the projects page got faster (mean latency below step 4''s)',
  :s5_ms < :s4_ms);
select lab.prove('step 6, pooled connections: throughput rises more than 3x',
  :s6_tps > 3 * :s5_tps);
select lab.prove('step 6: a warm session''s ingest touches under a third of the pages a fresh connection''s does',
  :s6_ingest * 3 < :s5_ingest);
select lab.prove('step 7, configuration: pages per transaction stay within 5%',
  abs(:s7_mix - :s6_mix) < 0.05 * :s6_mix);
select lab.prove('step 8, the rollup: the dashboard reads under 50 pages, over 3x fewer',
  :s8_dash < 50 and :s8_dash * 3 < :s7_dash);

表格告诉了我们什么

**第一项手段做了大部分工作。**仅靠索引，每个事务的页面数就降为原来的 1/120；后续所有改动又在此基础上降到约 1/20。几乎所有系统都是这个样子：一两条语句占据了大部分代价，而修好它们很便宜。

**瓶颈移到哪里，吞吐量才会随之变化。**添加索引、改写日期条件、使用连接池时，吞吐量都会跃升。在这些步骤之间，键集分页和 N+1 修复减少了实际工作，吞吐量却原地不动，因为每个请求仍要付出新建连接的代价，而这是 pg_stat_statements 看不到的。要测量整个请求。

**有些手段的收益要以后才显现。**填充因子在这里没有带来变化。它改变的是更新一个月之后，索引会变成什么样。

**更大的机器也帮不上忙。**每个事务依然要读 91,448 页。同一台笔记本，按这份操作指南花一个下午，处理的事务数就比原来多了三个数量级。有人要求更大的数据库时，先做这个实验。

横向扩展之前

你已经按操作指南做过一轮。如果测量仍然表明某项资源耗尽，现在可以考虑更大的机器。本书下一部分就从这里开始：先纵向扩容，再横向扩展把这些证据变成升级规格的申请，然后继续攀登后面的台阶。

本章要点

• 按顺序修：找出昂贵的工作、查询与索引、模式、往返、日常维护、连接池、配置、预计算。之后才购买硬件或横向扩展。
• 从 pg_stat_statements 和一次等待采样开始，写下瓶颈是什么。
• 每次只改一件事；数页面，页面数不会随噪声波动。
• 仅靠索引，每个事务的页面数就缩减至原来的 1/120；八次改动将页数从 91,448 降至 38，在同一台笔记本上让吞吐量达到原来的约一千倍。
• 先调配置，页面数几乎没变。配置设置上限，却不能让一次扫描少读数据。
• 瓶颈移到哪里，吞吐量才会随之变化：每个请求新建连接，掩盖了所有查询修复的收益，直到我们使用连接池。
• 先用数据证明，该换更大的机器了：升级规格的申请应附带证据。

延伸阅读

• Performance Tips，PostgreSQL 文档。从源头介绍 EXPLAIN、统计信息和数据库装载。
• pg_stat_statements，PostgreSQL 文档。每个字段，包括本章用调用次数作分母计算的缓冲区计数。
• pgbench，PostgreSQL 文档。自定义脚本、权重、--random-seed 和 -C，实验中都用到了。
• The cumulative statistics system，PostgreSQL 文档。等待事件，以及 HOT 占比背后的表统计信息。
• Server configuration: query planning，PostgreSQL 文档。random_page_cost、effective_cache_size 和代价模型的其他部分。
• Routine vacuuming，PostgreSQL 文档。为什么清理属于维护，而不是调优。