PostgreSQL（PG）
PostgreSQL（常简称 PG）是开源关系型数据库，标准 SQL 支持好、扩展能力强，很多团队当 OLTP 主库或分析型辅库。通用数据库概念见 数据库；本篇专注 PG 架构、MVCC 与面试高频点。
PG 解决什么
| 痛点 | PG 怎么帮 |
|---|---|
| 复杂查询、JOIN、约束 | 成熟 SQL 与优化器 |
| 半结构化数据 | JSONB 类型 + GIN 索引 |
| 扩展类型与逻辑 | 扩展（PostGIS、pgvector 等） |
| 强一致事务 | ACID + MVCC 并发控制 |
和 MySQL 都是 OLTP 常见选择；选型看团队栈、云托管、生态与运维习惯，没有绝对谁更好。
核心架构（直觉）
Client → 连接池(pgbouncer) → Postmaster
├─ Backend 进程（每连接/池化后每会话）
├─ Shared Buffers（共享缓冲）
├─ WAL（预写日志，崩溃恢复）
└─ 数据文件（表空间 / 段文件）
| 组件 | 作用 |
|---|---|
| Shared Buffers | 共享内存缓存数据页 |
| WAL | 先写日志再刷数据页；支持 PITR 与流复制 |
| Checkpoint | 定期把脏页落盘，缩短恢复时间 |
| Autovacuum | 回收 MVCC 死元组，防表膨胀 |
MVCC（面试高频）
PG 用 多版本并发控制：更新不原地覆盖，而是插入新版本；旧版本对未提交/旧快照可见。
| 现象 | PG 里怎么理解 |
|---|---|
| 读不阻塞写 | 读者看快照，不锁写者 |
| 写不阻塞读 | 写者产生新版本 |
| 死元组 | 旧版本需 VACUUM 回收 |
| 长事务 | 阻止 vacuum → 表膨胀、xid 回卷风险 |
默认隔离级别是 Read Committed（语句级快照）；可设为 Repeatable Read / Serializable。
索引
| 类型 | 适用 |
|---|---|
| B-tree | 默认；等值、范围、ORDER BY |
| Hash | 仅等值（场景少） |
| GIN | JSONB、数组、全文检索 |
| GiST | 几何、范围、相似度 |
| BRIN | 超大表、物理顺序相关（时序） |
-- 覆盖索引：INCLUDE 把非键列带进叶子，减少回表（PG 11+）
CREATE INDEX idx_orders_user ON orders(user_id) INCLUDE (amount, status);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 1;
面试：最左前缀主要谈 B-tree；JSONB 查具体 key 用 jsonb_path_ops 或表达式索引。
JSONB
| 点 | 说明 |
|---|---|
| 存储 | 二进制 JSON，支持索引 |
| 查询 | -> / ->>、@>、? 等操作符 |
| 场景 | 配置、元数据、schema 演进快的字段 |
| 注意 | 别把整个业务实体都塞 JSONB 逃避建模 |
复制与高可用
| 方式 | 说明 |
|---|---|
| 流复制（Streaming） | 主库 WAL 实时推从库；异步 / 同步（synchronous_commit） |
| 逻辑复制 | 表级变更流；跨版本、部分表同步 |
| 读从库 | 复制延迟 → 「写后读从」可能 stale |
| Failover | Patroni、云 RDS 自动切换等 |
连接与连接池
PG 一个 Backend 进程对应一个连接（较重），高并发常前置 PgBouncer：
| 模式 | 说明 |
|---|---|
| Session | 连接与会话绑定，兼容最好 |
| Transaction | 事务结束归还连接，池化效率高 |
| Statement | 更激进，限制多 |
应用侧仍用 HikariCP 等；连接数不是越多越好。
性能与排错
| 问题 | 方向 |
|---|---|
| 慢查询 | EXPLAIN (ANALYZE, BUFFERS)；看 Seq Scan、Nested Loop |
| 锁等待 | pg_locks、pg_stat_activity |
| 表膨胀 | pg_stat_user_tables；autovacuum 是否跟得上 |
| 大表分页 | OFFSET 深翻页慢 → keyset pagination（WHERE id > $last） |
| N+1 | JOIN / WHERE id = ANY($1) 批量 |
与 MySQL 对比（简表）
| 维度 | PostgreSQL | MySQL InnoDB |
|---|---|---|
| 默认隔离 | Read Committed | Repeatable Read |
| 并发模型 | MVCC 为主 | MVCC + 间隙锁等 |
| JSON | JSONB 索引成熟 | JSON 类型（8.0+ 增强） |
| 扩展 | 扩展生态强 | 插件/变种多 |
| 常见场景 | 复杂查询、GIS、向量 | 互联网 OLTP 极广 |
常见误解
- 「PG 不用 vacuum」——MVCC 必须回收死元组
- 「索引越多越好」——写放大、优化器选错、维护成本
- 「从库读一定实时」——流复制有延迟
- 「JSONB 替代所有列」——关系与约束仍应在列上建模
面试速记
- MVCC 是什么？ 多版本；读写少阻塞；靠 vacuum 回收旧版本。
- WAL 作用？ 预写日志，崩溃恢复与复制基础。
- JSONB 和 JSON？ JSONB 二进制、可索引，查询更快。
- 为什么用 PgBouncer？ PG 连接重；池化降 Backend 数。
- 慢查询怎么查？
EXPLAIN ANALYZE；索引、统计信息、锁、vacuum 状态。