PostgreSQL 核心概念

内容：从架构 -> 存储 -> 事务 -> 查询 -> 索引 -> 并发 -> 运维

一、PostgreSQL 是什么（定位）

PostgreSQL 是一个：

关系型数据库（RDBMS）
强一致性（ACID）
多进程架构
MVCC（多版本并发控制）
可扩展（Extension）
OLTP 为主，OLAP 可辅

👉 核心设计哲学：

“正确性 > 性能 > 便利性”

二、整体架构（非常核心）

1️⃣ 客户端 / 服务端模型

Client
  ↓
Postmaster（监听进程）
  ↓ fork
Backend Process（每个连接一个进程）

特点

一个连接 = 一个进程
无共享内存线程模型（对比 MySQL InnoDB）
稳定、隔离性强
连接数不能无限大 -> PgBouncer

2️⃣ 进程模型（重要）

进程	作用
postmaster	主进程
backend	处理客户端请求
checkpointer	定期刷脏页
bgwriter	后台写
walwriter	WAL 写入
autovacuum	回收垃圾
archiver	WAL 归档
logical replication	逻辑复制

三、存储模型（非常重要）

1️⃣ 表 = 文件 + Page

Page（页）

默认 8KB
最小 IO 单位
所有数据都在 Page 里

Page 结构

Page Header
Line Pointer
Tuple（行数据）

2️⃣ 行存储（Tuple）

PostgreSQL 是行存储
一行更新 ≠ 原地更新

👉 UPDATE 本质 = INSERT + DELETE

3️⃣ TOAST（大字段存储）

当字段很大时：

TEXT
JSON
BYTEA

会被自动拆分存储到 TOAST 表。

四、MVCC（PostgreSQL 灵魂）

1️⃣ 什么是 MVCC？

多版本并发控制

每一行都有：

xmin：创建该行的事务ID
xmax：删除该行的事务ID

核心思想

读不阻塞写
写不阻塞读

2️⃣ 可见性规则（简化）

一行是否可见取决于：

当前事务 ID
行的 xmin / xmax
当前事务快照（Snapshot）

3️⃣ 长事务 = 万恶之源

阻止 VACUUM
导致表膨胀
复制延迟

五、事务（ACID）

1️⃣ ACID

特性	PostgreSQL 实现
Atomicity	WAL
Consistency	约束 + 事务
Isolation	MVCC
Durability	WAL + fsync

2️⃣ 隔离级别

PostgreSQL 支持：

隔离级别	实现
READ COMMITTED	默认
REPEATABLE READ	快照
SERIALIZABLE	SSI

⚠️ PostgreSQL 没有真正的 READ UNCOMMITTED

六、WAL（写前日志）

1️⃣ WAL 是什么？

所有修改，先写日志，再写数据

顺序

WAL -> Memory
WAL -> Disk
Data Page -> Disk（延迟）

2️⃣ WAL 的作用

崩溃恢复
主从复制
PITR
逻辑复制

3️⃣ Checkpoint

将脏页刷盘
控制 WAL 增长
太频繁 -> IO 抖动
太少 -> 恢复慢

七、索引（执行性能核心）

1️⃣ 常见索引类型

索引	适用
B-Tree	=, <, >, BETWEEN
GIN	array / json / trgm
GiST	空间 / 模糊
BRIN	超大表，顺序数据
Hash	等值（很少用）

2️⃣ PostgreSQL 索引特点

索引也是表
索引有 MVCC
UPDATE 会导致索引膨胀

3️⃣ 复合索引左前缀原则

(a, b, c)

可用：

a
a, b
a, b, c

不可用：

八、查询执行流程（非常重要）

SQL
 ↓
Parser（语法）
 ↓
Rewriter（规则重写）
 ↓
Planner（成本估算）
 ↓
Executor（执行）

Planner 决策依据

统计信息
表大小
索引选择性
I/O 成本

统计信息来源

ANALYZE;
pg_statistic;

九、锁机制

1️⃣ 锁的分类

类型	说明
表级锁	DDL
行级锁	UPDATE
Advisory Lock	应用锁
Predicate Lock	SERIALIZABLE

2️⃣ PostgreSQL 锁特点

行锁是逻辑锁
锁与 MVCC 并存
SELECT 默认不加锁

十、VACUUM（不可缺少）

1️⃣ 为什么要 VACUUM？

MVCC 产生垃圾
回收 dead tuple
更新统计信息

2️⃣ autovacuum

自动运行
不等于万无一失
高写入场景必须调优

十一、时间与时区（高频坑）

TIMESTAMP vs TIMESTAMPTZ

类型	含义
TIMESTAMP	无时区
TIMESTAMPTZ	绝对时间

👉 日志 / 事件 / 审计：必须 TIMESTAMPTZ

十二、扩展体系（PostgreSQL 核心优势）

常见扩展

扩展	作用
pg_trgm	模糊搜索
uuid-ossp	UUID
hstore	KV
PostGIS	GIS
TimescaleDB	时序

十三、PostgreSQL vs MySQL（核心差异）

维度	PostgreSQL	MySQL
架构	多进程	多线程
MVCC	更完整	InnoDB
SQL 标准	高	一般
扩展	极强	较弱
OLAP	更适合	偏 OLTP

十四、总结

PostgreSQL 是一个以 MVCC + WAL 为核心、以正确性为第一优先级、通过 VACUUM 自我修复的关系型数据库。