PostgreSQL 物理存储结构

数据目录布局

bash

$PGDATA/
├── base/           # 用户数据（表、索引）
├── global/         # 系统表（pg_database, pg_class 等）
├── pg_wal/         # WAL 日志
├── pg_xact/        # 事务提交状态
├── pg_subtrans/    # 子事务位置
├── pg_dynshmem/    # 动态共享内存
├── pg_notify/      # LISTEN/NOTIFY 队列
├── pg_replslot/    # 复制槽
├── pg_serial/      # 已提交的可序列化事务
├── pg_snapshots/   # 快照
├── pg_stat/        # 统计信息文件
├── pg_stat_tmp/    # 临时统计信息
├── pg_ts_dict/     # 文本搜索词典
├── pg_ts_config/   # 文本搜索配置
├── pg_wal/         # WAL 文件
├── pg_xact/        # 事务状态
├── log/            # 数据库日志
└── postgresql.conf # 配置文件

表文件结构

base 目录

bash

$PGDATA/base/16402/
├── 1259      # pg_class 表（系统表）
├── 1259_fsm  # 空闲空间映射
├── 1259_vm  # 可见性映射
├── 1259_brin  # BRIN 索引
├── 1260      # pg_attribute 表
├── ...
├── 16384    # 用户表（如 users）
├── 16384.1  # 大表第二个文件（超过 1GB 分成多个）
├── 16384.2
└── ...

文件命名规则

小于 1GB 的表只有一个文件：16384
超过 1GB 会自动分成多个：16384.1, 16384.2, ...
文件扩展名用 . 而不是 _

表文件格式（Page 结构）

每个表文件由 8KB（block_size）的页组成：

┌────────────────────────────────────┐
│ Page Header (24 bytes)              │
│   - pd_lsn: 最后修改的 WAL LSN      │
│   - pd_tli: 最后修改的时间线         │
│   - pd_flags: 页标志                │
│   - pd_lower: 空闲空间起始位置       │
│   - pd_upper: 空闲空间结束位置       │
│   - pd_pagesize_version: 页版本     │
│   - pd_special: 特殊空间起始位置     │
├────────────────────────────────────┤
│ Items (指针数组)                    │
│   - 每个指针 4 字节，指向实际数据     │
│   - 按行偏移排序（不是按 CTID 排序）  │
├────────────────────────────────────┤
│ Free Space (空闲空间)               │
│   - Items 之间的空闲空间            │
├────────────────────────────────────┤
│ Tuple Data (实际数据)               │
│   - 数据从页尾开始向前存放           │
│   - 每个元组包含 Header + 用户数据   │
└────────────────────────────────────┘

行数据格式（HeapTuple）

┌────────────────────────────────┐
│ t_xmin: 创建事务 ID            │
│ t_xmax: 删除/更新事务 ID       │
│ t_cid: 命令序列号              │
│ t_ctid: 当前行/新行 CTID       │
│ t_infomask: 行标志位           │
│ t_len: 行长度                  │
├────────────────────────────────┤
│ 用户数据（按列顺序存储）        │
└────────────────────────────────┘

CTID 是什么

sql

-- CTID 是行的物理位置
SELECT ctid, * FROM users;

-- 结果示例：
-- ctid    | id | name
-- (0,1)   | 1  | 张三   # 第0个文件，第1个槽位
-- (0,2)   | 2  | 李四
-- (1,1)   | 3  | 王五   # 第1个文件（大于1GB时分出来的）

辅助文件

空闲空间映射（FSM）

bash

# 每个表有一个 .fsm 文件
16384_fsm

记录每个页面的空闲空间大小
VACUUM 需要更新 FSM
用于快速找到有空间的页面插入新数据

可见性映射（VM）

bash

# 每个表有一个 .vm 文件  
16384_vm

标记哪些页面"对所有事务可见"（不需要 VACUUM 清理）
索引扫描时跳过干净页面加快速度
VACUUM 后更新

BRIN 索引

bash

# BRIN 索引也有自己的文件
16384_brin

块范围索引，专门为顺序存储的大表设计
非常小，存储成本极低

系统表

pg_class

存储所有表、索引、视图、序列的信息：

sql

SELECT relname, relkind, relpages, reltuples 
FROM pg_class 
WHERE relname = 'users';

-- relkind: r=表, i=索引, v=视图, S=序列, m=物化视图
-- relpages: 占用多少 8KB 页（统计信息）
-- reltuples: 估算的行数

pg_attribute

存储每个表的列信息：

sql

SELECT attname, atttypid, attlen, attnotnull
FROM pg_attribute
WHERE attrelid = 'users'::regclass;

pg_database

存储数据库信息：

sql

SELECT datname, oid, datallowconn, datistemplate
FROM pg_database;

-- 全局目录，放在 global/ 目录

常见系统表

表名	存储内容
pg_class	表、索引、视图、序列
pg_attribute	表的列信息
pg_type	数据类型定义
pg_proc	函数/过程
pg_operator	操作符
pg_cast	类型转换
pg_constraint	约束
pg_index	索引详情
pg_namespace	模式（schema）
pg_roles	角色
pg_settings	配置参数

表空间（Tablespace）

sql

-- 创建表空间
CREATE TABLESPACE fastdisk LOCATION '/mnt/ssd/data';

-- 使用表空间
CREATE TABLE users (...) TABLESPACE fastdisk;
CREATE INDEX idx_users_email ON users(email) TABLESPACE fastdisk;

-- 移动表到表空间
ALTER TABLE users SET TABLESPACE fastdisk;

表空间目录结构

bash

