PostgreSQL 深度解析

#组件 · #PostgreSQL · #数据库 · #关系型 · #MVCC · #SQL

从进程模型到 MVCC 实现，从索引类型到核心特性，全面理解 PostgreSQL 的设计哲学。

一、进程模型

1.1 多进程架构（与 MySQL 的区别）

mermaid

flowchart TB
    Postmaster["Postmaster 主进程<br/>PID 固定, 监听 5432"]

    subgraph Backends["Backend 进程 (fork 创建)"]
        B1["Backend 1<br/>私有 work_mem"]
        B2["Backend 2<br/>私有 work_mem"]
        BN["Backend N<br/>私有 work_mem"]
    end

    subgraph Auxiliary["辅助进程"]
        WAL["WAL Writer<br/>刷 WAL Buffer"]
        BG["BGWriter<br/>刷 Shared Buffer"]
        CKPT["Checkpointer<br/>执行检查点"]
        AV["AutoVacuum<br/>回收死元组"]
    end

    subgraph Shared["共享内存"]
        SB["Shared Buffers<br/>数据页缓存"]
        WB["WAL Buffers<br/>WAL 写缓冲"]
    end

    Postmaster --> Backends
    Postmaster --> Auxiliary
    Backends <--> Shared
    Auxiliary <--> Shared

与 MySQL 线程模型对比：

维度	MySQL	PostgreSQL
并发模型	线程（轻量，共享地址空间）	进程（隔离强，每连接独立）
内存	Buffer Pool 全局共享	Shared Buffer 全局 + 每进程私有 work_mem
上下文切换	线程切换轻量	进程切换较重（大量连接时开销大）
稳定性	一个线程崩溃→整个 mysqld 可能挂	一个进程崩溃→不影响其他连接
连接池	线程池插件	必须用连接池（PgBouncer/Pgpool-II）

1.2 核心后台进程

进程	作用
WAL Writer	将 WAL Buffer 刷到磁盘
BGWriter	将 Shared Buffer 脏页刷盘
Checkpointer	执行检查点，更新控制文件
AutoVacuum	自动回收死元组、更新统计信息（PG 的核心自动化机制）
WAL Sender	流复制（主→从发送 WAL）
WAL Receiver	流复制（从→主接收 WAL）

二、MVCC 实现（与 MySQL 的本质区别）

2.1 无 Undo Log，原地多版本

PostgreSQL 的 MVCC 不使用 Undo Log，而是在数据页中保留旧版本的行（死元组）：

mermaid

flowchart LR
    subgraph Page["数据页"]
        Old["旧元组: xmin=100, xmax=200<br/>data='Alice'<br/>❌ 对事务201不可见"]
        New["新元组: xmin=201, xmax=0<br/>data='Bob'<br/>✅ 对事务201可见"]
    end
    Old -->|"UPDATE 产生"| New

字段	含义
`xmin`	创建此版本的事务 ID
`xmax`	删除此版本的事务 ID (0 = 未删除)
`cmin/cmax`	同一事务内的命令序号

2.2 可见性判断

mermaid

sequenceDiagram
    participant T1 as 事务101 (UPDATE)
    participant Page as 数据页
    participant T2 as 事务102 (SELECT)

    T1->>Page: UPDATE users SET name='Bob' WHERE id=1
    Note over Page: 标记旧元组 xmax=101<br/>插入新元组 xmin=101, xmax=0

    T2->>Page: SELECT * FROM users WHERE id=1

    Note over Page,T2: 可见性检查:<br/>新元组 xmin=101 → 101已提交 ✓<br/>旧元组 xmax=101 → 101已提交 ✗<br/>→ 返回新元组 'Bob'

判定规则（简化版）：

Tuple 可见 ⇔ (xmin 已提交 AND xmin < 当前 txid) AND (xmax = 0 OR xmax 未提交 OR xmax > 当前 txid)

2.3 AutoVacuum（PG 特有）

由于更新=标记旧元组+插入新元组，旧元组成为"死元组"，必须清理：

AutoVacuum Worker:

1. 扫描表（已更新过的页）
2. 移除不再被任何事务可见的死元组
3. 更新 FREEZE 标记（防止 XID 回卷）
4. 更新统计信息（ANALYZE）
5. 回收磁盘空间（归还给 OS → 需 VACUUM FULL）

