learnNote

主题

从 0 到 1 学习 PostgreSQL ​

一、项目目标 ​

打造一套适合零基础小白入门、同时覆盖底层原理与面试高频考点的 PostgreSQL 学习教程。要求内容由浅入深、图文并茂、能动手实操，最终达到「看完会用、用了懂原理、面试能答上」的效果。

与 MySQL 教程的差异化定位：

  • 强调「关系型 + 对象-关系」混合模型：PostgreSQL 不只是一个 SQL 数据库，它支持 JSONB、数组、自定义类型、扩展（PostGIS、pg_vector 等），讲解时要把这些差异化能力讲透。
  • 强调「标准 SQL 的范本」：尽量用标准 SQL 写法，并指出与 MySQL 的语法差异（例如 LIMIT/OFFSET、RETURNING、CTE、窗口函数、ON CONFLICT 等）。
  • 强调「MVCC 与 WAL」：这是 PostgreSQL 与 MySQL InnoDB 在并发控制上的核心区别，必须用图解讲清楚。

二、读者画像 ​

  • 主要受众：从未接触过 PostgreSQL 的初学者，可能用过 MySQL，但对 PG 的「元组版本链 / VACUUM / WAL / 流复制」一无所知。
  • 次要受众：用过 PG 但停留在 SELECT/INSERT 层面，希望系统化补齐底层原理（MVCC、执行计划、锁、复制）的开发者；以及准备 DBA / 后端面试的同学。
  • 风格要求：能用「图书馆借书卡」「快递柜」「银行流水账」「写日记本」这类生活化的比喻讲清楚 MVCC、WAL、Checkpoint、流复制、隔离级别等晦涩概念。

三、环境约定 ​

  • 服务端无需读者自己安装：教程默认环境中已经有可用的 PostgreSQL Server（本地 127.0.0.1:5432 或容器化均可），文档不再花篇幅讲安装。
  • 默认账号 / 库：示例统一使用 postgres 用户连接，演示数据库统一命名为 learn_pg，由各章节按需建表，不污染默认库。
  • 聚焦点：
    1. PostgreSQL 的正确使用姿势（SQL 语法、数据类型、索引、事务、视图、函数、扩展、客户端）。
    2. PostgreSQL 的底层实现逻辑（存储结构、MVCC、WAL、查询优化器、锁、复制与高可用）。

四、章节内容物（每个章节都必须包含以下 4 部分） ​

每个章节按照统一结构产出，缺一不可：

  1. 学习文档（.md）

    • 用「概念 → 生活类比 → SQL/代码示例 → 底层原理 → 与 MySQL 对比 → 小结」六段式组织。
    • 大量使用 ASCII 图 / Mermaid 图 展示存储页结构、元组版本链、B-Tree 索引、执行计划树、WAL 流转，避免纯文字堆砌。
    • 关键 SQL 必须给出 psql 实操记录（输入 + 输出），并配上 EXPLAIN (ANALYZE, BUFFERS) 的真实执行计划。

  2. 实战案例（可运行代码）

    • 至少 1 个贴近真实业务的场景，例如：电商订单系统、博客评论树（递归 CTE）、地理位置搜索（PostGIS）、JSONB 标签系统、向量检索（pgvector）、读写分离演示等。
    • 给出完整可运行的代码（推荐 Python psycopg / Node.js pg 任选其一并保持全教程统一），附带 docker-compose.yml（如需）与启动说明。
    • 提供初始化 SQL 脚本 init.sql 和清理脚本 teardown.sql，方便反复练习。

  3. HTML 演示页面（demo.html）

    • 单文件、零依赖（或仅引入 CDN），打开即可在浏览器中可视化演示该章节的核心概念。
    • 例如：用动画展示「元组的 xmin/xmax 如何随事务变化」「B-Tree 索引查找路径」「死元组如何被 VACUUM 回收」「WAL 写入与 Checkpoint 触发」「主从流复制延迟」等。
    • 页面要有交互（按钮、输入框、步进控制），让读者能「点一下看一步」。

  4. 面试题清单

    • 收集大厂真实面经中该章节的高频题目（至少 5 题）。
    • 每题给出：题目 → 考察点 → 标准答案（分点作答）→ 加分项 / 易错点 → 与 MySQL 的对比要点（如适用）。

五、内容深度与表达要求 ​

  • 从 0 到 1：第一次出现的术语必须解释，不能默认读者知道（比如第一次提到 MVCC、HOT、TOAST、WAL、LSN 都要先讲它是什么）。
  • 生活化类比优先：晦涩点（MVCC 多版本、VACUUM、WAL、Checkpoint、隔离级别、流复制、逻辑复制、热备 / 物理备份等）必须先用生活例子打比方，再讲技术细节。
  • 能动手：所有 SQL、代码、演示页面读者都能复制即用，不要出现伪代码或「此处省略」。
  • 由浅入深：先讲「怎么用」，再讲「为什么这么设计」，最后引申「源码思想 / 性能调优 / 踩坑」。
  • 横向对比：在合适位置加「📌 与 MySQL 的区别」小框，例如：
    • 自增主键：MySQL AUTO_INCREMENT vs PG SERIAL / IDENTITY
    • 隔离级别默认值：MySQL REPEATABLE READ vs PG READ COMMITTED
    • 行格式：InnoDB 聚簇索引 vs PG 堆表 + 索引分离
    • Undo Log vs MVCC 多版本元组

