learnNote

主题

PostgreSQL 学习计划 ​

📋 学习目标 ​

系统掌握 PostgreSQL 这款「世界上最先进的开源对象-关系数据库」，从基础 SQL 到 MVCC / WAL / VACUUM 内核原理，再到流复制、分区、扩展生态，最终达到：

  • 写得出：熟练使用 PG 特色 SQL（CTE、窗口函数、RETURNING、ON CONFLICT、JSONB 操作符）；
  • 看得懂：能读懂 EXPLAIN (ANALYZE, BUFFERS)，能从 pg_stat_* 视图诊断慢查询；
  • 讲得清：用生活化类比把 MVCC、WAL、Checkpoint、流复制说清楚；
  • 答得上：覆盖大厂 DBA / 后端面试 PG 高频考点（与 MySQL 对比、SSI、HOT、XID 回卷、pgvector 等）。

完成本计划后，你应能独立设计中等规模业务的 PG 库表，定位 90% 常见慢查询，并部署主从流复制 + PITR 的高可用方案。

第一阶段：入门启航（第 1 周） ​

Ch 1 ~ 2。先建立全局认知，再把 psql 玩熟。

1.1 PG 是什么 & 为什么火（Ch1） ​

  • [ ] 关系型数据库简史，PG 与 MySQL/Oracle 的定位区别
  • [ ] PG「对象-关系」设计哲学：扩展性、标准 SQL、ACID 极致正确
  • [ ] 核心特色：JSONB、数组、窗口函数、CTE、扩展（PostGIS / pgvector）
  • [ ] 为什么大厂越来越多迁移到 PG（成本、生态、Postgres-on-Cloud）
  • [ ] 版本演进与 PG 17 新特性概览

1.2 psql 与基础 SQL（Ch2） ​

  • [ ] 连接数据库：psql -h 127.0.0.1 -U postgres -d learn_pg
  • [ ] 三级结构：Cluster / Database / Schema / Table
  • [ ] psql 元命令：\l \c \dn \dt \d \df \du \timing \x
  • [ ] DDL/DML/DCL 基础语法，配合 RETURNING
  • [ ] 编码规范：大写关键字 + 小写标识符

练习任务：

  1. 新建 bookshop schema 与 books 表（含 tags TEXT[]、meta JSONB），用 INSERT ... RETURNING id 插入数据。
  2. 用 \timing 对比 SELECT * 与按需查列的耗时差异。

第二阶段：SQL 精通（第 2 ~ 3 周） ​

Ch 3 ~ 5。把「类型系统 + 高级查询」吃透——这是 PG 区别于 MySQL 的最大优势。

2.1 数据类型详解（Ch3） ​

  • [ ] 数值：smallint / integer / bigint / numeric / real / double precision
  • [ ] 字符串：varchar(n) / text / char(n)，PG 为什么推荐 text
  • [ ] 时间：date / timestamp / timestamptz / interval，时区坑
  • [ ] 布尔、UUID（gen_random_uuid()）
  • [ ] 数组类型：int[] / text[]、ANY / ALL 操作符
  • [ ] JSON vs JSONB：-> ->> @> ? 操作符、GIN 索引配合
  • [ ] 范围类型 tstzrange + 排他约束 EXCLUDE
  • [ ] 枚举与自定义复合类型 CREATE TYPE

2.2 约束、视图与序列（Ch4） ​

  • [ ] 主键 / 外键 / 唯一 / 检查 / 排他约束
  • [ ] SERIAL vs GENERATED ALWAYS AS IDENTITY（PG 10+ 推荐后者）
  • [ ] 普通视图 vs 物化视图 MATERIALIZED VIEW、REFRESH CONCURRENTLY
  • [ ] 序列 SEQUENCE：nextval / currval / setval

