PostgreSQL 基础

Chase Pierce
第一版 · PostgreSQL 18 · 2026-10-05
出版者：Flagon

开始阅读 · 打印整本书

PostgreSQL 性能几乎都归结为一个数字：8,192。这是页的大小，也是 Postgres 读取、写入和缓存的单位。快速查询只访问少量页；慢查询访问的页很多，或者在等待同样要访问很多页的其他操作。一旦看清这些页的访问情况，大多数疑问都会消失。

你可能已经遇到过这些症状：刚上线时响应迅速、如今却慢吞吞的仪表板；看起来“什么也没做”却仍然让网站停摆的迁移；增长速度始终超过其中数据的表；以及总有人提出换一个更大的实例就能解决一切问题。这本书提供了另一条路。

数一数页，然后用数据证明该换更大的机器了

本书从磁盘上的字节开始向上讲：行是怎样存储和变更的，索引和规划器怎样找到它们，怎样在不中断服务的情况下变更模式，以及怎样在生产环境中运行 Postgres。全书会培养两个习惯。在添加索引、调高参数或责怪 ORM 之前，先问问这项工作会访问多少页。在购买更大的数据库或第二个数据库之前，先用完便宜的修复手段，再用数字证明它们已经不够用。

这也包括知道 Postgres 的边界。在同样的两百万条事件数据上，两个列存储系统只用不到五分之一的字节数保存数据，查询速度约为调优后 Postgres 的十倍。本书会展示一个 Postgres 能走多远，以及什么信号说明该考虑别的系统了。

本书写给每天使用 Postgres 的应用开发者，很多人还会通过 ORM 使用它。你只需要掌握基础 SQL，不需要了解内部机制。

每个重要结论都配有检查

每个查询都在 PostgreSQL 18 上运行，使用的是一分钟内就能加载的示例模式；每个重要结论都附有可以在你自己的机器上重新运行的实验检查。每一章都是一篇可以单独阅读的短文；想深入时，再打开包含内部机制和完整测量结果的深入阅读。

免费，并以开放方式写作

你可以在这里逐章阅读，也可以把整本书下载为 PDF。和我们发布的其他内容一样，本书通过 git 进行版本管理；Postgres 发生变化时，书也会随之变化。如果发现错误或难以理解的地方，那就是一个 bug：提交问题或发送修复。

目录

图

1. 图 2-1 — 一台服务器、多个数据库，每个连接对应一个进程。每个连接都有自己的后端进程，在一个数据库中工作；数据库内部由模式容纳表。后端进程之外，服务器还运行自己的进程，角色和设置属于整个服务器。
2. 图 3-1 — 一页可能位于三种位置之一。shared_buffers 中的命中是一次内存查找；未命中时，EXPLAIN 称之为“read”，如果内核已经缓存该页，就需要一次系统调用和一次复制；如果没有缓存，就需要访问设备。Postgres 无法区分后面这两种情况。
3. 图 4-1 — 行指针从前端开始，元组从后端开始。两者向彼此靠近，碰到一起时页就满了。
4. 图 5-1 — 找到一个页只需要一次哈希查找。标签会落入 128 个分别加锁的分区之一。命中后钉住缓冲区即可完成；未命中则必须驱逐一个受害者，必要时先把它写出，再读入目标页。
5. 图 5-2 — 指针经过每个缓冲区时都会扣除一个点。它会跳过被钉住的缓冲区，并驱逐找到的第一个计数为零的缓冲区。一个不断读取的页在指针转回来前早已恢复到 5，因此永远不会走到这一步。
6. 图 6-1 — 更新就是插入并加上标记。旧版本留在槽位 1，被更新事务的 XID 标记，并带有指向 (0,4) 的 ctid；新版本是槽位 4 中的一份完整副本。现在四个元组承载着三行。
7. 图 7-1 — HOT 把新版本留在索引已经能找到的位置。冷更新把新版本放到另一页，并在每个索引中增加一个条目。HOT 更新把新版本写到同一页，通过 t_ctid 从旧版本串起来，不改变索引；页内剪枝后，lp1 会变成指向新版本的重定向。
8. 图 8-1 — 提交等待的是日志，而不是数据页。更新先让 shared_buffers 中的页变脏，然后追加一条 WAL 记录。COMMIT 只等待这条记录刷新到连续的 WAL；检查点进程或后台写进程稍后再把页写入数据文件。
9. 图 8-2 — 恢复从重做点开始。检查点从其重做点开始，写出脏页，并在完成后记录检查点记录。重做点之前的所有内容都已经在数据文件中，所以崩溃后从那里开始重放。
10. 图 8-3 — 每次检查点都会重置镜像状态。检查点后的第一次页变化会携带该页完整的 8 KB 镜像；之后对该页的变化都是小记录。下一次检查点之后，第一次变化又要付出同样的代价。
11. 图 11-1 — InnoDB 按键查找行，Postgres 按地址查找行。在 InnoDB 中，表就是主键 B-树，因此二级索引条目保存主键，通过它找到的每一行都要再次沿着这棵树向下查找。在 Postgres 中，每个索引，包括主键索引在内，都直接指向堆中的一个页。
12. 图 13-1 — 递增键追加；随机键散落。新的标识列或 UUIDv7 键都落在最右侧叶子上，叶子会留在内存中。UUIDv4 键会落在任意叶子上，满叶子会按 50/50 拆分，页最终大约有 70% 满，而追加写入时是 90%。
13. 图 17-1 — 查找每一层读取一个页，然后读取堆。events 的主键有 1 个根页、19 个内部页和 5,465 个叶页，堆共有 35,173 个页，因此查找一行会读取树中的三个页和堆中的一个页。
14. 图 21-1 — 找到节点，然后修复估算或工作量。如果估算行数与实际行数相差 10 倍或更多，就修复统计信息或误导统计信息的查询；如果行数正确，就修复索引、查询形状或代价。只有最后才考虑 enable_* 和提示。
15. 图 22-1 — OFFSET 从头计数；键集分页从书签开始。OFFSET 1000000 会读取 1,000,020 个索引项和 21,422 个页来显示 20 行。键集查询会跳到上一页结束的位置，在 5 个页中读取接下来的 20 行；每一页都相同。
16. 图 25-1 — 第二次写入会悄无声息地获胜。两个事务都读到 100，tx2 等待 tx1 的行锁，然后写入根据那次过时的读取计算出的值。余额最终为 80，tx1 的消费没有报错就丢失了。
17. 图 25-2 — 一个等待中的锁会让每个查询停下来。迁移在等待空闲的 reporting 事务，而它之后的每个查询，包括普通读取，也都排在迁移后面等待。reporting 提交后，迁移在一毫秒内运行完，队列随之排空。
18. 图 32-1 — 恢复就是基础备份加上重放。恢复基础备份，然后向前重放归档的 WAL，直到 recovery_target_time。重放在 14:02 的错误之前停止，服务器随后以该时点的状态开放访问。
19. 图 38-1 — 副本始终重放主库的 WAL。walsender 把 WAL 流传给备库的 walreceiver，startup 进程将其重放到页面中，备库再把自己的 LSN 位置报告给 pg_stat_replication。它接受读取，以 25006 拒绝写入。
20. 图 39-1 — 逐行删除一个月是做一番工作；删除一个月分区是移除文件。逐行删除留下了 164,383 个死元组，产生 32 MB WAL，后续清理又产生了 12 MB。分离并删除该月分区会移除它的文件，产生的 WAL 不到 150 KB。
21. 图 40-1 — 分片键决定由谁干活。指定 account_id 的查询发往存放该账户的唯一分片，该账户的行集中在那里。没有指定它的查询发往所有分片，再合并结果。

表

