[!info] related notes

• 课程 MOC：BodySense 数据库工程深化课程 MOC
• 前置：第 5 课
• 知识：Page 与 Buffer Pool

BodySense 数据库第 6 课：Heap、Tuple、Page 与 Buffer Pool——一条 Row 如何落到磁盘

[!abstract] 本课目标 从“SQL 是一行代码”下降一层：数据库真正处理的是 page、tuple、buffer 和 I/O。

从零建立概念：SQL 下面还有“物理存储世界”

前五课主要站在逻辑层：

Table
Row
Column
Constraint
SQL

但数据库最终跑在真实硬件上：

CPU
↓
RAM
↓
SSD / disk

性能优化真正问的是：

为了得到这些逻辑结果，数据库实际需要访问多少内存页、做多少 CPU 工作、发生多少磁盘 I/O？

Logical Row 与 Physical Tuple

我们平时说：

一行 user

这是业务/SQL 视角。

PostgreSQL 物理存储中更接近 tuple。

Tuple 除了你的业务列，还需要内部元数据来支持：

MVCC version visibility
NULL bitmap
tuple location
alignment
transaction information

所以：

业务字段总大小
≠
表文件实际占用大小

还有 tuple/page/index/WAL 等额外成本。

Page / Block：数据库 I/O 的基本单位

PostgreSQL 不会为了读一个 8-byte integer，就让存储设备“只拿这 8 bytes”。

表和索引由 Page 组成。

标准 PostgreSQL build 的 page size 通常是 8 KiB（编译时可以改变）。

粗略：

Heap relation
├─ Page 0
│  ├─ tuple A
│  ├─ tuple B
│  └─ free space
├─ Page 1
└─ Page 2

所以 query cost 常常更像：

需要触碰多少 pages

而不只是：

最终返回多少 rows

Page 内部为什么不能简单想成“连续 JSON”

一个 PostgreSQL heap page 概念上包含：

Page Header
Line Pointers
Free Space
Tuple Data
Special Space（某些 page 类型使用）

Line Pointer 帮助定位 page 里的 tuple。

因此 PostgreSQL 可以在 page 内移动 tuple bytes，而 tuple item identifier 仍有一层定位结构。

CTID 是什么，为什么不能当业务主键

PostgreSQL row 可以看到系统列 ctid，它表示某个 tuple version 当前的物理位置。

概念上类似：

(block number, item offset)

但 UPDATE 可能产生新 tuple version，位置会变化。

所以：

ctid 很适合内部定位/诊断，不是稳定业务 identity。

业务 identity 应该来自 PK，例如 BodySense UUID。

TOAST：一行太大装不进 Page 怎么办

BodySense 有很多：

JSONB
long text
agent output
metadata

如果一个 variable-length value 很大，PostgreSQL 可以使用 TOAST：

compress value
和/或
move value out-of-line
→ associated TOAST relation

所以看到表中一个超大 JSONB，并不意味着它一定原样完整塞在 heap page 内。

这也意味着：

读取大字段
→ 可能额外访问 TOAST data

因此 API 不需要某大字段时，projection 只选必要列可以减少 CPU/I/O/network 成本。

Buffer：为什么数据库不会每次都去 SSD

如果每次查询都从 SSD 重新读 page，性能会很差。

PostgreSQL 有 shared_buffers：

Disk Page
↓ first access
Shared Buffer
↓ repeated access
CPU / executor

同一个 page 再次访问时，可能已在 buffer 中。

操作系统也有自己的 filesystem/page cache，所以“物理 I/O”路径还有多层缓存。

Cold Cache 与 Warm Cache

这解释一个常见 benchmark 陷阱：

第一次：

200 ms

第二次：

20 ms

不一定是 Planner 突然变聪明。

可能只是：

第一次把 pages 读进缓存
第二次大量 shared hit

所以性能实验要明确：

cold or warm?
same dataset?
same plan?
BUFFERS hit/read?