关键参数：

参数	说明
`autovacuum_max_workers`	并行 VACUUM 进程数
`autovacuum_naptime`	每次 VACUUM 间隔
`autovacuum_vacuum_scale_factor`	表变更比例阈值

2.4 XID 回卷问题

PG 使用 32 位递增事务 ID，约 21 亿个。当 XID 耗尽时：

XID 生命周期:
  正常 → 1亿 → 10亿 → 20亿 → wraparound 危险 → 强制 VACUUM FREEZE

超过 autovacuum_freeze_max_age（默认 2 亿）未冻结，强制触发 AUTO VACUUM，否则数据库会关闭。

三、查询优化器（Cost-Based Optimizer）

3.1 成本模型

PG 的优化器不是基于规则的（RBO），而是基于代价的（CBO）。它对每个可能的执行计划计算一个"代价"值，选择代价最小的。

mermaid

flowchart TB
    Query["SQL 查询"] --> Parser["语法解析<br/>→ 查询树"]
    Parser --> Rewrite["规则重写<br/>视图展开、行级安全"]
    Rewrite --> Optimizer["优化器<br/>枚举执行计划 → 估算代价"]
    Optimizer --> Executor["执行器<br/>返回结果"]

    subgraph CostModel["代价模型"]
        SeqCost["seq_page_cost = 1.0<br/>顺序页读取代价"]
        RandCost["random_page_cost = 4.0<br/>随机页读取代价<br/>SSD → 设 1.1~2.0"]
        CPUCost["cpu_tuple_cost = 0.01<br/>CPU处理每行代价"]
    end

    Optimizer --> CostModel

优化器决策依赖统计信息：

sql

-- 查看统计信息
SELECT * FROM pg_stats WHERE tablename = 'orders';

-- 关键统计列:
-- n_distinct: 唯一值数量（负数表示比例）
-- most_common_vals: 高频值列表
-- histogram_bounds: 直方图边界（等高分桶）
-- correlation: 物理存储顺序与列值顺序的相关性

3.2 执行计划解读（EXPLAIN）

sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT u.name, COUNT(*)
FROM users u JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2024-01-01'
GROUP BY u.name;

-- 解读关键指标:
-- Seq Scan: 全表顺序扫描 → 检查是否缺索引
-- Index Scan: 索引扫描 + 回表
-- Index Only Scan: 仅索引扫描（无需回表，但需 VM 标记可见）
-- Bitmap Heap Scan: 位图扫描（多个索引条件 AND/OR 合并）
-- Nested Loop: 嵌套循环 JOIN（适合小表驱动大表）
-- Hash Join: 哈希 JOIN（适合等值连接，需内存建哈希表）
-- Merge Join: 归并 JOIN（适合已排序数据）

EXPLAIN 各字段含义：

字段	说明
`cost=0.00..12.50`	启动代价..总代价（单位：seq_page_cost）
`rows=100`	预估返回行数（统计信息估算）
`width=36`	每行平均字节数
`actual time=0.012..0.015`	实际启动时间..总时间（ANALYZE 时）
`Buffers: shared hit=5`	缓存命中数（hit=命中, read=磁盘读）

3.3 索引选择失败排查

sql

-- 1. 统计信息过期 → 重建
ANALYZE orders;

-- 2. 随机页代价过高 → 调低 random_page_cost
ALTER SYSTEM SET random_page_cost = 1.5;
SELECT pg_reload_conf();

-- 3. 禁用特定扫描方式（临时）
SET enable_seqscan = off;  -- 强制走索引（仅调试！）

四、客户端工具与诊断

4.1 psql 常用命令

bash

# 连接
psql -h localhost -U postgres -d mydb

# 元命令（psql 独有）
\d               # 列出所有表
\d+ orders        # 表详情（含存储、描述）
\di+             # 列出所有索引（含大小）
\dx              # 列出扩展
\du              # 列出用户/角色
\l+              # 列出数据库（含大小）
\df              # 列出函数
\dv              # 列出视图
\dp              # 列出权限
\dt+             # 列出表+大小

# 查询执行时间
\timing on       # 开启计时

4.2 系统视图速查

sql

