这是《PostgreSQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 PostgreSQL 是功能强大的开源关系型数据库，以标准 SQL 支持完善、扩展能力强、数据类型丰富和内核工程严谨著称。它既能承担传统业务系统的事务存储，也能支持 JSONB、GIS、全文检索、时序和向量等扩展场景。
本书以 PostgreSQL 16 为主线，兼容 13-15 常见生产版本，从 SQL 和索引讲到 MVCC、WAL、VACUUM、复制、高可用和生产治理。
1.1 PostgreSQL 的定位
PostgreSQL 常被称为“最先进的开源关系型数据库”。它的特点是：
- SQL 标准支持完整；
- 事务和 MVCC 实现清晰；
- 数据类型丰富；
- 索引类型多；
- 扩展生态强；
- 逻辑复制和物理复制都支持；
- 适合复杂查询和数据治理。
常见使用场景：
| 场景 | 依赖能力 |
|---|---|
| 核心交易系统 | ACID、MVCC、WAL |
| 复杂报表 | 窗口函数、CTE、并行查询 |
| 多租户 SaaS | Schema、RLS、权限体系 |
| 地理信息系统 | PostGIS |
| 半结构化数据 | JSONB |
| 全文检索 | tsvector / GIN |
| 时序数据 | TimescaleDB 扩展 |
| 向量检索 | pgvector 扩展 |
1.2 进程与内存架构
PostgreSQL 是多进程架构。
Client
-> postgres backend process
-> Parser
-> Planner / Optimizer
-> Executor
-> Shared Buffer
-> WAL
-> Data Files
重要后台进程：
| 进程 | 职责 |
|---|---|
| postmaster | 监听连接，创建 backend 进程 |
| backend | 服务一个客户端连接 |
| background writer | 刷脏页 |
| checkpointer | 创建检查点 |
| WAL writer | 写 WAL 缓冲 |
| autovacuum launcher | 自动清理调度 |
| autovacuum worker | 执行 VACUUM / ANALYZE |
| logical replication | 逻辑复制 |
重要内存区域：
| 区域 | 说明 |
|---|---|
| shared_buffers | 共享数据页缓存 |
| work_mem | 排序、哈希等操作内存 |
| maintenance_work_mem | VACUUM、索引构建等维护内存 |
| wal_buffers | WAL 缓冲 |
| local memory | 每个 backend 私有内存 |
1.3 查询执行流程
一条 SQL 的处理流程：
Parse
-> Analyze
-> Rewrite
-> Plan
-> Execute
示例：
EXPLAIN ANALYZE
SELECT user_id, count(*) AS order_count, sum(pay_amount) AS gmv
FROM orders
WHERE created_at >= now() - interval '7 days'
GROUP BY user_id
ORDER BY gmv DESC
LIMIT 20;
PostgreSQL 的执行计划以树状结构展示：
Limit
-> Sort
-> HashAggregate
-> Seq Scan / Index Scan
常见节点：
| 节点 | 说明 |
|---|---|
| Seq Scan | 顺序扫描 |
| Index Scan | 索引扫描并回表 |
| Index Only Scan | 覆盖索引扫描 |
| Bitmap Heap Scan | 位图回表 |
| Nest Loop | 嵌套循环 |
| Hash Join | 哈希连接 |
| Merge Join | 归并连接 |
| HashAggregate | 哈希聚合 |
| Sort | 排序 |
1.4 MVCC 特点
PostgreSQL 使用多版本并发控制，读写互不阻塞。
每行保存：
| 字段 | 含义 |
|---|---|
| xmin | 创建该版本的事务 ID |
| xmax | 删除或锁定该版本的事务 ID |
查询根据快照判断版本可见性。
与 MySQL InnoDB 不同，PostgreSQL 的旧版本保存在表中，需要由 VACUUM 清理。
因此 PostgreSQL 生产运维必须关注：
- 死元组数量；
- 表膨胀；
- autovacuum 配置；
- 长事务；
- 复制槽堆积；
- 未使用 prepared statement；
- 高频更新表。
查看表统计：
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
1.5 WAL
WAL（Write-Ahead Logging）是 PostgreSQL 崩溃恢复和复制的基础。
核心原则：
修改数据页前，先写 WAL 日志
WAL 提供：
- 崩溃恢复；
- 时间点恢复；
- 流复制；
- 逻辑复制；
- 归档备份。
相关参数：
| 参数 | 说明 |
|---|---|
| wal_level | minimal / replica / logical |
| max_wal_size | WAL 上限控制 |
| min_wal_size | WAL 下限 |
| checkpoint_timeout | 检查点间隔 |
| synchronous_commit | 提交是否等待 WAL 刷盘 |
| archive_mode | 是否归档 |
| archive_command | 归档命令 |
1.6 数据类型示例
PostgreSQL 的类型系统是重要优势。
CREATE TABLE app_users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
profile JSONB NOT NULL DEFAULT '{}'::jsonb,
tags TEXT[] NOT NULL DEFAULT '{}',
last_login_at TIMESTAMPTZ,
location GEOGRAPHY(POINT, 4326)
);
JSONB 查询：
SELECT id, profile ->> 'city' AS city
FROM app_users
WHERE profile @> '{"level": "VIP"}';
数组查询：
SELECT id
FROM app_users
WHERE 'admin' = ANY(tags);
时间建议使用 TIMESTAMPTZ，明确保存带时区语义的时间。
1.7 与 MySQL 的差异
| 维度 | PostgreSQL | MySQL |
|---|---|---|
| 架构 | 多进程 | 多线程 |
| 存储引擎 | 统一存储引擎 | Server 层 + 插件引擎 |
| MVCC 旧版本 | 表内死元组，需 VACUUM | Undo 版本链 |
| DDL | 事务性较好 | 8.0 原子 DDL，细节不同 |
| 复制 | 物理和逻辑复制成熟 | Binlog 复制生态广 |
| 扩展 | 扩展生态强 | 插件生态不同 |
| 复杂 SQL | 强 | 持续增强 |
| 高并发热点更新 | 需要治理表膨胀 | 行锁和热点治理成熟 |
选择建议：
- 复杂 SQL、GIS、JSONB、多租户 RLS 优先考虑 PostgreSQL；
- 高并发互联网交易和团队生态成熟时 MySQL 常见；
- 不要只用“性能排名”选型，要看查询模型、运维能力和团队经验；
- 迁移成本包括 SQL、驱动、备份、监控、HA 和团队技能。
1.8 生产红线
- 不允许长事务无限持有快照；
- 不允许关闭 autovacuum；
- 不允许忽略表膨胀；
- 不允许复制槽失效后无人处理；
- 不允许没有 WAL 归档监控；
- 不允许无备份演练；
- 不允许超权限用户连接业务；
- 不允许在主库跑无限制分析查询；
- 不允许随意
VACUUM FULL高峰表； - 不允许没有连接池。
本章小结
PostgreSQL 是功能完备的关系型数据库，采用多进程架构、MVCC、WAL 和扩展机制。它支持丰富数据类型和多种索引，适合复杂业务和数据密集场景。生产使用时必须重点治理死元组、长事务、连接数、WAL 和备份恢复。
思考题
- PostgreSQL 的 backend 进程和共享内存分别承担什么职责？
- PostgreSQL MVCC 与 MySQL InnoDB 的旧版本存储方式有什么不同？
- 为什么长事务会导致表膨胀？
- WAL 支撑哪些能力？
- 什么业务更适合 PostgreSQL 而不是 MySQL？