2.3 高级查询（Ch5） ​

  • [ ] JOIN 全家桶：INNER / LEFT / RIGHT / FULL / CROSS / LATERAL
  • [ ] 子查询、相关子查询、EXISTS / IN 性能对比
  • [ ] CTE 与递归 CTE（树形遍历）
  • [ ] 窗口函数：ROW_NUMBER / RANK / LAG / LEAD / SUM() OVER (...)
  • [ ] GROUPING SETS / ROLLUP / CUBE
  • [ ] upsert：INSERT ... ON CONFLICT (col) DO UPDATE SET ...
  • [ ] RETURNING 在 DML 中的妙用

练习任务：

  1. 设计博客评论表（id, parent_id, content），用递归 CTE 查询某根评论的子树。
  2. 写一条 INSERT ... ON CONFLICT 实现库存按 SKU 原子 upsert。
  3. 用 ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) 找出各分类 Top 3。

第三阶段：索引与事务（第 4 ~ 5 周） ​

Ch 6 ~ 8。PG 索引种类比 MySQL 丰富得多，事务模型也是面试重灾区。

3.1 索引体系（Ch6） ​

  • [ ] 6 种索引结构对比：B-Tree / Hash / GIN / GiST / BRIN / SP-GiST
  • [ ] GIN：JSONB / 数组 / 全文检索；GiST：地理 / 范围 / pg_trgm
  • [ ] BRIN：时序大表追加场景的「白菜价」索引
  • [ ] 部分索引 WHERE、表达式索引、覆盖索引 INCLUDE、CREATE INDEX CONCURRENTLY
  • [ ] EXPLAIN (ANALYZE, BUFFERS) 解读：Seq Scan / Index Scan / Bitmap Heap Scan
  • [ ] PG 没有「聚簇索引」，堆表 + 索引分离；CLUSTER 命令的真正含义

3.2 事务与隔离级别（Ch7） ​

  • [ ] 四种隔离级别在 PG 的真实行为（READ UNCOMMITTED ≡ RC）
  • [ ] PG 默认 RC 而不是 RR 的原因
  • [ ] SSI（Serializable Snapshot Isolation）：怎么在快照基础上实现真·SERIALIZABLE
  • [ ] SAVEPOINT 与子事务
  • [ ] 显式行锁：FOR UPDATE / FOR SHARE / SKIP LOCKED / NOWAIT

3.3 MVCC 与 VACUUM（Ch8） ​

  • [ ] 元组隐藏列：xmin / xmax / cmin / cmax / ctid
  • [ ] MVCC 可见性判断（类比：每个元组都有「出生证 + 死亡证」）
  • [ ] HOT 更新（Heap-Only Tuple）：触发条件、对索引膨胀的影响
  • [ ] 死元组（Dead Tuple）与堆膨胀
  • [ ] VACUUM / VACUUM FULL / autovacuum 工作机制
  • [ ] 事务 ID 回卷（XID Wraparound） 与防冻结
  • [ ] pg_stat_user_tables 监控膨胀

练习任务：

  1. 100 万行订单表上分别建 B-Tree / GIN（JSONB）/ BRIN（时间），用 EXPLAIN ANALYZE 对比性能。
  2. 双会话模拟 RC vs RR 的不可重复读差异；再用 SERIALIZABLE 跑冲突转账，观察 could not serialize access 报错。
  3. 反复 UPDATE 同一行后查 n_dead_tup，手动 VACUUM 观察回收效果。

第四阶段：内核原理（第 6 ~ 7 周） ​

Ch 9 ~ 11。PG 的「内功心法」，也是大厂面试拉分项。

4.1 存储与物理结构（Ch9） ​

  • [ ] PGDATA 目录结构：base / global / pg_wal / pg_xact
  • [ ] 页（8KB）/ 元组 / 行指针的物理布局
  • [ ] TOAST：>2KB 字段如何切片压缩、四种 TOAST 策略
  • [ ] FSM（Free Space Map）与 VM（Visibility Map）
  • [ ] 堆表 vs 索引文件分离的设计权衡（对比 InnoDB 索引组织表）

