数据库02：PostgreSQL
从 MVCC 实现、JSON 支持、CTE 查询到扩展生态，理解 PostgreSQL 为什么适合复杂业务场景和数据仓库。
PostgreSQL（简称 PG）是功能最全面的开源关系型数据库。如果你的业务需要复杂查询、JSON 数据、全文搜索、地理信息等高级特性，PG 可能比 MySQL 更合适。
这篇文章围绕四个核心点：MVCC 实现差异、JSON 支持、CTE 和窗口函数、扩展生态，把 PG 的设计逻辑和适用场景讲清楚。
PostgreSQL vs MySQL：核心差异
选择 PG 还是 MySQL，本质是”功能丰富度”和”生态成熟度”的权衡。
| 维度 | PostgreSQL | MySQL |
|---|---|---|
| MVCC | 快照隔离，无 Gap Lock | Repeatable Read，有 Gap Lock |
| JSON 支持 | 原生 JSONB，支持索引 | JSON 函数，无原生索引 |
| 全文搜索 | 内置 tsvector/tsquery | 需要 Elasticsearch 配合 |
| 地理信息 | PostGIS 扩展，功能强大 | 有限支持 |
| CTE / 窗口函数 | 完整支持，递归查询 | 部分支持 |
| 扩展性 | 插件化，自定义类型/函数 | 存储引擎可替换 |
| 生态 | 偏企业级，分析场景 | 互联网标配，运维成熟 |
简单判断：需要复杂查询、JSON 处理、数据分析 → 选 PG。需要成熟运维生态、读写分离 → 选 MySQL。两者可以互补，PG 做分析和复杂业务，MySQL 做在线事务。
MVCC：快照隔离 vs Repeatable Read
PG 的 MVCC 实现和 MySQL 有本质区别，这直接影响了并发控制行为。
PostgreSQL 的快照隔离
PG 使用 Snapshot Isolation（快照隔离），每个事务看到的是事务开始时的数据库快照。
关键特点：
- 没有 Gap Lock，不会锁住不存在的行
- 写冲突检测：两个事务修改同一行时，后提交的会失败
- 避免了幻读，但可能出现”写偏斜”（Write Skew）
MySQL 的 Repeatable Read
MySQL 使用 Repeatable Read + Gap Lock，通过锁住间隙来防止幻读。
关键特点：
- Gap Lock 会锁住范围，可能导致死锁
- 写操作不会因为冲突而失败，而是等待锁
- 避免了幻读和写偏斜，但并发度较低
选择建议
- 高并发写入、需要避免写冲突 → MySQL（锁等待比失败重试更友好）
- 复杂查询、分析场景、读多写少 → PG（快照隔离并发度更高）
JSON 支持：JSONB 为什么比 JSON 好
PG 的 JSON 支持是它最吸引人的特性之一，特别适合需要存储半结构化数据的场景。
JSON vs JSONB
| 类型 | 存储方式 | 查询性能 | 索引 | 适用场景 |
|---|---|---|---|---|
| JSON | 文本存储 | 慢（需解析） | 不支持 | 日志、配置 |
| JSONB | 二进制存储 | 快（直接操作） | GIN/GIST | 查询、索引、分析 |
JSONB 查询示例
-- 查询 JSONB 字段中的某个键
SELECT data->>'name' FROM users;
-- 路径查询
SELECT data#>'{address,city}' FROM users;
-- 条件查询
SELECT * FROM users WHERE data @> '{"age": 30}';
-- 创建 GIN 索引
CREATE INDEX idx_users_data ON users USING GIN (data);适用场景
- 用户配置、偏好设置
- 日志数据、事件记录
- 文档管理、内容存储
- API 返回数据缓存
CTE 和窗口函数：复杂查询的利器
PG 对复杂 SQL 的支持比 MySQL 更完善，特别是 CTE（Common Table Expression）和窗口函数。
CTE：可复用的子查询
WITH recent_orders AS (
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days'
)
SELECT
u.name,
COUNT(ro.id) as order_count,
SUM(ro.amount) as total_amount
FROM users u
LEFT JOIN recent_orders ro ON u.id = ro.user_id
GROUP BY u.id, u.name;CTE 的优势：
- 可读性更好，逻辑分层
- 支持递归查询（处理树形结构）
- 优化器可以更好地处理复杂查询
窗口函数：行级聚合
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
AVG(salary) OVER (PARTITION BY department) as avg_salary,
LAG(salary) OVER (ORDER BY salary) as prev_salary
FROM employees;常用窗口函数：
RANK()/ROW_NUMBER()：排名LAG()/LEAD()：前/后行数据SUM() OVER()/AVG() OVER()：滚动聚合FIRST_VALUE()/LAST_VALUE()：首尾值
扩展生态：按需添加功能
PG 的插件化设计让它可以按需扩展，这是它最大的优势之一。
常用扩展
| 扩展 | 功能 | 适用场景 |
|---|---|---|
| PostGIS | 地理信息处理 | 地图应用、位置服务 |
| pg_trgm | 模糊查询 | 搜索、拼写检查 |
| hstore | key-value 存储 | 配置、标签 |
| uuid-ossp | UUID 生成 | 分布式 ID |
| pg_stat_statements | 性能统计 | 慢查询分析 |
扩展安装示例
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- 创建索引
CREATE INDEX idx_name_trgm ON users USING gin (name gin_trgm_ops);全文搜索：内置搜索引擎
PG 内置了全文搜索功能，可以满足大部分搜索需求，不需要额外部署 Elasticsearch。
-- 创建全文索引
ALTER TABLE articles ADD COLUMN search_vector tsvector;
UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || content);
-- 查询
SELECT * FROM articles WHERE search_vector @@ to_tsquery('database & performance');
-- 自动更新索引
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON articles FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger(
search_vector, 'pg_catalog.english', title, content
);锁机制：并发控制的基石
PG 的锁机制和 MySQL 有很大不同，这源于它的 MVCC 实现方式。理解 PG 的锁机制，才能避免并发问题和性能瓶颈。
锁的分类
PG 支持多种锁类型，按粒度分为表级锁和行级锁。
表级锁：
| 锁模式 | 说明 | 阻塞 |
|---|---|---|
ACCESS SHARE | 读锁，SELECT 自动获取 | 不阻塞任何锁 |
ROW SHARE | 行共享锁，SELECT FOR UPDATE 自动获取 | 不阻塞读，阻塞 EXCLUSIVE |
ROW EXCLUSIVE | 行排他锁，INSERT/UPDATE/DELETE 自动获取 | 阻塞 EXCLUSIVE 和 SHARE UPDATE EXCLUSIVE |
SHARE UPDATE EXCLUSIVE | 共享更新排他锁，VACUUM/ANALYZE 自动获取 | 阻塞大部分写操作 |
SHARE | 共享锁，CREATE INDEX 自动获取 | 阻塞写操作 |
SHARE ROW EXCLUSIVE | 共享行排他锁 | 阻塞大部分写操作 |
EXCLUSIVE | 排他锁，ALTER TABLE/DROP TABLE 自动获取 | 阻塞所有操作 |
ACCESS EXCLUSIVE | 最高级排他锁，TRUNCATE/REINDEX 自动获取 | 阻塞所有操作 |
行级锁：
| 锁模式 | 说明 | 适用场景 |
|---|---|---|
FOR UPDATE | 排他行锁，阻止其他事务修改或加锁 | 需要更新数据 |
FOR NO KEY UPDATE | 非主键更新锁，允许其他事务加 KEY SHARE 锁 | 更新非主键字段 |
FOR SHARE | 共享行锁，允许其他事务读但阻止修改 | 需要读数据但不修改 |
FOR KEY SHARE | 键共享锁，允许其他事务加 NO KEY UPDATE 锁 | 需要读主键字段 |
锁的兼容性
PG 的锁兼容性矩阵决定了两个事务能否同时持有同一对象上的锁：
| 请求锁模式 | ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE |
|---|---|---|---|---|---|---|---|---|
| ACCESS SHARE | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✗ |
| ROW SHARE | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✗ | ✗ |
| ROW EXCLUSIVE | ✓ | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ | ✗ |
| SHARE UPDATE EXCLUSIVE | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ |
| SHARE | ✓ | ✓ | ✗ | ✗ | ✓ | ✗ | ✗ | ✗ |
| SHARE ROW EXCLUSIVE | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ |
| EXCLUSIVE | ✓ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ |
| ACCESS EXCLUSIVE | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ |
行级锁的实现
PG 的行级锁和 MySQL 有本质区别：
MySQL InnoDB：
- 使用 Next-Key Lock（行锁 + 间隙锁），通过锁机制防止幻读
- 锁存在于索引上，如果没有索引会退化为表锁
- 两个事务改同一行时，后到的阻塞等待
PostgreSQL：
- 没有 Gap Lock，依赖 SI（快照隔离）天然防止幻读
- 锁存在于元组（行）上，不是索引上
- 两个事务改同一行时，后提交的会收到
ERROR: could not serialize access due to concurrent update
PG 行级锁的特点：
- 锁的粒度：PG 的行级锁是真正的行级，不会锁住间隙
- 锁的获取时机：INSERT/UPDATE/DELETE 自动获取行级锁，SELECT 需要显式加锁
- 锁的释放：事务结束时自动释放所有锁
- 锁的冲突检测：通过 xmax 字段检测写冲突，后提交的事务失败
死锁处理
PG 和 MySQL 都有死锁检测机制，但实现方式不同。
死锁检测：
PG 有一个专门的死锁检测进程，每隔 deadlock_timeout（默认 1 秒）检测一次死锁。如果发现死锁，会选择持有锁最少的事务回滚，释放锁。
避免死锁的方法：
- 按固定顺序访问资源：多个事务按相同顺序访问表和行
- 缩短事务长度：减少持有锁的时间
- 使用乐观锁：通过版本号或时间戳避免锁竞争
- 设置合理的超时：
lock_timeout控制单个锁的等待时间
锁等待查询
查看当前锁等待情况：
-- 查看锁等待
SELECT
pid,
usename,
relation::regclass,
mode,
granted,
query
FROM pg_locks
WHERE NOT granted;
-- 查看所有锁
SELECT
pid,
usename,
relation::regclass,
mode,
granted
FROM pg_locks
WHERE relation IS NOT NULL;
-- 查看阻塞关系
SELECT
blocking.pid AS blocking_pid,
blocking.query AS blocking_query,
blocked.pid AS blocked_pid,
blocked.query AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_lock ON blocked.pid = blocked_lock.pid
JOIN pg_locks blocking_lock ON (
blocking_lock.locktype = blocked_lock.locktype AND
blocking_lock.database IS NOT DISTINCT FROM blocked_lock.database AND
blocking_lock.relation IS NOT DISTINCT FROM blocked_lock.relation AND
blocking_lock.page = blocked_lock.page AND
blocking_lock.tuple = blocked_lock.tuple AND
blocking_lock.virtualxid = blocked_lock.virtualxid AND
blocking_lock.transactionid = blocked_lock.transactionid AND
blocking_lock.classid = blocked_lock.classid AND
blocking_lock.objid = blocked_lock.objid AND
blocking_lock.objsubid = blocked_lock.objsubid AND
blocking_lock.pid != blocked_lock.pid
)
JOIN pg_stat_activity blocking ON blocking.pid = blocking_lock.pid
WHERE NOT blocked_lock.granted AND blocking_lock.granted;锁相关配置
| 参数 | 默认值 | 说明 |
|---|---|---|
deadlock_timeout | 1s | 死锁检测间隔 |
lock_timeout | 0（无限制） | 单个锁的等待时间 |
statement_timeout | 0（无限制） | 语句执行超时时间 |
idle_in_transaction_session_timeout | 0（无限制） | 事务中空闲超时时间 |
PG 锁机制总结
| 维度 | PostgreSQL | MySQL InnoDB |
|---|---|---|
| 行级锁粒度 | 真正行级，无间隙锁 | Next-Key Lock（行 + 间隙） |
| 锁存储位置 | 元组上 | 索引上 |
| 写冲突处理 | First-Updater-Wins（后提交失败） | 锁等待（后到的阻塞） |
| 幻读处理 | SI 天然避免 | Gap Lock 避免 |
| 死锁检测 | 定期检测（1 秒间隔） | 即时检测 |
| 锁等待超时 | lock_timeout | innodb_lock_wait_timeout |
| 长事务影响 | 表膨胀（旧版本无法回收） | Undo Log 增长 |
PG 的锁机制更轻量，适合读多写少、写冲突不频繁的场景。如果写冲突频繁，需要应用层处理重试逻辑。
项目判断清单
- 需要复杂查询、窗口函数、CTE → 选 PG
- 需要 JSON 存储和查询 → 选 PG（JSONB）
- 需要全文搜索但不想部署 ES → 选 PG
- 需要地理信息处理 → 选 PG（PostGIS）
- 互联网业务，读写分离成熟度优先 → 选 MySQL
- 分析场景，数据仓库 → 选 PG 或 ClickHouse