1. 表 1-1 — 先理解运行机制，再跳到痛点所在
2. 表 1-2 — 一套普通的 SaaS 模式贯穿每一章
3. 表 2-1 — 几条 psql 命令覆盖本书所需
4. 表 3-1 — 内存很便宜，其他一切都很贵
5. 表 3-2 — 往返比查询更贵
6. 表 4-1 — 一行的成本高于它的列
7. 表 5-1 — 你的查询不该负责写出脏页
8. 表 5-2 — 大型操作会使用环
9. 表 7-1 — 冷更新要为每个索引付费
10. 表 7-2 — 索引提到的每一列都算作已索引
11. 表 7-3 — 给页留下空间，否则每次更新都会移动
12. 表 7-4 — 给页留下空间，否则每次更新都会移动
13. 表 7-5 — 长事务会关闭 HOT
14. 表 7-6 — 自底向上的索引删除是安全网
15. 表 8-1 — wal_compression 花小代价就能见效
16. 表 8-2 — 提交意味着刷新
17. 表 9-1 — 四项承诺，四种机制
18. 表 9-2 — 只有可串行化能排除所有异常
19. 表 10-1 — 对大表来说，默认值不对
20. 表 10-2 — 回卷会在任何内容损坏前停止写入
21. 表 11-1 — 行和旧版本存放在哪里决定了一切
22. 表 11-2 — 在 InnoDB 中，二级索引查找要下探两次
23. 表 11-3 — 整数除法会截断，NULL 排在最后
24. 表 11-4 — 大多数 MySQL 惯用法都有直接的 Postgres 对应物
25. 表 12-1 — 整数比你想象中更快耗尽
26. 表 12-2 — 选择顺序永不变化的排序规则提供者
27. 表 13-1 — 随机键写入的 WAL 几乎是四倍
28. 表 13-2 — 随机键写入的 WAL 几乎是四倍
29. 表 13-3 — 每种 ID 格式都在顺序和泄露内容之间做权衡
30. 表 14-1 — 每个事实只存一处
31. 表 14-2 — 每次插入都要为每项检查付费
32. 表 14-3 — 历史记录也是你要设计的表
33. 表 15-1 — 每种模型各有一种形状，专门为它加速
34. 表 15-2 — 实体-属性-值是个陷阱
35. 表 15-3 — 闭包表存储每一对祖先关系
36. 表 16-1 — Contrib 是最安全的一层，不过其中有些已经过时
37. 表 16-2 — 会崩溃的扩展会断开所有连接，因此要审查它
38. 表 17-1 — 每个索引都在向写入收税
39. 表 18-1 — B-树之外
40. 表 18-2 — 对包含查询，jsonb_path_ops 胜过默认类
41. 表 18-3 — GIN 过滤三字符组；GiST 按相似度排序
42. 表 18-4 — 大小只有千分之一，页数却几乎相同
43. 表 18-5 — 操作符决定索引类型
44. 表 20-1 — 每个代价都相对于一次顺序页读取
45. 表 20-2 — 扩展统计信息联合测量多列
46. 表 21-1 — 5% 规则在两个方向上都不对
47. 表 21-2 — 连接顺序是一场搜索，而且搜索会变得昂贵
48. 表 22-1 — OFFSET 会读取它跳过的每一行
49. 表 23-1 — 存储 tsvector；不要索引表达式
50. 表 23-2 — 搜索扩展把 BM25 放进索引
51. 表 23-3 — ef_search 用延迟换召回率
52. 表 24-1 — 主库是你最昂贵的 CPU
53. 表 24-2 — 行触发器每行大约花费一微秒
54. 表 25-1 — 行锁存在行中，而不在 pg_locks 中
55. 表 26-1 — 选择与表流量匹配的索引构建方式
56. 表 26-2 — 选择与表流量匹配的索引构建方式
57. 表 26-3 — 单独运行并发构建，不设超时，并检查索引是否有效
58. 表 26-4 — 扩宽类型没有代价；大多数其他类型变更都会重写
59. 表 28-1 — 事务池模式会破坏会话状态
60. 表 29-1 — 只问一个问题
61. 表 30-1 — 上下文决定重载还是重启
62. 表 30-2 — 自动清理的默认值适合小表和慢磁盘
63. 表 30-3 — 低开销日志能回答大多数凌晨 3 点的问题
64. 表 32-1 — 先写下 RPO 和 RTO
65. 表 32-2 — pg_dump 不包含角色；单独转储它们
66. 表 32-3 — pg_upgrade --link 快速且只能单向进行
67. 表 33-1 — 应用永远不拥有自己的数据表
68. 表 34-1 — 无论谁运行这台机器，有些工作仍然是你的
69. 表 34-2 — 无论谁运行这台机器，有些工作仍然是你的
70. 表 34-3 — 服务商会在 CREATE EXTENSION 上再加一道门
71. 表 34-4 — I/O 被节流时，会表现为 IOPS 持平而延迟上升
72. 表 34-5 — 故障切换依赖 DNS，你的连接池必须注意到
73. 表 35-1 — 了解架构形态，就了解了取舍
74. 表 35-2 — 超级用户权限和保留期限可以排除一批服务
75. 表 36-1 — 10. 只有相应信号出现时，才横向扩展
76. 表 36-2 — 按操作指南的顺序，做八次改动
77. 表 36-3 — 按操作指南的顺序，做八次改动
78. 表 37-1 — 买任何东西之前，先测量
79. 表 37-2 — 预先算好要展示的内容
80. 表 38-1 — 同步提交要付出一次往返
81. 表 38-2 — 故障切换用流复制，具体任务用逻辑复制
82. 表 39-1 — 范围分区适合时间，列表和哈希各有专用场景
83. 表 39-2 — 处理过期数据：先分离，再删除
84. 表 39-3 — 每个无法剪枝的分区都要花规划时间
85. 表 39-4 — 每个无法剪枝的分区都要花规划时间
86. 表 40-1 — 每种方案都把难题移到别处
87. 表 40-2 — 每种方案都把难题移到别处
88. 表 41-1 — 列式存储只占十分之一的字节数
89. 表 41-2 — 每条查询，PostgreSQL 都读取了所有页
90. 表 41-3 — 列式存储快约十倍，单核上仍然更快
91. 表 41-4 — 列式存储快约十倍，单核上仍然更快
92. 表 41-5 — 汇总表和列式扩展能大幅缩小差距
93. 表 41-6 — 用到之前，先认清信号

部分

关于本版

© 2026 Flagon, Inc. All rights reserved. 本简体中文网页译本译自第一版 Eight Kilobytes: PostgreSQL from the page up，原版出版于 2026-10-05，示例运行在 PostgreSQL 18 上。原书正文和图形可在 Flagon 在线阅读。本书许可证允许在线阅读，并将再发布权保留给 Flagon, Inc.；本项目明确记录所依据的来源和版本。

实验套件及其采用 MIT 许可证的源文件仍可从原始的 Eight Kilobytes lab 获取。

1 开始之前

下面是针对同一张表、在同一台服务器上、相隔几秒执行的两个查询：

select * from events where id = 777777;
select count(*) from events where project_id = 4242;

第一个只访问了 4 个页，第二个却访问了 35,173 个页。

两个查询几乎都不返回数据，看起来也都很简单，ORM 生成它们中的任何一个都不会有意见。一个让数据库只用 3% CPU 空闲运行，另一个却会让某人在凌晨 2 点被叫起来处理告警。

这就是本书存在的原因。

数一数页面

大多数 PostgreSQL 建议都来自经验之谈。加一个索引。调高 shared_buffers。运行清理。每条建议都有一部分时间是对的；但如果你没有服务器的模型，就无法判断现在是否正好属于那部分时间。

这个模型比你想象的更小。Postgres 把所有东西（表、索引以及它们旁边的簿记信息）都存放在 8 KB 的页中，并以整页为单位读取、写入和缓存。慢查询几乎总是在做两件事之一：访问了远超需要的页，或者进行了远超需要的往返。

因此，本书要培养的第一个习惯只有一句话：数一数页面。

• 设计表时，问问一页能容纳多少行。
• 添加索引时，问问一次查找前后分别会访问多少个页。
• 阅读执行计划时，先看 Buffers: 行。它统计的是页数。
• 当表增长速度超过数据本身时，问问那些页里到底放着什么不属于你的东西。

你不需要整天思考字节数。你需要对 8 KB 能装下什么有一个直觉，因为 Postgres 几乎所有性能特征都由此而来。书名也因此得名。

先用数据证明，该换更大的机器了

第二个习惯与钱有关。数据库变慢时，最容易买的是一台更大的机器，而且一开始确实有效。然后数据追上来，你每个月花更多钱，却还是同一个问题。

索引每个月不花钱。重写查询只需要一个下午。连接池代理、清理设置，或者更小的行，只需要读一点材料。所以，本书有一个比其他观点都更坚持的看法：先用完便宜的修复手段，再购买硬件；真的要升级时，用数字证明确实需要升级（先用数据证明，该换更大的机器了）。每一章归根结底都是一个便宜的修复手段，以及证明它有效的证据。

先从一个数据库开始，凭证据离开

不断成长的产品会收集各种数据库：缓存、文档存储、队列、搜索引擎、向量存储。每一种都带来自己的备份、升级、故障模式和告警值班，而且它的数据还必须与其他所有系统保持同步。

Postgres 可以在一台服务器上，与其余数据一起处在同一个事务中，覆盖其中大多数需求。文档放进 jsonb。队列就是一张表，再配合 for update skip locked。全文检索和向量数据与它们所描述的行放在一起。时间序列适合 BRIN 和按月分区，地图则交给 PostGIS。这是一种能满足大多数需求的方案，而“大多数”能覆盖的范围比你猜的更远（你还不需要另一个数据库）。

大多数不等于全部，本书也会说明边界在哪里。在同样的两百万条事件数据上，两个列存储系统只用不到五分之一的字节数保存数据，查询速度约为调优后 Postgres 的十倍；即使只用一个核心，仍然至少快两倍。大规模搜索相关性、流、全局写入以及超大型向量集，都有各自的边界（Postgres 不擅长什么）。

所以，我们让每个产品都从一个 Postgres 开始；只有当我们能说清某项特性，并用数字证明确实需要它时，才添加第二个系统，同时仍然让 Postgres 作为权威数据源。这又回到了更大的机器：应用到架构上，就是从扎实的基础开始，凭证据离开。

每个结论都配有验证

你不该只因为一本数据库书这么说（包括这一本）就接受任何结论。因此，这里每个重要结论都附有证据。

这里的每个查询都在一台真实的 PostgreSQL 18 服务器上运行，使用的是一分钟内就能加载的示例模式。执行计划是真实的执行计划，大小是真实的大小；当某一章说索引有帮助时，还会展示添加索引前后读取的页数。重要结论旁边会放置动手验证框。每个框都包含该章实验脚本中的精确检查，也就是一个声明不成立就会大声失败的 lab.prove(...)，这样你可以重新运行证据，而不是相信我们。

这也是本书保持诚实的方式。Postgres 发生变化时，检查会失败，章节就会被修正。

你会 SQL；这里讲的是它的底层机制

你每天都在写应用代码、使用 Postgres，可能通过 ORM，也可能手写 SQL。你知道 select、join、group by、insert 和 update。也许最近因为总得有人负责，你成了“数据库那个人”。

你缺少的是底层机制的模型：为什么一次 update 会让表变大，为什么上个月还很快的查询现在变慢了，为什么规划器不使用你刚建的索引。没有这个模型，调优就只能靠猜。每个内部概念都会在第一次出现时解释。不需要懂 C、不需要读源代码，也不需要有生产经验。

以两种深度阅读

每一章其实是两本书合在一起。简明路线是一篇短文：它的论点、证明论点的一条证据，以及你该怎么做。每章只读这一部分，你就能获得整本书的内容。深入阅读包含内部机制、完整测量结果和边界情况，每一段都紧跟在它所展开的段落之后。

在网站上，章节顶部的简明 / 深入阅读开关会为整本书设置阅读深度；在简明模式下，每个深入阅读块会折叠成一行，你可以单独打开。在纸质版上，每个深入阅读都会打印出来，并单独排版，方便跳过；每个动手验证框也会打印出它所验证的结论，以及保存检查的实验文件。

我为什么写这本书

我是 Chase Pierce。我创办了 Flagon，希望公开地构建软件，也公开地经营一家公司。

我没有什么关于自己如何走到今天的传奇故事。我因为一些朴素的理由选择了 Postgres，然后一直坚持选择它。相较于 MySQL，是因为它能做更多事情：支持事务性的模式变更，拥有更丰富的类型，以及能让数据库成长为你所需形态的扩展系统。相较于 Oracle 和 SQL Server，是因为它开源、可以在任何地方运行，而且没有按核心数量计费的许可证。这些理由在之后的每个项目中都经受住了考验。