4.2 WAL 与 Checkpoint（Ch10） ​

  • [ ] WAL 的作用：原子性 + 持久性 + 复制基础
  • [ ] LSN（Log Sequence Number）含义与用法
  • [ ] wal_level：minimal / replica / logical 区别
  • [ ] 两阶段写：先写 WAL（fsync）再写数据页
  • [ ] Checkpoint 触发：checkpoint_timeout / max_wal_size
  • [ ] 崩溃恢复流程：从最近 Checkpoint 重放 WAL
  • [ ] full_page_writes 防 torn page

4.3 锁机制（Ch11） ​

  • [ ] 8 种表级锁及兼容矩阵
  • [ ] 行级锁 4 种：FOR UPDATE / FOR NO KEY UPDATE / FOR SHARE / FOR KEY SHARE
  • [ ] 咨询锁 pg_advisory_lock 实现应用级互斥
  • [ ] 死锁自动检测与 abort
  • [ ] pg_locks + pg_stat_activity 联表排查锁等待

练习任务：

  1. 用 EXPLAIN (ANALYZE, BUFFERS) 分析 JOIN 查询，估算 Buffer 命中率。
  2. pg_basebackup 后 kill -9 主进程，观察崩溃恢复日志中的「redo done」。
  3. 构造死锁场景，观察 PG 自动检测 + 回滚日志。

第五阶段：编程与运维（第 8 ~ 9 周） ​

Ch 12 ~ 15。从「用」走向「管」，DBA 必备。

5.1 服务端编程（Ch12） ​

  • [ ] PL/pgSQL 函数 vs 存储过程
  • [ ] 触发器：BEFORE / AFTER / INSTEAD OF、行级 vs 语句级
  • [ ] 事件触发器（Event Trigger）做 DDL 审计
  • [ ] LISTEN / NOTIFY 轻量消息总线

5.2 权限与安全（Ch13） ​

  • [ ] 角色体系：ROLE / USER / GROUP 本质统一
  • [ ] GRANT / REVOKE 多粒度（库 / Schema / 表 / 列）
  • [ ] 行级安全 RLS：多租户场景必备
  • [ ] pg_hba.conf：trust / md5 / scram-sha-256 / cert

5.3 备份与恢复（Ch14） ​

  • [ ] 逻辑备份：pg_dump / pg_restore，自定义格式 -Fc
  • [ ] 物理备份：pg_basebackup
  • [ ] PITR：archive_mode + restore_command + recovery_target_time
  • [ ] 备份策略：全量 + WAL 归档 + 定期校验

5.4 复制与高可用（Ch15） ​

  • [ ] 流复制原理：WAL Sender → WAL Receiver
  • [ ] 同步模式：async / on / remote_write / remote_apply / quorum
  • [ ] 热备 hot standby、级联复制
  • [ ] 逻辑复制 publication / subscription：跨大版本升级
  • [ ] HA 工具：Patroni / repmgr / pg_auto_failover

练习任务：

  1. 写 PL/pgSQL 触发器维护「订单总金额」冗余字段。
  2. 搭建一主一从流复制，制造主库压力后观察 pg_stat_replication.replay_lag。
  3. 完整演练 PITR：全备 → WAL 归档 → 模拟误删表 → 恢复到删除前 1 秒。

第六阶段：扩展与实战（第 10 ~ 11 周） ​

Ch 16 ~ 19。展示 PG「最强生态」的一面，用综合项目串联所有知识点。

6.1 分区与分库分表（Ch16） ​

  • [ ] 声明式分区：PARTITION BY RANGE / LIST / HASH
  • [ ] 分区裁剪（partition pruning）与执行计划
  • [ ] ATTACH / DETACH PARTITION 冷热数据管理
  • [ ] 横向扩展：Citus / pg_pathman 简介