六、建议的章节大纲（可在执行时微调） ​

  1. PostgreSQL 是什么 & 为什么火（关系型数据库简史、PG 的设计哲学、与 MySQL/Oracle 的定位差异、生态与扩展）
  2. psql 与基础 SQL（连接、库 / Schema / 表三级结构、DDL/DML/DCL、psql 元命令 \d \dt \df 等）
  3. 数据类型详解（数值、字符、时间、布尔、UUID、数组、JSON/JSONB、范围类型、枚举、自定义复合类型）
  4. 约束、视图与序列（主键 / 外键 / 唯一 / 检查 / 排他约束、普通视图 vs 物化视图、SERIAL vs IDENTITY）
  5. 高级查询（JOIN 全家桶、子查询、CTE 与递归 CTE、窗口函数、GROUPING SETS / ROLLUP / CUBE、RETURNING、ON CONFLICT upsert）
  6. 索引体系（B-Tree、Hash、GIN、GiST、BRIN、SP-GiST 适用场景；部分索引、表达式索引、覆盖索引；EXPLAIN ANALYZE 解读）
  7. 事务与隔离级别（ACID、四种隔离级别在 PG 下的真实行为、SERIALIZABLE 的 SSI 机制、保存点 SAVEPOINT）
  8. MVCC 与 VACUUM（xmin/xmax/cmin/cmax、可见性判断、HOT 更新、死元组、autovacuum、事务 ID 回卷与防冻结）
  9. 存储与物理结构（数据目录、表空间、页 / 元组 / TOAST、Free Space Map、Visibility Map、堆表 vs 索引文件）
  10. WAL 与 Checkpoint（WAL 的作用、LSN、wal_level、Checkpoint 触发条件、崩溃恢复流程、pg_waldump 实战）
  11. 锁机制（表级锁 8 种、行级锁、咨询锁 advisory lock、死锁检测、pg_locks 视图排查）
  12. 服务端编程（PL/pgSQL 函数与存储过程、触发器、规则系统 RULE、事件触发器、LISTEN / NOTIFY）
  13. 权限与安全（角色 / 用户 / 组、GRANT/REVOKE、行级安全 RLS、pg_hba.conf、SSL 连接）
  14. 备份与恢复（逻辑备份 pg_dump / pg_restore、物理备份 pg_basebackup、PITR 时间点恢复）
  15. 复制与高可用（流复制原理、同步 / 异步 / quorum、热备 hot standby、级联复制、逻辑复制 publication/subscription、Patroni 简介）
  16. 分区与分库分表（声明式分区：RANGE/LIST/HASH、分区裁剪、Citus / pg_pathman 简介）
  17. 性能调优（关键参数：shared_buffers work_mem effective_cache_size、慢查询日志、pg_stat_statements、auto_explain）
  18. 常用扩展生态（PostGIS 地理信息、pgvector 向量检索、TimescaleDB 时序、pg_cron 定时任务、pg_partman 分区管理）
  19. 综合实战项目（任选其一并贯穿：电商订单系统 / 博客评论 + 全文检索 / 附近商家地理搜索 / RAG 向量库）

七、产出格式约束 ​

  • 所有文档放在 learnNote/postgre/ 下，按 序号_主题.md 命名（如 01_intro.md、02_psql_basic.md）。

  • 每章节配套的演示页面与代码放在同名子目录中，例如：

    learnNote/postgre/
├── 0_learn_plan.md          # 总学习路线（由本文档拆出来的精简版）
├── 01_intro.md
├── 01_intro/
│   ├── demo.html
│   └── code/
│       └── hello_pg.py
├── 02_psql_basic.md
├── 02_psql_basic/
│   ├── demo.html
│   ├── init.sql
│   └── code/
│       └── basic_query.py
├── ...
├── interview.md             # 各章面试题总索引
└── qa/                      # 大厂真题分类整理（可选）

  • 文档中的代码块必须标注语言（```sql、```bash、```python、```mermaid 等），方便高亮。

  • 所有 SQL 示例统一使用小写关键字（如 select）还是大写关键字（如 SELECT）需在 01_intro.md 开篇统一约定，全教程保持一致；推荐使用大写关键字 + 小写标识符（与官方文档一致）。

  • 涉及执行计划的章节，必须给出 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 的完整输出，并配文字解读每一行节点的含义。

  • 面试题统一汇总到每章末尾的「面试高频题」小节，并在 learnNote/postgre/interview.md 做总索引。

八、写作执行顺序（建议） ​

  1. 先产出 0_learn_plan.md：把第六节的大纲展开成「每章预计字数 / 核心知识点 / 配套实战 / 面试题数量」的表格，作为后续章节的施工图。
  2. 再按章节顺序逐个产出 0X_xxx.md + 同名目录下的 demo.html + code/。
  3. 每完成 3 ~ 5 章，回顾一次 interview.md，把已完成章节的面试题归集进去。
  4. 全部章节产出后，再做一次总复盘：补充「PostgreSQL vs MySQL 对比速查表」「常用命令速查表」「踩坑案例集」三个附录文档。