真正经不起考验的是围绕它的建议。我想要的那本书并不存在于一个地方。内部机制类书籍默认你想参与服务器本身的开发。调优指南把设置交给你，却不给出解释这些设置的模型。博客文章只对某个人的负载、某个人的版本、某一年成立。我想把模型、证据和观点放在一起，写给那个刚刚接手数据库的人。所以我写了这本书，并让每个结论都带有一个检查，因为无法重新运行的结论，只是又一条经验之谈。

本书其余部分会说“我们”。这是 Flagon 的声音，也是在同一个终端前看着同一份执行计划的你和我。我们持有什么观点，就直说，并展示背后的数字。如果你发现错误，那就是一个 bug：提交问题或发送修复。

先理解运行机制，再跳到痛点所在

首先是入门篇先认识这些基本概念：快速认识 SQL 和服务器词汇，包括表、查询、更新、连接、索引、数据库、模式、角色和目录。如果 search_path 和 pg_class 对你来说已经是老朋友，可以跳过这一章。运行机制是基础。如果其他内容都不读，至少读一切都从页说起和更新就是插入；后面几乎每一章都会依赖它们。然后跳到你正在遭遇的问题。慢查询正在毁掉你的一周？从读执行计划，别只看耗时开始。你从 MySQL 转来？Postgres 不是 MySQL会指出哪些习惯不能直接迁移。

每章最后都有“本章要点”和“延伸阅读”。

每个示例都运行在 PostgreSQL 18 上

本书以PostgreSQL 18为目标。几乎所有示例都运行在 18.6 上；少数需要第三方镜像的章节会固定另一个小版本，并明确说明（最后再考虑分片中的 Citus 使用 18.4，Postgres 不擅长什么中的 pg_duckdb 使用 18.1）。其中大多数内容适用于从 13 开始的任何版本；较新的特性会标注引入它的版本，例如 MERGE（PostgreSQL 15+）。页面布局、MVCC、清理、规划器和预写式日志在细节上发生过变化，但形态没有改变。

psql 和 EXPLAIN 的输出紧跟在生成它的查询后面，单独放在代码块中；长输出会用 ... 截短。其中有些内容在你的机器上会不同，这是正常的：

• 事务 ID、OID 和文件名由服务器在运行时分配。你的值会不同，但它们之间的关系不会变。
• 耗时取决于硬件和负载。我们的实验在笔记本电脑上的 Docker 中运行，旁边还有其他负载；有几个写入量很大的实验会打开 synchronous_commit = off 以更快完成（实验会明确说明），所以把每一毫秒都视为粗略值。
• 缓冲区数量和行数是证据。 对于给定的数据集和执行计划，它们每次都会相同。完整查询的计数可能会比表本身多访问几十个页，因为还要读取目录和其他小对象：对包含 35,173 个页的 events 堆做一次全表扫描，报告的数量约为 35,249。
• 大小是 pg_size_pretty 按二进制口径报告的数值：288,137,216 字节的 events 堆是 275 MB，而不是 288。

提示框会标出你绝对不能错过的内容。每一条警告都描述了一种可能让生产数据库瘫痪的方式。

运行你自己的 PostgreSQL 18

你需要 Docker 和 bash：Linux 或 macOS 终端都可以，Windows 上则可以使用 Git Bash 或 WSL。实验套件是一个小型 Compose 项目：里面有带示例数据的 PostgreSQL 18，以及每章一个将章节结论转为检查的脚本。下载后运行某一章：

curl -L https://www.flagon.io/books/eight-kilobytes/lab.tar.gz | tar xz
cd eight-kilobytes-lab
./lab 03-storage

章节编号或名称都可以使用（./lab 3、./lab storage），单独运行 ./lab 则会列出全部章节。第一次运行会启动服务器并创建示例数据库，大约需要一分钟。每次运行都会获得一份全新的数据副本，所有成立的结论都会打印 PROVED:，遇到第一个不成立的结论就会停止。实验页面会解释其余内容；运行 ./lab all 会执行所有章节，并列出失败的章节。

如果你更喜欢图形化客户端，服务器只监听本机的 127.0.0.1:5418。有几章（复制、分片、MySQL 对比）需要多台服务器；对于这些章节，./lab 会启动额外容器，并在结束后将其移除。

套件中的 postgres 用户是超级用户。这一点很重要：本书最有用的一些工具（尤其是读取页原始字节的 pageinspect）默认只有超级用户才能使用，而托管服务通常不会给你超级用户权限。完成后，docker compose down -v 会把所有内容彻底删除。

一套普通的 SaaS 模式贯穿每一章

每章都使用同一套小型 SaaS 模式：账户、账户中的用户、它们拥有的项目，以及一张不断增长的大型事件表（部署、构建、评论、告警、登录）。它足够普通，也足够大，能让规划器做出真实决策，让错误变成可以测量的代价：

每张表都有一个 bigint 类型的 identity id，以及一个 created_at。数据使用固定的随机种子生成，时间从一个固定时刻，即 2026 年 10 月 6 日，向前回溯，因此你的执行计划、计数和日期都会与我们的相同。

有两点是故意这样设计的，所以暂时不要“修复”它们：

• events 没有任何二级索引，只有主键。索引相关章节会逐一添加索引，让你准确看到每个索引带来了什么。
• 只有一个外键列有索引，而且只是碰巧。 users.account_id 是 unique (account_id, email) 约束所建索引的前导列；其他引用列都没有索引。Postgres 不会替你为引用列建立索引，而这正是现实模式中最常见的错误之一。有目的地选择约束会展示它的代价。

create table events (
  id          bigint generated always as identity primary key,
  account_id  bigint not null references accounts (id),
  project_id  bigint not null references projects (id),
  user_id     bigint references users (id),
  kind        text not null,
  payload     jsonb not null default '{}',
  created_at  timestamptz not null default now()
);

select setseed(0.42);
-- ... accounts, users and projects first ...

insert into events (account_id, project_id, user_id, kind, payload, created_at)
select p.account_id, p.id, p.owner_id, r.kind,
       jsonb_build_object('status', r.status,
                          'duration_ms', r.duration_ms,
                          'region', r.region),
       r.created_at
from (
  select g,
         1 + floor(random()*20000)::bigint as project_id,
         (array['deploy','deploy','build','build','build',
                'comment','alert','login'])[1 + floor(random()*8)::int] as kind,
         (array['ok','ok','ok','failed'])[1 + floor(random()*4)::int] as status,
         floor(random()*5000)::int as duration_ms,
         (array['iad','sfo','ams','fra','syd'])
           [1 + floor(random()*5)::int] as region,
         timestamptz '2026-10-06' - interval '365 days'
           + (g * interval '365 days' / 2000000) as created_at
  from generate_series(1, 2000000) g
) r
join projects p on p.id = r.project_id
order by r.g;

vacuum analyze;

套件会把它加载到名为 book 的数据库中（约 345 MB），并将其标记为模板。复制模板比重新运行种子便宜得多，所以请在副本中探索：

docker compose exec postgres psql -c "create database scratch template book"
docker compose exec postgres psql -d scratch

在这里搞坏东西，别在生产环境里搞

有些示例是故意具有破坏性的：它们会更新数百万行、长时间保持事务打开，或者删除半张表来观察清理工作。请在可丢弃的服务器上运行它们，永远不要在任何人依赖的数据库上运行。对于面向生产环境的技术（迁移模式、监控查询、设置），正文会说明这一点，以及它会加什么锁、付出什么代价。

在简要认识这些术语之后，第一站是硬件，因为一个页在你请求它时所处的位置不同，访问它所需的时间也可能有天壤之别。

本章要点

• 几乎每个 Postgres 性能问题都可以归结为访问了多少页、进行了多少次往返。数一数页面。
• 先用完便宜的修复手段，再购买硬件；真的要升级时，用数字证明需要升级。先用数据证明，该换更大的机器了。
• 从一个 Postgres 开始。只有当你能说出某项特性，并用数字证明确实需要它时，才添加第二个系统。
• 先看执行计划的 Buffers: 行，再看耗时。缓冲区数量和行数可以复现，毫秒数不行。
• 每个重要结论都有一个动手验证检查。重新运行它，不要只相信我们。
• 只读简明路线来理解论点；想看内部机制或完整测量结果时，再打开深入阅读。
• 获取实验套件，在名为 book 的模板中运行带有示例数据、可随时丢弃的 PostgreSQL 18。
• 在套件目录中运行 ./lab <chapter>，重新执行任意章节的证据；动手验证框会展示具体检查。
• 在副本中工作（create database scratch template book），弄乱后将其删除。

延伸阅读

• PostgreSQL 18 documentation，PGDG。整本书都依赖的参考资料；先学会在里面找到所需内容。
• PostgreSQL 18 release notes，PGDG。本书所有“（PostgreSQL 18+）”标签的来源。
• Versioning policy，PGDG。五年的支持窗口，以及你的大版本还剩多长时间支持。
• psql reference，PGDG。每个示例背后的客户端；花一个小时读它的反斜杠命令很值得。
• Set returning functions，PGDG。了解 generate_series 如何生成种子，也了解如何生成自己的测试数据。

2 先认识这些基本概念

本书会讨论页、执行计划和锁。这些讨论假设你了解一些 SQL 和少数几个 Postgres 术语。如果你曾经对 Postgres 写过 select、insert 和 update，也知道什么是模式，可以扫一眼下面的标题，然后继续往后读；你不会漏掉什么。如果你还不了解，这一章就是你的速成课：后面每一章都至少需要这些知识。

先提醒你接下来会遇到什么：本书其余部分都会假设你掌握这里的内容，并且很快深入到磁盘上的字节，因为“为什么这么慢？”的答案就在那里。本章的每个结论都在实验服务器上运行验证，和本书其他章节一样，所以连基础知识也有证据。

表由共享相同列的行组成

Postgres 把你的数据放在表中。表有命名且带类型的列，每一行为每一列保存一个值，也可以保存 NULL，它表示“没有值”（不是零，也不是空字符串）。下面是本书最大的表 events：

create table events (
  id          bigint generated always as identity primary key,
  account_id  bigint not null references accounts (id),
  project_id  bigint not null references projects (id),
  user_id     bigint references users (id),
  kind        text not null,
  payload     jsonb not null default '{}',
  created_at  timestamptz not null default now()
);

逐行读一遍，你就认识了模式定义中的大多数内容。bigint 是大整数，text 是长度不限的字符串，jsonb 是 JSON 文档，timestamptz 是一个时间点。not null 禁止 NULL，default 则在你没有提供值时填入默认值。id 是主键：没有其他行与它共享的值，Postgres 会替你为它编号，因此可以通过它找到任意一行。每一条 references 定义都是一个外键：account_id 必须指向 accounts 中已经存在的一行。

