主题
从 0 到 1 学习 PostgreSQL
一套面向零基础、覆盖底层原理与面试高频考点的中文 PostgreSQL 系统教程——看完会用、用了懂原理、面试能答上。
✨ 教程亮点
- 📚 19 章正文：从
psql入门到 MVCC、WAL、流复制、pgvector，由浅入深一气呵成 - 📄 150+ 文件：每章
.md学习文档 +demo.html可视化演示 +code/*.py真实可跑脚本 +init.sql一键建表 - 🎮 19 个交互演示页：纯 HTML/JS，零依赖，浏览器双击即开，点一下看一步
- 💼 19 套面试高频题：每章 5+ 题，难度分级（⭐~⭐⭐⭐⭐），直接对标大厂一面真题
- 🛠 真实可跑代码：100% 用
psycopgv3 写的 Python 脚本，复制即用，不留伪代码 - 🐘 生活化类比：MVCC ≈「图书馆借书卡」、WAL ≈「写日记本」、Checkpoint ≈「攒一摞日记一次抄正本」
- 🥊 横向对比 MySQL：每章带「📌 与 MySQL 的区别」小框，已会 MySQL 的同学加速学习
- 📦 一键开发环境：根目录
docker-compose.yml已带 pgvector + pg_stat_statements + 调优参数
🚀 快速开始（3 步上手）
第 1 步：起一个 PostgreSQL 实例
方式 A：用本目录提供的 docker-compose（强烈推荐） —— 已经预装 pgvector，并提前打开 pg_stat_statements，可直接跑前 18 章的所有代码。
bash
cd /path/to/learnNote/postgre
docker compose up -d
docker compose logs -f pg # 看到 "database system is ready" 就 OK方式 B：本地已经有 PG 16/17 —— 只要满足以下条件即可：
bash
# 1) 数据库与账号
psql -h 127.0.0.1 -U postgres -c "CREATE DATABASE learn_pg;"
# 2) postgresql.conf 里建议加（避免章节里反复提示）：
# shared_preload_libraries = 'pg_stat_statements'
# pg_stat_statements.track = 'all'
# 然后 systemctl restart postgresql 或 pg_ctl restart⚠️ 如果你的 PG 没装 pgvector，第 18/19 章的向量检索 demo 会报
extension "vector" is not available，请改用方式 A 或自行apt-get install postgresql-16-pgvector。
第 2 步：装 Python 依赖
bash
python -m venv .venv
source .venv/bin/activate # Windows: .venv\Scripts\activate
pip install -r requirements.txt第 3 步：跑第一个 demo
bash
# 进入第 2 章，先建一份示例库
psql -h 127.0.0.1 -U postgres -d learn_pg -f 02_psql_basic/init.sql
# 跑第一段 Python 演示
python 02_psql_basic/code/basic_query.py
# 双击打开第 2 章的演示页（纯前端，零依赖）
# Linux: xdg-open 02_psql_basic/demo.html
# macOS: open 02_psql_basic/demo.html
# Win: start 02_psql_basic\demo.html看到列出的表数据 + 浏览器里的 psql 元命令演示页，就算环境跑通啦 🎉。
📂 目录结构总览
learnNote/postgre/
├── README.md ← 你正在看的这份门面
├── docker-compose.yml ← 一键起 PG 16 + pgvector + pg_stat_statements
├── requirements.txt ← 前 18 章统一的 Python 依赖
├── .gitignore ← Python / 编辑器 / 数据卷忽略规则
│
├── task.md ← 教程总纲（产品定位 + 章节大纲）
├── 0_learn_plan.md ← 11 周学习路线图（含每周练习任务）
├── interview.md ← 19 章面试题总索引
├── appendix_pg_vs_mysql.md ← 附录 A：PG vs MySQL 对比速查表
├── appendix_cheatsheet.md ← 附录 B：PG 常用命令速查表
├── appendix_pitfalls.md ← 附录 C：30 个真实踩坑案例集
│
├── 01_intro.md ← 第 1 章正文
├── 01_intro/ ← 第 1 章配套
│ ├── demo.html ← 浏览器交互演示
│ └── code/*.py ← 可运行 Python 代码
│
├── 02_psql_basic.md
├── 02_psql_basic/{demo.html, init.sql, code/}
│
├── ... 同样的结构重复 17 次（03 ~ 18）...
│
├── 19_project.md ← 综合实战章节正文
└── 19_project/ ← 综合实战项目（自带 docker-compose / README）
├── README.md
├── docker-compose.yml ← ⚠️ 与根目录 compose 端口冲突，二选一启
├── requirements.txt
├── init.sql
├── seed.sql
└── code/{api.py, posts.py, search_*.py, ...}💡 每章的代码 / SQL / demo 都自包含，可以从任意章节切入，不强制顺序。
🗺 学习路线（6 阶段 11 周）
学习路线完整版见 0_learn_plan.md，下表是速览：
| 阶段 | 章节 | 内容主题 | 时长 | 关键产物 / 学完能干嘛 |
|---|---|---|---|---|
| 一 · 入门启航 | Ch 1 ~ Ch 2 | PG 是什么 + psql 基础 | 1 周 | 玩转 \d \dt \df，会写 DDL/DML/RETURNING |
| 二 · SQL 精通 | Ch 3 ~ Ch 5 | 类型系统 + 高级查询 | 2 周 | JSONB / 数组 / CTE / 窗口函数 / ON CONFLICT upsert |
| 三 · 索引与事务 | Ch 6 ~ Ch 8 | 索引选型 + 隔离级别 + MVCC | 2 周 | 看懂 EXPLAIN ANALYZE、理解 SSI 与 HOT 更新 |
| 四 · 内核原理 | Ch 9 ~ Ch 11 | 存储页 + WAL/Checkpoint + 锁 | 2 周 | 用 pageinspect 看页结构，能分析死锁 |
| 五 · 编程与运维 | Ch 12 ~ Ch 15 | PL/pgSQL + RLS + 备份恢复 + 流复制 | 2 周 | 写触发器、配 RLS、做 PITR、搭一主一从 |
| 六 · 扩展与实战 | Ch 16 ~ Ch 19 | 分区 + 调优 + PostGIS/pgvector + 综合项目 | 2 周 | 做月分区、定位 Top N 慢 SQL、跑 RAG demo |
只想达到「会用 + 能面试」可以跳过第六阶段的 PostGIS / TimescaleDB，8 周完成。
📖 每章速览
表格按 task.md 第六节大纲对齐，标题保持一致；点击列内链接直达。
| # | 主题 | 学习文档 | 演示页 | 代码目录 | 关键难点 |
|---|---|---|---|---|---|
| 01 | PostgreSQL 是什么 & 为什么火 | 01_intro.md | demo | code/ | 与 MySQL 定位差异 / 进程模型 |
| 02 | psql 与基础 SQL | 02_psql_basic.md | demo | code/ | 三级结构 / \d \dt \df / search_path |
| 03 | 数据类型详解 | 03_data_types.md | demo | code/ | JSONB 操作符 / 时区 / 数组 / 范围类型 |
| 04 | 约束、视图与序列 | 04_constraint_view.md | demo | code/ | 排他约束 EXCLUDE / 物化视图 / IDENTITY |
| 05 | 高级查询 | 05_advanced_query.md | demo | code/ | 递归 CTE / 窗口函数 / LATERAL / upsert |
| 06 | 索引体系 | 06_index.md | demo | code/ | 6 种索引选型 / 部分 / 表达式 / EXPLAIN 解读 |
| 07 | 事务与隔离级别 | 07_transaction.md | demo | code/ | RC vs RR vs SSI / SAVEPOINT / 显式行锁 |
| 08 | MVCC 与 VACUUM | 08_mvcc_vacuum.md | demo | code/ | xmin/xmax / HOT / 死元组 / XID 回卷 |
| 09 | 存储与物理结构 | 09_storage.md | demo | code/ | 页 / TOAST / FSM / VM / pageinspect |
| 10 | WAL 与 Checkpoint | 10_wal_checkpoint.md | demo | code/ | LSN / wal_level / 崩溃恢复 / full_page_writes |
| 11 | 锁机制 | 11_lock.md | demo | code/ | 8 种表锁 / advisory lock / 死锁排查 |
| 12 | 服务端编程 | 12_server_programming.md | demo | code/ | PL/pgSQL / 触发器 / LISTEN/NOTIFY |
| 13 | 权限与安全 | 13_security.md | demo | code/ | 角色模型 / 行级安全 RLS / pg_hba.conf |
| 14 | 备份与恢复 | 14_backup_recovery.md | demo | code/ | pg_dump/pg_restore / pg_basebackup / PITR |
| 15 | 复制与高可用 | 15_replication.md | demo | code/ | 流复制 / 同步模式 / 逻辑复制 / 故障切换 |
| 16 | 分区与分库分表 | 16_partition.md | demo | code/ | RANGE/LIST/HASH 分区 / 分区裁剪 / FDW |
| 17 | 性能调优 | 17_performance.md | demo | code/ | pg_stat_statements / auto_explain / 关键参数 |
| 18 | 常用扩展生态 | 18_extension.md | demo | code/ | PostGIS / pgvector / pg_cron / pg_trgm / unaccent |
| 19 | 综合实战项目 | 19_project.md | demo | code/ | 博客全文检索 + 向量 RAG + 多租户 + 性能审计 |
📌 各章脚本统一连接：
host=127.0.0.1 port=5432 dbname=learn_pg user=postgres，密码默认postgres，连接信息通常以环境变量PGHOST/PGUSER/PGDATABASE/PGPASSWORD兜底。 📌 跨章节表名已加chN_前缀（如ch6_orders、ch11_inventory），同库练习不会互相覆盖。
🧭 配套元文档
| 文件 | 作用 |
|---|---|
task.md | 教程总纲与产品需求文档：定位、读者画像、章节产出标准（每章 4 件套）、写作执行顺序 |
0_learn_plan.md | 11 周学习路线图：6 阶段拆解 + 每阶段练习任务 + 推荐资源 + 时间预算 |
interview.md | 19 章面试题总索引：按章节 + 难度（⭐~⭐⭐⭐⭐）归类，跳冲刺复习神器 |
appendix_pg_vs_mysql.md | 附录 A：PG 16 vs MySQL 8.0 / InnoDB 全维度对比速查表 |
appendix_cheatsheet.md | 附录 B：psql 元命令 / 库表 / 角色 / 索引 / EXPLAIN 速查工具书 |
appendix_pitfalls.md | 附录 C：30 个真实踩坑案例（5 段式：现象→原因→复现→解决→预防） |
🧰 环境要求
| 组件 | 推荐版本 | 说明 |
|---|---|---|
| PostgreSQL | 16 / 17 | 13 也大体兼容；涉及 SQL MERGE 的章节需 ≥15 |
| Python | 3.10+ | 3.9 也能跑，但少数类型注解会警告 |
| psycopg | v3.1+ | 教程统一用 v3 接口，不要装 psycopg2 |
| Docker / Compose | 24+ / v2 | 仅在用根目录或 19 章 compose 时需要 |
| psql 客户端 | 与服务端同大版本 | 想用增强版可装 pgcli |
| 浏览器 | Chrome / Edge / Firefox 任意 | 打开 demo.html 即可，无需 Web 服务器 |
🐳 不想装本地 PG？ 直接
docker compose up -d一行完事。 🪟 Windows 用户：建议在 WSL2 里跑；Native Windows 也能跑，shell 脚本（第 14/15 章）改用 PowerShell 等价命令即可。
❓ 常见问题 FAQ
Q1：CREATE EXTENSION xxx 报 extension "xxx" is not available？
A：扩展二进制没装。
- 用根目录
docker-compose.yml起的容器：pgvector/pg_stat_statements/pgcrypto/pg_trgm/btree_gin/gist/tablefunc/unaccent等都自带；只有 PostGIS / pg_cron 不自带，参考docker-compose.yml文件底部的备选镜像注释。 - 自己装的 PG：Debian/Ubuntu 装
postgresql-16-<扩展名>，比如postgresql-16-pgvector、postgresql-16-cron、postgresql-16-postgis-3；Mac 用brew install <扩展>。 - 装好后重启 PG，再
CREATE EXTENSION IF NOT EXISTS xxx;。
Q2：Python 脚本报 connection to server at "127.0.0.1", port 5432 failed: Connection refused？
A：服务还没起，或者端口被防火墙挡了。
bash
# 自检三连
docker compose ps # 容器是否 healthy
psql -h 127.0.0.1 -U postgres -d learn_pg # 命令行能否连
ss -lntp | grep 5432 # 5432 端口是否在监听如果你本地已有别的 PG 在用 5432，把根目录 docker-compose.yml 的 5432:5432 改成 5433:5432，并相应设置 export PGPORT=5433 给脚本。
Q3：跨章节练习时，不同章节的表名 / 索引名是不是会打架？
A：教程已经主动避免：
- 各章
init.sql中的表名都加了chN_前缀（如ch6_orders、ch11_inventory）。 - 共用的「user / order / book」之类宽泛名字只出现在第 19 章综合项目，且第 19 章建议另开一个 DB / 容器（用
19_project/docker-compose.yml）。 - 想彻底重置：
docker compose down -v把数据卷删掉重来，30 秒回到出厂设置。
Q4：不同 PG 版本，章节内容兼容吗？
A：教程目标版本是 PG 16，整体也覆盖 PG 13 ~ 17：
- ⛔ 用到
MERGE的示例需要 PG 15+（第 5 / 19 章会标注）。 - ⛔ 用到
pg_basebackup --incremental、SQL/JSON 路径表达式的需要 PG 17（第 14 章会标注）。 - ⚠️
gen_random_uuid()在 PG 13 之前要先CREATE EXTENSION pgcrypto；PG 13+ 内置。 - ✅ 其余 SQL / 索引 / MVCC / WAL / 复制语义 PG 13~17 一致。
Q5：pgvector / PostGIS 装不上 / 镜像太大怎么办？
A：
- pgvector：用
pgvector/pgvector:pg16镜像（已经是默认），完全无痛。 - PostGIS：镜像约 700MB，第 18 章 02 用得到。临时改
docker-compose.yml的image:行为postgis/postgis:16-3.4即可，详见文件底部备选注释。 - 只是想看效果不想下镜像？每章的
demo.html是纯前端模拟，无需后端也能看动画演示原理。
Q6：脚本里有些 EXPLAIN ANALYZE 数字和我跑出来不一样？
A：完全正常。EXPLAIN ANALYZE 的耗时和 buffer 命中受机器、缓存、统计信息影响——关注节点类型与相对量级，不要纠结绝对毫秒数。第 6 / 17 章会反复强调这一点。
Q7：第 14 章 PITR / 第 15 章流复制需要起两个实例，但根目录 compose 只起了一个？
A：是的，根目录 compose 只是「日常学习用的 1 实例」。
- 第 14 章 PITR 演示用同一个实例的
pg_basebackup+archive_command即可，无需第二实例。 - 第 15 章流复制需要主从两个实例，章节里给了
02_setup_streaming_replication.sh，端口默认 5440 / 5441，错开根目录的 5432，可以同时启。
Q8：我能不能跳着学？
A：可以！但建议至少按下面顺序「踩点」：
Ch1 → Ch2 → Ch3 → Ch6 → Ch7 → Ch8
（搞定 SQL + 索引 + 事务 + MVCC，已是合格 PG 用户）
然后按兴趣挑：
做 OLTP → Ch9/10/11/14/15/17
做 AI/搜索 → Ch5/18/19
做运维 → Ch13/14/15/17🤝 如何贡献 / 反馈
- 发现错别字、SQL 报错、脚本兼容性问题？欢迎直接 PR / Issue（在你接入的代码托管平台）。
- 想新增「真实业务场景案例」「面试真题」「踩坑案例」？参考已有章节结构，保持「概念 → 类比 → 示例 → 原理 → 对比 MySQL → 小结」六段式即可。
- 长期欢迎补充 PG 17 / 18 新特性、云上托管 PG（RDS / AlloyDB / Aurora）专题、TimescaleDB / Citus 进阶玩法。
🙏 致谢与参考
本教程在结构、举例、面试题选型上参考了大量优秀资料，向社区致敬：
官方资料
- PostgreSQL 17 官方手册 —— 最权威的真理来源
- PostgreSQL Tutorial —— 官方入门教程
- PostgreSQL Wiki —— 常见问题 / 性能调优 / 升级清单
推荐书籍
- 《PostgreSQL 修炼之道》（第 2 版）— 唐成
- 《PostgreSQL 技术内幕：查询优化深度探索》— 张树杰
- 《The Internals of PostgreSQL》— Hironobu Suzuki（免费在线版）
- 《PostgreSQL 实战》— 谭峰、张文升
在线资源
- PG Exercises —— 交互式 SQL 练习
- Use The Index, Luke! —— 索引调优圣经
- Postgres Weekly —— 英文每周更新
- Planet PostgreSQL —— 核心开发者博客聚合
- 德哥 PG 博客 —— 中文海量实战文章
- explain.depesz.com / explain.dalibo.com —— 在线 EXPLAIN 计划可视化
工具
- psql / pgcli —— CLI 与增强版
- pgAdmin 4 / DBeaver —— GUI 管理工具
- pgbadger —— 慢查询日志可视化
- pg_stat_statements —— 内置慢 SQL 统计扩展
祝你早日成为团队里的 PG 专家！开始你的 0 → 1 之旅吧 → Ch 1: PostgreSQL 是什么 🚀