6.2 性能调优（Ch17） ​

  • [ ] 关键参数：shared_buffers（约 25% 内存）/ work_mem / effective_cache_size
  • [ ] pg_stat_statements 找耗时 SQL Top N
  • [ ] auto_explain 自动记录慢查询计划
  • [ ] Hash Join vs Merge Join vs Nested Loop 选择逻辑
  • [ ] ANALYZE 与 pg_statistic 对执行计划的影响

6.3 常用扩展生态（Ch18） ​

  • [ ] PostGIS：地理位置 + GiST 空间索引
  • [ ] pgvector：向量检索（AI / RAG 必备）
  • [ ] TimescaleDB：时序自动分区 + 连续聚合
  • [ ] pg_cron：库内定时任务
  • [ ] CREATE EXTENSION 机制

6.4 综合实战项目（Ch19） ​

  • [ ] 任选其一贯穿全栈：电商订单 / 博客全文检索 / 附近商家 / RAG 向量库
  • [ ] 库表设计 → 索引规划 → 慢查询优化 → 主从部署 → 监控接入

练习任务：

  1. 把订单表按 created_at 做 RANGE 月分区，灌 1000 万行后对比分区前后 EXPLAIN。
  2. 装 pg_stat_statements，找出 Top 5 慢 SQL 并提出优化方案。
  3. 用 pgvector 搭最小 RAG demo：100 篇文章向量化入库，<=> 余弦检索。

📚 推荐学习资源 ​

官方文档 ​

书籍 ​

  • 《PostgreSQL 修炼之道》（第 2 版）— 唐成：中文最系统的 PG 入门 + 进阶
  • 《PostgreSQL 技术内幕：查询优化深度探索》— 张树杰：源码讲优化器
  • 《The Internals of PostgreSQL》— Hironobu Suzuki：英文免费电子书，图文讲存储 / MVCC / WAL
  • 《PostgreSQL 实战》— 谭峰、张文升：贴近生产的运维实战

在线资源 ​

实用工具 ​

  • psql / pgcli — 官方 CLI 与增强版（语法高亮 + 自动补全）
  • pgAdmin 4 / DBeaver — Web / 桌面 GUI 管理工具
  • pg_top — 类 top 的 PG 实时监控
  • pgbadger — 慢查询日志可视化分析
  • pg_stat_statements — 内置扩展，找慢 SQL 必备
  • explain.depesz.com / explain.dalibo.com — 在线 EXPLAIN 计划可视化

⏱ 学习时间建议 ​

阶段章节内容时间重点
一Ch 1-2入门启航1 周psql 用熟、PG 三级结构
二Ch 3-5SQL 精通2 周JSONB / 数组 / CTE / 窗口函数 / upsert
三Ch 6-8索引与事务2 周索引选型、SSI、MVCC 与 VACUUM
四Ch 9-11内核原理2 周存储页、WAL/Checkpoint、锁与死锁
五Ch 12-15编程与运维2 周PL/pgSQL、RLS、备份恢复、流复制
六Ch 16-19扩展与实战2 周分区、调参、PostGIS / pgvector、综合项目

总计约 11 周（每天 1.5 小时）。只想达到「会用 + 能面试」可跳过第六阶段的 PostGIS / TimescaleDB，压缩到 8 周完成。

💡 建议 ​

  • 务必动手：PG 的乐趣 90% 在 psql 里。MVCC、锁、流复制这类「看文字没味道」的章节，一定开两个终端实测。
  • 先用后懂：第一遍只要求「会写 SQL、跑得通示例」；第二遍再回头啃 MVCC / WAL / SSI 的原理与源码思想。
  • 横向对比：如果会 MySQL，每章问自己「PG 和 InnoDB 这里为什么不一样？」对比记忆效率最高。
  • 拥抱生态：PG 的灵魂是扩展。挑一个方向（地理 / 向量 / 时序）做一个 demo，会瞬间打开新世界。
  • 跟版本走：PG 每年发大版本，订阅 Postgres Weekly 每周花 10 分钟跟进新特性。

祝你早日成为团队里的 PG 专家！