events 保存两百万行，每一行都有同样的七列。表是形状，行是数据。

select lab.prove('events has 2,000,000 rows, every one with the same seven columns',
  (select count(*) from events) = 2000000
  and (select count(*) from information_schema.columns
       where table_schema = 'public' and table_name = 'events') = 7);

SELECT 用来提问，而且什么也不改变

用 select 读取数据。写出你需要的列，写明它们来自 from 后面的表，再用只保留目标行的 where 子句；order by 会排序，limit 会提前停止：

select id, kind, created_at
from events
where project_id = 4242
order by created_at desc
limit 3;

再掌握两个词，就能覆盖大多数报表。count(*) 统计行数，group by 按值分别统计：

select kind, count(*)
from events
group by kind;

select 从不改变任何东西。它甚至不会取得事务 ID（XID），也就是 Postgres 为每次变更分配的编号。但这不代表它没有代价。整本书都在讨论：一个 select 为了回答你的问题，必须读取多少页。

select lab.prove('a select takes no transaction id: reading writes nothing',
  pg_current_xact_id_if_assigned() is null);

INSERT 添加一行，在 Postgres 中 UPDATE 也是如此

有三条语句会改变数据。insert 添加行，update 修改行，delete 删除行。下面是在 notes 上执行的例子；这是一张带有 id 和 body 的试验表：

insert into notes (id, body) values (1, 'first draft');
update notes set body = 'second draft' where id = 1;
delete from notes where id = 1;

总要为 update 和 delete 写上 where。没有它们，这两条语句会作用于表中的每一行，而 Postgres 不会问你是不是写错了。

这就是让 Postgres 成为 Postgres 的第一个事实。update 不会修改它所在位置的那一行。它会写出一份完整的新行副本；如果页中有空间，新副本就放在同一个页上，并把旧副本标记为已替换。delete 会把行标记为已死亡，但仍然留在原处。两个旧副本都会留在页上，直到后台清理将它们清除。在一张只有一行的表上，一次 update 会让页上留下两份副本；执行 delete 后，表中已经没有这行了，页上却仍然有两份。

这一个事实解释了表为什么会变大、清理为什么存在，以及繁忙的表为什么可能达到数据大小的两倍。本书把它称为更新就是插入，几乎每一章都会回到它。

select lab.prove('an update gives the row a new address',
  (select ctid::text from notes where id = 1) <> :'ctid_before');
select lab.prove('and leaves the old version on the page: one row, two copies',
  (select count(*) from notes) = 1
  and (select count(*) from heap_page_items(get_raw_page('notes', 0))) = 2);
select lab.prove('a delete leaves the row on the page too, marked dead, for vacuum to clear',
  (select count(*) from notes) = 0
  and (select count(*) from heap_page_items(get_raw_page('notes', 0))) = 2);

JOIN 把表重新拼在一起

好的模式会让每项事实只存一次：用户的电子邮件地址放在 users 中，账户的计划放在 accounts 中。连接通过匹配不同表中的行，把它们重新组合起来，而且几乎总是通过外键完成：

select u.email, a.plan
from users u
join accounts a on a.id = u.account_id
where u.id = 295;

u 和 a 是表的短名称，on 说明哪些行相匹配：这里指的是 id 等于用户 account_id 的那个账户。用户 295 属于账户 296。普通的 join 只保留两侧都匹配的行；left join 保留左侧的每一行，对于没有匹配项的行则填入 NULL。

select lab.prove('a join follows account_id from a user to its account: user 295 is in account 296',
  (select a.id from users u join accounts a on a.id = u.account_id where u.id = 295) = 296);

索引让一次读取变便宜，让每次写入更贵

按电子邮件地址查找一个用户时，如果没有额外帮助，Postgres 必须读取整张 users 表才能找到它：为了找一行，要读取 1,126 个页。（unique (account_id, email) 约束有一个索引，但它先按账户排序，因此通过它查找电子邮件就意味着每个账户都要搜索一次，规划器最终还是直接读取了表。）索引是一种独立的有序结构，指向各行，就像书末的索引。为 email 建一个索引，同样的查找就只读取 4 个页：

create index users_email_idx on users (email);

索引不是免费的。每次 insert，以及每次修改了索引列的 update，都必须同时更新每个索引。因此，只为查询实际会搜索的列建索引，不要为你拥有的每一列都建索引。B-树挑起大梁会展示索引如何只用几个页找到一行。

select lab.prove('finding one user by email reads hundreds of pages without an index and a handful with one',
  :before_index > 300 and :after_index < 10);

掌握这些 SQL 就足以读懂本书了。本章其余部分讲的是支撑这些 SQL 的服务器。

一台服务器运行多个数据库，而它们不共享表

Postgres 服务器由一个数据目录和运行其中的进程组成。文档把它称为一个集簇，这和多台机器没有关系；它就是一台服务器。一个服务器中可以有很多数据库。实验服务器有四个：

 datname   | datistemplate
-----------+---------------
 book      | t
 postgres  | f
 template0 | t
 template1 | t

一次连接只选择一个数据库，也只能看到那个数据库。试图读取另一个数据库时，Postgres 会拒绝：

ERROR:  cross-database references are not implemented: "book.public.events"

这是设计决定，而不是缺少功能：每个数据库都有自己的表、自己的目录和自己的扩展。角色、表空间和服务器配置文件是例外，它们由整个服务器共享。如果应用的两个部分需要连接各自的数据，它们就应该位于同一个数据库的两个模式中。

除非你指定其他模板，否则 create database 会复制 template1；template0 是一份未经修改的备用模板。实验套件也把 book 标记为模板，所以 create database scratch template book 比再次加载数据便宜得多。

select lab.prove('the server holds several databases, book among them, and book is a template',
  (select count(*) from pg_database) >= 4
  and (select datistemplate from pg_database where datname = 'book'));
select lab.prove('a query can''t reach into another database',
  lab.try($$ select count(*) from book.public.events $$)
    like '0A000: cross-database references are not implemented%');

每个连接都是一个独立进程

应用连接时，服务器会启动一个新的操作系统进程来为它提供服务，这个后端进程会一直存在到连接关闭。十个连接就是十个进程，每个进程都有自己的内存。这个事实解释了连接为什么昂贵、max_connections 为什么是上限而不是容量，也解释了为什么每个生产应用最后都要放在连接池代理之后（连接并非免费）。

服务器自己也会运行进程。在实验服务器上，除了一个客户端进程外，还有：

 backend_type                 | count
------------------------------+-------
 autovacuum launcher          |     1
 background writer            |     1
 checkpointer                 |     1
 client backend               |     1
 io worker                    |     3
 logical replication launcher |     1
 walwriter                    |     1

本书的大部分内容都在讲这些进程做什么：WAL 写入器在后台刷新预写式日志；检查点进程和后台写入器把变更后的页写回各自的文件；自动清理启动器启动清理工作进程；而 I/O 工作进程（PostgreSQL 18+）则代表后端进程执行读取。

select lab.prove('a second connection is served by a different server process',
  (select pid from dblink('other', 'select pg_backend_pid()') as t(pid int)) <> pg_backend_pid());
select lab.prove('the server runs its own processes too: a checkpointer, a WAL writer, an autovacuum launcher',
  (select count(distinct backend_type) = 3 from pg_stat_activity
   where backend_type in ('checkpointer', 'walwriter', 'autovacuum launcher')));

模式是文件夹，search_path 选择你想要的那个

在数据库内部，表位于模式中：模式是命名的文件夹，只有一层。每个新数据库都有一个名为 public 的模式；如果你从未创建过其他模式，那么你拥有的一切都在这里。public.invoices 和 billing.invoices 是两张不同的表。

当你写下不带模式名的 invoices 时，Postgres 会按顺序检查 search_path 中列出的模式，并使用第一个匹配项。把 billing 放在首位，同一个查询就会读取另一张表。这对组织大型模式很方便。它也可能让某个函数最终调用你没有编写的对象，所以安全章节会要求每个以提升权限运行的函数都固定 search_path。

select lab.prove('with the default search_path, invoices means public.invoices',
  (select note from invoices) = 'public');
set search_path = billing, public;
select lab.prove('put billing first and the same query reads billing.invoices',
  (select note from invoices) = 'billing');

角色既是用户，也是组，或者两者皆是

Postgres 只有一种身份主体，就是角色。带有 LOGIN 属性的角色就是通常所说的用户；没有该属性的角色就是组；任何角色都可以成为另一个角色的成员，并继承它的权限。每张表都有一个所有者角色，它可以对表做任何事情；其他角色只能使用授予自己的权限。

现在就养成一个习惯：应用应当以一个不拥有任何对象、也不是超级用户的角色进行连接，这样应用中的 bug 就不能删除一张表。不要信任任何租户会展示这种设置。

select lab.prove('a role with LOGIN is a user; one without is a group, and membership links them',
  (select rolcanlogin from pg_roles where rolname = 'ana')
  and not (select rolcanlogin from pg_roles where rolname = 'reporting')
  and pg_has_role('ana', 'reporting', 'member'));

每条语句都在事务中运行

Postgres 从不在事务之外运行语句。单独发送一条 update 时，Postgres 会把它包装在一个事务中，并在语句结束时提交；这就是自动提交，除非你执行 begin，否则驱动程序就会这样做。以这种方式发送两条语句，就会得到两个事务，并分别为自己的提交付出代价。

在 begin 和 commit 之间，所有内容要么一起生效，要么一起不生效；在 Postgres 中，“所有内容”还包括模式变更。回滚的 create table 会消失，因此在一个事务中运行、最终失败的迁移不会留下半完成状态（如实看待 ACID）。这也意味着，长事务会一直持有它接触过的所有东西，包括锁，直到事务结束。在本书中，你会一次又一次遇到这种打开的事务。

select lab.prove('a rolled-back transaction takes its create table with it',
  to_regclass('scratch_t') is null);
select lab.prove('two statements outside begin run as two transactions, with two transaction ids',
  :xid1 < :xid2);

服务器在表中描述自己