-- 锁等待分析（最重要的诊断视图）
SELECT
    blocked_locks.pid AS blocked_pid,
    blocking_locks.pid AS blocking_pid,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks
    ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
    AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
    AND blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page
    AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
    AND blocked_locks.virtualxid IS NOT DISTINCT FROM blocking_locks.virtualxid
    AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
    AND blocked_locks.pid != blocking_locks.pid
    AND NOT blocked_locks.granted
JOIN pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid
JOIN pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid;

-- 活跃查询
SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity WHERE state != 'idle';

-- 表大小排行
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

-- 索引使用情况
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes ORDER BY idx_scan DESC;

-- 死元组比例（判断是否需要 VACUUM）
SELECT relname,
       n_dead_tup,
       n_live_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 0 ORDER BY dead_ratio DESC;

4.3 慢查询分析

sql

-- 启用慢查询日志
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- 1秒以上记录
SELECT pg_reload_conf();

-- PG 13+ 使用 pg_stat_statements 扩展
CREATE EXTENSION pg_stat_statements;

-- Top 10 最耗时查询
SELECT queryid, calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       left(query, 80) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

五、索引

3.1 索引类型总览

类型	适用场景	特色
B-Tree	>, <, =, BETWEEN, ORDER BY	默认，最通用
Hash	等值查询 `=`	仅等值，WAL 不记录（10+）
GiST	全文搜索、几何数据、范围	通用搜索树框架
GIN	数组、JSON、全文搜索	倒排索引，写入慢查询快
BRIN	超大规模顺序数据	块范围索引，极小空间
SP-GiST	非平衡结构（IP/电话）	空间分区搜索树

3.2 B-Tree 与 MySQL 的差异

属性	MySQL InnoDB	PostgreSQL
聚簇	✅ 主键即聚簇，二级索引存主键	❌ 所有索引非聚簇，存 ctid(页号+行号)
回表	二级索引 → 主键 → 数据	ctid → 数据页精确位置（更快）
索引组织	主键有序存储，数据行插入有序	Heap 表，元组位置无要求

3.3 GIN 索引（全文/JSON/数组）

倒排索引结构:
行 → 分词 → GIN entry

文档1: "hello world"         GIN 树:
文档2: "hello postgresql"    hello → {1, 2}
                              world → {1}
                              postgresql → {2}

sql

-- JSON 快速查找
CREATE INDEX idx_gin ON users USING GIN (data jsonb_path_ops);

-- 全文搜索
CREATE INDEX idx_fts ON articles USING GIN (to_tsvector('english', content));

四、核心特性

4.1 窗口函数

sql

SELECT
    dept, name, salary,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rank,
    SUM(salary)  OVER (PARTITION BY dept) as dept_total,
    LAG(salary)  OVER (PARTITION BY dept ORDER BY salary) as prev_salary
FROM employees;

4.2 CTE 与递归查询

sql

WITH RECURSIVE org_tree AS (
    SELECT id, name, parent_id, 1 as level
    FROM org WHERE parent_id IS NULL
    UNION ALL
    SELECT o.id, o.name, o.parent_id, t.level + 1
    FROM org o
    JOIN org_tree t ON o.parent_id = t.id
)
SELECT * FROM org_tree ORDER BY level, id;

4.3 JSONB

sql

-- 建表
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- 查询
SELECT data->>'user' FROM events;
SELECT * FROM events WHERE data @> '{"type": "login"}';
SELECT * FROM events WHERE data->'tags' ? 'urgent';

-- 索引
CREATE INDEX idx_events_data ON events USING GIN (data);

4.4 分区表

sql

