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

> 按什么顺序让 Postgres 更快：逐项优化，每一项都有表明你需要它的指标，并在一个小应用上完成端到端验证。

---

LLMS 索引： [llms.txt](/llms.txt)

---

<span id="basics-chapter-36"></span>

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

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

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

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

## 顺序就是方法 {#the-order-is-the-method}

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

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

整张清单都遵循两条规则。每次只改一件事，改动前后测量相同的工作负载（[只改一件事，并端到端测量](/29-benchmarking/#change-one-thing-and-measure-it-end-to-end)）。数页面，不数毫秒：每次调用访问的页面数在不同运行间几乎不变，因此即使机器上有干扰，它也能告诉你改动起了什么作用（[缓冲区才是证据](/19-explain/#buffers-are-the-evidence)）。书末的[检查清单](/42-checklists/#basics-chapter-42)也采用这个顺序，每个习惯一行。

<span id="basics-36-1-find-the-expensive-work"></span>

## 1. 找出昂贵的工作 {#1-find-the-expensive-work}

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

**相关内容：**[用四种方式给同一视图排序，先看总时间](/31-observability/#rank-the-same-view-four-ways-total-time-first)、[采样等待，看看时间花在哪里](/31-observability/#sample-waits-to-see-where-the-time-goes)、[慢查询的方法](/19-explain/#a-method-for-slow-queries)。

<span id="basics-36-2-fix-the-query-and-the-index"></span>

## 2. 修查询和索引 {#2-fix-the-query-and-the-index}

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

**相关内容：**[从查询出发设计索引，而不是从列出发](/17-btree/#design-an-index-from-a-query-not-a-column)、[保持索引列裸露](/22-queries/#keep-the-indexed-column-bare)、[OFFSET 会读取它跳过的每一行](/22-queries/#offset-reads-every-row-it-skips)、[错误计划有四种原因](/21-plans/#a-wrong-plan-has-one-of-four-causes)。

<span id="basics-36-3-fix-the-schema"></span>

## 3. 修模式 {#3-fix-the-schema}

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

**相关内容：**[每个字节都要按行付费](/12-types/#every-byte-is-paid-per-row)、[慎选键，少后悔](/13-keys/#basics-chapter-13)、[HOT 是一次性做出的设计决定](/07-hot/#hot-is-a-design-decision-you-make-once)。

<span id="basics-36-4-fix-the-round-trips"></span>

## 4. 减少往返 {#4-fix-the-round-trips}

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

**相关内容：**[N+1 是一个循环，不是慢查询](/22-queries/#n1-is-a-loop-not-a-slow-query)、[快速加载数据](/27-loading/#basics-chapter-27)。

<span id="basics-36-5-keep-vacuum-and-statistics-healthy"></span>

## 5. 保持清理和统计信息健康 {#5-keep-vacuum-and-statistics-healthy}

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

**相关内容：**[对大表来说，默认值不对](/10-vacuum/#the-defaults-are-wrong-for-big-tables)、[清理无法移除旧快照可能需要的内容](/10-vacuum/#vacuum-cant-remove-what-an-old-snapshot-might-need)、[自动清理的分析会漏掉三类变化](/20-planner/#three-kinds-of-change-slip-past-autovacuums-analyze)。

<span id="basics-36-6-pool-connections"></span>

## 6. 使用连接池 {#6-pool-connections}

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

**相关内容：**[每个连接都是一个进程](/28-connections/#every-connection-is-a-process)、[根据核心数而不是用户数确定连接池大小](/28-connections/#size-the-pool-from-cores-not-users)。

<span id="basics-36-7-tune-the-configuration"></span>

## 7. 调整配置 {#7-tune-the-configuration}

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

**相关内容：**[内存：先做预算，再调优](/30-configuration/#memory-budget-it-before-you-tune-it)、[从这份 4 vCPU、16 GB 配置开始，然后测量](/30-configuration/#start-from-this-config-for-4-vcpu-and-16-gb-then-measure)。

<span id="basics-36-8-precompute-and-cache"></span>

## 8. 预计算与缓存 {#8-precompute-and-cache}

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

**相关内容：**[预先算好要展示的内容](/37-scale/#precompute-what-you-show)、[把工作移出请求路径](/37-scale/#move-work-off-the-request-path)。

<span id="basics-36-9-buy-the-resource-that-ran-out"></span>

## 9. 购买耗尽的资源 {#9-buy-the-resource-that-ran-out}

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

**相关内容：**[用数据争取更大的机器](/37-scale/#earn-the-bigger-box)、[迁移之前，先证明问题出在磁盘](/35-providers/#prove-the-disk-is-the-problem-before-you-move)。

<span id="basics-36-10-scale-out-only-for-the-signal-that-asks-for-it"></span>

## 10. 只有相应信号出现时，才横向扩展 {#10-scale-out-only-for-the-signal-that-asks-for-it}

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

**相关内容：**[证据说到头了，纵向扩容才算到头](/37-scale/#youre-done-scaling-up-when-the-evidence-says-so)。

<span id="what-each-lever-typically-buys-and-what-it-costs-to-undo" class="pgbasics-deep-dive-anchor" data-td-print-local></span><span id="basics-36-what-each-lever-typically-buys-and-what-it-costs-to-undo" class="pgbasics-deep-dive-anchor"></span>

> [!DETAILS]- 深入阅读：各项手段通常能带来什么，以及撤销它们的成本
>
> 以下是典型结果；有测量数据的地方，采用本书的测量。你的收益取决于起点离合理状态有多远。
>
> | 手段 | 典型收益 | 投入 | 撤销 |
> |---|---|---|---|
> | 查询和索引 | 语句读页量的缩减倍数为 10 到 1,000 | 几小时 | 删除索引 |
> | 模式 | 页数最多减少约 30%；非 HOT 更新更少 | 一次迁移 | 再做一次迁移 |
> | 往返 | N 条查询变成 1 条；延迟的降低幅度取决于网络距离 | 一次代码改动 | 容易 |
> | 清理和统计信息 | 防止性能衰退；修正坏计划 | 按表设置 | 容易 |
> | 连接池 | 每个请求都建连接时，吞吐量达到原来的 5 到 70 倍 | 一天 | 容易 |
> | 配置 | 消除上限；页面数不变 | 几小时，可能需要重启 | 容易 |
> | 汇总表和缓存 | 聚合工作量不再随数据增长 | 几天，再加新鲜度维护 | 中等 |
> | 更大的实例或存储 | 为耗尽的资源增加余量 | 每月付费 | 缩回原来的规格 |
> | 副本 | 读容量；故障切换 | 几天，再加路由 | 中等 |
> | 分区 | 低成本的保留期管理和清理 | 一个迁移项目 | 困难 |
> | 分片 | 写入和存储容量 | 数月 | 很困难 |
> 
> <span id="tbl-36-1"></span>
> 
> **Table 36-1.** 10. 只有相应信号出现时，才横向扩展

>
> 去哪里寻找表明下一项该做什么的数字：查询和索引看 `pg_stat_statements` 与 `EXPLAIN`；模式看 `pg_stat_user_tables` 与 `pg_column_size`；往返看应用追踪；连接看 `pg_stat_activity` 和连接池统计；配置看日志和 `pg_stat_checkpointer`；汇总表看 `pg_stat_statements` 随时间的变化；硬件看等待采样和 `pg_stat_io`；分区看 `pg_stat_progress_vacuum`；分片看 `pg_stat_wal` 和写入等待。

## 实证：一个应用，八次改动 {#the-proof-one-app-eight-changes}

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

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

> [!NOTE] 页面数是证据，吞吐量是我们机器上的结果
>
> 页面数是确定的：你得到的数字应该只相差几个百分点。吞吐量属于我们的笔记本，而且在性能较高的阶段，实验各次执行的结果会相差两到三倍，所以表中并排列出了两次完整执行的结果。实验只验证差距足够大的变化方向。
>
> 实验还设置了 `synchronous_commit = off`，因为在 Docker Desktop 中，每次提交的刷新都要几十毫秒，会淹没其他所有效果。这样会抬高吞吐量，尤其是写入吞吐量，但它改变的是提交等待多久，不是读取了多少页面（[提交意味着刷新](/08-wal/#commit-means-flush)）。在真实磁盘上启用持久提交后，预计数字会更低，但变化趋势相同。

<span id="the-four-endpoints-and-how-we-measured-them" class="pgbasics-deep-dive-anchor" data-td-print-local></span><span id="basics-36-the-four-endpoints-and-how-we-measured-them" class="pgbasics-deep-dive-anchor"></span>

> [!DETAILS]- 深入阅读：四个端点，以及我们的测量方法
>
> 每个端点都对应一份 `pgbench` 脚本，位于实验的 `workload/playbook` 文件夹中，按权重混合：30% 仪表盘、30% 动态列表、10% 项目页、30% 摄入。
>
> ```sql
> -- 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;
> ```
>
> 每个请求新建连接，就是 `pgbench -C`。每次运行都按相同顺序发送相同请求。“每个事务的页面数”是各端点语句的 shared hit 加 read，除以其事务数，再按混合权重加权。
>
> 每次 `pgbench` 运行都用 4 个客户端。在实验的一次完整执行中，基线只运行一次，总共 120 个事务，因为它很慢；添加索引的步骤运行一次，共 1,000 个事务；后续每个步骤都运行三次，每次 4,000 个事务，吞吐量取三次的中位数。整个实验耗时两分半到四分钟，所以较快步骤的一次运行只有一两秒，这也是吞吐量波动的部分原因。结果表中的每个吞吐量列，都代表实验的一次完整执行。

### 基线：每个事务 91,448 页 {#the-baseline-91448-pages-per-transaction}

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

> [!EXAMPLE]- 动手验证 · lab `35-playbook`
>
> ```sql
> 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);
> ```
>
> [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/35-playbook.sql)

### 先调配置，重要的东西并没有改变 {#tuning-first-changes-nothing-that-matters}

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

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

> [!EXAMPLE]- 动手验证 · lab `35-playbook`
>
> ```sql
> 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);
> ```
>
> [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/35-playbook.sql)

### 按操作指南的顺序，做八次改动 {#eight-changes-in-the-playbooks-order}

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

| 步骤 | 手段 | 每事务页数 | 事务/秒，实验 1 | 事务/秒，实验 2 |
|---|---|---|---|---|
| 0 | 基线 | 91,448 | 13 | 13 |
| | 先调配置（未保留） | 91,510 | 8 | 17 |
| 1 | 索引 | 752 | 299 | 329 |
| 2 | 直接对列做日期范围筛选 | 214 | 1,024 | 1,197 |
| 3 | 键集分页 | 143 | 1,128 | 1,005 |
| 4 | 填充因子 90 | 143 | 1,185 | 1,260 |
| 5 | 一条查询，代替 21 条 | 144 | 1,113 | 1,273 |
| 6 | 连接池 | 81 | 10,624 | 9,076 |
| 7 | 配置 | 82 | 4,135 | 11,297 |
| 8 | 每日汇总表 | 38 | 13,978 | 17,028 |

<span id="tbl-36-2"></span>

**Table 36-2.** 按操作指南的顺序，做八次改动


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

> [!EXAMPLE]- 动手验证 · lab `35-playbook`
>
> ```sql
> 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);
> ```
>
> [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/35-playbook.sql)

<span id="steps-1-to-3-three-indexes-one-cast-and-keyset" class="pgbasics-deep-dive-anchor" data-td-print-local></span><span id="basics-36-steps-1-to-3-three-indexes-one-cast-and-keyset" class="pgbasics-deep-dive-anchor"></span>

> [!DETAILS]- 深入阅读：步骤 1 到 3，三个索引、一次类型转换和键集分页
>
> **步骤 1，索引。**每个索引都从需要它的查询出发设计，等值列在前，排序列在后：
>
> ```sql
> 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);
> ```
>
> 每个事务的页面数从 91,448 降至 752，缩减为约 1/120。项目页现在读取约 108 页：每次项目查找四页（三个索引页、一个堆页），再加项目列表。仪表盘每次调用仍读取约 1,960 页：`created_at::date` 给列套上了函数，因此索引找出了该账户一整年约 2,000 行数据，却无法将范围缩小到 30 天。
>
> **步骤 2，裸列。**把同一个筛选条件写成 `and created_at >= '2026-09-06'`（实验会话使用 UTC，因此结果相同）。仪表盘降到约 172 页，吞吐量变为原来的三倍多。
>
> **步骤 3，键集分页。**平均偏移量为 240 时，`OFFSET` 为了返回每页 20 行，会读取约 260 个页面，且随翻页深度增长。从用户看到的最后一行开始，同一个索引只需约 22 个页面就能提供任意一页结果：
>
> ```sql
> 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;
> ```
>
> 每个事务的页面数从 214 降到 143。吞吐量几乎没变，步骤 6 会说明原因。
>
> > [!EXAMPLE]- 动手验证 · lab `35-playbook`
> >
> > ```sql
> > 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);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/35-playbook.sql)

<span id="steps-4-to-8-hot-one-query-a-pool-settings-a-rollup" class="pgbasics-deep-dive-anchor" data-td-print-local></span><span id="basics-36-steps-4-to-8-hot-one-query-a-pool-settings-a-rollup" class="pgbasics-deep-dive-anchor"></span>

> [!DETAILS]- 深入阅读：步骤 4 到 8，HOT、一条查询、连接池、配置和汇总表
>
> **步骤 4，填充因子。**更新计数器不会修改索引列，因此是 HOT-safe 的，即满足 HOT 对不改索引列的要求。但 `projects` 加载时按填充因子 100 填满了页面，所以大多数页上的第一次更新找不到空间，只能进行非 HOT 更新，为每个索引写入新条目。步骤 0 到 3 中，有几百次这样的更新。
>
> ```sql
> alter table projects set (fillfactor = 90);
> vacuum full projects;   -- rewrites at the new fillfactor; pg_repack in production
> ```
>
> 从那以后，每次计数器更新都是 HOT。在这么短的运行中，每个事务的页面数没有变化；收益在于索引不再增长、WAL 更少、清理工作更少（[让更新保持 HOT](/07-hot/#basics-chapter-7)）。
>
> **步骤 5，一条查询。**把项目页改成单条 `left join lateral (... order by created_at desc limit 1)`。页数不变，约为 108，因为数据库仍要执行同样的 20 次索引查找；省掉的是 20 次往返、解析和规划。在同一主机上运行 `pgbench` 时，两次实验的平均延迟都下降了（3.3 ms 降到 1.5 ms，3.0 ms 降到 2.4 ms）。如果跨真实网络、每次往返半毫秒，每次页面访问就能省下 10 ms 或更多。
>
> **步骤 6，连接池。**步骤 2 到 5 的吞吐量一直在 1,000 到 1,200 左右，而页面数已经减少了三分之一。瓶颈是建立连接，在我们的机器上约需 2 ms。保持四个连接打开后，吞吐量达到 9,000 到 10,600。页面数也从 144 降至 81，因为摄入从约 239 页降到了 36 页：新会话要读取系统目录页，为插入的外键检查做准备，而连接池中的会话只需做一次。
>
> **步骤 7，配置。**和“先调配置”时相同的五项设置。页数为 82，之前是 81。这里没有哪项工作排序多到需要溢出、扫描多到需要并行，或代价高到需要 JIT。这不是在反对[真正重要的配置](/30-configuration/#basics-chapter-30)，那些设置能预防一个持续几分钟的实验无法体现的问题。这里反对的是用调参代替修问题。
>
> **步骤 8，汇总表。**一张包含 `(account_id, day, events, failed_deploys)`、主键为 `(account_id, day)` 的表，由增量刷新逐日写入和构建，只需约 34 页就能服务仪表盘。刷新任务不在混合负载中；它就是[用插入或更新保持新鲜](/37-scale/#keep-it-fresh-with-an-upsert)中的定时 upsert。
>
> 每个端点每次调用，页面花在了哪里：
>
> | 步骤 | 仪表盘 | 动态列表 | 项目页 | 摄入 |
> |---|---|---|---|---|
> | 0 基线 | 35,182 | 35,208 | 702,683 | 207 |
> | 1 索引 | 1,963 | 262 | 108 | 247 |
> | 2 裸列范围筛选 | 172 | 261 | 108 | 245 |
> | 3 键集分页 | 175 | 23 | 109 | 242 |
> | 4 填充因子 90 | 178 | 23 | 108 | 239 |
> | 5 一条查询 | 182 | 23 | 108 | 239 |
> | 6 连接池 | 179 | 23 | 102 | 36 |
> | 7 配置 | 182 | 23 | 102 | 36 |
> | 8 汇总表 | 34 | 23 | 102 | 36 |
> 
> <span id="tbl-36-3"></span>
> 
> **Table 36-3.** 按操作指南的顺序，做八次改动

>
> > [!EXAMPLE]- 动手验证 · lab `35-playbook`
> >
> > ```sql
> > 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);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/35-playbook.sql)

### 表格告诉了我们什么 {#what-the-table-says}

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

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

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

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

## 横向扩展之前 {#before-you-scale-out}

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

## 本章要点 {#the-short-version}

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

## 延伸阅读 {#further-reading}

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