PostgreSQL

Appearance

数据页存储机制 ​

数据页是数据库存储的基本单位，就像书本的"页"一样：

sql
一本书（表文件）
├── 第1页（数据页1，8KB）
├── 第2页（数据页2，8KB）
├── 第3页（数据页3，8KB）
├── ...
└── 第N页（数据页N，8KB）

-- 实际的文件结构
/var/lib/postgresql/data/base/16384/
├── 12345       -- 表文件（包含所有数据页）
├── 12345_fsm   -- 空闲空间映射文件（Free Space Map）
├── 12345_vm    -- 可见性映射文件（Visibility Map）
└── 23456       -- 某个索引的物理文件（索引也是独立的表文件，有自己的 OID）
  • PostgreSQL 默认数据页大小：8KB（8192字节）
  • 每个表被分成多个8KB的数据页
  • 数据页是磁盘I/O的最小单位：PostgreSQL 从磁盘读取或向磁盘写入数据的最小单位也是一个数据页。这意味着，即使你只需要读取一行数据（可能只有几十字节），PostgreSQL 也会将整个数据页（通常为8KB）从磁盘加载到内存中。同样，当你写入一行数据时，PostgreSQL 也是以整个数据页为单位将数据写回磁盘。

数据页的内部结构

sql
-- 想象一个数据页的内部结构：
+-----------------------------------+
| 页头 (约 24 字节)                | ← 元数据：LSN、校验、空闲空间边界等
+-----------------------------------+
| 行指针数组 (每项 4 字节)         | ← ItemId 数组：指向实际行数据的“槽位”
+-----------------------------------+
| 空闲空间                         |
+-----------------------------------+
| 实际行数据                       | ← 真正的表数据（每行前还有元组头）
| - 行1: (元组头 + id=1, name='Alice') |
| - 行2: (元组头 + id=2, name='Bob')   |
| - ...                            |
+-----------------------------------+
| 特殊空间 (可选)                  |
+-----------------------------------+

为什么这样设计？ ​

机械硬盘的访问成本

sql
# 读取1字节 vs 读取8KB的成本对比
读取1字节: 寻道(5ms) + 旋转(4ms) + 传输(0.0001ms) ≈ 9ms
读取8KB:  寻道(5ms) + 旋转(4ms) + 传输(0.1ms)   ≈ 9.1ms

# 结果：读取1字节和读取8KB时间几乎一样！

# 内存管理简化
缓冲池 = [页1, 页2, 页3, 页4, ...]  # 每个槽位刚好放一个数据页

# 如果大小不固定，管理会非常复杂
缓冲池 = [23字节, 156字节, 8KB, 45字节, ...]  # 混乱！

这种设计虽然在处理小数据时看似浪费，但在整体性能上是最优的选择！

顺带补充一个直观的概念：

  • 每一页在表文件里的位置是用 页号（block number） 标记的
  • 每一页里的每一行在页内的位置用 偏移号（offset number） 标记
  • 在 SQL 里，这两者合在一起，就对应一行的 ctid：
sql
SELECT ctid, *
FROM your_table
LIMIT 5;

-- ctid 形如 (页号, 行槽位号)，比如 (42,3)

这样“ctid = (页号, 行号)”就可以精确定位到某一行在某个 8KB 页中的具体位置。

数据页如何影响随机 I/O ​

  • 顺序 I/O

指从磁盘连续读取数据块的操作，例如全表扫描（Sequential Scan）。磁头只需寻道一次（机械硬盘的磁头只需要寻道旋转下沉一次），然后就可以连续读取，速度非常快。

  • 随机 I/O

指读取分布在磁盘不同位置的数据块的操作。每次读取都需要移动磁头到新的位置（寻道），这个过程非常耗时，因此随机 I/O 的性能远低于顺序 I/O。

  • 索引扫描 (Index Scan)

当你使用索引来查询少量数据时，数据库首先在索引中找到对应数据行的物理位置（即 ctid = (数据页号, 行槽位号)）。然后，根据这些位置去访问对应的数据页。如果这些行分布在许多不同的数据页上，这就需要多次随机 I/O 操作来读取这些分散的页面，即使每个页面只读取少量数据。

这里还有一个很重要的优化：Index Only Scan（仅索引扫描）。

  • 正常的 Index Scan：在索引里找到键 → 根据 ctid 回表读数据页

  • Index Only Scan：如果某个数据页在 visibility map（可见性映射）里被标记为“全部行对所有事务可见”，那么就可以不回表，直接使用索引里的值

  • 好处：减少回表时的随机 I/O（少读很多 heap 页）

  • 空间碎片

如果表由于大量的更新和删除而变得碎片化，原本逻辑上连续的数据可能会被分散到物理上不连续的多个数据页中。这会导致即便是范围扫描（在索引上逻辑连续），也可能引发大量的随机 I/O，因为需要跳跃式地访问不同的数据页。

  • 更新和删除如何影响数据页

当一行数据被更新（UPDATE）时，PostgreSQL 并不会在原来的位置修改它，而是会写入一个新的行版本，并在旧的行版本上做个标记，表明它已被更新（元组头里的 xmax 等信息会改变）。

删除（DELETE）操作也类似：并不是立刻把这一行从页面中“抹掉”，而是把它标记为对后续事务不可见。至于它占用的空间，只有当系统确认“已经没有任何事务再需要看到这行”时，才会在后续的 VACUUM 操作中被真正回收和重用。

这也解释了为什么：

  • 大量 UPDATE / DELETE 后，表文件不会立刻变小
  • 需要定期 VACUUM / VACUUM FULL 来清理和压缩空间

为了减轻频繁 UPDATE 带来的“跨页写新版本”的问题，可以在建表时设置 fillfactor（填充因子），给未来的更新预留空间，例如：

sql
CREATE TABLE t (
	id   int,
	info text
) WITH (fillfactor = 70);  -- 每页只填到 70%，预留 30% 给更新

这样，更新时更有机会在同一数据页里放下新版本，减少跨页写入、降低随机 I/O 和碎片化。

最后，还有一个经常被忽略但很重要的机制：TOAST。

  • 一行数据必须放在单个数据页里（减去页头、行指针数组、元组头后的剩余空间）
  • 对于非常大的字段（比如很长的 text、bytea），PostgreSQL 会把它们拆成多个块，存放在一个隐藏的 TOAST 辅助表里
  • 主表里的这一行只保留一个指向 TOAST 数据的“指针”

这保证了普通数据页不会被单个大字段“挤爆”，也让大字段的读写更可控，但同时也意味着：大量大字段的更新，会带来更多的额外 I/O（既要动主表页，又要动 TOAST 表页）。