-- 声明式分区 (PG 10+)
CREATE TABLE orders (
    id SERIAL,
    created_at DATE NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024Q1
    PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

五、WAL 与复制

5.1 WAL 结构

WAL 文件:
pg_wal/000000010000000000000001  (16MB, 固定大小)

包含: INSERT/UPDATE/DELETE 的完整行数据
      (与 MySQL Redo Log 的页修改记录不同)

5.2 流复制

主库:
  WAL → WAL Sender → 网络

从库:
  WAL Receiver → WAL → Startup Process (回放)

参数	说明
`synchronous_commit`	`on`/`remote_apply`/`remote_write`/`off`
`wal_level`	`replica` (至少) 或 `logical`

5.3 逻辑复制（PG 10+）

与流复制不同，按表/数据库粒度的逻辑变化复制：

sql

CREATE PUBLICATION my_pub FOR TABLE users, orders;
CREATE SUBSCRIPTION my_sub CONNECTION '...' PUBLICATION my_pub;

六、PostgreSQL MVCC 深度 — 与 MySQL InnoDB 的根本差异

两者都实现了 MVCC，但实现方式完全不同。这直接影响 VACUUM 的必要性、表膨胀行为和存储开销：

mermaid

flowchart LR
    subgraph PG["PostgreSQL MVCC: 堆内多版本"]
        PG1["UPDATE 某行"]
        PG2["旧版本留在原表页中"]
        PG3["新版本 INSERT 为新行"]
        PG4["每行存 xmin/xmax (事务ID)"]
        PG5["可见性: 通过 xmin/xmax 和快照判断"]
        PG6["旧版本需要 VACUUM 清理!"]
        PG1 --> PG2 --> PG3 --> PG4 --> PG5 --> PG6
    end

    subgraph MySQL["MySQL InnoDB: Undo Log 多版本"]
        M1["UPDATE 某行"]
        M2["原地更新 + 旧版本写入 Undo Log"]
        M3["Undo Log 链: 通过 roll_ptr 回溯"]
        M4["Undo Log 有 Purge 线程自动清理"]
        M1 --> M2 --> M3 --> M4
    end

维度	PostgreSQL	MySQL InnoDB
旧版本存储	存于原表（堆内多版本）	存于 Undo Log（表外）
UPDATE 操作	INSERT 新行 + 标记旧行	原地更新 + Undo Log 记录旧值
表膨胀风险	❌ 严重（需 VACUUM）	✅ 较小（Undo 有 Purge 线程）
回滚效率	✅ 快（直接标记即可）	❌ 慢（需从 Undo Log 还原）
清理机制	VACUUM（手动/自动）	Purge Thread（后台自动）
事务 ID 回卷	❌ 有风险（需 VACUUM FREEZE）	❌ 无此问题

VACUUM 原理 — PG 运维的核心之一

因为 PostgreSQL 的 UPDATE 会在原表中留下"死亡元组"（dead tuples），VACUUM 的任务就是回收这些空间：

表行状态变换：
INSERT → 行插入 (xmin=当前事务ID)
DELETE → 行标记为删除 (xmax=当前事务ID)，但物理不删除！
UPDATE → xmax标记旧行 + INSERT新行（= DELETE+INSERT）

VACUUM 做的事：
1. 扫描表，找到所有 dead tuples（xmax < 所有活跃事务ID）
2. 标记这些空间为可复用（不还给 OS！）
3. 更新 visibility map（索引扫描时跳过已清理的页）
4. 更新 freeze 标记（防事务ID回卷）

VACUUM FULL:
  比普通 VACUUM 更激进 — 重写整个表 → 空间还给 OS
  但会锁表！生产环境慎用

VACUUM 调优指南：

sql

-- 查看表膨胀情况
SELECT schemaname, relname,
       n_dead_tup, n_live_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

参数	建议值	说明
`autovacuum_max_workers`	3-5	并行 VACUUM worker 数量
`autovacuum_naptime`	30s	每隔多久检查一次
`autovacuum_vacuum_scale_factor`	0.05-0.1	表行数变更 5-10% 时触发
`autovacuum_vacuum_cost_limit`	200→2000	增大以加快 VACUUM 速度
`vacuum_freeze_min_age`	50000000	建议保持默认
大表专用	表级设置	`ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor=0.01)`

PG 运维黄金法则：不要关闭 autovacuum！如果表膨胀严重，用 pg_repack 替代 VACUUM FULL（在线重组，不锁表）。事务 ID 回卷（wraparound）是 PG 的致命故障——超过 20 亿个事务后数据库会强制关闭，必须 VACUUM FREEZE 才能恢复。

七、MySQL vs PostgreSQL 决策矩阵

场景	推荐	原因
简单 CRUD、高并发读	MySQL	线程模型轻量、主从成熟
复杂查询、分析型	PG	优化器更好、窗口函数、CTE
JSON/文档存储	PG	JSONB 索引高效
全文搜索	PG	原生强大，无需 ES
地理/GIS 数据	PG	PostGIS 插件
超大规模写入	MySQL	InnoDB 稳定性
需要自定义函数/类型	PG	扩展性极强
云原生/k8s	MySQL	生态更成熟

PostgreSQL 深度解析

一、进程模型

1.1 多进程架构（与 MySQL 的区别）

1.2 核心后台进程

二、MVCC 实现（与 MySQL 的本质区别）

2.1 无 Undo Log，原地多版本

2.2 可见性判断

2.3 AutoVacuum（PG 特有）

2.4 XID 回卷问题

三、查询优化器（Cost-Based Optimizer）

3.1 成本模型

3.2 执行计划解读（EXPLAIN）

3.3 索引选择失败排查

四、客户端工具与诊断

4.1 psql 常用命令

4.2 系统视图速查

4.3 慢查询分析

五、索引

3.1 索引类型总览

3.2 B-Tree 与 MySQL 的差异

3.3 GIN 索引（全文/JSON/数组）

四、核心特性

4.1 窗口函数

4.2 CTE 与递归查询

4.3 JSONB

4.4 分区表

五、WAL 与复制

5.1 WAL 结构

5.2 流复制

5.3 逻辑复制（PG 10+）

六、PostgreSQL MVCC 深度 — 与 MySQL InnoDB 的根本差异

VACUUM 原理 — PG 运维的核心之一

七、MySQL vs PostgreSQL 决策矩阵

参考

💬 文章评论

PostgreSQL 深度解析 ​

一、进程模型 ​

1.1 多进程架构（与 MySQL 的区别） ​

1.2 核心后台进程 ​

二、MVCC 实现（与 MySQL 的本质区别） ​

2.1 无 Undo Log，原地多版本 ​

2.2 可见性判断 ​

2.3 AutoVacuum（PG 特有） ​

2.4 XID 回卷问题 ​

三、查询优化器（Cost-Based Optimizer） ​

3.1 成本模型 ​

3.2 执行计划解读（EXPLAIN） ​

3.3 索引选择失败排查 ​

四、客户端工具与诊断 ​

4.1 psql 常用命令 ​

4.2 系统视图速查 ​

4.3 慢查询分析 ​

五、索引 ​

3.1 索引类型总览 ​

3.2 B-Tree 与 MySQL 的差异 ​

3.3 GIN 索引（全文/JSON/数组） ​

四、核心特性 ​

4.1 窗口函数 ​

4.2 CTE 与递归查询 ​

4.3 JSONB ​

4.4 分区表 ​

五、WAL 与复制 ​

5.1 WAL 结构 ​

5.2 流复制 ​

5.3 逻辑复制（PG 10+） ​

六、PostgreSQL MVCC 深度 — 与 MySQL InnoDB 的根本差异 ​

VACUUM 原理 — PG 运维的核心之一 ​

七、MySQL vs PostgreSQL 决策矩阵 ​

参考 ​

💬 文章评论

PostgreSQL 深度解析

一、进程模型

1.1 多进程架构（与 MySQL 的区别）

1.2 核心后台进程

二、MVCC 实现（与 MySQL 的本质区别）

2.1 无 Undo Log，原地多版本

2.2 可见性判断

2.3 AutoVacuum（PG 特有）

2.4 XID 回卷问题

三、查询优化器（Cost-Based Optimizer）

3.1 成本模型

3.2 执行计划解读（EXPLAIN）

3.3 索引选择失败排查

四、客户端工具与诊断

4.1 psql 常用命令

4.2 系统视图速查

4.3 慢查询分析

五、索引

3.1 索引类型总览

3.2 B-Tree 与 MySQL 的差异

3.3 GIN 索引（全文/JSON/数组）

四、核心特性

4.1 窗口函数

4.2 CTE 与递归查询

4.3 JSONB

4.4 分区表

五、WAL 与复制

5.1 WAL 结构

5.2 流复制

5.3 逻辑复制（PG 10+）

六、PostgreSQL MVCC 深度 — 与 MySQL InnoDB 的根本差异

VACUUM 原理 — PG 运维的核心之一

七、MySQL vs PostgreSQL 决策矩阵

参考