Postgres 了解的关于你的模式的一切，都存在于可以查询的普通表中，这些表就是系统目录。pg_class 列出每张表和每个索引，pg_attribute 列出它们的列，pg_roles 列出角色，pg_settings 列出每个设置。pg_stat_ 视图会统计服务器一直在做什么。psql 的反斜杠命令（例如 \d events）其实就是对这些表发起查询。

目录已经替你统计了页数：

 relname | relkind | relpages | reltuples
---------+---------+----------+-----------
 events  | r       |    35173 |   1999995

这是规划器眼中的 events：35,173 个页和约两百万行，由 vacuum 和 analyze 更新。这个页数正是整本书不断回到的那个数字。

select lab.prove('pg_class already knows events is 35,173 pages and about 2 million rows',
  (select relpages = 35173 and reltuples between 1990000 and 2010000 from pg_class where relname = 'events'));

一个设置有值、来源和作用域

Postgres 有几百个设置，例如 work_mem，它是一次排序可以使用的内存（默认 4 MB）。每个设置都有一个值、一个来源和一个作用域。配置文件设置服务器默认值。alter database 和 alter role 可以针对某个数据库或某个角色覆盖它。set 修改当前会话的值，set local 则只在一个事务中修改，事务结束后便恢复原值。

你最常用的是最窄的作用域。某个报表需要更多内存来完成一次大型排序时，应当在自己的事务中执行 set local work_mem，而不是为服务器上的每个连接调高它。show work_mem 读取当前值，pg_settings 则告诉你这个值来自哪里（真正重要的配置）。

select lab.prove('work_mem defaults to 4MB',
  current_setting('work_mem') = '4MB');
begin;
set local work_mem = '64MB';
select lab.prove('set local changes it for this transaction only',
  current_setting('work_mem') = '64MB');
commit;
select lab.prove('and it''s back at commit',
  current_setting('work_mem') = '4MB');
select lab.prove('pg_settings says where each value came from',
  (select source from pg_settings where name = 'work_mem') = 'default');

一条命令就能添加扩展

扩展通过 create extension 为一个数据库添加类型、函数、运算符或索引方法。有些扩展随 Postgres 一起提供（即“contrib”模块）；另一些则需要先安装到服务器上。实验数据库有本书依赖的五个扩展：pg_stat_statements 用于为查询排名，pageinspect 用于读取页的原始内容，pgstattuple 和 pg_buffercache 用于测量表和缓存，pg_trgm 用于文本搜索。

托管服务只允许使用固定列表，所以在围绕某个扩展设计之前，先检查这个列表（值得了解的扩展）。

select lab.prove('the lab database has the book''s five extensions installed',
  (select count(*) from pg_extension
   where extname in ('pg_stat_statements', 'pg_trgm', 'pageinspect', 'pg_buffercache', 'pgstattuple')) = 5);

你的数据是你永远不会碰的文件

归根结底，服务器数据目录中的每张表和每个索引都是一个文件，文件名使用数字，而不是你的表名：

       file       |   bytes
------------------+-----------
 base/16841/16644 | 288137216

288,137,216 字节就是 35,173 个、每个 8,192 字节的页，和目录给出的数量相同。Postgres 一次读写一个页，本书的其余内容也从这里开始。一切都从页说起会把其中一个页打开给你看。

永远不要手动编辑、复制或删除数据目录中的任何内容，尤其是 pg_wal。只有服务器理解这些文件，而从正在运行的服务器中直接复制出来的内容并不是备份。

select lab.prove('events is a file under base/ of exactly 35,173 pages of 8,192 bytes',
  pg_relation_filepath('events') like 'base/%'
  and pg_relation_size('events') = 35173 * 8192);

几条 psql 命令覆盖本书所需

本书的每个示例都在 psql 中运行，它是随 Postgres 提供的命令行客户端。图形化客户端当然也没问题，但有几条反斜杠命令值得背下来：

想看其余命令，加上 \?。反斜杠命令显示出有用信息时，执行 \set ECHO_HIDDEN on 会打印它背后的目录查询，这是学习系统目录最快的方法。

本章要点

• 表由共享类型化列的行组成。主键可以找到一行；外键指向另一张表中的一行。
• select 只读取，不改变任何东西。在 Postgres 中，update 会写出一份新的行副本，而 delete 会把旧行留在原处，直到清理将其清除。
• 一个索引把一次查找从 1,126 个页降到了 4 个页，而每次写入都要付出让它保持最新的代价。
• 一台服务器容纳多个不能互相查询的数据库；在一个数据库内部，模式是文件夹，search_path 在其中选择目标。角色由整个服务器共享，应用应当以一个不拥有任何对象的角色连接。
• 每个连接都是一个拥有独立内存的进程，这就是连接成本高的原因。
• 每条语句都运行在事务中。模式变更也会回滚，打开的事务会持有它接触过的内容。
• 系统目录是可查询的表，设置有作用域（使用最窄的那个），表则是由 8 KB 页组成、不能手动碰触的文件。

延伸阅读

• The SQL Language，PostgreSQL 文档。手册自带的教程：表、查询、连接和更新，每项都用一页简短介绍。
• Architectural Fundamentals，PostgreSQL 文档。教程中用两页介绍进程模型。
• Schemas，PostgreSQL 文档。模式、search_path 以及手册建议的使用方式。
• Database Roles，PostgreSQL 文档。角色、属性和成员关系。
• System Catalogs，PostgreSQL 文档。每张目录表的每一列，供 \d 不够用时查阅。
• psql，PostgreSQL 文档。每条反斜杠命令和每个变量。

3 时间都花在哪里

生产环境中，一个列出团队项目的端点需要 180 ms。你从日志中复制查询，在 psql 里运行，结果 0.4 ms 就完成了。再来一次：0.3 ms。数据库显然没问题。

然后你开始计数。这个端点并不只运行一个查询。它运行一个查询来获取 100 个项目，为每个项目运行一个查询来获取所有者，再为每个项目运行一个查询来获取最近的部署。总共是 201 个查询，每个查询在服务器上都很快，但每个查询都要经过网络再回来。每次往返只需半毫秒，网络本身却会在数据库开始工作之前就花掉 100 ms。

这个故事中没有什么东西真的慢。只是数错了。大致知道每种操作的成本，你就可以在测量之前估算一个查询、一种访问模式或代码中的一个循环。

内存很便宜，其他一切都很贵

在知道各个部分的成本之前，你无法估算一个查询。下面是数据库的延迟阶梯。标为“实测”的行来自我们笔记本上的实验套件；其余数值只是数量级，不是保证。

三个教训几乎支配着每个数据库决策。内存很便宜，其他一切都很贵，所以面对任何查询时，第一个问题都应该是它访问了多少个页，以及这些页在哪里。网络并非免费，即使距离很近也一样：同一可用区内的一次往返，成本大约和读取一次网络磁盘相当。还有，持久性是每次写入中最慢的部分，这就是预写式日志存在的原因。

查询就是页和往返

一个足够应付大多数决策的粗略模型：

query time  ~  round trips x network latency
            +  pages touched x cost per page (depends on where the page is)
            +  CPU work on the rows in those pages

对于典型的应用查询，CPU 项很小，前两项占主导。要观察页这一项，拿序言中的两个查询，让 Postgres 报告它做了什么：

explain (analyze, costs off, timing off, summary off)
select * from events where id = 777777;

 Index Scan using events_pkey on events (actual rows=1.00 loops=1)
   Index Cond: (id = 777777)
   Index Searches: 1
   Buffers: shared hit=4

这就是查询的执行计划，本书里你会读到数百份执行计划。先掌握四条规则，就足以读懂它们，直到读执行计划，别只看耗时讲完其余内容。每一行都是一个步骤，即一个节点；缩进最深的节点先运行，然后把结果提供给上方节点。actual rows 是某个节点每轮产生的行数。Buffers: 统计它访问的 8 KB 页：hit 表示该页已经在 Postgres 的内存中，read 表示 Postgres 必须向操作系统请求它。还要先读这一行：耗时会随缓存和负载波动，页数不会。（PostgreSQL 18 默认会显示它；18 之前需要写 explain (analyze, buffers)。）

四个页，全都在内存中：三个属于主键索引（B-树章节会展示它的形状），另一个属于保存这行数据的表。

select lab.prove(
  'a primary key lookup touches 4 pages: root, internal, leaf, and the table page',
  lab.buffers($$ select * from events where id = 777777 $$) = 4
  and (select level from bt_metap('events_pkey')) = 2);

现在再看一个针对没有索引的列的过滤：

explain (analyze, costs off)
select count(*) from events where project_id = 4242;

 Finalize Aggregate (actual time=26.707..28.186 rows=1.00 loops=1)
   Buffers: shared hit=2049 read=33124
   I/O Timings: shared read=8.623
...
         ->  Parallel Seq Scan on events (actual ... rows=28.33 loops=3)
               Filter: (project_id = 4242)
               Rows Removed by Filter: 666638
...
 Execution Time: 28.211 ms

为了找到 85 行，读取了 35,173 个页：也就是表的每一个页。它仍然只花了约 30 ms，因为三个进程分担了工作，而且那些“read”页来自操作系统内存，而不是磁盘。

四个页对 35,173 个页，两者都“很简单”。报表每小时运行一次时，30 ms 没问题。每次页面加载都运行一次时，它就是数据库中最昂贵的操作。这里的本领在于注意这个比例。

select lab.buffers($$ select count(*) from events where project_id = 4242 $$) as scan_pages \gset

select lab.prove(
  'a filter on project_id (no index) touches all 35,173 pages to find 85 rows',
  :scan_pages = 35173
  and (select count(*) from events where project_id = 4242) = 85
  and lab.nodes($$ select count(*) from events where project_id = 4242 $$)
      && array['Seq Scan', 'Parallel Seq Scan']);

-- pg_temp.explain_default runs plain explain (analyze, format json)
select lab.prove(
  'since PostgreSQL 18, EXPLAIN ANALYZE reports buffers without being asked',
  pg_temp.explain_default($$ select * from events where id = 777777 $$) ? 'Shared Hit Blocks');

一页可能位于三种位置之一

Postgres 需要一个页时，会在三个地方查找，而每个地方都比前一个慢得多：书桌、房间另一边的文件柜、城市另一头的仓库。

shared_buffers 是 Postgres 自己的页缓存，由每个服务器进程共享。默认值是 128 MB，故意设得很小；生产环境通常从 RAM 的约 25% 开始（真正重要的配置）。缓冲区缓存决定哪些页留下会展示它如何选择要保留的内容。

