主题
第 9 章 存储与物理结构
「数据库的所有炫技，最终都要落到磁盘上的一段段字节。」
想真正读懂 PostgreSQL，就必须打开数据目录、看见每一个文件、理解每一页 8KB 的内部布局。这一章我们把「PG 的硬盘到底长啥样」彻底讲透。
0. 导读：本章你能学到什么
读完本章你应该能够回答下面这些问题：
- PostgreSQL 启动后，硬盘上到底放了哪些文件？
PGDATA目录的每个子目录是干嘛的？ - 我建了一张表
ch9_demo_orders，它在磁盘上的文件叫什么？为什么文件名是一串数字而不是ch9_demo_orders.dat？ - 一页（Page）8KB 内部是怎么排列的？为什么行指针从前往后增长、数据从后往前增长？
- 我往一个
text列里塞了 1MB 的字符串，PG 是怎么存的？为什么我SELECT时还是一行？ - 为什么 PostgreSQL「没有聚簇索引」？这跟 MySQL 的 InnoDB 有什么本质区别？
fillfactor、FSM、VM 这些参数和文件到底解决了什么问题？
为了真正「看见」存储，我们会：
- 用
pg_relation_filepath()把表名翻译成磁盘文件路径 - 启用
pageinspect扩展，像内窥镜一样看到页头、行指针、元组头 - 故意塞超大字段，观察
pg_toast.pg_toast_<oid>自动出现 - 对比
fillfactor=100与fillfactor=70时 HOT 更新比例
1. 数据目录 PGDATA：PostgreSQL 的「家」
1.1 生活类比
把 PostgreSQL 想象成一家仓储式超市：
- 仓库总地址（
PGDATA环境变量） = 超市地址 base/子目录 = 卖场货架（每个数据库是一片区域，每张表是一组货架）global/= 总服务台（保存「所有数据库共享」的台账，比如角色列表）pg_wal/= 收银小票本（每一笔操作都先记一笔，方便对账和复盘）pg_xact/= 交易状态板（每个事务现在是「成功 / 失败 / 进行中」）postgresql.conf= 超市经营规章postmaster.pid= 「营业中」的牌子（记录主进程 PID）
1.2 PGDATA 目录全景
$PGDATA/
├── base/ # 用户数据库的所有数据
│ ├── 1/ # template1 库 (OID=1)
│ ├── 13757/ # template0
│ └── 16384/ # learn_pg 库
│ ├── 16385 # 一个表的主数据文件 (relfilenode)
│ ├── 16385_fsm # FSM：空闲空间映射
│ ├── 16385_vm # VM：可见性映射
│ ├── 16385.1 # 当文件 > 1GB 时的下一个分段
│ └── ...
├── global/ # 集群级（跨库共享）对象
│ ├── 1213 # pg_authid (角色) 等系统表
│ └── pg_control # 集群控制文件（最最关键，存最近 checkpoint LSN）
├── pg_wal/ # WAL 预写日志（旧名 pg_xlog）
│ ├── 000000010000000000000001 # 每个 segment 默认 16MB
│ └── archive_status/
├── pg_xact/ # 事务提交状态（旧名 pg_clog）
├── pg_commit_ts/ # 事务提交时间戳（track_commit_timestamp 开启时）
├── pg_multixact/ # 多事务 ID 元数据（行级共享锁场景）
├── pg_subtrans/ # 子事务父子关系
├── pg_tblspc/ # 表空间软链接 (symlink → 真实路径)
├── pg_stat/ # 持久化统计信息
├── pg_stat_tmp/ # 临时统计信息
├── pg_logical/ # 逻辑复制 slot 数据
├── pg_replslot/ # 物理 / 逻辑复制 slot
├── pg_snapshots/ # 导出的事务快照
├── pg_twophase/ # 两阶段提交准备好的事务
├── pg_dynshmem/ # 动态共享内存映射文件
├── pg_notify/ # LISTEN/NOTIFY 队列
├── pg_serial/ # 可串行化事务用的 SLRU
├── postgresql.conf # 主配置
├── pg_hba.conf # 客户端认证（host-based authentication）
├── pg_ident.conf # OS 用户 → DB 用户映射
├── postmaster.pid # 主进程 PID + socket 等
├── postmaster.opts # 启动时的命令行参数
└── PG_VERSION # 主版本号（如 "16"）📌 术语速记
- OID（Object IDentifier）：32 位无符号整数，PG 给系统中每个对象（数据库、表、索引、函数、类型……）分配的唯一编号。
- Relfilenode：表 / 索引在磁盘上主数据文件的文件名。它通常等于 OID，但
VACUUM FULL、CLUSTER、TRUNCATE、REINDEX等会重写文件的操作会让 relfilenode 变化（OID 不变）。- Tablespace（表空间）：一个「物理路径别名」。可以把表 / 索引指定到 SSD 路径，把冷数据指定到 HDD 路径。
1.3 在 psql 里查看自己数据库的目录信息
sql
-- 查看当前 data 目录
SHOW data_directory;
-- data_directory
-- ----------------------------------
-- /var/lib/postgresql/16/main
-- (1 row)
-- 当前数据库的 OID
SELECT oid, datname FROM pg_database WHERE datname = current_database();
-- oid | datname
-- -------+----------
-- 16384 | learn_pg
-- (1 row)
-- 一张表的 relfilenode 与文件路径
SELECT
c.oid AS table_oid,
c.relfilenode,
pg_relation_filepath('ch9_demo_orders') AS file_path
FROM pg_class c
WHERE c.relname = 'ch9_demo_orders';
-- table_oid | relfilenode | file_path
-- -----------+-------------+----------------
-- 16400 | 16400 | base/16384/16400
-- (1 row)
-- 看看这个文件实际多大（每段最大 1GB）
SELECT pg_size_pretty(pg_relation_size('ch9_demo_orders'));
-- pg_size_pretty
-- ----------------
-- 0 bytes📌 与 MySQL 的区别
维度 MySQL (InnoDB) PostgreSQL 表数据文件 db_name/tbl_name.ibd（文件名 = 表名）base/<db_oid>/<relfilenode>（纯数字）文件分段 单文件，可达数 TB 每 1GB 一个 segment 文件，自动加后缀 .1、.2行格式 InnoDB 数据行存在 聚簇索引（主键）的叶子节点 堆表 + 独立索引文件，索引项指向元组 ctid 数据字典 5.7 之前 .frm，之后存在mysql.ibd全部存在系统目录表（ pg_class等）
2. 表空间 Tablespace：决定数据落在哪块盘
2.1 概念
Tablespace（表空间）= 一个有名字的目录别名。它本质就是 pg_tblspc/<oid> 下的一个软链接，指向你想用的物理路径。建好之后，可以在创建表 / 索引时指定 TABLESPACE xxx。
典型用途：
- 把热表放在 NVMe SSD，把冷表放在普通 HDD
- 把索引放到独立磁盘，让数据 IO 与索引 IO 分开
- 临时表 / 临时排序文件放到独立盘（
temp_tablespaces参数）
2.2 实操
sql
-- 1. 在文件系统上提前 mkdir，并 chown postgres:postgres
-- mkdir -p /mnt/ssd/pg_ts && chown postgres:postgres /mnt/ssd/pg_ts
-- 2. 在 PG 里登记
CREATE TABLESPACE ts_ssd LOCATION '/mnt/ssd/pg_ts';
-- 3. 创建表 / 索引时指定
CREATE TABLE hot_log (id BIGSERIAL, payload TEXT) TABLESPACE ts_ssd;
CREATE INDEX ON hot_log (id) TABLESPACE ts_ssd;
-- 4. 把已有表迁过去（会全表重写、加 ACCESS EXCLUSIVE 锁，谨慎）
ALTER TABLE big_table SET TABLESPACE ts_ssd;
-- 5. 默认表空间
SET default_tablespace = 'ts_ssd';
-- 6. 查看
\db+
-- Name | Owner | Location | ...
-- -----------+-----------+----------------+
-- pg_default| postgres | |
-- pg_global | postgres | |
-- ts_ssd | postgres | /mnt/ssd/pg_ts |📌 PostgreSQL 自带两个表空间：
pg_default（=PGDATA/base）、pg_global（=PGDATA/global，存共享系统表）。MySQL 8 也引入了表空间，但远没 PG 这样灵活的「软链接 + 任意路径」机制。
3. 页 Page：8KB 的最小存储单位（核心）
PostgreSQL 一切磁盘 IO 的最小单位都是「页（Page，又叫 Block）」，默认 8KB（编译时 --with-blocksize=8 决定，可改为 4 / 16 / 32KB）。每张表、每个索引文件，都是一连串 8KB 页拼起来的。
3.1 页的物理布局（必须背下来）
┌────────────────────────────────────────────┐ ← 偏移 0
│ PageHeader (24 字节) │
│ pd_lsn | pd_checksum | pd_flags | │
│ pd_lower | pd_upper | pd_special | │
│ pd_pagesize_version | pd_prune_xid │
├────────────────────────────────────────────┤ ← pd_lower
│ ItemId 数组（行指针，每个 4 字节） │
│ ┌────┐┌────┐┌────┐ │
│ │#1 ││#2 ││#3 │ →→→ 从前往后增长 │
│ └────┘└────┘└────┘ │
│ │
├────────────────────────────────────────────┤
│ │
│ 空闲空间 Free Space │
│ │
├────────────────────────────────────────────┤ ← pd_upper
│ │
│ HeapTuple #3 │
│ HeapTuple #2 │
│ HeapTuple #1 │
│ ←←← 从后往前增长 │
├────────────────────────────────────────────┤ ← pd_special
│ Special Space（仅索引页用，堆表为空） │
└────────────────────────────────────────────┘ ← 偏移 8192为什么这么设计？两端往中间挤的好处：
- 行指针（ItemId）固定 4 字节，长度可预测，从头往后排列方便定位（行号 → 偏移量 =
(行号-1)*4）。 - Tuple 的长度不定，从尾部往中间填可以避免「插一行就要把后面所有数据往后挪」。
- 中间剩下的就是 Free Space，
pd_lower和pd_upper一夹，剩多少空间一目了然，无需扫描整页。
3.2 PageHeader 24 字节字段一一过
| 字段 | 大小 | 含义 |
|---|---|---|
pd_lsn | 8B | 这一页最近一次修改对应的 WAL LSN，崩溃恢复要用 |
pd_checksum | 2B | 数据校验和（initdb -k 开启） |
pd_flags | 2B | 标志位：是否 all-visible / has-free-lines 等 |
pd_lower | 2B | 行指针数组结束的偏移（向下增长边界） |
pd_upper | 2B | 元组数据起始的偏移（向上增长边界） |
pd_special | 2B | special space 起始偏移（堆表 = 8192） |
pd_pagesize_version | 2B | 页大小 + 版本号（PG 16 是版本 4） |
pd_prune_xid | 4B | 上一次有事务删除元组留下的最老 xid，用于 HOT 修剪触发 |
📌 空闲空间 =
pd_upper - pd_lower。VACUUM、HOT 修剪都在维护这个值。
3.3 用 pageinspect 真实查看一页
sql
CREATE EXTENSION IF NOT EXISTS pageinspect;
-- 看第 0 页的页头（init.sql 已经创建并塞了 5 行 ch9_page_demo）
SELECT * FROM page_header(get_raw_page('ch9_page_demo', 0));
-- lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-- --------------+----------+-------+-------+-------+---------+----------+---------+-----------
-- 0/19A8C918 | 0 | 0 | 44 | 8000 | 8192 | 8192 | 4 | 0
-- (1 row)
-- lower=44 → 24(头) + 5*4(指针) = 44，吻合！
-- upper=8000 → 8192(尾) - 5 个元组共占 192 字节 = 8000
-- 看每一行的元组头
SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2, t_hoff
FROM heap_page_items(get_raw_page('ch9_page_demo', 0));
-- lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2 | t_hoff
-- ----+--------+--------+---------+------------+-------------+--------
-- 1 | 752 | 0 | (0,1) | 2306 | 2 | 24
-- 2 | 752 | 0 | (0,2) | 2306 | 2 | 24
-- ...4. HeapTuple：一行数据的物理布局
PostgreSQL 把表的每一行叫做 HeapTuple（堆元组）。它在页里的排布如下：
┌──────────────────────────────────────────────────────┐
│ HeapTupleHeader (23 字节 + 对齐) │
├────────────┬────────────────────────────────────────┤
│ t_xmin (4) │ 创建该版本的事务 ID │
│ t_xmax (4) │ 删除/锁定该版本的事务 ID（0 = 没人删） │
│ t_cid / │ 创建/删除命令号（同一事务内多语句） │
│ t_xvac (4) │（联合体，VACUUM FULL 用 t_xvac） │
│ t_ctid (6) │ 当前 tuple 的物理地址 (block, offset) │
│ │ HOT/MVCC 时也指向新版本 │
│ t_infomask2│ 列数 + HOT/HEAP_HOT_UPDATED 等标志位 │
│ (2) │ │
│ t_infomask │ 通用标志位：是否有 NULL、是否有 OID … │
│ (2) │ │
│ t_hoff (1) │ 用户数据起始偏移 = 头长度（含 NULL 位图│
│ │ 与对齐填充） │
├────────────┴────────────────────────────────────────┤
│ NULL bitmap (可选, 每列 1 bit, 8 字节对齐) │
├──────────────────────────────────────────────────────┤
│ OID (可选, 4 字节, PG 12 起已废弃用户表 OID) │
├──────────────────────────────────────────────────────┤
│ 用户数据：col1 | col2 | col3 ... │
│ 按列定义顺序排列；定长在前更省对齐空间 │
└──────────────────────────────────────────────────────┘关键点：
t_xmin / t_xmax / t_ctid是 MVCC 的灵魂（参见第 8 章），所有可见性判断都靠它们。t_hoff决定了「头部到底有多大」，因为 NULL bitmap 是可选的，长度也按列数变化。- 定长字段放前面可以减少 padding（4 字节对齐 / 8 字节对齐都按 C 结构体规则），是 schema 设计的小优化。
4.1 ctid：行的物理地址
ctid 是一个 (block_number, offset) 元组，其实是一个伪列，每张表都自带：
sql
SELECT ctid, * FROM ch9_page_demo;
-- ctid | id | name
-- --------+----+---------
-- (0,1) | 1 | hello-1
-- (0,2) | 2 | hello-2
-- (0,3) | 3 | hello-3注意：ctid 会随 UPDATE 变化（生成新版本就是新的 ctid）。所以永远不要在应用层缓存 ctid，要用主键。
5. TOAST：超大字段怎么存
5.1 痛点：一行不能跨页
一页只有 8KB，那如果我有一个 text 列存了 1MB JSON 呢？这一行根本塞不进任何一页。
PostgreSQL 的解法叫 TOAST（The Oversized-Attribute Storage Technique，俗称「烤面包」）。
5.2 触发条件
当单行总长度 > TOAST_TUPLE_THRESHOLD（约 2KB，源码常量） 时，PG 会按「列存储策略」选择：
- 优先压缩（pglz / lz4）
- 压缩后还是太大，把超大字段外移到一张「TOAST 表」
- 在原表里只留一个 18 字节的「指针」（
varatt_external，含 toast 表 oid、value oid、原始大小、压缩大小）
TOAST 表的命名约定：
pg_toast.pg_toast_<主表 OID>
└── 列结构：
chunk_id OID -- 这条 value 的唯一 id
chunk_seq INT -- 第几片
chunk_data BYTEA -- 实际内容（每片约 2000 字节）5.3 4 种 STORAGE 策略
| 策略 | 是否压缩 | 是否可外移 | 适用场景 |
|---|---|---|---|
PLAIN | ❌ | ❌ | 定长类型默认；int bool 用不上 |
MAIN | ✅ | 仅在万不得已 | 想压缩但尽量留在主表（提高读速度） |
EXTERNAL | ❌ | ✅ | 想直接读取原始数据，避免 CPU 解压（如全文检索分块） |
EXTENDED | ✅ | ✅ | 可变长度类型默认：text varchar bytea json jsonb |
修改策略：
sql
ALTER TABLE articles ALTER COLUMN body SET STORAGE EXTERNAL;
-- 之后 INSERT 的新数据按新策略，老数据要 VACUUM FULL 重写才会变5.4 实操观察 TOAST
sql
-- 找出 toast 表（init.sql 已经创建 ch9_big_doc）
SELECT
c.relname,
t.relname AS toast_relname,
pg_relation_filepath(t.oid) AS toast_path
FROM pg_class c
JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relname = 'ch9_big_doc';
-- relname | toast_relname | toast_path
-- -------------+--------------------+------------------
-- ch9_big_doc | pg_toast_16410 | base/16384/16412
-- 插入 1MB 数据
INSERT INTO ch9_big_doc VALUES (1, repeat('A', 1024 * 1024));
-- 主表很小，TOAST 表大
SELECT pg_size_pretty(pg_relation_size('ch9_big_doc')) AS heap,
pg_size_pretty(pg_relation_size('pg_toast.pg_toast_16410')) AS toast;
-- heap | toast
-- ---------+--------
-- 16 kB | 264 kB
-- 直接看 toast chunks
SELECT chunk_id, chunk_seq, length(chunk_data)
FROM pg_toast.pg_toast_16410
ORDER BY chunk_id, chunk_seq
LIMIT 5;
-- chunk_id | chunk_seq | length
-- ----------+-----------+--------
-- 16413 | 0 | 1996
-- 16413 | 1 | 1996
-- 16413 | 2 | 1996
-- 16413 | 3 | 1996
-- 16413 | 4 | 1996📌 与 MySQL 的区别
- InnoDB 长字段策略叫 行外存储（off-page），由行格式（
COMPACT/DYNAMIC/COMPRESSED）决定。DYNAMIC/COMPRESSED时，BLOB/TEXT 大字段只在主键索引页里留 20 字节指针，剩下放溢出页。- PG 的 TOAST 是显式独立表，可以单独
pg_dump、单独 VACUUM，可视性更高。- PG 自动处理压缩（pglz / PG14+ 的 lz4），InnoDB 需要显式
ROW_FORMAT=COMPRESSED。
6. FSM 与 VM：表的两个「附属小账本」
每张表（除了哈希索引等少数例外）在磁盘上都有 3 个文件：
base/<dbid>/<relfilenode> # 主数据
base/<dbid>/<relfilenode>_fsm # Free Space Map
base/<dbid>/<relfilenode>_vm # Visibility Map6.1 FSM（Free Space Map）
- 干嘛用的：记录「每一页还剩多少字节」，让 INSERT 能在 O(log N) 时间内找到「能塞下我这行」的页，而不是一页页扫。
- 数据结构：一棵存在磁盘上的二叉堆（min-heap），叶子节点存每页的剩余空间（按 32 字节对齐量化为 1 字节）。
6.2 VM（Visibility Map）
- 干嘛用的：记录「这一页里所有元组是否对所有事务都可见（all-visible），是否已经全部冻结（all-frozen）」。
- 每页 2 bit，所以 1MB 的 VM 文件能覆盖 32GB 的表。
- 三大用途：
- Index-Only Scan：如果 VM 标记某页 all-visible，索引扫描就不用回堆表（极大提升性能）
- VACUUM 跳过：already all-visible/all-frozen 的页可以跳过，加速大表清理
- 防回卷：all-frozen 页里的 xid 都已冻结成 FrozenXid，不会触发 wraparound
sql
-- 查看 vm 信息
CREATE EXTENSION IF NOT EXISTS pg_visibility;
SELECT * FROM pg_visibility('ch9_page_demo'::regclass);
-- blkno | all_visible | all_frozen | pd_all_visible
-- -------+-------------+------------+----------------
-- 0 | t | f | t7. 堆表 vs 索引：PG 没有聚簇索引（重磅）
这是 PG 与 MySQL 最容易令初学者「翻车」的差异，必须理解。
7.1 PostgreSQL 的存储模型
- 所有表都是「堆表」：行没有顺序，新插入的行就放到 FSM 推荐的页里。
- 主键 / 唯一索引 / 二级索引一视同仁：都是独立的 B-Tree 文件，叶子节点存「索引键 + 行的 ctid」。
- 通过任何一个索引找到的最终一步，都是「拿着 ctid 去堆表那一页捞出元组」（Index Scan + Heap Fetch）。
主键索引 (B-Tree) 堆表 (Heap)
┌──────────────────┐ ┌────────────────────┐
│ ... │ │ Page 0 │
│ 100 → (5, 3) │ ─┐ │ ... ┌─(5,3) row┐ │
│ 101 → (12, 1) │ └──→ │ └─────────┘ │
│ 102 → (5, 7) │ │ │
│ ... │ │ Page 5 │
└──────────────────┘ │ ... [3] [7] ... │
└────────────────────┘7.2 InnoDB 的存储模型（对比）
- 主键索引 = 数据（聚簇索引）：行直接住在主键 B+ 树的叶子节点里。
- 二级索引的叶子节点存「索引键 + 主键值」，先二级索引查主键，再回主键索引查数据（「回表」）。
7.3 这个差异带来的实际影响
| 场景 | InnoDB | PostgreSQL |
|---|---|---|
| 主键插入 | 严格按主键顺序追加，主键单调递增能跑满磁盘 | 顺序无关，由 FSM 决定 |
| 更新主键 | 等于删除 + 插入，开销大 | 同样开销大（要更新所有索引），但「走的路一样」 |
| 二级索引查询 | 要「回表」 | 要「回堆表」（index → heap），其实差不多 |
| 想要「行物理有序」 | 天然就是 | 需要手动 CLUSTER tbl USING idx，且不维持（之后的 INSERT 又乱了） |
SELECT * 全表扫描 | 走聚簇索引 | 走 Heap Sequential Scan |
7.4 CLUSTER 命令
sql
-- 一次性按 idx_ch9_demo_orders_user 重排物理顺序（要 ACCESS EXCLUSIVE 锁）
CLUSTER ch9_demo_orders USING idx_ch9_demo_orders_user;
-- 之后再有 INSERT/UPDATE，物理顺序就乱了；除非定期再 CLUSTER7.5 fillfactor：给 HOT 更新留点位置
fillfactor 是建表 / 建索引时设置的「插入时每页只填到百分之多少」的参数。
- 默认 100（对表）：尽量塞满，省空间
- 默认 90（对索引）：B-Tree 留点空间方便分裂
为什么要调小？ 给 HOT（Heap-Only Tuple）更新留位置：
- 当 UPDATE 没有修改任何被索引的列、且新版本能放在同一页时，PG 会做 HOT 更新：新元组留在原页，索引完全不动，t_ctid 链起来。
- 如果把 fillfactor 设为 70，每页留 30% 空闲，UPDATE 频繁时 HOT 命中率大幅提高，写放大暴跌。
sql
-- init.sql 已经准备了 ch9_hot_70 / ch9_hot_full 两张对照表
CREATE TABLE ch9_hot_test (id INT PRIMARY KEY, val INT) WITH (fillfactor = 70);
-- 改已有表
ALTER TABLE ch9_demo_orders SET (fillfactor = 80);
-- 注意：现有页不会立即重排，要等下一次 VACUUM/HOT 修剪8. 统计信息：PG 怎么知道一张表多大
PG 优化器靠「统计信息」决定走 Index Scan 还是 Seq Scan。统计信息存在两处：
8.1 pg_class（粗粒度，每表一行）
sql
SELECT relname, reltuples, relpages
FROM pg_class
WHERE relname = 'ch9_page_demo';
-- relname | reltuples | relpages
-- ---------------+-----------+----------
-- ch9_page_demo | 5 | 1reltuples：估算行数relpages：占多少页- 由
ANALYZE（手动 / autovacuum 触发）更新
8.2 pg_stats（细粒度，每列一行）
sql
SELECT attname, n_distinct, most_common_vals, histogram_bounds
FROM pg_stats
WHERE tablename = 'ch9_demo_orders'
ORDER BY attname
LIMIT 3;n_distinct：去重值个数（负数表示比例）most_common_vals/most_common_freqs：最常见值（MCV）数组histogram_bounds：等频直方图
📌 优化器靠它估算「
WHERE user_id=42大概返回多少行」，再决定走 Index 还是 Seq Scan。
9. 与 MySQL 的全面对比表
| 维度 | MySQL（InnoDB） | PostgreSQL |
|---|---|---|
| 数据文件名 | db_name/tbl_name.ibd | base/<dbid>/<relfilenode>（纯数字） |
| 行存储 | 聚簇索引（主键 = 数据） | 堆表 + 独立索引 |
| 二级索引存什么 | 索引键 + 主键值（回表） | 索引键 + ctid（回堆表） |
| 行外大字段 | InnoDB Off-Page | TOAST 独立表 |
| 自动压缩大字段 | 否（需 ROW_FORMAT=COMPRESSED） | 是（pglz / lz4 默认开） |
| 表空间 | InnoDB 可设 file-per-table | 任意目录、灵活软链接 |
| 数据字典 | mysql.ibd 内表 | 系统 catalog（pg_class 等） |
| 表大小限制 | 单表无硬上限 | 32TB（受单 segment 1GB × 2^32） |
| 行大小限制 | 65535 字节（不含 BLOB） | 1.6TB（受 TOAST 加持） |
| 「行物理顺序」 | 永远按主键有序（聚簇） | 无序；CLUSTER 一次性重排 |
| 元组头大小 | DYNAMIC ≈ 5 字节起 | ≥ 23 字节（MVCC 多字段） |
| 空闲空间维护 | InnoDB 内部 free list | 独立 _fsm 文件 |
10. 小结
- PGDATA 八件套要烂熟：
base / global / pg_wal / pg_xact / pg_tblspc / postgresql.conf / pg_hba.conf / postmaster.pid。 - OID 是逻辑 ID、relfilenode 是物理文件名，二者通常相等，但
VACUUM FULL/CLUSTER/TRUNCATE/REINDEX会让 relfilenode 变化。 - 每页 8KB，两端往中间挤：行指针（ItemId）从前往后，元组从后往前，pd_lower / pd_upper 夹住中间空闲。
- HeapTupleHeader 23 字节带着 t_xmin / t_xmax / t_ctid 等 MVCC 关键字段，决定可见性。
- TOAST 自动处理超大字段：> 2KB 触发，4 种策略 PLAIN / MAIN / EXTERNAL / EXTENDED；外移后存
pg_toast.pg_toast_<oid>，按 chunk_id + chunk_seq 切片。 - FSM 与 VM 是每张表的两个「小账本」：一个管「哪页有空」，一个管「哪页都可见 / 都冻结」。
- PG 没有聚簇索引：所有表都是堆表，主键也是独立 B-Tree。
CLUSTER只能一次性重排。 - fillfactor 调小 → 给 HOT 更新留空间 → 写放大降低、索引不必更新。
🎮 配套演示
用浏览器打开
./09_storage/demo.html，跟着可视化动画再走一遍本章核心概念。配套代码在
./09_storage/code/，每个脚本都可以独立python xxx.py运行，先跑init.sql准备数据。
11. 面试高频题（≥ 5 题）
题 1：请解释 PostgreSQL 一张表在磁盘上对应哪些文件？OID 与 relfilenode 是什么关系？
考察点：物理结构、系统目录、元数据。
参考答案：
PostgreSQL 中，一张普通表通常对应磁盘上 3 个文件家族：
- 主数据文件：路径形如
$PGDATA/base/<dbid>/<relfilenode>，存放所有 8KB 的页。当文件超过 1GB 时会自动切割成<relfilenode>.1、<relfilenode>.2等 segment。 - FSM 文件（
<relfilenode>_fsm）：Free Space Map，用一棵磁盘上的最小堆记录每页剩余空间，让 INSERT 能 O(log N) 找到合适页。 - VM 文件（
<relfilenode>_vm）：Visibility Map，每页 2 bit，标记 all-visible / all-frozen，服务于 Index-Only Scan 与 VACUUM 跳过。
如果表里有可压缩 / 可外移的列（text、bytea、jsonb），还会自动伴生一个 TOAST 表 pg_toast.pg_toast_<oid> 及其索引。
OID 与 relfilenode 的关系：
- OID：表的逻辑唯一编号，存在
pg_class.oid，永不变（除非 DROP）。 - Relfilenode：表当前主数据文件的文件名。默认等于 OID，但以下操作会让它发生变化（实际是「重写一个新文件」）：
VACUUM FULL、CLUSTER、TRUNCATE、REINDEX、ALTER TABLE ... TYPE（变更列类型）等。
可以用 pg_relation_filepath('tbl_name') 直接查到文件路径。
易错点：很多新人误以为 pg_class.oid 永远等于文件名，做迁移备份时直接按 OID 找文件，结果跑过 VACUUM FULL 后就找不到了——一定要用 relfilenode 字段或 pg_relation_filepath。
题 2：详细描述 PostgreSQL 一页（Page）的物理布局，并解释为什么行指针和数据要从两端往中间挤？
考察点：页结构、设计哲学、性能。
参考答案：
PG 一页默认 8KB，从前往后依次是：
PageHeader (24B) | ItemId 数组 → ... ← Tuple 数据 | Special Space具体五段：
- PageHeader（24 字节）：固定头，含
pd_lsn（最近一次修改的 WAL LSN）、pd_checksum、pd_flags、pd_lower（行指针结束偏移）、pd_upper（元组数据起始偏移）、pd_special、pd_pagesize_version、pd_prune_xid。 - ItemId 数组（行指针）：每个 4 字节的
(offset, length, flags)，从pd_lower起从前往后增长。它的索引就是「页内行号」。 - Free Space：中间未使用区域，等于
pd_upper - pd_lower。 - HeapTuple 数据：每个元组包括 23+ 字节的元组头（t_xmin / t_xmax / t_ctid 等）和用户数据，从
pd_upper起从后往前追加。 - Special Space：仅 B-Tree 等索引页用（保存兄弟指针等），堆表为空。
两端往中间挤的好处：
- 行号到元组的映射 O(1)：行指针固定 4 字节，定位行 N 直接
header_size + (N-1)*4，不用扫元组头。 - 变长元组追加 O(1)：从尾部往前贴，不用搬动既有数据；新指针追加在数组末尾即可。
- 空闲空间瞬间可知：
pd_upper - pd_lower，无须遍历。 - 删除轻量：DELETE 只把行指针的 flags 标
LP_DEAD，元组本体保留以维护 MVCC 可见性，等 VACUUM 来回收。 - HOT 更新友好：UPDATE 在同页生成新版本，只追加新 tuple、新 ItemId，把老 tuple 的 t_ctid 指向新 ItemId，索引完全不用动。
加分项：可以提到这种布局也方便 pageinspect 直接读取，符合「磁盘格式即调试格式」的设计哲学。
题 3：什么是 TOAST？什么时候触发？4 种 STORAGE 策略各有什么区别？
考察点：超大字段、压缩、性能权衡。
参考答案：
TOAST = The Oversized-Attribute Storage Technique，是 PG 自动把超大字段「切片 + 压缩 + 外移」的机制。
触发条件：当一行经过常规存储后总长度超过 TOAST_TUPLE_THRESHOLD（≈ 2KB，约页大小的 1/4）时，PG 会按列的 STORAGE 策略尝试压缩或外移，直到这一行能塞进一页为止。
4 种 STORAGE：
| 策略 | 压缩 | 外移 | 适用 |
|---|---|---|---|
| PLAIN | ❌ | ❌ | 定长类型（int、bool）默认；强制不优化 |
| MAIN | ✅ | 仅在万不得已 | 想压缩但尽量内联，提高读速度 |
| EXTERNAL | ❌ | ✅ | 不想压缩（如已压缩二进制、节省 CPU），允许外移 |
| EXTENDED | ✅ | ✅ | 可变长字段（text、bytea、jsonb）默认 |
外移到哪里：每个有可压缩列的主表都自动伴生一张 pg_toast.pg_toast_<oid>，结构为 (chunk_id OID, chunk_seq INT, chunk_data BYTEA)，每片约 2000 字节。主表只保留 18 字节的指针 varatt_external。
修改策略：ALTER TABLE ... ALTER COLUMN col SET STORAGE EXTERNAL/EXTENDED/MAIN/PLAIN。注意只对新写入的数据生效，老数据要 VACUUM FULL 才会重写。
与 MySQL 对比：InnoDB 行外存储由行格式（DYNAMIC/COMPRESSED）控制，溢出页内联在表空间内；PG 的 TOAST 表是独立可视对象，可以单独 pg_dump、VACUUM，运维粒度更细。
易错点：JSONB 的 GIN 索引项不会被 TOAST，但原始 JSONB 列会被压缩+外移；如果你 SELECT jsonb_col 高频但只用其中一个 key，应该用 jsonb_path_ops 索引或干脆改成普通列。
题 4：为什么说「PostgreSQL 没有聚簇索引」？这跟 InnoDB 有什么实际差别？
考察点：存储模型、性能直觉、设计权衡。
参考答案：
「没有聚簇索引」指的是 PG 的所有表都是堆表，行的物理顺序与任何索引都无关。
InnoDB 的模型：
- 主键索引 = 数据本身（聚簇索引），叶子节点直接存整行。
- 二级索引的叶子节点存「索引键 + 主键值」，查询走「二级索引 → 主键索引」叫回表。
- 单调递增的主键能让插入顺序追加、IO 顺序友好。
PostgreSQL 的模型：
- 主键、唯一索引、普通索引一视同仁，都是独立的 B-Tree 文件。
- 索引项的载荷是
ctid（页号 + 槽位），所有索引扫完最后都要Heap Fetch拿数据。 - 行的物理位置由 FSM 决定，与主键无关。
实际差别：
| 行为 | InnoDB | PostgreSQL |
|---|---|---|
| 单调递增主键插入 | 顺序 IO，性能极好 | 顺序无关，能复用空页 |
| UPDATE 主键 | 几乎等于全表行迁移 | 同样昂贵（更新所有索引） |
| 一行变化是否影响所有索引 | 主键变才影响 | UPDATE 非索引列时HOT 可不更新索引 |
| 想物理有序 | 天然 | 需要 CLUSTER tbl USING idx，且不维持 |
| 全表扫描 | 走聚簇索引 | Heap Sequential Scan |
| 索引大小 | 大（叶子带整行） | 小（只带 ctid + 键） |
加分项：可以提到 PG 这个设计让 MVCC 实现非常自然——多个版本就是堆里的多个元组，索引项只指向最新可见版本，不需要 InnoDB 的 Undo Log 回溯。
题 5：fillfactor 是什么？调小有什么好处？什么时候应该调？
考察点：HOT 更新、写放大、参数调优。
参考答案：
fillfactor 是建表 / 建索引时指定的「INSERT 时每页只填到百分之多少」的参数。剩余空间留给后续的 UPDATE 在同页生成新版本（HOT 更新）。
- 表默认 100，索引默认 90。
- 改已有表：
ALTER TABLE tbl SET (fillfactor = 80)，对新写入或新生成的页生效。
调小的好处：
- HOT 更新命中率提高：UPDATE 没改任何索引列、且新版本放得下时，新元组留在同页、用
t_ctid链起来；索引完全不需更新，写放大暴跌。 - VACUUM 更轻：HOT 修剪在同页就能回收死元组（HOT prune），无需重写整页。
- 页内分裂减少：B-Tree 索引把 fillfactor 调小（比如 70）能延后页分裂、降低锁竞争。
何时调小：
- 更新很频繁的表（订单状态、用户画像、计数器表）：建议
fillfactor = 70 ~ 80。 - 被频繁 UPSERT 的索引：把对应索引也设小一点。
何时保持 100：
- 只追加的日志表 / 时序表：永不更新，留空间纯浪费。
- 内存极宝贵 / 表巨大：减小 fillfactor 等于扩大磁盘和 buffer pool 占用。
加分项：HOT 触发要求「新版本能放进同页 + 不修改任何索引列」，所以最好同时避免触发器、表达式索引覆盖被改列、JSONB GIN 索引等可能扩大索引列集合的情况。
题 6：FSM 和 VM 文件分别解决什么问题？为什么 VM 对 Index-Only Scan 很关键？
考察点：辅助文件、查询性能、索引扫描机制。
参考答案：
FSM（Free Space Map）：
- 文件名
<relfilenode>_fsm。 - 用一棵存在磁盘上的二叉最小堆记录每页剩余空间（量化为 1 字节）。
- 服务对象是 INSERT / UPDATE：选择目标页时不用扫表，O(log N) 直接拿到「能塞下我这行」的页。
- 由 INSERT、VACUUM 维护。
VM（Visibility Map）：
- 文件名
<relfilenode>_vm。 - 每页 2 bit：
all-visible（这一页所有元组都对所有当前活动事务可见）+all-frozen（所有元组都已冻结，不参与 wraparound）。 - 1MB VM 文件能覆盖 32GB 表，体积极小。
- 由 VACUUM 维护。
VM 对 Index-Only Scan 的关键作用：
正常索引扫描的步骤是「索引找到 ctid → 去堆表读这一页 → 按 MVCC 判断可见性 → 返回」。如果一个查询的所有列都被索引覆盖（covering index），按理说不需要回堆表，但 PG 必须确认「这条记录对当前事务可见」——而可见性信息只存在堆元组的 t_xmin/t_xmax 里，所以仍要回堆表。
VM 解决了这个矛盾：如果 VM 标记某页 all-visible，意味着这一页所有元组都对所有当前事务可见，当然也对我可见，那索引扫描就可以直接拿索引里的列返回，不必回堆表。这就是 Index-Only Scan，性能可比普通 Index Scan 高 5~10 倍。
易错点：
- 即便建了覆盖索引，如果表很久没 VACUUM，VM 没及时更新，依然会触发 Heap Fetch（
EXPLAIN ANALYZE里能看到Heap Fetches: N）。 - PG 11+ 引入
INCLUDE子句的覆盖索引（CREATE INDEX ... ON tbl (a) INCLUDE (b, c)），把额外列放在索引叶子但不参与排序，能省空间还能让 IOS 命中。
至此，第 9 章「存储与物理结构」就讲完了。下一章我们顺势进入 PG 持久化的另一半灵魂：WAL 与 Checkpoint。
🔗 延伸阅读
- 第 6 章 索引：索引文件与堆表是分离的，B-Tree 叶子如何通过 ctid 回堆。
- 第 10 章 WAL 与 Checkpoint：8KB 页与 WAL 段、checkpoint 之间的物理关系。
- 第 16 章 分区表：每个分区都是独立的物理文件，存储与 vacuum 的工作量也被天然切分。
🎬 可视化演示
演示加载缓慢或样式异常？点此在新标签页打开 ↗
💻 示例代码
python
"""
01_pgdata_explorer.py
---------------------
通过系统视图查看 PostgreSQL 数据目录结构、数据库 / 表的 OID、relfilenode、文件路径与大小。
依赖：
pip install "psycopg[binary]>=3.1"
运行：
python 01_pgdata_explorer.py
"""
import psycopg
CONN_STR = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
def section(title: str) -> None:
print("\n" + "=" * 70)
print(f" {title}")
print("=" * 70)
def main() -> None:
with psycopg.connect(CONN_STR) as conn, conn.cursor() as cur:
# ---------- 1. 数据目录与版本信息 ----------
section("1. 数据目录全局信息")
cur.execute(
"""
SELECT
setting AS data_directory
FROM pg_settings WHERE name = 'data_directory';
"""
)
print(f"PGDATA = {cur.fetchone()[0]}")
cur.execute("SHOW server_version;")
print(f"version = {cur.fetchone()[0]}")
cur.execute("SHOW block_size;")
print(f"page = {cur.fetchone()[0]} bytes")
# ---------- 2. 当前数据库的 OID ----------
section("2. 当前数据库 OID 与磁盘目录")
cur.execute(
"""
SELECT oid, datname,
pg_size_pretty(pg_database_size(oid)) AS size
FROM pg_database
WHERE datname = current_database();
"""
)
oid, name, size = cur.fetchone()
print(f"db_name = {name}")
print(f"db_oid = {oid} → base/{oid}/ 目录")
print(f"db_size = {size}")
# ---------- 3. 表空间（pg_tblspc/<oid> 软链接） ----------
section("3. 表空间（Tablespace）列表")
cur.execute(
"""
SELECT spcname,
pg_tablespace_location(oid) AS location,
pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace
ORDER BY spcname;
"""
)
rows = cur.fetchall()
print(f"{'name':<15} {'location':<40} size")
print("-" * 70)
for r in rows:
loc = r[1] or "(默认 PGDATA/base)"
print(f"{r[0]:<15} {loc:<40} {r[2]}")
# ---------- 4. 用户表的 OID / relfilenode / 文件路径 ----------
section("4. 用户表的 OID 与物理文件路径")
cur.execute(
"""
SELECT
c.oid AS table_oid,
c.relname,
c.relfilenode,
pg_relation_filepath(c.oid) AS file_path,
pg_size_pretty(pg_relation_size(c.oid)) AS heap_size,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
AND c.relkind IN ('r', 'p')
ORDER BY c.relname;
"""
)
rows = cur.fetchall()
print(
f"{'oid':>7} {'relname':<20} {'relfilenode':>12} {'file_path':<25} "
f"{'heap':>8} {'total':>8}"
)
print("-" * 95)
for r in rows:
print(
f"{r[0]:>7} {r[1]:<20} {r[2]:>12} {r[3]:<25} {r[4]:>8} {r[5]:>8}"
)
print("\n→ 注意 file_path 形如 base/<dbid>/<relfilenode>")
print("→ heap = 主数据；total = 主数据 + 索引 + TOAST + 附属文件")
# ---------- 5. relfilenode 会被哪些操作改变 ----------
section("5. 实验：什么操作会让 relfilenode 变化？")
cur.execute(
"SELECT relfilenode FROM pg_class WHERE relname = 'ch9_demo_orders';"
)
before = cur.fetchone()[0]
print(f"VACUUM FULL 之前 relfilenode = {before}")
# VACUUM FULL 不能在事务里跑，开自动提交
with psycopg.connect(CONN_STR, autocommit=True) as c2, c2.cursor() as cur2:
cur2.execute("VACUUM FULL ch9_demo_orders;")
cur2.execute(
"SELECT relfilenode FROM pg_class WHERE relname = 'ch9_demo_orders';"
)
after = cur2.fetchone()[0]
print(f"VACUUM FULL 之后 relfilenode = {after}")
print(f"oid 不变，但物理文件被重写：{before} → {after}\n")
# ---------- 6. 列出该表的所有附属文件 ----------
section("6. 一张表附属的全部物理文件 (heap / fsm / vm / toast)")
cur.execute(
"""
SELECT
c.relname AS object,
CASE c.relkind
WHEN 'r' THEN 'heap'
WHEN 'i' THEN 'index'
WHEN 't' THEN 'toast-heap'
WHEN 'S' THEN 'sequence'
ELSE c.relkind::text
END AS kind,
pg_relation_filepath(c.oid) AS path,
pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.oid IN (
SELECT oid FROM pg_class WHERE relname = 'ch9_demo_orders'
UNION ALL
SELECT reltoastrelid FROM pg_class WHERE relname = 'ch9_demo_orders'
UNION ALL
SELECT indexrelid
FROM pg_index
WHERE indrelid = 'ch9_demo_orders'::regclass
);
"""
)
for r in cur.fetchall():
print(f" {r[1]:<10} {r[0]:<35} {r[2] or '(无)':<25} {r[3]}")
print(
"\n提示：FSM / VM 文件 (_fsm / _vm) 不在 pg_class 里，"
"它们是隐藏附属文件，文件名 = relfilenode + '_fsm' / '_vm'。"
)
if __name__ == "__main__":
main()python
"""
02_page_inspect.py
------------------
用 pageinspect 扩展像内窥镜一样查看 8KB 页的内部结构：
· PageHeader：pd_lsn / pd_lower / pd_upper / pd_special
· ItemId 数组：lp_off / lp_len / lp_flags
· HeapTuple 头：t_xmin / t_xmax / t_ctid / t_infomask / t_hoff
要求：先在数据库里执行
CREATE EXTENSION IF NOT EXISTS pageinspect;
"""
import psycopg
CONN_STR = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
def banner(title: str) -> None:
print("\n" + "─" * 70)
print(f" {title}")
print("─" * 70)
def render_page(lower: int, upper: int) -> str:
"""画一个简单的 8KB 页 ASCII 比例尺图。"""
width = 60
used_head = round(lower / 8192 * width)
used_tail = round((8192 - upper) / 8192 * width)
free = width - used_head - used_tail
return (
"[" + "#" * used_head + "." * free + "*" * used_tail + "]\n"
f" |←头(24)+ItemId 共{lower}B| Free Space {upper-lower}B "
f"|←Tuple 共{8192-upper}B|"
)
def main() -> None:
with psycopg.connect(CONN_STR, autocommit=True) as conn, conn.cursor() as cur:
# 重建一张测试表，可控制元组数
cur.execute("DROP TABLE IF EXISTS ch9_pi_demo;")
cur.execute("CREATE TABLE ch9_pi_demo (id INT, name TEXT);")
cur.execute(
"INSERT INTO ch9_pi_demo "
"SELECT g, repeat('x', 50) FROM generate_series(1, 8) g;"
)
# ---------- 1. PageHeader ----------
banner("1. PageHeader (page_header)")
cur.execute(
"""
SELECT lsn, checksum, flags, lower, upper, special,
pagesize, version, prune_xid
FROM page_header(get_raw_page('ch9_pi_demo', 0));
"""
)
cols = [d.name for d in cur.description]
row = cur.fetchone()
for c, v in zip(cols, row):
print(f" {c:<12} = {v}")
lower, upper = row[3], row[4]
print()
print(" 页布局示意图（8192 字节）：")
print(render_page(lower, upper))
# ---------- 2. ItemId 行指针 ----------
banner("2. ItemId 行指针 (heap_page_items 部分列)")
cur.execute(
"""
SELECT lp, lp_off, lp_flags, lp_len
FROM heap_page_items(get_raw_page('ch9_pi_demo', 0))
ORDER BY lp;
"""
)
print(f" {'lp':>3} {'lp_off':>6} {'lp_flags':>8} {'lp_len':>6}")
for r in cur.fetchall():
print(f" {r[0]:>3} {r[1]:>6} {r[2]:>8} {r[3]:>6}")
print(
"\n lp_flags 含义：1=NORMAL(有效) 2=REDIRECT 3=DEAD(VACUUM待回收)"
)
# ---------- 3. 元组头 ----------
banner("3. HeapTuple 头部字段")
cur.execute(
"""
SELECT lp, t_xmin, t_xmax, t_field3 AS t_cid_or_xvac,
t_ctid, t_infomask, t_infomask2, t_hoff
FROM heap_page_items(get_raw_page('ch9_pi_demo', 0))
ORDER BY lp;
"""
)
print(
f" {'lp':>2} {'t_xmin':>8} {'t_xmax':>8} {'t_cid':>6} "
f"{'t_ctid':>8} {'mask':>6} {'mask2':>6} {'t_hoff':>6}"
)
for r in cur.fetchall():
print(
f" {r[0]:>2} {r[1]:>8} {r[2]:>8} {r[3]:>6} "
f"{str(r[4]):>8} {r[5]:>6} {r[6]:>6} {r[7]:>6}"
)
# ---------- 4. 制造 UPDATE 看 ctid 链 ----------
banner("4. 制造一次 UPDATE 看 t_ctid 形成版本链")
cur.execute("UPDATE ch9_pi_demo SET name = 'updated' WHERE id = 1;")
cur.execute(
"""
SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask
FROM heap_page_items(get_raw_page('ch9_pi_demo', 0))
ORDER BY lp;
"""
)
print(f" {'lp':>2} {'t_xmin':>8} {'t_xmax':>8} {'t_ctid':>10} infomask")
for r in cur.fetchall():
print(f" {r[0]:>2} {r[1]:>8} {r[2]:>8} {str(r[3]):>10} {bin(r[4])}")
print("\n → id=1 的老版本 t_xmax 不再为 0，t_ctid 指向新版本（HOT）。")
# ---------- 5. 表的总页数 ----------
banner("5. 表的总页数")
cur.execute(
"SELECT relpages, pg_relation_size('ch9_pi_demo')/8192 AS real_pages "
"FROM pg_class WHERE relname = 'ch9_pi_demo';"
)
relpages, real_pages = cur.fetchone()
print(f" pg_class.relpages = {relpages}")
print(f" 实际页数 = {real_pages}")
print(" (relpages 由 ANALYZE 更新，可能略有滞后)")
if __name__ == "__main__":
main()python
"""
03_toast_demo.py
----------------
插入超大 text 列触发 TOAST：
· 找到主表的 reltoastrelid（pg_toast.pg_toast_<oid>）
· 比较主表 / TOAST 表的物理大小
· 直接 SELECT pg_toast 表，看到一行被切成多个 chunk
· 切换 STORAGE 策略（EXTENDED → EXTERNAL → MAIN → PLAIN），
重新插入后看大小如何变化
要求：表 ch9_big_doc 已存在（init.sql 已创建）。
"""
import psycopg
CONN_STR = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
PAYLOAD_SIZE = 1024 * 1024 # 1MB
def banner(title: str) -> None:
print("\n" + "─" * 70)
print(f" {title}")
print("─" * 70)
def main() -> None:
with psycopg.connect(CONN_STR, autocommit=True) as conn, conn.cursor() as cur:
cur.execute("TRUNCATE ch9_big_doc;")
# ---------- 1. 找到 TOAST 表 ----------
banner("1. 主表与 TOAST 表的对应关系")
cur.execute(
"""
SELECT c.oid AS heap_oid,
c.relname,
t.oid AS toast_oid,
t.relname AS toast_relname,
pg_relation_filepath(t.oid) AS toast_path
FROM pg_class c
JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relname = 'ch9_big_doc';
"""
)
heap_oid, relname, toast_oid, toast_name, toast_path = cur.fetchone()
print(f"主表 : {relname:<10} oid={heap_oid}")
print(f"TOAST : pg_toast.{toast_name} oid={toast_oid}")
print(f"路径 : {toast_path}")
# ---------- 2. 插入 1MB 数据，观察 TOAST 表膨胀 ----------
banner(f"2. 插入 {PAYLOAD_SIZE/1024:.0f}KB 字符串后两表大小对比")
# 用可压缩的内容（重复字符）→ pglz 能压得很厉害
cur.execute(
"INSERT INTO ch9_big_doc (id, body) VALUES (%s, %s);",
(1, "A" * PAYLOAD_SIZE),
)
# 用不易压缩的随机字节 → 几乎全部进 toast chunk
cur.execute(
"INSERT INTO ch9_big_doc (id, body) VALUES (%s, encode(gen_random_bytes(%s), 'hex'));",
(2, PAYLOAD_SIZE // 2),
)
cur.execute(
f"""
SELECT
pg_size_pretty(pg_relation_size('ch9_big_doc')) AS heap_size,
pg_size_pretty(pg_relation_size('pg_toast.{toast_name}')) AS toast_size,
pg_size_pretty(pg_total_relation_size('ch9_big_doc')) AS total_size;
"""
)
h, t, total = cur.fetchone()
print(f" heap (ch9_big_doc) = {h}")
print(f" toast ({toast_name}) = {t}")
print(f" total (heap+toast+ix) = {total}")
# ---------- 3. 看 TOAST chunks ----------
banner("3. TOAST 表中的 chunk 分布")
cur.execute(
f"""
SELECT chunk_id,
COUNT(*) AS chunk_count,
pg_size_pretty(SUM(length(chunk_data))) AS total_bytes
FROM pg_toast.{toast_name}
GROUP BY chunk_id
ORDER BY chunk_id;
"""
)
print(f" {'chunk_id':>10} {'#chunks':>10} {'sum_bytes':>12}")
for r in cur.fetchall():
print(f" {r[0]:>10} {r[1]:>10} {r[2]:>12}")
cur.execute(
f"""
SELECT chunk_id, chunk_seq, length(chunk_data) AS chunk_len
FROM pg_toast.{toast_name}
ORDER BY chunk_id, chunk_seq
LIMIT 5;
"""
)
print("\n 前 5 片明细：")
print(f" {'chunk_id':>10} {'chunk_seq':>10} chunk_len")
for r in cur.fetchall():
print(f" {r[0]:>10} {r[1]:>10} {r[2]}")
print("\n → 每片约 1996 字节（接近 BLCKSZ/4）")
# ---------- 4. 切换 STORAGE 策略对比 ----------
banner("4. STORAGE 策略影响（重写后看大小变化）")
def measure(label: str) -> None:
cur.execute("VACUUM FULL ch9_big_doc;") # 重写以应用新策略
cur.execute(
f"""
SELECT pg_size_pretty(pg_relation_size('ch9_big_doc')),
pg_size_pretty(pg_relation_size('pg_toast.{toast_name}'));
"""
)
h2, t2 = cur.fetchone()
print(f" {label:<30} heap={h2:<10} toast={t2}")
for storage in ("EXTENDED", "EXTERNAL", "MAIN", "PLAIN"):
try:
cur.execute(
f"ALTER TABLE ch9_big_doc ALTER COLUMN body SET STORAGE {storage};"
)
measure(f"STORAGE = {storage}")
except psycopg.errors.ProgramLimitExceeded as e:
# PLAIN/MAIN 可能因为单行 > 页大小而失败
print(f" STORAGE = {storage:<10} → 失败：{e}")
# 回滚一下
cur.execute("DELETE FROM ch9_big_doc;")
cur.execute(
"INSERT INTO ch9_big_doc VALUES (1, repeat('A', 100));"
)
# ---------- 5. 关闭压缩看差异 ----------
banner("5. 压缩算法（仅 PG14+）")
try:
cur.execute("SHOW default_toast_compression;")
algo = cur.fetchone()[0]
print(f" 当前默认 TOAST 压缩算法 = {algo}")
print(" 可选值：pglz（默认）/ lz4（速度快）")
print(" 改单列：ALTER TABLE t ALTER COLUMN c SET COMPRESSION lz4;")
except psycopg.Error:
print(" PG13 及以下：只有 pglz 一种压缩算法")
if __name__ == "__main__":
main()python
"""
04_fillfactor_hot.py
--------------------
对比 fillfactor=100 与 fillfactor=70 时 HOT 更新的比例。
实验思路：
1. ch9_hot_full / ch9_hot_70 两表结构相同，仅 fillfactor 不同（init.sql 创建）。
2. 各跑同样次数的 UPDATE（不修改索引列）。
3. 通过 pg_stat_user_tables 中 n_tup_upd / n_tup_hot_upd 看 HOT 命中率。
4. 通过 pg_relation_size 看物理体积差异。
"""
import time
import psycopg
CONN_STR = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
N_UPDATES = 10 # 每行更新多少次
def reset(cur: psycopg.Cursor, table: str, fill: int) -> None:
cur.execute(f"DROP TABLE IF EXISTS {table};")
cur.execute(
f"CREATE TABLE {table} (id INT PRIMARY KEY, val INT) "
f"WITH (fillfactor = {fill});"
)
cur.execute(
f"INSERT INTO {table} SELECT g, 0 FROM generate_series(1, 5000) g;"
)
cur.execute(f"VACUUM ANALYZE {table};")
def stats(cur: psycopg.Cursor, table: str) -> dict:
cur.execute(
"""
SELECT n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = %s;
""",
(table,),
)
upd, hot, live, dead = cur.fetchone()
cur.execute(f"SELECT pg_relation_size(%s);", (table,))
size = cur.fetchone()[0]
return {
"upd": upd or 0,
"hot": hot or 0,
"live": live or 0,
"dead": dead or 0,
"size_kb": size // 1024,
}
def run_updates(cur: psycopg.Cursor, table: str) -> None:
for i in range(N_UPDATES):
cur.execute(f"UPDATE {table} SET val = val + 1;")
def main() -> None:
with psycopg.connect(CONN_STR, autocommit=True) as conn, conn.cursor() as cur:
print("=" * 70)
print(" fillfactor 与 HOT 更新比例对比实验")
print("=" * 70)
for table, fill in (("ch9_hot_full", 100), ("ch9_hot_70", 70)):
reset(cur, table, fill)
# 等 stats collector 写入
time.sleep(1)
before = {t: stats(cur, t) for t in ("ch9_hot_full", "ch9_hot_70")}
for table in ("ch9_hot_full", "ch9_hot_70"):
print(f"\n>>> 对 {table}（fillfactor={'100' if table.endswith('full') else '70'}） 运行 {N_UPDATES} 次全表 UPDATE ...")
t0 = time.perf_counter()
run_updates(cur, table)
print(f" 耗时 {time.perf_counter()-t0:.2f}s")
time.sleep(2) # 等待统计信息刷新
after = {t: stats(cur, t) for t in ("ch9_hot_full", "ch9_hot_70")}
print("\n" + "─" * 70)
print(
f" {'table':<14}{'fillfactor':>11}{'updates':>10}"
f"{'hot_upd':>10}{'hot %':>10}{'dead':>8}{'size(KB)':>10}"
)
print("─" * 70)
for table, fill in (("ch9_hot_full", 100), ("ch9_hot_70", 70)):
d_upd = after[table]["upd"] - before[table]["upd"]
d_hot = after[table]["hot"] - before[table]["hot"]
ratio = (d_hot / d_upd * 100) if d_upd else 0
print(
f" {table:<14}{fill:>11}{d_upd:>10}{d_hot:>10}"
f"{ratio:>9.1f}%{after[table]['dead']:>8}{after[table]['size_kb']:>10}"
)
print(
"\n解读：\n"
" · fillfactor=70 留了 30% 空闲，UPDATE 时新版本能放在同页 →\n"
" HOT 命中率明显升高，索引几乎不用动，写放大降低。\n"
" · fillfactor=100 把页塞满，UPDATE 不得不把新版本放到其它页 →\n"
" HOT 比例低，索引更新频繁，物理体积增长更快。\n"
)
if __name__ == "__main__":
main()markdown
# 第 9 章 存储与物理结构 - 配套代码
本章脚本带你「亲眼看到」PostgreSQL 在硬盘上的样子：从 PGDATA 目录、表的 OID/relfilenode、8KB 页内布局，到 TOAST 切片、HOT 更新比例。
## 准备工作
1. 跑 `psql -h 127.0.0.1 -U postgres -d learn_pg -f ../init.sql` 初始化测试表（带 `ch9_` 前缀）
2. 安装依赖：`pip install "psycopg[binary]>=3.1"`
3. （可选）通过环境变量覆盖默认连接：`export PG_DSN="host=... port=... dbname=... user=..."`
> 多个脚本依赖 `pageinspect`、`pgstattuple`、`pg_visibility` 三个扩展，已经在 `init.sql` 中 `CREATE EXTENSION IF NOT EXISTS` 了，**需要 superuser 权限**。
## 脚本一览（推荐运行顺序）
| 脚本 | 一句话说明 | 关键 PG 特性 |
|------|------------|--------------|
| `01_pgdata_explorer.py` | 把表名翻译成磁盘文件路径，演示 OID vs relfilenode | `pg_relation_filepath` / `VACUUM FULL` 后 relfilenode 变更 |
| `02_page_inspect.py` | 用 `pageinspect` 看 8KB 页头、行指针、HeapTuple 头 | `page_header` / `heap_page_items` / HOT 链 |
| `03_toast_demo.py` | 插入超大字段触发 TOAST，比较 4 种 STORAGE 策略 | TOAST chunk / `pg_toast.pg_toast_<oid>` / 压缩算法 |
| `04_fillfactor_hot.py` | 对比 fillfactor=100 / 70 时 HOT 更新比例 | `n_tup_hot_upd` / 物理体积 |
运行示例：
```bash
python 01_pgdata_explorer.py
python 02_page_inspect.py
python 03_toast_demo.py
python 04_fillfactor_hot.py预期输出
01_pgdata_explorer.py 会列出 ch9_demo_orders 在磁盘上的相对路径（形如 base/16384/16400），并演示 VACUUM FULL 后 relfilenode 的变化：
VACUUM FULL 之前 relfilenode = 16400
VACUUM FULL 之后 relfilenode = 16453
oid 不变，但物理文件被重写：16400 → 1645304_fillfactor_hot.py 会打印类似：
table fillfactor updates hot_upd hot % dead size(KB)
──────────────────────────────────────────────────────────────────────
ch9_hot_full 100 50000 12345 24.7% 37655 640
ch9_hot_70 70 50000 49123 98.2% 877 192常见报错
connection refused→ PostgreSQL 服务未启动，检查pg_isreadyrelation "ch9_demo_orders" does not exist→ 没跑init.sql，先psql ... -f ../init.sqlpermission denied for extension pageinspect→ 需要 superuser；用postgres用户跑init.sqlfunction get_raw_page(...) does not exist→ 缺少pageinspect扩展，CREATE EXTENSION pageinspect;out of memory或 chunk 数为 0 → TOAST 行内压缩成功，本就不会写 chunk，属正常
01_pgdata_explorer.py ↗ · 02_page_inspect.py ↗ · 03_toast_demo.py ↗ · 04_fillfactor_hot.py ↗ · README.md ↗