PostgreSQL

事务

MVCC

相比于 MySQL,PostgreSQL 的 MVCC 不是把旧版本放到 undo log,而是把多个版本直接存在表的 heap(行里),每个行版本包含创建/删除事务信息(xmin/xmax 等)

内部结构

每个 heap tuple(行版本)头部包含用于 MVCC 的关键信息(简化表达):

另外还有 visibility map(标记某页是否“all-visible”或“all-frozen”),用于加速 VACUUM 和索引优化。

可见性规则

当事务(或语句,见隔离级别差异)开始读数据时,会创建一个快照,快照里会包含:xmin(低界)、xmax(高界)以及一个活跃事务 ID 列表(在 xmin 到 xmax 范围内的活跃事务)

快照定义了“谁在我看数据时还没完成”,只有在创建时间点之前完成的创建/删除会被视作可见/不可见

更新删除细节

HOT(Heap Only Tuple)优化:如果 UPDATE 只修改非索引列,且页中有足够空间插入新版本,Postgres 可以将新版本放在同一页并利用 HOT 链接,避免更新索引(避免索引膨胀),性能和空间效率都高

VACUUM

为什么要 VACUUM? 因为旧的 tuple 版本占空间并且持有 XID 元信息,如果不清理会导致表膨胀(bloat),并最终因 XID 回绕(wraparound)导致数据丢失风险

InnoDB 把旧版本存在 undo log 中;Postgres 把旧版本存在表 heap。结果是:Postgres 依赖 VACUUM 回收死版本

锁机制

行级锁

表级锁

支持 ACCESS SHARE / ROW EXCLUSIVE / ACCESS EXCLUSIVE 等多种模式

Advisory Lock

PostgreSQL 还支持 用户自定义锁(Advisory Lock),可以在应用层用来控制逻辑

索引机制

PostgreSQL 的索引是独立于表的数据结构,索引不会覆盖表中的所有数据(不像 MySQL 的 InnoDB 聚簇索引)

索引类型

索引特性

# 部分索引CREATE INDEX idx_active_user ON users(last_login) WHERE active = true;# 表达式索引CREATE INDEX idx_lower_name ON users(LOWER(name));# 覆盖索引,可以再索引里包含额外的列CREATE INDEX idx_orders_user ON orders(user_id) INCLUDE(total_amount);# 并发创建索引 不阻塞读写CREATE INDEX CONCURRENTLY

索引扫描方式