database-lessons.md 69 KB
数据库开发
术核 AI · 后端工程课件
01 准备 PostgreSQL 与 psql 练习环境
为 notes-service 建立独立练习库，区分数据库进程、数据库、角色与客户端。
本节目标
- 启动 PostgreSQL 16 并创建非超级用户
- 用 psql 明确连接地址、端口、角色和数据库
- 区分连接失败、认证失败和 SQL 错误
必要概念
服务端与客户端
PostgreSQL 进程负责保存数据和执行 SQL；psql 是向它发送命令的终端工具。关闭 psql 不会停止数据库。一个 PostgreSQL 实例可以包含多个数据库，连接时必须确定要进入哪个库。
角色与数据库分别创建
角色表示谁在连接，数据库表示在哪里读写。练习的 app_user 是 notes_lab 所有者，但不是超级用户。第 11 课再拆分迁移角色与运行时角色，避免 API 进程拥有修改表结构的权限。
实验边界
本模块所有命令都是学员在独立 notes_lab 中执行的练习，不是让你操作线上数据库。需要 Python 3.11+ 和已安装、启动的 Docker。此处只用容器获得一致的 PostgreSQL 16 环境；镜像、卷与容器网络在 Docker 模块系统学习。
动手实现
1. 建立项目与本机密码文件
以下为 Linux / macOS Bash 命令；Windows 可在 WSL 中执行。本节使用 127.0.0.1:5433，容器内部仍是 5432。密码只用于本机练习，不要提交到 Git。终端在 notes-service 中执行。
文件：终端 / 当前课时
mkdir -p notes-service
cd notes-service
umask 077
printf 'POSTGRES_PASSWORD=%s\n' "$(python3 -c 'import secrets; print(secrets.token_urlsafe(24))')" > .postgres.env
printf '.postgres.env\n.env\nbackups/\n.venv/\n' >> .gitignore
docker run --name notes-postgres --env-file .postgres.env \
-p 127.0.0.1:5433:5432 \
-v notes_pgdata:/var/lib/postgresql/data -d postgres:16
docker exec notes-postgres pg_isready -U postgres
2. 创建练习角色和数据库
等待 pg_isready 显示 accepting connections。先执行 docker exec -it notes-postgres psql -U postgres -d postgres，然后在该 psql 中执行下列命令。\password 会交互要求输入两遍 app_user 密码，不在 SQL 历史中直接写出密码。下面建库命令只执行一次。
文件：终端 / 当前课时
CREATE ROLE app_user LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;
\password app_user
CREATE DATABASE notes_lab OWNER app_user;
\q
3. 用业务身份重新连接
执行 docker exec -it notes-postgres psql -h 127.0.0.1 -U app_user -d notes_lab -W，输入刚设置的密码。该命令在容器内访问容器的 5432。若本机已安装 psql，则使用 psql -h 127.0.0.1 -p 5433 -U app_user -d notes_lab -W。后续未特别标明的 SQL 都在此连接运行。
文件：终端 / 当前课时
\conninfo
SELECT current_database(), current_user, version();
SHOW server_encoding;
SHOW TimeZone;
\set VERBOSITY verbose
\timing on
4. 验证持久化与 SQL 命令边界
运行两条 SELECT，观察分号用于结束 SQL，反斜杠命令由 psql 处理。退出客户端后重新连接，数据库仍存在。数据库卷保留了实例数据；不要为了重新做某节练习删除整个卷。
文件：终端 / 当前课时
SELECT 6 * 7 AS answer;
-- answer = 42
SELECT current_database() = 'notes_lab' AS in_lab;
-- in_lab = t
\q
验收结果
- current_database() 为 notes_lab，current_user 为 app_user。
- version() 显示 PostgreSQL 16，server_encoding 为 UTF8。
- 退出并重新连接后 SELECT 6 * 7 仍能执行；数据库进程没有因客户端退出而停止。
生产约束与故障演练
新服务需要一个可重复创建、不会误连线上实例的开发数据库。
实现决策
- 连接串明确包含数据库名与端口。
- 先用非超级用户练习，环境文件加入忽略规则。
- 生产环境选择受维护的 16.x 补丁版并锁定镜像摘要；postgres:16 是本课程环境入口。
隔离环境演练
在独立练习环境中用错误端口尝试连接，依据 pg_isready 与实际端口定位原因。
恢复验收
- 能从连接信息确认当前实例和身份。
- 数据库卷与容器用途可区分。
- 仓库没有密码文件。
常见错误
connection refused
原因：容器未启动、端口尚未就绪或混用了宿主机与容器端口。
处理：用 docker ps 与 pg_isready 检查状态；本机使用 5433，docker exec 内部使用 5432。
role app_user does not exist 或 password authentication failed
原因：创建角色步骤未执行，或输入的是 postgres 密码。
处理：使用管理连接检查 \du；通过 \password app_user 重设本机练习密码，再用业务身份重连。
扩展练习
分别在一个正确连接和一个故意写错数据库名的连接中观察结果，记录错误发生在哪一层。
提示：将 notes_lab 暂时写成 notes_missing；不要真的创建这个库。
参考答案：
正确连接能执行 SELECT；错误库名在连接阶段返回 database does not exist，SQL 尚未执行。连接地址错通常是网络层失败，密码错是认证失败，SELECT 1/0 则是连接成功后的 SQL 执行错误。
技术文档
02 定义笔记表、数据类型与约束
把 Python 对象转成有明确边界的数据表，让非法数据在数据库入口被拒绝。
本节目标
- 建立 notes 表并理解主键
- 选择 text、boolean 和 timestamptz
- 通过 NOT NULL、CHECK 与默认值维护数据规则
必要概念
列类型约束存储形式
bigint 存储整数编号，varchar(120) 限制标题字符数，text 存储正文。boolean 使用 true / false，不能用随意约定的字符串。时间使用 timestamptz 表示一个时间点，显示格式受会话时区影响。
默认值与非空是两条规则
DEFAULT 只在插入时省略列或使用 DEFAULT 时生效；显式传 NULL 仍然是 NULL。NOT NULL 才拒绝空值，CHECK 再约束有效内容。应用校验改善错误提示，数据库约束保护所有写入入口。
主键和生成编号
identity 自动提供编号，PRIMARY KEY 保证编号唯一且非空。编号用于定位，不保证连续，也不代表严格的业务提交顺序。updated_at 的默认值只在插入时生效，更新时需要显式修改或使用统一触发器。
动手实现
1. 确认连接后创建表
在第 01 课的 app_user / notes_lab 连接中执行。保存为 sql/001_notes.sql，首次运行一次；后续变更使用单独迁移文件，不要反复删表重建。
文件：sql/001_notes.sql
CREATE TABLE notes (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title varchar(120) NOT NULL CHECK (char_length(btrim(title)) > 0),
content text NOT NULL DEFAULT '',
pinned boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
\d notes
2. 插入合法数据并看默认值
本节写入的标题用 db02- 前缀，避免与后续实验混淆。RETURNING 在同一次写入中返回实际保存的行。记录生成的编号，但后续练习不依赖它必须从 1 开始。
文件：终端 / 当前课时
INSERT INTO notes (title) VALUES ('db02-first')
RETURNING id, title, content, pinned, created_at, updated_at;
-- content 为空串，pinned 为 f，两列时间均非空
SELECT count(*) FROM notes WHERE title = 'db02-first';
3. 让数据库拒绝非法数据
以下两条语句在 psql 默认自动提交状态逐条执行；预期分别失败，不要包进同一个 BEGIN 后继续运行。随后 count(*) 仍为 1。
文件：终端 / 当前课时
INSERT INTO notes (title) VALUES (NULL);
-- SQLSTATE 23502: not_null_violation
INSERT INTO notes (title) VALUES (' ');
-- SQLSTATE 23514: check_violation
SELECT count(*) FROM notes WHERE title = 'db02-first';
4. 观察同一时间点的时区显示
这里改的是当前会话显示方式。UTC 与上海时间显示相差 8 小时，epoch 秒数相同。业务对外使用带时区 ISO 8601，不能在 Python 中去掉 tzinfo 后再猜测时区。
文件：终端 / 当前课时
SET TIME ZONE 'UTC';
SELECT created_at, extract(epoch FROM created_at) FROM notes WHERE title='db02-first';
SET TIME ZONE 'Asia/Shanghai';
SELECT created_at, extract(epoch FROM created_at) FROM notes WHERE title='db02-first';
SET TIME ZONE 'UTC';
验收结果
- \d notes 显示主键、identity、非空与标题 CHECK。
- 省略 content 和 pinned 时得到默认值；显式 NULL 标题被拒绝。
- 切换时区后显示不同，epoch 值保持一致。
生产约束与故障演练
同一张表将被 API、导入脚本和后台任务共同写入。
实现决策
- 关键合法性规则落到数据库。
- 编号只当标识符，不用于推算数据量。
- 统一存时间点、在展示层选择时区。
隔离环境演练
向 notes_lab 提交空标题、NULL 和超长标题，逐条记录 SQLSTATE。
恢复验收
- 非法行没有落库。
- 合法写入能返回真实生成值。
- 能说明 updated_at 的维护位置。
常见错误
UPDATE 后 updated_at 没变化
原因：DEFAULT now() 只参与插入。
处理：后续每次更新同时设置 updated_at=now()；多人维护时把规则收敛到统一写入层或触发器。
空标题进入数据库
原因：只使用了 NOT NULL，空串不是 NULL。
处理：加入 btrim 与长度 CHECK，并在应用侧做一致校验；已有不合格数据需先清理。
扩展练习
验证 121 个字符的标题被拒绝，再插入带单引号的合法标题。
提示：SQL 字面量中的单引号用两个单引号表示；Python 动态值在下一课使用参数化。
参考答案：
INSERT INTO notes(title) VALUES(repeat('a',121)); 会触发长度错误。INSERT INTO notes(title) VALUES('db02-O''Reilly') RETURNING title; 返回 db02-O'Reilly。SQL 中的引号转义不替代 Python 驱动的参数化接口。
技术文档
03 用参数化 SQL 完成增删改查
从 Python 读写笔记，处理不存在的数据，并验证用户输入不会变成 SQL。
本节目标
- 使用 psycopg 3 传递参数
- 通过 RETURNING 判断实际写入结果
- 用明确列名、过滤与排序控制查询
必要概念
SQL 结构与参数分开传递
execute 的 SQL 字符串描述操作结构，第二个参数提供数据。值占位符统一写 %s，不自行加引号，也不用 f-string 拼接输入。表名、列名不能作为普通值参数传入，动态排序应使用固定白名单。
写入结果要检查
UPDATE 或 DELETE 匹配不到数据通常不是 SQL 错误。RETURNING 返回零行能表示资源不存在；不要在未检查结果时声称修改成功。
读取结果必须有边界
查询显式列名能稳定输出结构。列表查询需要 LIMIT 和确定排序，否则数据库不承诺返回顺序。id DESC 可作为当前课程按编号倒序的简单规则。
动手实现
1. 安装驱动与设置连接
在 notes-service 创建并激活 Python 3.11+ 虚拟环境，执行 python -m pip install "psycopg[binary]>=3.2,<4"。把实际解析版本写入 requirements-lock.txt。Linux 使用下面的 PG 环境变量，PGPASSWORD 在当前 shell 中交互输入，不写进脚本。正式环境使用密钥注入并避免输出连接串。
文件：终端 / 当前课时
export PGHOST=127.0.0.1 PGPORT=5433 PGDATABASE=notes_lab PGUSER=app_user
read -rsp 'Database password: ' PGPASSWORD
printf '\n'
export PGPASSWORD
python -m pip freeze > requirements-lock.txt
2. 保存并运行完整 CRUD 脚本
保存为 db_crud.py，再执行 python db_crud.py。connect("") 读取 PG 环境变量；上下文正常退出提交，异常退出回滚。单元素参数必须带逗号。此脚本先创建自己的数据，最后删除同一行，可以重复执行。
文件：db_crud.py
import psycopg
from psycopg.rows import dict_row
with psycopg.connect("", row_factory=dict_row) as conn:
created = conn.execute(
"INSERT INTO notes(title, content) VALUES (%s, %s) RETURNING id, title",
("db03-O'Reilly", "parameterized insert"),
).fetchone()
note_id = created["id"]
print("created", created)
print("read", conn.execute(
"SELECT id, title, pinned FROM notes WHERE id = %s", (note_id,)
).fetchone())
print("updated", conn.execute(
"UPDATE notes SET pinned = %s, updated_at = now() WHERE id = %s RETURNING id, pinned",
(True, note_id),
).fetchone())
print("deleted", conn.execute(
"DELETE FROM notes WHERE id = %s RETURNING id", (note_id,)
).fetchone())
print("missing", conn.execute(
"SELECT id FROM notes WHERE id = %s", (note_id,)
).fetchone())
3. 提交一段看起来像 SQL 的标题
单独保存为 db_input.py 并运行。预期只插入一条普通笔记，输入里的 DROP TABLE 不会执行。finally 外层连接上下文会正常提交这个插入和删除的组合；最后 notes 表仍能查询。
文件：db_input.py
import psycopg
payload = "db03-x'); DROP TABLE notes; --"
with psycopg.connect("") as conn:
row = conn.execute(
"INSERT INTO notes(title) VALUES (%s) RETURNING id, title", (payload,)
).fetchone()
assert row[1] == payload
print(row)
conn.execute("DELETE FROM notes WHERE id = %s", (row[0],))
print("table exists", conn.execute("SELECT count(*) FROM notes").fetchone())
4. 练习有边界的列表查询
回到 psql 执行，记录结果列与顺序。修改 LIMIT 观察数量变化。对照 Python 中的写法：LIMIT 也可用 %s 传值，但应用应限制 1 到 100，不能让客户端请求任意数量。
文件：终端 / 当前课时
SELECT id, title, pinned
FROM notes
WHERE pinned = false
ORDER BY id DESC
LIMIT 10;
UPDATE notes SET pinned = true WHERE id = -1 RETURNING id;
-- 0 rows / UPDATE 0，不是 SQL 异常
验收结果
- CRUD 输出依次出现同一编号，最后 missing 为 None。
- 含单引号和 SQL 片段的标题保持原样，notes 表仍存在。
- 不存在编号的 UPDATE 返回零行；列表最多 10 行且编号递减。
生产约束与故障演练
笔记标题来自浏览器，数据库不能把任何一段用户输入当成可执行语法。
实现决策
- 参数化所有动态值。
- 列表显式限制数量和排序。
- 以 RETURNING 判断变更结果。
隔离环境演练
在 notes_lab 用包含单引号与 SQL 片段的标题创建笔记，核验原值与表完整性。
恢复验收
- 没有 SQL 字符串插值。
- 空结果有明确处理。
- 连接与事务在脚本结束后释放。
常见错误
syntax error 或用户输入改变了 SQL 含义
原因：把输入放进 f-string 或字符串拼接。
处理：恢复固定 SQL 和独立参数元组；不要手写通用转义函数。
脚本输出成功但另一个连接查不到新增行
原因：事务尚未提交，或者最后主动删除了练习行。
处理：确认连接上下文已经退出；若只验证插入，暂时移除删除步骤后在独立连接核查。
扩展练习
写一个只更新标题的函数，返回更新后的 id/title；不存在时返回 None。
提示：WHERE id = %s，并在同一 UPDATE 中 RETURNING id, title。
参考答案：
def rename_note(conn, note_id, title):
return conn.execute("UPDATE notes SET title=%s, updated_at=now() WHERE id=%s RETURNING id,title", (title,note_id)).fetchone()
调用方决定事务提交边界；函数不私自创建连接或提交。
技术文档
04 用关联表、JOIN 与聚合表达关系
为笔记添加标签，使用外键保证关联有效，再统计每条笔记的标签数量。
本节目标
- 建立多对多关系和联合主键
- 区别 INNER JOIN 与 LEFT JOIN
- 避免 JOIN 重复行导致聚合错误
必要概念
多对多关系单独建表
一条笔记可有多个标签，一个标签可属于多条笔记。note_tags 的每一行代表一组关联，联合主键阻止重复关联。将标签塞进逗号字符串会让检索、唯一性与改名都变得困难。
外键检查关联完整性
note_id 必须对应真实笔记，tag_id 必须对应真实标签。此例删除笔记时级联清理关联行，删除仍被使用的标签则拒绝；删除策略来自业务，而不是所有外键统一加 CASCADE。
聚合前先辨认行数
LEFT JOIN 会保留没有标签的笔记，并补一个右侧为空的连接结果。COUNT(*) 会把这一行算进去，COUNT(nt.tag_id) 只统计非空标签。聚合查询返回的是分组后的结果。
动手实现
1. 建立标签和关联表
在 notes_lab 首次执行一次；保存为 sql/002_tags.sql。引用 notes 主键，不复制标题。外键列并不会自动获得适合所有反向查询的索引，后续根据查询创建。
文件：sql/002_tags.sql
CREATE TABLE tags (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name varchar(40) NOT NULL UNIQUE CHECK (char_length(btrim(name)) > 0)
);
CREATE TABLE note_tags (
note_id bigint NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
tag_id bigint NOT NULL REFERENCES tags(id) ON DELETE RESTRICT,
PRIMARY KEY (note_id, tag_id)
);
2. 准备两条笔记和两个标签
以下是 psql 脚本；\gset 将 RETURNING 值写进本会话变量。创建笔记返回语句末尾不加分号，由 \gset 执行。标签可以重复准备，笔记每次执行都会产生两条独立样本。
文件：终端 / 当前课时
INSERT INTO notes(title) VALUES ('db04-with-tags') RETURNING id AS tagged_id \gset
INSERT INTO notes(title) VALUES ('db04-no-tags') RETURNING id AS empty_id \gset
INSERT INTO tags(name) VALUES ('db04-python'), ('db04-api') ON CONFLICT (name) DO NOTHING;
INSERT INTO note_tags(note_id,tag_id)
SELECT :tagged_id, id FROM tags WHERE name IN ('db04-python','db04-api');
3. 分别查看关联明细和数量
第一条查询得到 2 行标签明细。第二条查询保留无标签笔记，数量分别为 2 与 0。注意按 id 筛选的是上一步新建的两行，不受此前重复运行影响。
文件：终端 / 当前课时
SELECT n.id,n.title,t.name
FROM notes n JOIN note_tags nt ON nt.note_id=n.id
JOIN tags t ON t.id=nt.tag_id
WHERE n.id=:tagged_id ORDER BY t.name;
SELECT n.id,n.title,count(nt.tag_id) AS tag_count
FROM notes n LEFT JOIN note_tags nt ON nt.note_id=n.id
WHERE n.id IN (:tagged_id,:empty_id)
GROUP BY n.id,n.title ORDER BY n.id;
4. 验证重复关联和删除策略
先单独执行重复 INSERT，预期 23505 唯一性错误；再单独执行 DELETE tags，预期 23503 外键错误。最后删除本课带标签笔记，对应关联行自动清理。只删除本会话创建的样本。
文件：终端 / 当前课时
INSERT INTO note_tags(note_id,tag_id)
SELECT :tagged_id,id FROM tags WHERE name='db04-python';
-- 23505：重复关联
DELETE FROM tags WHERE name='db04-python';
-- 23503：仍被笔记引用
DELETE FROM notes WHERE id=:tagged_id;
SELECT count(*) FROM note_tags WHERE note_id=:tagged_id;
-- 0
验收结果
- 关联明细返回两个标签，聚合结果分别为 2 与 0。
- 重复添加同一关联失败，不存在编号的外键写入也会失败。
- 删除练习笔记后仅其关联行消失，标签本身仍存在。
生产约束与故障演练
生产列表页需要展示标签数，同时允许笔记暂时没有标签。
实现决策
- 通过关联表表达多对多。
- 用唯一约束阻止重复关系。
- 明确父记录删除行为并验证空关系。
隔离环境演练
在 notes_lab 重复插入关联、删除被引用标签，记录数据库拒绝原因。
恢复验收
- 零标签结果正确。
- 重复关系无法落库。
- 统计没有因连接放大而重复计算。
常见错误
没有标签的笔记消失了
原因：使用 INNER JOIN，或在 WHERE 中过滤右表导致 LEFT JOIN 效果被破坏。
处理：需要保留左表时使用 LEFT JOIN；右表匹配条件按语义放到 ON，并检查空值结果。
无标签笔记显示数量 1
原因：COUNT(*) 统计了 LEFT JOIN 生成的空关联行。
处理：改用 COUNT(nt.tag_id)，并检查连接多个一对多表时是否又放大行数。
扩展练习
查询拥有 db04-python 标签的笔记，要求每条笔记最多出现一次。
提示：可使用 EXISTS 子查询表达是否存在，不需要把标签列放进输出。
参考答案：
SELECT n.id,n.title FROM notes n WHERE EXISTS (SELECT 1 FROM note_tags nt JOIN tags t ON t.id=nt.tag_id WHERE nt.note_id=n.id AND t.name='db04-python') ORDER BY n.id; 第 04 步已删除本课关联时结果为空；重新执行第 02 步后应出现新笔记。
技术文档
05 把多次写入放进同一个事务
一次保存笔记并关联标签，主动制造第二步失败，确认第一步也回滚。
本节目标
- 划定需要同时成功的写入边界
- 识别失败事务状态并执行回滚
- 使用保存点隔离可选步骤
必要概念
事务覆盖一个业务动作
创建笔记和保存必需标签属于同一次提交。只有两步都成功才向调用者返回成功。数据库事务不能自动回滚已经发送的邮件或外部 HTTP 请求，跨系统一致性在 API + 数据库和异步任务模块继续处理。
错误后的事务需要结束
BEGIN 后发生约束错误，事务进入失败状态。继续 SELECT 通常得到 25P02；应 ROLLBACK 整个事务，或者在预先建立的 SAVEPOINT 上回滚到保存点。
事务越短越容易控制
事务持有连接与可能的锁。不要在 BEGIN 与 COMMIT 之间等用户输入、调用模型或上传文件。identity 消耗的编号即使回滚也可能不复用，所以不能用编号是否连续判断事务正确性。
动手实现
1. 用成功路径保存笔记和标签
先完成第 04 课表结构。此脚本在 psql 中执行；标签使用已有 db04-api，若无该标签先执行 INSERT INTO tags(name) VALUES ('db04-api') ON CONFLICT DO NOTHING。事务末尾应 COMMIT。
文件：终端 / 当前课时
BEGIN;
INSERT INTO notes(title) VALUES ('db05-atomic-ok') RETURNING id AS ok_id \gset
INSERT INTO note_tags(note_id,tag_id)
SELECT :ok_id,id FROM tags WHERE name='db04-api';
COMMIT;
SELECT n.id,n.title,t.name FROM notes n
JOIN note_tags nt ON nt.note_id=n.id JOIN tags t ON t.id=nt.tag_id
WHERE n.id=:ok_id;
2. 制造第二步失败
本例用不存在的负标签编号触发外键错误。逐条执行，观察提示符从 notes_lab=* 改为失败事务状态。不要把预期错误脚本用 ON_ERROR_STOP 直接执行后遗漏回滚。
文件：终端 / 当前课时
BEGIN;
INSERT INTO notes(title) VALUES ('db05-must-rollback') RETURNING id AS failed_id \gset
INSERT INTO note_tags(note_id,tag_id) VALUES (:failed_id,-999);
-- 23503：外键错误
SELECT 1;
-- 25P02：当前事务已失败
ROLLBACK;
SELECT count(*) FROM notes WHERE id=:failed_id;
-- 0
3. 用保存点处理可选标签
只有当业务允许“笔记成功、可选标签失败”时才采用本例。保存点前的笔记保留，保存点之后失败的关联被回滚。若标签是业务必填，则应沿用上一步整笔回滚。
文件：终端 / 当前课时
BEGIN;
INSERT INTO notes(title) VALUES ('db05-optional-tag') RETURNING id AS optional_id \gset
SAVEPOINT optional_tag;
INSERT INTO note_tags(note_id,tag_id) VALUES (:optional_id,-999);
ROLLBACK TO SAVEPOINT optional_tag;
COMMIT;
SELECT id,title FROM notes WHERE id=:optional_id;
-- 1 row
4. 在独立会话核对提交边界
打开第二个 psql，执行相同 SELECT。成功笔记和可选标签笔记可见，must-rollback 不可见。再在会话 A BEGIN 后插入一条新笔记，暂不提交；会话 B 查询该标题为零行，A COMMIT 后 B 再查才能看到。最后结束两个会话中所有未结束事务。
文件：终端 / 当前课时
SELECT title,count(*) FROM notes
WHERE title LIKE 'db05-%' GROUP BY title ORDER BY title;
-- 包含 db05-atomic-ok、db05-optional-tag
-- 不包含 db05-must-rollback
验收结果
- 第二步外键失败后，回滚事务中的笔记不存在。
- 失败状态执行 SELECT 返回 25P02，ROLLBACK 后恢复。
- 另一会话只能读取已提交数据；保存点例子符合明确的可选业务规则。
生产约束与故障演练
保存按钮触发多张表写入，任何必需步骤失败都不能产生半成品记录。
实现决策
- 事务以业务动作划分。
- 可选步骤才允许保存点局部恢复。
- 外部调用在数据库事务之外安排。
隔离环境演练
在 notes_lab 用无效外键中断保存，重新连接检查是否留下半条业务记录。
恢复验收
- 必需写入要么全有要么全无。
- 失败后连接能够继续服务。
- 日志能定位原始约束错误而不是只有后续 25P02。
常见错误
笔记创建了但标签没保存
原因：每条 SQL 独立提交，缺少完整事务边界。
处理：将必需写入放入同一事务，仅在成功提交后返回业务成功。
重试 SQL 一直提示 current transaction is aborted
原因：在失败事务中继续执行，没有先回滚。
处理：退出事务上下文或显式 ROLLBACK，再决定是否重试整个业务动作。
扩展练习
把可选标签例子改成标签必填，说明应该删除哪些语句。
提示：必填意味着不能在失败后只回滚局部。
参考答案：
移除 SAVEPOINT 和 ROLLBACK TO SAVEPOINT。标签插入异常时执行整个事务 ROLLBACK，笔记和关联都不保留；应用把错误转成合适的响应，而不是返回创建成功。
技术文档
06 用两个会话观察隔离级别与 MVCC
按固定顺序执行读写，比较 Read Committed 和 Repeatable Read 的可见结果。
本节目标
- 用两个 psql 会话复现读取差异
- 解释语句快照与事务快照
- 识别需要重试的序列化失败
必要概念
快照决定读者能看见什么
Read Committed 下，每条查询读取该语句开始时已经提交的数据。Repeatable Read 通常固定在事务第一条实际查询建立的快照。下面的实验用同一行、同一个读取动作对照，避免只背隔离级别名字。
普通读与写锁不是同一机制
MVCC 使普通 SELECT 可以读取已提交版本，不必因为另一会话正在更新同一行而等待其提交。SELECT FOR UPDATE 是带锁读取，行为不同。长事务会保留旧快照，增加旧版本清理压力。
更强隔离伴随重试责任
Repeatable Read 或 Serializable 在并发冲突下可能返回 SQLSTATE 40001。重试应从事务开始重新读取和计算，设置次数上限与退避；不能在已经失败的事务里重发最后一条 SQL。
动手实现
1. 准备专用行并打开会话 A 与 B
在任意会话执行一次第一组语句，记录返回的编号。A、B 均使用 app_user / notes_lab。每个会话执行 \set iso_id 实际编号，示例中的 123 必须替换。每轮实验开始前确保双方已 COMMIT 或 ROLLBACK。
文件：终端 / 当前课时
INSERT INTO notes(title,content) VALUES ('db06-isolation','v1') RETURNING id;
-- 假设返回 123，在 A 和 B 各自设置：
\set iso_id 123
SHOW default_transaction_isolation;
-- read committed
2. 执行 Read Committed 时序
按注释切换窗口，不要一次性粘贴到同一会话。A 第一次读 v1；B 更新但未提交时 A 仍读 v1；B 提交后 A 再读变为 v2。A 的事务没有重开，但下一条查询已经取得新快照。
文件：终端 / 当前课时
-- A：先执行
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT content FROM notes WHERE id=:iso_id;
-- B：执行，先不提交
BEGIN;
UPDATE notes SET content='v2',updated_at=now() WHERE id=:iso_id;
-- A：此时仍为 v1
SELECT content FROM notes WHERE id=:iso_id;
-- B：现在提交
COMMIT;
-- A：现在为 v2，然后结束
SELECT content FROM notes WHERE id=:iso_id;
COMMIT;
3. 执行 Repeatable Read 时序
延续上一步的 v2。A 建立快照后，B 写入 v3 并提交；A 在原事务内仍读 v2。A 提交并开始新查询后读到 v3。记录四次读取结果与相应窗口。
文件：终端 / 当前课时
-- A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT content FROM notes WHERE id=:iso_id;
-- B：自动提交状态
UPDATE notes SET content='v3',updated_at=now() WHERE id=:iso_id;
-- A：原快照仍为 v2
SELECT content FROM notes WHERE id=:iso_id;
COMMIT;
SELECT content FROM notes WHERE id=:iso_id;
-- v3
4. 复现并发更新失败并恢复
A 用 Repeatable Read 读取 v3，B 改成 v4 并提交，A 再更新同一行会遇到 40001。最后 A ROLLBACK 并重新读取，确认新事务看到 v4。这里只演示失败机制，不对包含外部副作用的动作进行盲目重试。
文件：终端 / 当前课时
-- A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT content FROM notes WHERE id=:iso_id;
-- B
UPDATE notes SET content='v4',updated_at=now() WHERE id=:iso_id;
-- A
UPDATE notes SET content='v5' WHERE id=:iso_id;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK;
SELECT content FROM notes WHERE id=:iso_id;
-- v4
验收结果
- Read Committed 在 B 提交前后读到 v1、v2。
- Repeatable Read 在事务中保持 v2，事务结束后读到 v3。
- 并发更新实验出现 40001，ROLLBACK 后读取恢复。
生产约束与故障演练
后台导出需要一致读取，编辑接口同时持续更新笔记。
实现决策
- 根据读取范围选择隔离级别。
- 限制长事务，避免把导出网络传输放在读事务内。
- 40001 重试覆盖完整事务。
隔离环境演练
在 notes_lab 双会话执行快照与并发更新实验，保留实际输出顺序。
恢复验收
- 每个结果可对应到一次提交。
- 实验后无遗留事务。
- 能区分数据可见性与行锁等待。
常见错误
两种隔离级别看起来没有区别
原因：A 没有显式 BEGIN，或者 B 在 A 建立快照之前已提交。
处理：严格按窗口与注释顺序执行，记录每次 BEGIN、SELECT 和 COMMIT 的时点。
实验结束后连接一直 idle in transaction
原因：只观察结果没有结束事务。
处理：两个窗口都执行 ROLLBACK；生产应用用上下文管理事务并限制事务内工作量。
扩展练习
说明报表需要同一时点多次读取，而普通单条查询通常不用提高到 Repeatable Read 的原因。
提示：先确定需要保证的是一条 SQL，还是多条 SQL 共享快照。
参考答案：
单条 SELECT 本身在一个语句快照内执行。需要多条 SELECT 共享一致快照时，可选择短的只读 Repeatable Read 事务；若能用一条 SQL 表达汇总，通常可减少事务持续时间。提高隔离级别不能代替业务约束与正确的重试实现。
技术文档
07 定位锁等待、复现死锁并加入版本检查
观察同一行写入的等待关系，再分别用固定加锁顺序和乐观锁解决不同冲突。
本节目标
- 用 pg_blocking_pids 找到锁等待源头
- 复现并处理死锁回滚
- 用 version 条件阻止覆盖其他人的编辑
必要概念
行锁持续到事务结束
UPDATE 和 SELECT FOR UPDATE 可阻止另一个事务更新同一行。等待不一定是坏事，但必须有时限与可观测性；持锁时调用外部服务会让数据库承担外部延迟。
死锁来自循环等待
A 持有行一等待行二，B 持有行二等待行一，形成闭环。数据库会中止其中一个事务，不能预言一定中止谁。固定顺序获取多行锁可减少死锁，应用仍需识别 40P01 并有限重试整个事务。
版本检查表达编辑冲突
客户端读取 version=1，更新时要求 WHERE version=1 并同时 version=version+1。若别人已提交，更新返回零行，调用者应重新读取并解决冲突。版本检查防止旧内容覆盖新内容，不用于替代所有数据库锁。
动手实现
1. 建立两个实验行并添加版本列
完成前面表结构后执行一次 ADD COLUMN；若已存在 version 则只运行后两条。把返回编号记为 first_id 和 second_id，在 A、B 两个窗口各设置相同变量。
文件：终端 / 当前课时
ALTER TABLE notes ADD COLUMN version integer NOT NULL DEFAULT 1 CHECK (version > 0);
INSERT INTO notes(title) VALUES ('db07-first') RETURNING id;
INSERT INTO notes(title) VALUES ('db07-second') RETURNING id;
-- 在两个窗口替换为实际返回值
\set first_id 124
\set second_id 125
2. 观察一个有上限的锁等待
先 A 加锁，再 B 执行 UPDATE。B 等待期间切回 A 查询阻塞信息；如果 5 秒不够观察，可重做一次。B 约 5 秒后收到 lock timeout，之后必须 ROLLBACK；A 也 ROLLBACK 释放锁。
文件：终端 / 当前课时
-- A
BEGIN;
SELECT id FROM notes WHERE id=:first_id FOR UPDATE;
-- B
BEGIN;
SET LOCAL lock_timeout='5s';
UPDATE notes SET content='waiting' WHERE id=:first_id;
-- A：在 B 等待期间执行
SELECT pid,wait_event_type,wait_event,pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity WHERE datname='notes_lab';
-- B 超时后，以及 A 观察完后，各自执行
ROLLBACK;
3. 按顺序制造死锁
先确认双方没有遗留事务。先 A 锁第一行，再 B 锁第二行，然后 A 请求第二行进入等待，最后 B 请求第一行。一个窗口返回 deadlock detected，另一个窗口可继续获得锁。无论哪个失败，两个窗口最后都执行 ROLLBACK。
文件：终端 / 当前课时
-- A
BEGIN;
SELECT id FROM notes WHERE id=:first_id FOR UPDATE;
-- B
BEGIN;
SELECT id FROM notes WHERE id=:second_id FOR UPDATE;
-- A：此处会等待
SELECT id FROM notes WHERE id=:second_id FOR UPDATE;
-- B：触发循环等待
SELECT id FROM notes WHERE id=:first_id FOR UPDATE;
-- SQLSTATE 40P01 出现在其中一个会话
-- A 和 B 各执行 ROLLBACK;
4. 改为相同顺序锁定多行
在 A 执行下面代码并保持事务，B 执行同样代码会等待 A。A COMMIT 后 B 能继续，然后 B COMMIT。双方都按 id 升序取得目标锁，避免本例的相反顺序。
文件：终端 / 当前课时
BEGIN;
SELECT id FROM notes WHERE id IN (:first_id,:second_id) ORDER BY id FOR UPDATE;
-- A 先 COMMIT，B 等待结束后再 COMMIT
COMMIT;
5. 用旧版本号重放编辑
在同一个窗口按顺序执行即可模拟两个客户端都读到版本 1。先把专用行重置到 version=1；第一次更新得到 2，第二次带旧版本条件返回 0 行。只有实际更新成功才向客户端返回成功。
文件：终端 / 当前课时
UPDATE notes SET version=1,content='base' WHERE id=:first_id;
UPDATE notes SET content='editor A',version=version+1,updated_at=now()
WHERE id=:first_id AND version=1 RETURNING id,content,version;
-- version=2
UPDATE notes SET content='editor B',version=version+1,updated_at=now()
WHERE id=:first_id AND version=1 RETURNING id,content,version;
-- 0 rows
SELECT content,version FROM notes WHERE id=:first_id;
-- editor A, 2
验收结果
- 锁等待能查到非空 blockers，超时后事务可回滚。
- 死锁仅保证一个参与事务被中止，两个窗口最后均无活动事务。
- 旧版本更新返回零行，editor A 的内容没有被覆盖。
生产约束与故障演练
两名用户同时编辑笔记，后台任务又可能批量修改相同记录。
实现决策
- 短事务与锁超时控制等待。
- 多行写入约定一致的锁顺序。
- 普通编辑用版本检查向用户呈现冲突。
隔离环境演练
仅在 notes_lab 双会话制造行锁与死锁，记录等待链、SQLSTATE 和回滚结果。
恢复验收
- 没有无限等待。
- 所有写入入口维护 version。
- 冲突时保留已提交内容并报告更新失败。
常见错误
设置 statement_timeout 后还是不清楚在等什么
原因：语句超时能中断执行，但没有分析锁等待来源。
处理：在等待期间查看 wait_event 与 pg_blocking_pids，结合持锁会话事务时长定位。
版本检查存在但更新仍覆盖内容
原因：SELECT 后在 Python 检查版本，UPDATE 没带 version 条件。
处理：把版本比较和递增放在同一条 UPDATE 中，依据 RETURNING 判定成功。
扩展练习
将批量修改改为按 id 升序锁定，并说明收到 40P01 后重试范围。
提示：不要只重试死锁报错的最后一条 SELECT。
参考答案：
先确定全部目标编号，在同一事务按升序 SELECT ... FOR UPDATE，再执行修改。收到 40P01 后回滚并重新读取整个业务动作的输入，有限退避重试；涉及外部副作用时先使用幂等机制。
技术文档
08 围绕查询设计单列、组合与部分索引
准备足够的数据，对照查询条件与排序建立索引，再核算索引带来的写入成本。
本节目标
- 从 WHERE 与 ORDER BY 推导组合索引
- 用部分索引服务低占比条件
- 知道主键、唯一约束与外键索引的区别
必要概念
索引缩小查找范围
B-tree 索引按键组织数据，使等值、范围与有序读取可以少访问一些数据页。索引占用磁盘，写入和更新时也要维护。是否使用索引由优化器估计成本决定，小表顺序扫描常常更合适。
组合键的顺序来自查询
WHERE pinned=true ORDER BY created_at DESC,id DESC LIMIT 20 可用 pinned、created_at、id 的组合索引。前面的等值条件固定范围，后面提供稳定排序。只对 pinned 这个低基数字段建索引不一定有收益。
部分索引只保存需要的行
如果置顶笔记仅占少数，可只索引 WHERE pinned=true 的记录。查询条件需能匹配该谓词；不要假设参数化的任意布尔条件总能使用该索引。主键和唯一约束会创建索引，外键引用列不自动创建索引。
动手实现
1. 在独立练习库生成性能样本
仅 notes_lab 执行，写入 50,000 条带 db08-bench- 前缀的笔记。此脚本只运行一次，重复执行会额外产生同量数据。固定时间与稀疏 pinned 分布便于对照，但不能代表真实业务分布。
文件：终端 / 当前课时
INSERT INTO notes(title,content,pinned,created_at,updated_at)
SELECT 'db08-bench-' || g, repeat('x',200), g % 100 = 0,
timestamptz '2026-01-01 00:00:00+00' + g * interval '1 second',
timestamptz '2026-01-01 00:00:00+00' + g * interval '1 second'
FROM generate_series(1,50000) AS g;
ANALYZE notes;
SELECT count(*),count(*) FILTER(WHERE pinned) FROM notes;
2. 保存无专用索引时的计划
先只用 EXPLAIN 观察估计计划，下一课运行带 ANALYZE 的实际计划。记录扫描方式、Sort 是否存在及估计行数，不把某个固定 cost 数值作为正确答案。
文件：终端 / 当前课时
EXPLAIN
SELECT id,title,created_at FROM notes
WHERE pinned=true ORDER BY created_at DESC,id DESC LIMIT 20;
SELECT indexname,indexdef FROM pg_indexes WHERE tablename='notes';
3. 建立组合索引并对比
建索引后重新执行相同 EXPLAIN。数据量足够时通常能减少扫描和排序工作，但输出由版本、统计和数据决定。索引包含 id 用于同一时间点下稳定排序。
文件：终端 / 当前课时
CREATE INDEX notes_pinned_created_id_idx ON notes(pinned,created_at DESC,id DESC);
ANALYZE notes;
EXPLAIN
SELECT id,title,created_at FROM notes
WHERE pinned=true ORDER BY created_at DESC,id DESC LIMIT 20;
SELECT pg_size_pretty(pg_relation_size('notes_pinned_created_id_idx')) AS index_size;
4. 将置顶查询改为部分索引实验
为能明确对照，只在 notes_lab 删除刚创建的实验索引，改建部分索引，再执行同样查询。生产建索引可评估 CREATE INDEX CONCURRENTLY 以减少阻塞，但它不能放在事务块中，失败后还需检查无效索引。
文件：终端 / 当前课时
DROP INDEX notes_pinned_created_id_idx;
CREATE INDEX notes_pinned_recent_idx ON notes(created_at DESC,id DESC) WHERE pinned=true;
EXPLAIN
SELECT id,title,created_at FROM notes
WHERE pinned=true ORDER BY created_at DESC,id DESC LIMIT 20;
SELECT pg_size_pretty(pg_relation_size('notes_pinned_recent_idx')) AS index_size;
CREATE INDEX note_tags_tag_id_idx ON note_tags(tag_id);
验收结果
- 记录至少两个版本计划和各自索引大小。
- 能说明 pinned=true 查询与部分索引谓词的关系。
- note_tags 的联合主键覆盖 note_id 开头查找，反向 tag_id 查询有单独索引。
生产约束与故障演练
首页需要快速显示置顶笔记，写入仍持续发生。
实现决策
- 索引对应一条明确查询。
- 用接近真实分布的数据评估读写成本。
- 生产在线建索引单独规划事务与失败恢复。
隔离环境演练
在 notes_lab 比较无索引、组合索引与部分索引的计划与大小。
恢复验收
- 查询顺序确定。
- 有性能证据支持索引选择。
- 没有把练习库 DROP INDEX 直接当成生产变更方案。
常见错误
建了索引仍然 Seq Scan
原因：表很小、命中行过多、统计过旧或查询不匹配索引。
处理：先 ANALYZE 并检查实际 SQL、类型与选择率；不要通过关闭顺序扫描作为生产修复。
索引越加越多，写入越来越慢
原因：每个索引都增加存储与维护，部分索引可能重复覆盖同一查询。
处理：保留使用证据与目标查询，对重复索引评估后再清理，结合写入延迟一起验收。
扩展练习
为 WHERE tag_id=? 查询关联的 note_id 设计索引，并判断现有主键是否足够。
提示：联合主键是 (note_id,tag_id)，注意最左列。
参考答案：
现有主键擅长 note_id 或 note_id+tag_id 条件，不应默认满足按 tag_id 反查的性能需求。可使用 CREATE INDEX ON note_tags(tag_id,note_id)，并在实际数据上对比；若采用此组合索引，应评估是否还需要单列 tag_id 索引。
技术文档
09 读取 EXPLAIN ANALYZE 并改造深分页
用执行计划验证实际工作量，再把大 OFFSET 改成稳定的游标条件。
本节目标
- 读懂实际行数、loops 与 buffers
- 比较浅分页和深分页的扫描量
- 使用 created_at 与 id 实现键集分页
必要概念
ANALYZE 会真的执行 SQL
EXPLAIN 只生成计划，EXPLAIN ANALYZE 会执行语句并收集结果。此课仅分析 SELECT；不要直接拿它分析线上 DELETE 或 UPDATE。实际耗时受缓存与负载影响，需要同时看扫描量和缓冲访问。
深 OFFSET 仍需跳过前面的记录
LIMIT 20 OFFSET 40000 返回 20 行，但执行器通常仍需走过被跳过的记录。键集分页使用上一页最后一行的排序键，直接定位下一段范围。
游标必须与排序完全一致
按 created_at DESC,id DESC 排序，就用 (created_at,id)<(游标时间,游标编号)。id 处理相同时间的并列。游标不是快照；并发删除、排序字段修改仍可能影响跨页视图，导出要另行定义一致性。
动手实现
1. 创建列表索引并收集实际计划
继续使用第 08 课的 50,000 条样本。首次建索引一次，运行 ANALYZE。记录节点名称、actual rows、loops、Buffers 和 Execution Time。不要要求每台机器出现完全相同的执行时间。
文件：终端 / 当前课时
CREATE INDEX notes_created_id_idx ON notes(created_at DESC,id DESC);
ANALYZE notes;
EXPLAIN (ANALYZE,BUFFERS)
SELECT id,title,created_at FROM notes ORDER BY created_at DESC,id DESC LIMIT 20;
2. 比较深分页的额外工作
运行相同排序的深 OFFSET。即使最后只返回 20 行，下层扫描节点通常处理约 40,020 行。若计划不同，按真实节点追踪返回与丢弃的行，不强行匹配示例计划。
文件：终端 / 当前课时
EXPLAIN (ANALYZE,BUFFERS)
SELECT id,title,created_at FROM notes
ORDER BY created_at DESC,id DESC LIMIT 20 OFFSET 40000;
3. 从第一页取得下一页游标
以下 psql 查询取第一页最后一行的时间和编号，\gset 保存为 cursor_created、cursor_id。实际 API 应从已返回的第 20 行取值，不再单独 OFFSET 查询。使用 :'cursor_created' 让 psql 正确引用时间文本。
文件：终端 / 当前课时
SELECT created_at AS cursor_created,id AS cursor_id
FROM notes ORDER BY created_at DESC,id DESC LIMIT 1 OFFSET 19 \gset
SELECT id,title,created_at FROM notes
WHERE (created_at,id) < (:'cursor_created'::timestamptz,:cursor_id)
ORDER BY created_at DESC,id DESC LIMIT 20;
EXPLAIN (ANALYZE,BUFFERS)
SELECT id,title,created_at FROM notes
WHERE (created_at,id) < (:'cursor_created'::timestamptz,:cursor_id)
ORDER BY created_at DESC,id DESC LIMIT 20;
4. 在相近深度公平对照
为了教学比较，先花一次 OFFSET 成本拿到深处游标，再测游标查询本身。实际连续翻页只传前页游标。连续运行两次，说明缓存预热影响，不用单次毫秒值证明普遍结论。
文件：终端 / 当前课时
SELECT created_at AS deep_created,id AS deep_id
FROM notes ORDER BY created_at DESC,id DESC LIMIT 1 OFFSET 39999 \gset
EXPLAIN (ANALYZE,BUFFERS)
SELECT id,title,created_at FROM notes
WHERE (created_at,id) < (:'deep_created'::timestamptz,:deep_id)
ORDER BY created_at DESC,id DESC LIMIT 20;
验收结果
- 记录浅 OFFSET、深 OFFSET 与深游标的实际扫描量。
- 下一页与前页没有重复编号，排序键与过滤条件完全一致。
- 能区分计划估计值与实际执行值，不把 cost 当成毫秒。
生产约束与故障演练
笔记数量增长后，第几百页响应变慢，而第一页正常。
实现决策
- 以实际扫描量解释深分页成本。
- 连续滚动用键集分页，任意跳页另做产品和性能取舍。
- 游标字段选择稳定值，分页不默认承诺全程快照。
隔离环境演练
在 notes_lab 对比相同深度的 OFFSET 与游标查询，记录执行计划。
恢复验收
- 深页工作量明显下降或有证据解释未下降原因。
- 同时间记录不重复。
- 测试记录注明样本规模与缓存状态。
常见错误
分页重复或漏掉相同时间的记录
原因：只把 created_at 放进排序或游标，缺少唯一的并列键。
处理：排序和比较同时包含 id，并让排序方向与比较符号一致。
游标查询变慢且出现 Sort
原因：缺少匹配排序的索引，或添加了新筛选条件改变访问方式。
处理：对实际查询重新 EXPLAIN ANALYZE，检查复合索引与筛选顺序，而不是只调 LIMIT。
扩展练习
给置顶列表加入键集分页，并说明使用哪个已有索引。
提示：保留 pinned=true，同时添加二元组范围条件。
参考答案：
SELECT id,title,created_at FROM notes WHERE pinned=true AND (created_at,id)<($1,$2) ORDER BY created_at DESC,id DESC LIMIT 20; 这是数据库参数形式示意，psycopg 中使用 %s。第 08 课的 notes_pinned_recent_idx 对该固定谓词和排序有用，仍需实际执行计划验证。
技术文档
10 用 psycopg 连接池管理连接与事务
让并发代码借用有限连接，复现池耗尽，确保异常后连接能够归还。
本节目标
- 建立一个可显式开启和关闭的连接池
- 把事务包含在借还连接的作用域内
- 通过超时和统计观察池拥塞
必要概念
连接池是有限资源预算
每个连接对应数据库后端资源。池在 min_size 与 max_size 范围复用连接，借不到时等待而非无限新建。进程数乘每进程最大池容量，再加任务、运维与其他服务连接，必须落在数据库预算内。
连接不等于业务并发数
多个请求可以排队等待少量连接。持有连接的时间越长，可服务吞吐越低。一个连接上的查询串行执行；不同线程不要把同一连接当作各自独立事务。同步 psycopg 应运行在同步代码或受控线程中。
借还边界包含提交与回滚
pool.connection() 的上下文负责正常结束时提交、异常退出时回滚并归还连接。应用启动创建池、退出关闭池；业务函数借用连接，不在每次调用时重新创建整个池。异步 API 对应 AsyncConnectionPool，在 API + 数据库模块接入。
动手实现
1. 安装池依赖
沿用第 03 课 PG 环境变量。在虚拟环境运行 python -m pip install "psycopg[binary,pool]>=3.2,<4"，然后更新 requirements-lock.txt。psycopg_pool 是单独分发的依赖，安装 psycopg 本体不代表已安装连接池。
文件：终端 / 当前课时
python -m pip install "psycopg[binary,pool]>=3.2,<4"
python -m pip freeze > requirements-lock.txt
2. 运行并发读的完整示例
保存为 db_pool.py 并运行。4 个工作线程共用最大 2 条连接；每次函数调用独立借还。open=False 加 open(wait=True) 让启动阶段就确认连接可用，退出通过 finally 关闭。应用不应把密码或整条连接信息打印到日志。
文件：db_pool.py
from concurrent.futures import ThreadPoolExecutor
from psycopg_pool import ConnectionPool
pool = ConnectionPool("", min_size=1, max_size=2, timeout=2, open=False,
kwargs={"application_name": "notes-service-db-lab"})
def count_notes(_):
with pool.connection() as conn:
with conn.cursor() as cur:
cur.execute("SET LOCAL statement_timeout='2s'")
cur.execute("SELECT count(*) FROM notes")
return cur.fetchone()[0]
try:
pool.open(wait=True, timeout=10)
with ThreadPoolExecutor(max_workers=4) as workers:
print(list(workers.map(count_notes, range(8))))
print(pool.get_stats())
finally:
pool.close()
3. 验证异常后事务回滚
保存为 db_pool_rollback.py。故意抛出 Python 异常，异常必须离开 connection 上下文后再捕获，否则上下文可能把事务当作正常结束而提交。第二次借用连接后查询结果应为 0。
文件：db_pool_rollback.py
from psycopg_pool import ConnectionPool
with ConnectionPool("", min_size=1, max_size=1) as pool:
pool.wait(timeout=10)
note_id = None
try:
with pool.connection() as conn:
note_id = conn.execute(
"INSERT INTO notes(title) VALUES (%s) RETURNING id",
("db10-rollback",),
).fetchone()[0]
raise RuntimeError("simulated failure after insert")
except RuntimeError:
pass
with pool.connection() as conn:
count = conn.execute("SELECT count(*) FROM notes WHERE id=%s", (note_id,)).fetchone()[0]
assert count == 0
print("rolled back", count)
4. 可控复现池耗尽
保存为 db_pool_timeout.py，只在本机练习运行。max_size=1 时外层已经占用唯一连接，内层借用 0.2 秒后超时；捕获异常后退出外层归还连接，再借用应成功。生产代码应消除嵌套借还并缩短持有时间，不能只扩大池。
文件：db_pool_timeout.py
from psycopg_pool import ConnectionPool, PoolTimeout
with ConnectionPool("", min_size=1, max_size=1) as pool:
pool.wait(timeout=10)
with pool.connection():
try:
with pool.connection(timeout=0.2):
pass
except PoolTimeout:
print("expected pool timeout")
with pool.connection() as conn:
print("recovered", conn.execute("SELECT 1").fetchone()[0])
print(pool.get_stats())
验收结果
- 并发读取成功，连接数量受 max_size=2 限制。
- 异常插入的笔记在下一次借用中查不到。
- 池耗尽产生 PoolTimeout，归还后 SELECT 1 恢复。
生产约束与故障演练
API 与任务进程同时访问 PostgreSQL，突发请求不应耗尽全部数据库连接。
实现决策
- 按进程数计算总连接预算。
- 连接等待和 SQL 执行分别设时限。
- 业务异常离开事务边界后再转为响应。
隔离环境演练
在 notes_lab 用单连接池制造超时和事务异常，再确认连接能归还复用。
恢复验收
- 没有连接泄漏。
- 等待有时限与可记录指标。
- 连接池在应用生命周期内创建和关闭。
常见错误
高并发时不断出现 PoolTimeout
原因：连接持有时间过长、嵌套借用或总并发超过容量。
处理：检查等待数和事务时长，先缩短查询与持有范围，再按数据库预算调整池。
async 路由中数据库调用阻塞其他请求
原因：在事件循环中直接执行同步 psycopg 查询。
处理：用 AsyncConnectionPool 与 await，或在受控线程执行同步代码；不要只把函数名改成 async。
扩展练习
4 个 API 进程各 max_size=8，两个任务进程各 max_size=5，预留运维 10 条连接，至少需要规划多少连接？
提示：进程之间不共享一个 Python 连接池。
参考答案：
仅这些进程峰值预算是 4×8+2×5+10=52 条，还应核查迁移、监控、其他服务和数据库保留连接。52 是这组假设下的预算，不是直接把 max_connections 设为 52 的建议。
技术文档
11 拆分数据库权限并管理连接配置
让运行时账号只做业务读写，限制危险语句，并检查日志和连接配置中的敏感信息。
本节目标
- 区分迁移账号与 API 运行账号
- 配置现有对象和未来对象的授权
- 验证最小权限以及超时配置
必要概念
权限应按工作职责分配
app_user 保留为练习的对象所有者和迁移身份；新增 notes_runtime 供 API 运行。运行时需要 SELECT、INSERT、UPDATE、DELETE，不需要 CREATE ROLE、DROP TABLE 或 ALTER TABLE。数据库 CONNECT、schema USAGE、表权限是不同层。
未来对象不会自动继承手工授权
GRANT ON ALL TABLES 只覆盖当时存在的表。ALTER DEFAULT PRIVILEGES 针对特定创建者未来创建的对象生效，必须由正确身份设置。生产服务若只需要部分表，应显式授权，不能照搬练习的全部业务表授权。
连接加密与秘密存储
生产连接优先使用 TLS 且校验证书和主机名，例如 sslmode=verify-full 配合可信根证书。本机官方镜像没有自动配置可校验 TLS，不应伪称本课容器已提供 TLS。密码通过环境或秘密文件注入，SQL 参数和业务正文也可能包含敏感数据。
动手实现
1. 管理身份创建运行角色
使用第 01 课 docker exec -it notes-postgres psql -U postgres -d notes_lab 进入管理连接。密码通过 \password 交互设置。PUBLIC 是所有角色的隐式集合，撤销公开连接只限此独立练习库，不在共享线上库直接执行。
文件：终端 / 当前课时
CREATE ROLE notes_runtime LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;
\password notes_runtime
REVOKE CONNECT ON DATABASE notes_lab FROM PUBLIC;
GRANT CONNECT ON DATABASE notes_lab TO app_user,notes_runtime;
ALTER ROLE notes_runtime IN DATABASE notes_lab SET statement_timeout='3s';
ALTER ROLE notes_runtime IN DATABASE notes_lab SET lock_timeout='1s';
ALTER ROLE notes_runtime IN DATABASE notes_lab SET idle_in_transaction_session_timeout='30s';
2. 所有者身份授权现有与未来对象
重新用 app_user 连接 notes_lab 执行。当前课程所有业务表都位于 public，生产可改成独立 schema 并按表授权。identity 之外若使用显式序列默认值，需要相应 sequence 权限；本例将业务序列使用权限一并授予。
文件：终端 / 当前课时
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO notes_runtime;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO notes_runtime;
GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA public TO notes_runtime;
ALTER DEFAULT PRIVILEGES FOR ROLE app_user IN SCHEMA public
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO notes_runtime;
ALTER DEFAULT PRIVILEGES FOR ROLE app_user IN SCHEMA public
GRANT USAGE,SELECT ON SEQUENCES TO notes_runtime;
3. 运行身份验证允许与拒绝操作
通过 docker exec -it notes-postgres psql -h 127.0.0.1 -U notes_runtime -d notes_lab -W 连接。插入成功后删除本次编号；尝试 ALTER TABLE 会因不是所有者失败，不应产生新列。
文件：终端 / 当前课时
SELECT current_user;
INSERT INTO notes(title) VALUES ('db11-runtime') RETURNING id AS runtime_id \gset
DELETE FROM notes WHERE id=:runtime_id;
ALTER TABLE notes ADD COLUMN should_not_exist text;
-- ERROR: must be owner of table notes
SHOW statement_timeout;
SHOW lock_timeout;
SELECT pg_sleep(4);
-- 约 3 秒取消，SQLSTATE 57014
4. 验证未来表权限并调整应用配置
以 app_user 创建临时验收表，再以 notes_runtime SELECT 应成功。验收后 app_user 删除该专用表。把应用 PGUSER 改为 notes_runtime 并注入对应密码，迁移工具继续单独使用 app_user。检查日志只记录操作名、耗时和 SQLSTATE，不打印密码或完整正文。
文件：终端 / 当前课时
-- app_user
CREATE TABLE permission_probe(id integer PRIMARY KEY);
INSERT INTO permission_probe VALUES (1);
-- notes_runtime：应该读到 1
SELECT id FROM permission_probe;
-- app_user：验收结束清理本课专用表
DROP TABLE permission_probe;
验收结果
- notes_runtime 可以执行业务 CRUD，不能修改表结构。
- 新连接显示 3 秒语句超时和 1 秒锁超时。
- app_user 创建的未来表遵循默认权限，密码没有写进仓库和示例日志。
生产约束与故障演练
API 连接配置泄漏或代码出现错误时，需要限制其能影响的数据库对象。
实现决策
- 运行角色与迁移角色使用不同秘密。
- 超时配置需重连生效并实测。
- 审计日志避免包含原始敏感正文。
隔离环境演练
在 notes_lab 用运行角色尝试 ALTER TABLE 并执行超时查询，验证拒绝和恢复。
恢复验收
- 运行角色不是所有者或超级用户。
- 权限随未来迁移保持可用。
- 生产 TLS、凭据注入和轮换方式有明确配置责任。
常见错误
现有表能访问，新迁移建的表报 permission denied
原因：只给现有表 GRANT，或默认权限针对了错误创建角色。
处理：检查实际迁移角色，给该角色设置 ALTER DEFAULT PRIVILEGES，并补授权已经创建的对象。
sslmode=require 就被当成完成服务器身份校验
原因：只要求加密不等于完整证书与主机名校验。
处理：生产按基础设施证书配置 verify-full 与根证书；本机未配 TLS 的容器使用明确的开发配置。
扩展练习
给只读报表账号设计最小权限，说明为什么不应复用 app_user。
提示：需要 CONNECT、schema USAGE 和必要表的 SELECT；不授予写入或对象所有权。
参考答案：
创建独立 LOGIN 角色，授予 notes_lab 的 CONNECT、public 的 USAGE、指定业务表的 SELECT。为其设置合适超时与只读事务默认值作为附加限制；最终权限仍由 GRANT 决定。app_user 是对象所有者，权限范围远超报表需求。
技术文档
12 备份恢复与可回退的结构变更
导出练习库并恢复到另一个数据库，通过数据核对和迁移演练完成生产验收。
本节目标
- 用 pg_dump 创建逻辑备份并校验文件
- 在独立恢复库验证结构、数据和序列
- 使用扩展、回填、验证的步骤变更表结构
必要概念
备份文件可生成不代表能恢复
逻辑备份包含数据库对象与数据，恢复需要兼容的工具、角色与目标环境。单库 pg_dump 不包含整个实例的角色定义，也不能独自提供任意时间点恢复；严格 RPO 需要基础备份与 WAL 归档等方案。
RPO 和 RTO 是验收条件
RPO 是可接受的数据丢失时间范围，RTO 是恢复服务需要的时间上限。备份频率、保留、异地副本与恢复速度必须与业务目标一致。本课实际测量恢复步骤，而不把本机几秒恢复推断为线上保证。
结构变更考虑新旧代码并存
增加可空列通常比直接删除或改名更容易兼容。先加字段，再部署兼容代码，分批回填并验证，最后收紧约束。回滚代码不意味着可以随意 DROP 已有用户数据的新列，破坏性变更需要单独恢复设计。
动手实现
1. 生成备份与源库验收数据
以下 Linux Bash 在 notes-service 执行，需要 PostgreSQL 16 客户端 pg_dump、psql、pg_restore；沿用 PGHOST/PGPORT/PGDATABASE，PGUSER 使用 app_user 且注入其密码。备份目录限制权限。命令失败必须停止，不继续上传空文件。
文件：终端 / 当前课时
set -e
mkdir -p backups
chmod 700 backups
pg_dump --version
pg_dump -Fc --no-owner --no-acl -f backups/notes_lab.dump
sha256sum backups/notes_lab.dump > backups/notes_lab.dump.sha256
pg_restore --list backups/notes_lab.dump > backups/notes_lab.contents.txt
psql -At -v ON_ERROR_STOP=1 -c "SELECT count(*),min(id),max(id),count(*) FILTER(WHERE pinned) FROM notes" > backups/source-notes.txt
sha256sum -c backups/notes_lab.dump.sha256
2. 创建隔离恢复目标并恢复
管理连接执行 CREATE DATABASE notes_restore OWNER app_user;，只在本机首次创建该专用恢复库。不要向原 notes_lab 恢复。下面 --no-owner --no-acl 使本课恢复对象由 app_user 持有，权限在恢复后按第 11 课脚本重建；这不是自动恢复所有生产角色。
文件：终端 / 当前课时
# Linux Bash，PGUSER=app_user，使用相应密码
pg_restore --exit-on-error --single-transaction --no-owner --no-acl \
-h 127.0.0.1 -p 5433 -U app_user -d notes_restore backups/notes_lab.dump
psql -d notes_restore -At -v ON_ERROR_STOP=1 \
-c "SELECT count(*),min(id),max(id),count(*) FILTER(WHERE pinned) FROM notes" > backups/restored-notes.txt
diff backups/source-notes.txt backups/restored-notes.txt
3. 核查恢复后的关系与新写入
使用 app_user 连 notes_restore 执行。反关联查询均应为 0。插入能生成合法新编号，ROLLBACK 避免保留验收笔记；序列可能前进一位，这是正常行为。若源库备份期间持续写入，外部另查的 count 不一定对应 pg_dump 快照；本课在停止练习写入后对照。
文件：终端 / 当前课时
SELECT count(*) FROM note_tags nt LEFT JOIN notes n ON n.id=nt.note_id WHERE n.id IS NULL;
SELECT count(*) FROM note_tags nt LEFT JOIN tags t ON t.id=nt.tag_id WHERE t.id IS NULL;
BEGIN;
INSERT INTO notes(title) VALUES ('db12-restore-probe') RETURNING id,title;
ROLLBACK;
\d notes
\d note_tags
4. 在练习源库做一次兼容扩展迁移
重新连接 notes_lab / app_user。创建 archived_at 可空列表示未归档；保存为 sql/003_archived.sql，使用 psql -v ON_ERROR_STOP=1 -f sql/003_archived.sql 执行一次。短 lock_timeout 保证拿不到结构锁时尽快失败，再由人检查阻塞后择机重试。它不能保证 DDL 永远无阻塞。
文件：sql/003_archived.sql
BEGIN;
SET LOCAL lock_timeout='1s';
SET LOCAL statement_timeout='5s';
ALTER TABLE notes ADD COLUMN archived_at timestamptz;
COMMIT;
5. 验证新旧访问兼容并记录恢复方案
旧查询只选择明确列名，不受新列影响。新查询把 archived_at IS NULL 当作活动笔记。故障演练是在恢复库模拟错误更新再从备份另建恢复目标，不覆盖源库。本课不实际给出删除全表语句；验收恢复耗时、权限重建与业务请求都必须列入恢复记录。
文件：终端 / 当前课时
SELECT id,title,pinned FROM notes ORDER BY id DESC LIMIT 3;
SELECT id,title FROM notes WHERE archived_at IS NULL ORDER BY id DESC LIMIT 3;
SELECT column_name,is_nullable,data_type
FROM information_schema.columns
WHERE table_name='notes' AND column_name='archived_at';
-- is_nullable=YES，data_type=timestamp with time zone
验收结果
- 备份校验和正确，pg_restore 清单包含业务表、约束和数据。
- 独立恢复库行数与静止源库一致，外键无孤儿，新插入成功。
- 新增字段后旧查询与新查询都可运行，迁移失败有停止和重试策略。
生产约束与故障演练
发布结构变更之前，需要证明数据库可恢复，旧版本服务仍能短暂并存。
实现决策
- 恢复目标与源库严格分开。
- 备份验收覆盖数据、约束、权限与新写入。
- 新增字段先兼容，再部署和回填；迁移文件纳入版本管理。
隔离环境演练
在独立 notes_restore 检查备份恢复，然后在 notes_lab 用短锁超时演练新增可空字段；所有操作保持目标库可辨认。
恢复验收
- 记录实际恢复耗时与备份时间。
- 恢复库能完成业务 CRUD。
- 迁移前后访问兼容，生产 RPO/RTO 不由本机测试推定。
常见错误
备份命令成功，但恢复时缺角色或权限
原因：把单库逻辑备份当成完整实例备份，未规划角色与授权。
处理：在恢复流程中显式重建角色和授权，或由受控配置管理维护；本课使用 no-owner/no-acl 后重新授权。
上线加字段一直等待，API 也开始变慢
原因：DDL 正在等待强锁，可能与长事务形成阻塞队列。
处理：用短 lock_timeout 退出，查等待链并缩短长事务；在明确窗口重试，不无限等锁。
恢复行数不等于备份后源库行数
原因：源库继续写入，两个统计不属于同一快照。
处理：练习对比时暂停写入；生产验收使用与备份快照一致的方法或业务不变量，不把后续变化误判为备份损坏。
扩展练习
为“每日备份、允许最多丢失 15 分钟数据”的需求判断现有方案是否合格，并写出补充项。
提示：先将备份间隔与 RPO 对照，再考虑恢复演练。
参考答案：
单独每日 pg_dump 无法满足 15 分钟 RPO。需要能够持续归档的 WAL 与基础备份或受管数据库时间点恢复，并验证归档延迟、保留期、异地副本、恢复步骤和监控；按目标时间点实际演练并测量 RTO。