$PGDATA/pg_tblspc/
├── 16405/           # 指向 /mnt/ssd/data 的符号链接
│   └── PostgreSQL/
│       └── 13/      # 数据库 OID
│           └── 16384 # 表文件

数据类型存储

定长 vs 变长

sql

-- 定长类型（内部存储，不额外占空间）
-- INTEGER: 4 字节
-- BIGINT: 8 字节
-- BOOLEAN: 1 字节
-- DATE: 4 字节

-- 变长类型（额外 4 字节长度前缀）
-- VARCHAR(n): 最多 n 字节 + 4 字节
-- TEXT: 实际长度 + 4 字节
-- JSONB: 实际长度 + 4 字节

NULL 值存储

不存储在行数据中
通过 attnotnull 约束或 NULL bitmap 标记
NULL bitmap 在 t_infomask 中

TOAST 机制

当行数据超过 2KB 时，自动使用 TOAST（The Oversized-Attribute Storage Technique）：

sql

-- 查看表是否有 TOAST
SELECT relname, reltoastrelid
FROM pg_class
WHERE relkind = 'r';

-- TOAST 表自动创建
-- 列值被压缩/切片存储到 TOAST 表

统计信息

pg_stat_user_tables

sql

SELECT 
    relname,
    seq_scan,        -- 顺序扫描次数
    seq_tup_read,    -- 顺序扫描读取行数
    idx_scan,        -- 索引扫描次数
    idx_tup_fetch,   -- 索引扫描获取行数
    n_tup_ins,       -- 插入行数
    n_tup_upd,       -- 更新行数
    n_tup_del,       -- 删除行数
    n_live_tup,      -- 活跃行数
    n_dead_tup,      -- 死亡元组数
    last_vacuum,     -- 上次 VACUUM 时间
    last_autovacuum, -- 上次自动 VACUUM
    last_analyze     -- 上次 ANALYZE
FROM pg_stat_user_tables
WHERE relname = 'users';

pg_statio_user_tables

sql

SELECT 
    relname,
    heap_blks_read,   -- 从磁盘读取的页数
    heap_blks_hit,    -- 从缓存读取的页数
    idx_blks_read,    -- 索引磁盘读取
    idx_blks_hit,     -- 索引缓存命中
    toast_blks_read,  -- TOAST 读取
    toast_blks_hit    -- TOAST 命中
FROM pg_statio_user_tables;

页面内部布局详解

Item Pointer（行指针）

每个行指针 4 字节：
┌────────────────┬───────────┐
│  偏移量(2字节)  │  长度(2字节)│
└────────────────┴───────────┘

行数据布局

┌────────────────────────────────────┐
│ HeapTupleHeader                      │
│   - t_xmin (4)                      │
│   - t_xmax (4)                      │
│   - t_cid (4)                       │
│   - t_ctid (6)                      │
│   - t_infomask2 (2)                 │
│   - t_infomask (2)                  │
│   - t_hoff (1)                      │
│   - t_bits (变长)                   │
├────────────────────────────────────┤
│ NULL Bitmap (变长)                  │
│   - 每个列对应 1 bit                │
│   - 有 NULL 为 1，无为 0            │
├────────────────────────────────────┤
│ 用户数据                           │
│   - 按列定义顺序存储                │
│   - 变长列从行尾向前放              │
└────────────────────────────────────┘

估算表大小的方法

sql

-- 查看表和索引大小
SELECT 
    pg_size_pretty(pg_relation_size('users')) AS table_size,
    pg_size_pretty(pg_total_relation_size('users')) AS total_size,
    pg_size_pretty(pg_indexes_size('users')) AS index_size;

-- 查看数据库大小
SELECT pg_size_pretty(pg_database_size('mydb'));

-- 查看所有表大小（降序）
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

物理存储设计思想

1. 8KB 页为单位
   - 减少磁盘寻道时间
   - 适合 SSD 的块大小
   
2. 顺序追加
   - 新数据总是写到页尾
   - 避免随机写
   
3. 延迟更新
   - 不原地修改数据
   - 靠 VACUUM 清理旧版本
   
4. 空间预分配
   - 表超过 1GB 自动分文件
   - 避免单个文件过大

Spring基础

MySQL数据库

PostgreSQL数据库

核心原理

PostgreSQL 物理存储结构 ​

数据目录布局 ​

表文件结构 ​

base 目录 ​

文件命名规则 ​

表文件格式（Page 结构） ​

行数据格式（HeapTuple） ​

CTID 是什么 ​

辅助文件 ​

空闲空间映射（FSM） ​

可见性映射（VM） ​

BRIN 索引 ​

系统表 ​

pg_class ​

pg_attribute ​

pg_database ​

常见系统表 ​

表空间（Tablespace） ​

表空间目录结构 ​

数据类型存储 ​

定长 vs 变长 ​

NULL 值存储 ​

TOAST 机制 ​

统计信息 ​

pg_stat_user_tables ​

pg_statio_user_tables ​

页面内部布局详解 ​

Item Pointer（行指针） ​

行数据布局 ​

估算表大小的方法 ​

物理存储设计思想 ​

PostgreSQL 物理存储结构

数据目录布局

表文件结构

base 目录

文件命名规则

表文件格式（Page 结构）

行数据格式（HeapTuple）

CTID 是什么

辅助文件

空闲空间映射（FSM）

可见性映射（VM）

BRIN 索引

系统表

pg_class

pg_attribute

pg_database

常见系统表

表空间（Tablespace）

表空间目录结构

数据类型存储

定长 vs 变长

NULL 值存储

TOAST 机制

统计信息

pg_stat_user_tables

pg_statio_user_tables

页面内部布局详解

Item Pointer（行指针）

行数据布局

估算表大小的方法

物理存储设计思想