操作系统页缓存是内核对文件内容的缓存，使用其他地方没有占用的 RAM。Postgres 读取的每个页也会落在那里，所以一个热点页可能同时存在于两个缓存中。这种双重缓冲就是 shared_buffers 不会被设置为 RAM 90% 的原因。

“Read”不等于“来自磁盘”

在执行计划中，read 表示“不在 shared_buffers 中，所以 Postgres 向内核请求了它”。内核是否访问了设备，Postgres 看不见。时钟可以告诉你：实验套件打开了 track_io_timing，它会添加一行 I/O Timings。下面是散布在整张表中的 2,000 次主键查找：

 Aggregate (actual time=21.062..21.064 rows=1.00 loops=1)
   Buffers: shared hit=4861 read=3139
   I/O Timings: shared read=15.037
...

读取 3,139 个页用了 15.0 ms：每页约 5 µs（我们多次运行得到的范围是 4 到 5 µs）。没有任何存储设备能把随机读取做得这么快；这些页来自操作系统页缓存。每页几十 µs 或更久，就意味着访问了设备。

explain (analyze, costs off, summary off)
select sum(length(kind)) from events
where id = any(array(select (g * 997)::bigint from generate_series(1, 2000) g));

select lab.plan($$
  select sum(length(kind)) from events
  where id = any(array(select (g * 997)::bigint from generate_series(1, 2000) g)) $$) as p \gset

select lab.prove(
  'thousands of "read" pages at under 20 us each came from the OS page cache',
  (:'p'::jsonb -> 'Plan' ->> 'Shared Read Blocks')::int > 1000
  and (:'p'::jsonb -> 'Plan' ->> 'Shared I/O Read Time')::numeric
      / (:'p'::jsonb -> 'Plan' ->> 'Shared Read Blocks')::numeric < 0.02);

大型扫描不会冲掉缓存

你可能会以为，对 275 MB 的 events 表做一次全表扫描，就会把其他内容全部挤出 128 MB 的缓存。事实并非如此。大型顺序扫描会循环使用一小块私有缓冲区环（PostgreSQL 18 默认设置下为 2,304 KB），所以一份报表不会把应用正在使用的页赶出去（大型操作会使用环形缓冲区）。

每次提交都要等待磁盘

读取可以缓存。提交不行，前提是提交必须在崩溃后仍然存在。执行 commit 时，Postgres 会向预写式日志写入一条记录，并等待磁盘确认它已经持久化。这个等待就是一次 fsync，硬件决定它的价格：我们安静的笔记本 SSD 上约 0.65 ms，其他容器向同一磁盘写入时为 18 ms，而优质服务器存储上远低于 1 ms。

客户端每次只提交一行，就要为每一行刷新一次。0.65 ms 意味着理论上最多约每秒 1,500 次提交；18 ms 则约为 55 次。因此，10,000 行分成 10,000 个事务，就要刷新 10,000 次；放在一个事务中，则只需一次。

批量写入（快速加载数据），对于写入密集型系统，在购买快速读取的存储之前，先购买 fsync 快的存储。提交意味着刷新会测量提交速率，以及绕过等待的两种方式：多个会话共享一次刷新，或者完全不等待。

/usr/lib/postgresql/18/bin/pg_test_fsync -s 1 -f /var/lib/postgresql/fsynctest.out
rm /var/lib/postgresql/fsynctest.out

Compare file sync methods using one 8kB write:
...
        fdatasync                          1524.249 ops/sec     656 usecs/op
...
Non-sync'ed 8kB writes:
        write                           2870550.553 ops/sec       0 usecs/op

IOPS 受在途读取限制，而不是由你付费购买的数量决定

每次同时未命中两个缓存的读取，以及每次提交，都会落到存储上。如果你使用托管服务，数据很可能位于网络块存储中：这种存储看起来像本地磁盘，实际上位于走廊另一头的服务器上，因此每次缓存未命中都是一次网络请求。这种存储用三个很容易混淆的数字来销售：延迟（一次 I/O 需要多久）、IOPS（每秒 I/O 次数）和吞吐量（每秒字节数）。

队列深度，也就是在途 I/O 的数量，把它们联系起来：达到的 IOPS = 在途 I/O 数量 / 延迟。一个进程一次等待一个 1 ms 的读取，无论你付费购买了多少 IOPS，得到的都只有 1,000 IOPS。（这就是 Little 定律；邮局排队也遵循它。）大型扫描可以让许多读取同时在途；一次错过缓存的索引查找，则只是到存储服务器的一次往返。

算一算。AWS gp3 的起点是 3,000 IOPS 和 125 MiB/s：对所有连接合计来说，大约是每秒 3,000 次随机冷页读取。只有在测量确认自己受 I/O 限制时，才购买更快的卷，永远不要用它替代索引。在托管服务上，这通常是你遇到的第一个上限（现在瓶颈是存储卷）。

突发额度会在任务中途用完

gp2 卷和许多较小的实例类型依赖突发额度运行：一开始很快，额度用完后就回到低得多的基线。测试运行正常，繁重任务的前半小时也正常；然后流量没有变化，延迟却开始上升。如果 I/O 悬崖与负载持续时间相吻合，而不是与负载大小相吻合，请先检查突发余额，再检查查询。

本地 NVMe 很快，但主机没了它也没了

本地 NVMe 的读取延迟只有几十 µs，但主机没了它也就没了。只有在配套使用权威数据的副本和未经验证的备份只是传闻，并且它们能应对节点随时丢失时，才使用本地 NVMe（本地 NVMe：快，但主机没了它也没了）。网络卷可以在主机丢失后继续存在，但每次缓存未命中都会让你付出延迟。

根据工作集配置 RAM

对 Postgres 来说，最有效的硬件决策是为你的工作集准备足够的 RAM：比如一个小时内负载会访问的页。不是整个数据库，而是实际会用到的那部分。

events 加上索引后一共是 318 MB。但假设应用主要展示最近一周的数据。事件按时间顺序到达，所以最近 7 天的数据集中在表的末尾：675 个页，约 5 MB，比 users 还小。这是常见的形状：一个很少读取的大尾部，以及一个持续读取的热头部。你的工作集就是每张表的热头部、用于找到它的索引页，以及任何周期性任务会访问的内容。

如果工作集能放进 RAM（shared_buffers 加上操作系统页缓存），几乎每次读取都只需几微秒。如果放不下，每个查询都会变成存储延迟查询，再多 IOPS 也无法让它有内存访问的感觉。RAM 通常比 IOPS 便宜，也远比工程时间便宜。

select relname,
       pg_size_pretty(pg_relation_size(relid))       as heap,
       pg_size_pretty(pg_indexes_size(relid))        as indexes,
       pg_size_pretty(pg_total_relation_size(relid)) as total
from pg_stat_user_tables
order by pg_total_relation_size(relid) desc;

 relname  |  heap   | indexes |  total
----------+---------+---------+---------
 events   | 275 MB  | 43 MB   | 318 MB
 users    | 9008 kB | 8336 kB | 17 MB
 projects | 1528 kB | 456 kB  | 2024 kB
 accounts | 72 kB   | 40 kB   | 152 kB

select count(distinct (ctid::text::point)[0]) as pages,
       pg_size_pretty(count(distinct (ctid::text::point)[0]) * 8192) as size
from events
where created_at > (select max(created_at) from events) - interval '7 days';

 pages |  size
-------+---------
   675 | 5400 kB

select lab.prove(
  'the latest 7 days of events sit in about 675 pages (5 MB), smaller than users',
  count(distinct (ctid::text::point)[0]) between 640 and 710
  and count(distinct (ctid::text::point)[0]) * 8192 < pg_relation_size('users'))
from events
where created_at > (select max(created_at) from events) - interval '7 days';

往返比查询更贵

回到开头的端点。用随 Postgres 提供的负载生成器 pgbench 做端到端计时（pgbench 已经安装），一次主键查找耗时78 µs，包括往返，客户端和服务器位于同一台笔记本上。同一可用区中的另一台主机会为每个查询增加约 100 到 500 µs；另一个可用区最多增加几 ms；另一个区域则增加 30 ms 或更多。

现在来算N+1。一个页面展示 100 个项目，并分别加载每个项目的所有者，就会运行 101 个查询：

查询并没有变慢。变的只有距离。这就是为什么 N+1 bug 在笔记本上看不出来，在生产环境中却很痛苦；也是为什么把数据库搬到“更好的区域”却不搬应用，会让一切都变慢。

ORM 版本看起来很无害：

const projects = await db.project.findMany({ where: { accountId }, take: 100 });
for (const p of projects) {
  // one round trip per project: 100 in all
  p.owner = await db.user.findUnique({ where: { id: p.ownerId } });
}

改为一次查询获取它们，可以使用连接或数组参数：

select * from users where id = any($1::bigint[]);

用这种方式按主键获取 100 行，只需一次往返，耗时约一毫秒。大多数 ORM 都能请求这种行为（“eager loading”“includes”“preload”）；经得住数据增长的查询会讲 SQL 方面的内容。

explain (analyze, costs off, summary off)
select * from projects
where id = any(array(select generate_series(1, 20000, 200)));

 Index Scan using projects_pkey on projects (actual ... rows=100.00 loops=1)
   Index Cond: (id = ANY ((InitPlan 1).col1))
   Index Searches: 55
   Buffers: shared hit=57 read=156 written=156

select lab.prove(
  'one query fetches 100 projects by id in one index scan, a few hundred pages at most',
  lab.buffers($$ select * from projects
                 where id = any(array(select generate_series(1, 20000, 200))) $$) < 300
  and 'Index Scan' = any(lab.nodes($$ select * from projects
                 where id = any(array(select generate_series(1, 20000, 200))) $$))
  and (select count(*) from projects where id = any(array(select generate_series(1, 20000, 200)))) = 100);

\set id random(1, 2000000)
select * from events where id = :id;

pgbench -n -c 1 -T 5 -f lookup.sql scratch

number of transactions actually processed: 64088
latency average = 0.078 ms
tps = 12820.761600 (without initial connection time)

先估算，再测量

现在，你已经可以不运行查询就估算大多数查询了。三个同可用区往返、访问 50 个缓存页：约 1.5 ms，几乎全是网络成本。网络卷上的 2,000 个未缓存页：2 秒，而且增加 CPU 也没有帮助。在一块 fsync 需要 2 ms 的磁盘上，每行提交一次的批处理任务：每秒 500 行，最多如此。