Sequential I/O 与 Random I/O 的成本直觉

如果没有合适 index：

Seq Scan
→ 扫很多相邻 heap pages

有 index：

Index traversal
→ 找 tuple locations
→ 再访问对应 heap pages

如果匹配很多离散 rows，第二种可能造成大量随机访问。

所以：

有 Index 不意味着一定比 Seq Scan 便宜。

这会在第 7、8 课进入 Planner cost model。

UPDATE 为什么和物理存储有关

在 PostgreSQL MVCC 下：

UPDATE logical row
→ old tuple version remains
→ new tuple version created

旧 version 等所有相关 snapshot 都不再需要后，才由 VACUUM 回收/重用。

所以 update-heavy workload 会影响：

page occupancy
dead tuples
bloat
index maintenance
vacuum

这不是单纯 SQL 层能看出来的。

BodySense 为什么值得学这一层

BodySense 不是只有小 User 表。

它包含：

Runtime Events
Job Events
BodyState revision/history
JSONB Agent metadata
Diagnosis/Treatment provenance
pgvector knowledge

随着历史积累：

row count ↑
page count ↑
index size ↑
buffer working set ↑

从这时开始，“数据库为什么慢”必须能落到物理 page 和访问路径。

1. Heap Table

PostgreSQL 普通表默认是 heap：

Table
→ many Pages
→ many Tuples

“heap”不是 Go heap，而是行没有按主键物理排序的表存储组织。

2. Page 为什么重要

数据库通常不是每次只从磁盘读一个字段，而是按 page 做 I/O。于是查询成本常来自：

要访问多少 page？
这些 page 已在内存吗？
访问是连续还是随机？
能否通过索引减少 heap page 访问？

这也是为什么“只返回一个字段”不一定自动让磁盘读取变成一个字段大小。

3. Buffer Pool / Shared Buffers

PostgreSQL 会把热点 page 缓存在内存。第一次查询可能需要 physical read，后续查询可能主要是 buffer hit。

因此性能实验必须注意：

• cold vs warm cache；
• 数据量；
• EXPLAIN (ANALYZE, BUFFERS)；
• OS page cache 与 PostgreSQL shared buffers。

4. Tuple 不是永远原地改

PostgreSQL MVCC 下 UPDATE 往往创建新的 row version，而不是简单覆盖旧 bytes。旧 tuple 后续需要 VACUUM 回收。这会把本课和 MVCC/VACUUM 连接起来。

Prediction

[!question]

1. 表有 100 万行，但查询只返回 1 行，为什么仍可能读取大量 page？
2. 连续执行两次相同查询，第二次更快是否一定说明 SQL/索引更优？
3. SELECT * 与只选必要列为什么可能影响 I/O 和网络，但不等于每次都按列独立读磁盘？
4. PostgreSQL UPDATE 后为什么可能出现 dead tuple？
5. Heap table 是否意味着完全没有任何顺序？

[!success]- Answer / 解析 1. 如果没有有效访问路径，数据库可能扫描大量 heap page 后才找到目标行。

2. 不一定。 可能只是 page 已进入 PostgreSQL/OS cache。性能比较要控制 warm/cold 条件并看 buffers。

3. Heap page 通常包含整行 tuple；投影仍可减少 tuple deform、CPU、网络和某些 index-only 场景成本，但底层 page I/O 不是简单“只读这一列文件”。

4. MVCC 需要保留旧版本给仍可能看到它的 snapshot；旧版本失去可见性后成为可回收 dead tuple。

5. Heap 本身不保证按主键物理排序；但某些 page 内/插入历史会形成局部顺序，不能当成查询契约。

Lab

选一张 BodySense 大表，记录：row count、table size、index size。执行一次 seq scan 类查询和一次主键查询，使用 EXPLAIN (ANALYZE, BUFFERS) 比较 touched buffers。

L4 Gate

能从“访问多少 page / 是否命中 buffer”的角度解释为什么一个查询慢，而不是只说“数据库数据太多”。