出现慢操作时，问问哪个项很大：往返次数、页数，还是这些页来自哪里。本书其余内容会逐一缩小它们，从页本身开始。

本章要点

• 查询耗时大致等于往返次数乘以网络延迟，再加上访问页所在位置的每页成本：内存中是几微秒，网络卷上则可能达到数毫秒。
• 先读执行计划的 Buffers: 行；页数会重复出现，耗时不会。read 表示“没有命中 shared_buffers”，不表示“来自磁盘”。
• 一次提交要等待一次 WAL 刷新，无论事务包含什么内容。把行批量放进事务，并用 pg_test_fsync 测量你的 fsync。
• RAM 应按工作集配置，而不是按整个数据库配置。在云卷上，达到的 IOPS 等于在途 I/O 数量除以延迟；要关注突发额度。
• N+1 查询消耗的是往返次数，不是服务器时间。用一次查询获取相关行，并让应用和数据库处在同一个区域、同一个可用区。

延伸阅读

• Resource consumption settings，PGDG。shared_buffers、work_mem 和 I/O 设置（包括 io_method）的主要参考资料。
• WAL reliability，PGDG。持久提交要求磁盘做什么，以及写缓存可能如何撒谎。
• pg_test_fsync，PGDG。在选择存储前，测量你自己的 fsync 延迟。
• Amazon EBS volume types，AWS。网络块存储的实际 IOPS、吞吐量和延迟数字；General Purpose SSD page 记录了 gp3 基线和 gp2 突发额度。
• Numbers Every Programmer Should Know By Year，Colin Scott。可以按年份调整的延迟阶梯。

4 一切都从页说起

下面是两张拥有同样六列、保存同样一百万行的表：

 relname | size  | pages
---------+-------+-------
 padded  | 73 MB |  9346
 packed  | 57 MB |  7353

唯一的区别是声明列的顺序。其中一张比另一张大 27%：要永久扫描、缓存、备份和复制的页也多 27%。

Postgres 实际上并不存储表。它存储页，而表就是能放进这些页里的东西。想象一下页的样子，你就能说清任意一行占多少字节，以及怎样让它占得更少。我们会从外向内展开，主要通过 pageinspect 这个可以从 SQL 读取页原始内容的扩展。

表是由页组成的文件

服务器数据目录中的每张表和每个索引，都是一个或多个普通文件。你可以查询它们的路径：

select pg_relation_filepath('events');

 pg_relation_filepath
----------------------
 base/16764/16644

16764 是数据库 ID，16644 是表的关系文件节点（relfilenode），也就是它的文件名（你的值会不同）。在容器中的 shell 里（先运行 docker compose exec postgres bash，再执行 cd /var/lib/postgresql/18/docker）：

-rw------- 1 postgres postgres 288137216 Oct  6 04:07 base/16764/16644
-rw------- 1 postgres postgres     90112 Oct  6 04:07 base/16764/16644_fsm
-rw------- 1 postgres postgres     16384 Oct  6 04:07 base/16764/16644_vm

正如先认识这些基本概念所展示的那样，288,137,216 除以 8,192 恰好是 35,173。这个文件除了页什么都没有，页从 0 开始依次编号。（超过 1 GB 后，表会继续写入 16644.1、16644.2 等文件。）

select lab.prove(
  'the events file is nothing but 8 KB pages: 288,137,216 bytes is exactly 35,173 pages',
  pg_relation_size('events') = 288137216
  and pg_relation_size('events') % 8192 = 0
  and pg_relation_size('events') / 8192 = 35173);

任何会重写整张表的操作（truncate、vacuum full、某些 alter table）都会用新名字写出一个新文件，所以永远不要假设文件名稳定不变。

表不只有主文件

events 有三个文件，而不是一个。每个文件都是一个有自己用途的分支文件：main 保存行，_fsm 是空闲空间映射，_vm 是可见性映射；下面会分别介绍这两个映射。未记录表还会增加一个空的 _init 分支文件，崩溃后会把它复制到其他分支文件上。

因此，对于“这张表有多大？”这个问题，诚实的答案很少只是主文件的大小：

select pg_size_pretty(pg_relation_size('events'))       as main,
       pg_size_pretty(pg_total_relation_size('events')) as total;

  main  | total
--------+--------
 275 MB | 318 MB

pg_relation_size 只统计主分支文件。pg_table_size 会加上其他分支文件和表的 TOAST 存储（见下文），pg_total_relation_size 还会加上每个索引。请使用总大小。

每个页都有相同的布局

这 35,173 个页中的每一个都按下面的方式布局。这是本书最重要的一张图：

它就像一本笔记本：目录从顶部开始写，条目则从底部向上写。

一个 24 字节的页头记录页最后一次变更、校验和，以及空闲空间的起止位置。每个 4 字节的行指针从前端向后增长；每个行指针都指向一个元组。元组从后端向前增长；元组就是存储在页上的一份行版本。两者碰到一起时，页就满了。（索引会在末尾的特殊空间中保存簿记信息；表页则把这部分留空。）

select lsn, checksum, flags, lower, upper, special, pagesize
from page_header(get_raw_page('events', 0));

    lsn     | checksum | flags | lower | upper | special | pagesize
------------+----------+-------+-------+-------+---------+----------
 0/AF5BFA50 |    27634 |     4 |   248 |   384 |    8192 |     8192

select lab.prove(
  'lower = 24-byte header + 56 line pointers x 4 bytes = 248; upper = 384; 136 bytes free',
  lower = 24 + 4 * 56 and lower = 248 and upper = 384 and upper - lower = 136)
from page_header(get_raw_page('events', 0));

select lab.prove(
  '136 free bytes is too few for another events row (136 to 144 bytes, plus a line pointer)',
  (select upper - lower from page_header(get_raw_page('events', 0)))
    < (select min(lp_len) + 4 from heap_page_items(get_raw_page('events', 0))));

行指针让元组移动而不改变地址

行指针允许 Postgres 在页内移动元组，以压缩空闲空间，同时不改变元组的地址。这个地址就是行的 ctid，由页号和行指针编号组成；每张表都有这个隐藏列：

select ctid, id, kind from events order by id limit 3;

 ctid  | id |  kind
-------+----+---------
 (0,1) |  1 | build
 (0,2) |  2 | deploy
 (0,3) |  3 | comment

(0,1) 表示第 0 页、第 1 个行指针。索引会存储 ctid：当索引查找到 id = 2 时，它找到的其实是“第 0 页、第 2 个槽位”。沿着 ctid 找到它所在的表页，就是查询就是页和往返中四页查找的第四次读取。

8 KB 是编译时选择，应当保持不变

八千字节不是自然法则。它是常量 BLCKSZ，在编译 Postgres 时固定（./configure --with-blocksize，取值为 1 到 32 KB 之间的 2 的幂），数据目录中的每个文件都会按它写入。用 16 KB 页构建的服务器无法打开用 8 KB 页创建的数据目录，pg_upgrade 也不能在两者之间跨越。运行中的服务器可以告诉你它是用什么构建的，而且没有任何设置能改变它：

show block_size;

 block_size
------------
 8192

更大的页听起来像是需要数更少的页，而且对于某些工作确实如此。顺序扫描会进行更少次、更大的读取；每个索引页可以容纳更多键，因此树会稍微更浅；宽行也能更久地保持在行内，因为压缩和移出大值的阈值（见下文的 TOAST）会随页大小变化。

但 Postgres 会读取并缓存整页，有时还会整页记录日志，所以其他方面都会变差。在 32 KB 页上，只需要一行的查找会把四倍的字节加载到内存中。同样大小的 shared_buffers 只能容纳四分之一的页，因此你的热点行会和多四倍的冷邻居共享缓存。而检查点之后第一次修改一个页时，会记录整个页，所以每个整页镜像的大小都可能达到四倍。

所以我们保留 8 KB，你也应该这样做。托管服务反正不让你选择页大小。本书中的每个调节手段都可以在不自定义构建的情况下改变一页能容纳多少行：列顺序、更窄的类型、fillfactor、TOAST 和正确的索引。更大的页主要适合在自己编译的服务器上运行、以扫描为主的分析负载，而这种负载无论如何都更适合列存储（Postgres 不擅长什么）。

select lab.prove('the page size is fixed when Postgres is built: block_size is 8192, and no setting can change it',
  current_setting('block_size') = '8192'
  and (select context from pg_settings where name = 'block_size') = 'internal');

select lab.prove('WAL blocks are a separate compile-time constant, also 8 KB here',
  current_setting('wal_block_size') = '8192'
  and (select context from pg_settings where name = 'wal_block_size') = 'internal');

一行的成本高于它的列

打开一个页，数一数每行实际存储了什么。heap_page_items 会解码页上的每个元组：

select lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid, t_hoff
from heap_page_items(get_raw_page('events', 0))
limit 3;

 lp | lp_off | lp_len | t_xmin | t_xmax | t_ctid | t_hoff
----+--------+--------+--------+--------+--------+--------
  1 |   8048 |    144 |    766 |      0 | (0,1)  |     24
  2 |   7904 |    144 |    766 |      0 | (0,2)  |     24
  3 |   7760 |    144 |    766 |      0 | (0,3)  |     24

元组 1 在偏移量 8048 处，占 144 字节，正好延伸到页尾；元组 2 紧接在它下面。每个元组都从 23 字节的元组头开始。

头部中最重要的两个字段是 t_xmin，即插入这一版本的事务；以及 t_xmax，即删除或锁定它的事务（如果没有则为 0）。它们是 Postgres 并发控制的核心，会在更新就是插入中单独介绍。这里每一行都是由事务 766（种子事务）插入的，没有一行被删除。t_ctid 是元组自己的地址，或者它的新版本的地址。t_hoff 表示列值从第 24 个字节开始，也就是头部和一个用于对齐的字节之后；列值按声明顺序首尾相接。

每行都有 24 字节头部和一个 4 字节行指针。一行如果只有两个整数，大部分空间都是开销。

select raw_flags
from heap_page_items(get_raw_page('events', 0)),
     heap_tuple_infomask_flags(t_infomask, t_infomask2)
where lp = 1;

                        raw_flags
----------------------------------------------------------
 {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID}

select substr(t_data, 1, 16)
from heap_page_items(get_raw_page('events', 0))
where lp = 1;

               substr
------------------------------------
 \x0100000000000000a903000000000000

select lab.prove(
  'pageinspect decodes t_infomask 2306 as HASVARWIDTH, XMIN_COMMITTED, XMAX_INVALID',
  t_infomask = 2306
  and raw_flags = '{HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID}')
from heap_page_items(get_raw_page('events', 0)),
     heap_tuple_infomask_flags(t_infomask, t_infomask2)
where lp = 1;

select lab.prove(
  'the next 8 bytes are account_id: 0x03a9 = 937',
  substr(t_data, 9, 8) = '\xa903000000000000'::bytea
  and (select account_id from events where id = 1) = 937
  and x'03a9'::int = 937)
from heap_page_items(get_raw_page('events', 0)) where lp = 1;

NULL 只占一位

NULL 在数据区不占空间。包含任何 NULL 的行会在头部带有空值位图：每列一位，NULL 对应 0。在 projects 中，未归档项目的 archived_at 是 NULL：

 lp |  t_bits  | lp_len
----+----------+--------
  1 | 11110100 |     72
  2 |          |     80
  3 | 11110100 |     72

第 1 行和第 3 行没有归档：它们的第五位是 0，比第 2 行短 8 字节，因为第 2 行保存了时间戳。一位换八个字节。（最多 8 列时，位图可以放进头部多出来的一个字节。超过 8 列后，只要行中有 NULL，就需要更长的位图，这可能把数据起点从第 24 个字节推到第 32 个字节。）

select lp, t_bits, lp_len
from heap_page_items(get_raw_page('projects', 0))
limit 3;

select lab.prove(
  'projects rows 1 and 3 (not archived) carry a null bitmap 11110100; row 2 (archived) has none',
  array_agg(coalesce(t_bits, '') order by lp) = '{11110100,"",11110100}')
from heap_page_items(get_raw_page('projects', 0)) where lp <= 3;

select lab.prove(
  'row 2 is archived and 8 bytes longer, because it stores the timestamp',
  (select lp_len from heap_page_items(get_raw_page('projects', 0)) where lp = 2)
    = (select lp_len from heap_page_items(get_raw_page('projects', 0)) where lp = 1) + 8
  and (select archived_at is not null from projects where ctid = '(0,2)')
  and (select archived_at is null from projects where ctid = '(0,1)'));

列顺序就是空闲空间

现在回到本章开头的谜题。CPU 在地址为自身大小整数倍的位置读取值最快，所以 Postgres 会让值按这种方式排列：bigint 或 timestamptz 从 8 字节倍数开始，integer 或 date 从 4 字节倍数开始，smallint 从 2 字节倍数开始，而 boolean 可以从任意位置开始。当一个 8 字节列紧跟在结束于奇数偏移的位置之后时，Postgres 会填充中间的空隙。想象一个停车场：卡车只能停在每第八条车位线上；如果让一辆摩托车先占用第一个位置，后面七个位置就会空着。

下面逐列展示本章开头的两张表。pg_column_size 表明 padded 的一行是 72 字节，而 packed 的一行是 52 字节：

padded (72 bytes)              packed (52 bytes, 56 on the page)
offset  contents               offset  contents
   0    header (24)               0    header (24)
  24    is_active (1)            24    id (8)
  25    padding (7)              32    created_at (8)
  32    id (8)                   40    score (8)
  40    is_admin (1)             48    flags (2)
  41    padding (7)              50    is_active (1)
  48    created_at (8)           51    is_admin (1)
  56    flags (2)                52    end
  58    padding (6)
  64    score (8)
  72    end

这种差异会叠加：每个元组在页上也从 8 的倍数开始（所以 packed 的 52 个字节在页上占 56 个字节），而且每行还需要一个行指针。结果是每页只能放 107 行，而不是 136 行；需要 9,346 个页，而不是 7,353 个页。

由此得到的规则是：

1. 先放固定宽度的 8 字节列：bigint、timestamptz、double precision。
2. 然后放 4 字节列：integer、real、date。
3. 再放 2 字节列，最后放 1 字节列：smallint、boolean。
4. 可变长度列（text、jsonb、numeric）放在最后。短值不需要对齐，因此可以在末尾紧密排列。

create table padded (
  is_active  boolean,
  id         bigint,
  is_admin   boolean,
  created_at timestamptz,
  flags      smallint,
  score      bigint
);

create table packed (
  id         bigint,
  created_at timestamptz,
  score      bigint,
  flags      smallint,
  is_active  boolean,
  is_admin   boolean
);

insert into padded
  select true, g, false, now(), 1, g from generate_series(1, 1000000) g;
insert into packed
  select g, now(), g, 1, true, false from generate_series(1, 1000000) g;

padded: 8168 usable bytes / (72 + 4) per row  =  107 rows per page
packed: 8168 usable bytes / (56 + 4) per row  =  136 rows per page

select lab.prove(
  'a padded row is 72 bytes; the same columns packed take 52',
  (select pg_column_size(p.*) from padded p limit 1) = 72
  and (select pg_column_size(p.*) from packed p limit 1) = 52);

select lab.prove(
  'page 0 of padded holds 107 tuples, page 0 of packed holds 136',
  (select count(*) from heap_page_items(get_raw_page('padded', 0))) = 107
  and (select count(*) from heap_page_items(get_raw_page('packed', 0))) = 136
  and 8168 / (72 + 4) = 107 and 8168 / (56 + 4) = 136);

select lab.prove(
  '9,346 pages versus 7,353: padded is about 27% bigger',
  pg_relation_size('padded') / 8192 = 9346
  and pg_relation_size('packed') / 8192 = 7353
  and pg_relation_size('padded')::numeric / pg_relation_size('packed') between 1.26 and 1.28);

大值会先压缩，再移出行外

一行必须放进 8 KB 的页中，但一个 text 值最多可以有 1 GB。TOAST（The Oversized-Attribute Storage Technique，超长属性存储技术）解决了这两者之间的矛盾。当即将存储的行大于约 2 KB（8 KB 页下为 2,032 字节，因此一页至少能放四行）时，Postgres 会从最大的可变长度列开始处理：

1. 在原处压缩这个值。
2. 如果行仍然太大，就把这个值移入表自己的 TOAST 表中，切成略小于 2 KB 的块，并在原处留下一个 18 字节的指针。

在一张测试表中，100,000 个重复字符被压缩为 1,156 字节，始终没有离开行。32,000 个随机十六进制字符无法压缩，因此被移出并切成 17 个块，行中只留下一个 50 字节的元组。读回这个值意味着要在 TOAST 表中进行一次索引查找，再读取 17 个元组；这些操作在你的 SQL 中是看不见的。

这对查询意味着：

• 宽表上的 select * 不是免费的。 不显示文档正文的列表页不应该读取它，而大多数 ORM 除非你明确告诉它们，否则会读取每一列。
• 修改经过 TOAST 处理的值会重写整个值。 修改一个大型 jsonb 文档中的一个键，Postgres 也会再次存储整个文档。把经常更新的计数器移出文档；更好的办法是给它单独建一张窄表（把热点计数器移到窄表）。
• 中等大小的值最棘手。 1 到 2 KB 的值不会经过 TOAST 处理，却会让每行都变大，所以每页只能放下少数几行。

create table docs (
  id   bigint generated always as identity primary key,
  body text
);
insert into docs (body) values (repeat('a', 1000));
insert into docs (body) values (repeat('a', 3000));
insert into docs (body) values (repeat('a', 100000));
insert into docs (body)
  select string_agg(md5(random()::text), '') from generate_series(1, 100);
insert into docs (body)
  select string_agg(md5(random()::text), '') from generate_series(1, 1000);

select id, length(body) as chars, pg_column_size(body) as stored,
       pg_column_compression(body) as compression
from docs order by id;

 id | chars  | stored | compression
----+--------+--------+-------------
  1 |   1000 |   1004 |
  2 |   3000 |     44 | pglz
  3 | 100000 |   1156 | pglz
  4 |   3200 |   3200 |
  5 |  32000 |  32000 |

 chunk_id | chunks | bytes | max_chunk
----------+--------+-------+-----------
    17446 |      2 |  3200 |      1996
    17447 |     17 | 32000 |      1996

create table toast_edge (body text);
insert into toast_edge values (repeat('a', 2000)), (repeat('a', 2010));
select lab.prove(
  'a 2,028-byte row is stored as is; a 2,038-byte one crosses 2,032 bytes and is compressed',
  (array_agg(lp_len order by lp))[1] = 2028
  and (array_agg(lp_len order by lp))[2] < 100
  and (select array_agg(pg_column_compression(body) is not null order by length(body))
       from toast_edge) = '{f,t}')
from heap_page_items(get_raw_page('toast_edge', 0));

select lab.prove(
  'random hex doesn''t compress, so rows 4 and 5 move out of line',
  bool_and(pg_column_compression(body) is null)
  and bool_and(pg_column_toast_chunk_id(body) is not null)
  and array_agg(pg_column_size(body) order by id) = '{3200,32000}')
from docs where id in (4, 5);

select reltoastrelid::regclass as toast_table from pg_class where relname = 'docs' \gset
select lab.prove(
  'out-of-line values are cut into chunks of at most 1,996 bytes: 2 chunks for row 4, 17 for row 5',
  (select array_agg(n order by n) from
     (select count(*) as n from :toast_table group by chunk_id) c) = '{2,17}'
  and (select max(length(chunk_data)) from :toast_table) = 1996);

alter table docs add column title text;
select pg_column_toast_chunk_id(body) as chunk_before from docs where id = 5 \gset
update docs set title = 'renamed' where id = 5;
select lab.prove(
  'an update that doesn''t change a toasted value keeps the same TOAST pointer',
  pg_column_toast_chunk_id(body) = :chunk_before)
from docs where id = 5;

update docs set body = body || 'x' where id = 5;
select lab.prove(
  'an update that changes a toasted value stores the whole new value, every chunk',
  pg_column_toast_chunk_id(body) <> :chunk_before
  and (select count(*) from :toast_table t
       where t.chunk_id = pg_column_toast_chunk_id(d.body)) = 17)
from docs d where id = 5;

alter table docs alter column body set compression lz4;
insert into docs (body) values (repeat('a', 100000));
select id, pg_column_size(body), pg_column_compression(body)
from docs where id = 6;