场景 线上 250 万行的 order_items 要加一列，操作不当全站写入卡死。

讲解 关键在「要不要重写整张表」：加列带 DEFAULT（PG 11+）只改元数据，秒回；varchar(20) 放宽到 varchar(50) 不重写，很快；收窄长度或跨类型转换（varchar 改 int）要全表重写，期间持锁。动手前先在测试库跑一遍看耗时。

alter table order_items add column note text default '';          -- 秒回
alter table order_items alter column note type varchar(50);    -- 不重写，快
alter table order_items alter column note type integer;        -- 全表重写，锁到跑完

易错 「加列带默认值会锁表」是 PG 10 及更早的老黄历；但「改类型」的雷是真的--生产 DDL 前必须测耗时。

场景 新建 payments 表：主键用自增还是 uuid？这不是口味题。

讲解 bigserial 是老语法（本质是个宏：建序列 + 默认值）；identity 是 SQL 标准（PG 10+），语义更严--always 版阻止手动插 id，细节不受干扰；uuid 全局唯一，多库合并、分布式生成不冲突。练习库 orders 用的 uuid（导出文件就是无序编号），payments 可以用 identity。

create table payments (
  id bigint generated always as identity primary key,
  ...
);

易错 serial 不是一种「类型」--面试官爱问这个。新项目默认 identity，需要跨系统唯一才上 uuid。

场景 后端说 uuid 好看又安全，全表都上--先看账单。

讲解 ① 索引膨胀：uuid 是 16 字节，bigint 是 8 字节，索引天然大一倍；② 随机写放大：uuid 无序，新行落在 B+ 树随机位置，频繁页分裂、缓存局部性差。写入量大的表，uuid 索引的插入明显慢。缓解：uuidv7（时间前缀、单调递增）。

易错 面试答「uuid 有什么问题」只说「占空间」不够--写放大和页分裂才是核心。

场景 三个最高频的类型问题一次结清。

讲解 ① 金额：numeric 是精确十进制，float 是二进制近似（0.1 存不准）；② timestamptz 磁盘上存的是 UTC 时刻（自 2000-01-01 的微秒数），时区只是显示层的换算；③ PG 的 text 无长度上限，varchar(n) 有硬上限（超长报错不截断）--真要限制长度，text + check(length(x) <= n) 更灵活。

select 0.1::float8 + 0.2::float8,      -- 0.30000000000000004
       0.1::numeric + 0.2::numeric;      -- 0.3

show timezone;      -- 只影响显示，不影响存储
select now() at time zone 'Asia/Shanghai';

易错 「timestamptz 存的是带时区的时间」是常见错答--存的是 UTC 时刻，时区是会话显示参数。

场景 商品的扩展属性老变、状态值固定、标签一对多但懒得建表--三种「不走寻常路」的类型。

讲解 jsonb：半结构化数据（扩展属性、接口报文），可 GIN 索引查询；enum：值集固定不变的状态（可读性好），加值要 ALTER 类型；数组：一对多的轻量场景（标签、权限位）。生成列 generated always as (表达式) stored：写入时自动算好存盘，读取零成本。

create table order_items (
  ...,
  line_total numeric generated always as (qty * unit_price) stored
);
insert into order_items(order_id, product_id, qty, unit_price)
values (1, 1, 3, 9.90);   -- line_total 自动 = 29.70

易错 enum 加值容易删值难（删要重排）；值集将来会变就用 text + check。jsonb 滥用会把「无 schema 的灵活」变成「无 schema 的泥潭」。

场景 后端说「数据库要能挡脏数据」--挡脏数据的就是这五个门卫。

讲解 各管一件事：PK = 唯一 + 非空（行的身份证）；FK = 引用必须存在（明细指向的订单必须是真订单）；UNIQUE = 列组合不重复；CHECK = 自定义规则（金额 > 0）；NOT NULL = 必填。约束在写入时检查，违反即整条语句失败--脏数据根本进不了库。

create table payments (
  id      bigint generated always as identity primary key,
  order_id uuid not null references orders(id),
  method  text not null,
  amount  numeric(10,2) not null check (amount > 0),
  status  text not null,
  paid_at timestamp,
  unique (order_id, method)          -- 同一订单同渠道只有一笔
);

易错 约束挡的是「结构性错误」；「业务逻辑错」（把 100 元记成 1000 元）永远要靠流程和评审。

场景 删一个用户，他的订单、明细怎么办？建表时就要回答。

讲解 on delete cascade：连带删光子行；set null：子行保留、外键置空（列必须可空）；restrict（默认）：有子行就拒绝删父行。选哪个是业务决策：审计要求高的库默认 restrict，别让一次 delete 引发雪崩。

-- 实验：删用户，订单连带消失
alter table orders
  drop constraint orders_user_id_fkey,
  add constraint orders_user_id_fkey
    foreign key (user_id) references users(id) on delete cascade;

delete from users where id = '...';   -- orders 里他的订单同时没了

易错 cascade 链会一路传导（订单没了 -> 明细也没了）且静默发生；生产上删数据前先数一下子行有多少。

场景 面试题：唯一约束和唯一索引是不是一回事？

讲解 在 PG 里功能等价：建唯一约束时 PG 自动建一个唯一索引来实现它。区别在意图层：约束是声明（表达业务规则），索引是实现（顺带加速）。写 DDL 用约束语义更清晰；已存在的唯一索引也被视作可用的冲突目标（on conflict 能用）。另外 PG 默认多个 NULL 不算重复。

-- 两种写法效果等价
alter table users add constraint uq_email unique (email);
create unique index idx_email on users (email);

select count(*) from (values (null),(null)) t(x);   -- 两个 null 并存
-- unique 列同理：多个 NULL 行可以共存（PG 15+ 可用 nulls not distinct 改变）

易错 「唯一列可以有多个 NULL」常被当成 bug 上报--其实是 SQL 标准语义。

场景 技术评审会上最热闹的一题：后端嫌外键碍事，DBA 嫌没有外键心慌。

讲解 支持：数据库兜底正确性（应用有 bug 也挡得住）、自带文档作用、防止孤儿数据。反对：每次写入多一次引用检查（高频写场景有感）、锁竞争、分库分表后外键根本没法跨库。成熟观点不是站队，是分场景：单库中小规模用；超大规模 / 分库分表靠应用保证 + 定期跑一致性校验脚本。

易错 面试回答这题要给出「在什么规模、什么前提下我会改变结论」--只喊口号不得分。

场景 掰扯范式之前，先学会说「谁决定谁」。

讲解 函数依赖：知道 A 就能唯一确定 B，记作 A -> B（user_id -> 用户名）。候选键：能唯一确定整行的最小列集（orders 的 id；user_id + created_at 若唯一也是候选键）。范式理论的全部推导都建立在这两个概念上，不难，难的是把业务里「谁决定谁」说准。

-- 检验 user_id -> city 是否成立：一个用户出现两个城市就是反例
select user_id, count(distinct city) as cities
from (select o.user_id, u.city from orders o join users u on u.id = o.user_id) t
group by user_id
having count(distinct city) > 1;

易错 函数依赖是业务事实不是 SQL 特性--它由「一个用户只有一个城市」这类现实规则决定，表设计只能遵守或违反它。

场景 面试让你「讲讲三范式」--拿订单表一步步拆，比背定义有力得多。

讲解 ① 1NF：字段原子化--别在一个列里塞「红色,XL」；② 2NF：消除部分依赖--非键列不能只依赖复合键的一部分（明细表里放商品名，商品名只依赖 product_id，不依赖整个 (order_id, product_id)）；③ 3NF：消除传递依赖--键 -> A -> B 的 B 不该存（存了 category_id 就别存 category 名）；④ BCNF：所有决定因素都得是候选键，是 3NF 的收紧版。

-- 反例：一张「订单宽表」
-- orders_wide(order_id, product_id, 商品名, 类目名, 用户名, 城市, ...)
-- 2NF 违例：商品名只依赖 product_id
-- 3NF 违例：类目名依赖 类目id（不在键里）、城市依赖 user_id
-- 拆法：order_items 只留 (order_id, product_id, qty, unit_price)

易错 讲范式别背「确保数据一致性」这种空话--每级都配一个「会出什么异常」的例子：改商品名要改一万行、插入没有订单的新商品没地方放。

场景 范式全守住了，报表要连七张表才出数--该反范式出场了。

讲解 三种形态按激进程度排：① 冗余字段（订单里存下单时的用户名）；② 预聚合列（orders 上加 item_count，免得每次 count 明细）；③ 宽表（为报表单独建一张大宽表，ETL 定期刷新）。共同点：用一致性维护成本换读取性能。

-- ② 预聚合列的读取收益
select id, item_count from orders;              -- 免 join 明细
-- 维护成本：每次增删明细都要同步（见下一条）

易错 反范式不是「更先进」，是拿写复杂度换读性能的交易--读得少的表做反范式是纯亏本。

场景 后端问：那冗余的用户名，用户改名了怎么办？--问到点子上了。

讲解 冗余数据会漂移。两种处理：① 当快照（订单存的是下单当时的名字，故意不同步--历史就该是历史）；② 当缓存（要的就是当前值，必须同步：触发器 / 应用双写 / 定时校验，三选一）。冗余前先回答是哪种。order_items.unit_price 为什么冗余得理直气壮？因为它是价格快照：商品后来改价，当时的成交价不该变。

-- 定时校验（兜底方案）：找出漂移的冗余行
select o.id
from orders_wide o
join users u on u.id = o.user_id
where o.用户名 <> u.name;   -- 有结果就该同步了

易错 说不清「快照还是缓存」的冗余列，上线半年后没人敢动它--这是最贵的技术债。

场景 再也不用一行一条地插了。

讲解 values (...), (...), ... 一条语句插多行（减少往返，比循环单插快一个量级）；insert into t select ... 从查询结果直接灌数--你已经在 seed.sql 里见过它几百次了。

insert into payments(order_id, method, amount, status, paid_at)
values ('...', 'alipay', 99.00, 'paid', now()),
       ('...', 'wechat', 59.00, 'paid', now());

insert into payments(order_id, method, amount)
select id, 'free', 0 from orders where status = 3;   -- 从查询灌

易错 单条多行插入是一个语句：任何一行违反约束，整条全部失败（原子性）。

场景 同一笔回调来三次，账只能入一次--幂等的核心武器。

讲解 语法 = 冲突目标 + 动作：on conflict (唯一键列)，撞上唯一约束时执行 DO NOTHING（跳过）或 DO UPDATE（改为更新）。DO UPDATE 里用 excluded.列 引用「这次想插进去的那行」。

insert into payments(order_id, method, amount, status, paid_at)
values ('...', 'alipay', 99.00, 'paid', now())
on conflict (order_id, method)          -- 撞唯一约束
do update set status = 'paid',
             paid_at = excluded.paid_at;  -- 用新值更新
-- 重复执行 N 次，payments 里永远只有这一行 = 幂等

易错 前提是先有唯一约束--没有唯一键，on conflict 不知道「冲突」看哪里。excluded 别名是固定写法，不是表名。

场景 插完订单要用它的自增 id 建明细--以前的写法是再查一遍？

讲解 insert / update / delete ... returning * 把受影响的行直接返回，一步拿回自增 id、默认值、生成列。应用层省一次往返，还避免「插完再按条件查」的竞态。

insert into orders(user_id, status, total_amount, created_at)
values ('...', 1, 128.00, now())
returning id, created_at;   -- 直接拿到生成的 uuid 和默认值

delete from payments where status = 'failed'
returning id;               -- 删了哪些，当场对账

易错 returning 返回的是本次语句影响的行；0 行返回就是没删没改，应用层别当成功处理。

场景 「把某类目下所有商品调价 10%」--更新条件在另一张表里。

讲解 PG 方言两件套：update t set ... from 参照表 where 关联条件；delete from t using 参照表 where 关联条件。等价于「join 着改 / 删」，比「先查 id 再 in (...)」少一步。

update products p
set price = round(p.price * 1.1, 2)
from categories c
where c.id = p.category_id
  and c.name = '手机';

delete from order_items i
using orders o
where i.order_id = o.id
  and o.status is null;   -- 脏订单的明细清掉

易错 update ... from 里若 join 出多行，结果是「随机一行生效」--关联键不唯一时会悄悄错；先确保一对一。

场景 要灌 10 万行 CSV，insert 跑了一分钟。

讲解 copy 表 from ... csv 绕过 SQL 解析层，按二进制/文本协议直灌，大批量导入快一个数量级。psql 里用 \copy（读客户端本地文件）；SQL 的 COPY 读服务端文件（要权限）。

-- psql 客户端：导入本地 CSV
\copy products(id, name, price) from 'products.csv' with csv header

-- 先建表结构，copy 只管数据
copy products(id, name, price) from '/tmp/products.csv' with csv header;

易错 copy 不走 on conflict / returning；要幂等或拿回结果还得 insert。它是纯粹的「快」。

场景 扣款成功订单没建--两条写入必须同生共死，这就是事务存在的意义。

讲解 教科书答案：A（原子）靠回滚日志、C（一致）靠约束、I（隔离）靠锁 + MVCC、D（持久）靠 WAL 预写日志。PG 特殊点要能接得住追问：PG 没有 undo log，A 靠 MVCC--回滚时新写的行版本直接标记作废（死元组），vacuum 收尾。答「PG 用 undo 保证原子性」是把 MySQL 的答案串了台。

begin;
update products set stock = stock - 1 where id = 1;
insert into orders(user_id, status, total_amount, created_at)
values ('...', 1, 99.00, now());
-- 中间任何一步失败：
rollback;   -- 库存和白下的单一起消失
commit;     -- 或者一起落盘

易错 ACID 每个字母「由什么机制保证」是高频面试题；PG 版答案和通用版答案的差异是加分位。

场景 事务里的「存档点」：一批操作里有一部分失败了，只想重做那一部分。

讲解 begin 开事务、commit 落盘、rollback 全撤销；savepoint 名字 在事务里打存档点，rollback to savepoint 名字 只回滚到存档点、事务继续。适合「批量处理里单条失败跳过继续」的场景。

begin;
savepoint sp1;
insert into payments(order_id, method, amount) values ('...', 'x', -1);
-- 违反 check 报错，事务进入 aborted 状态
rollback to savepoint sp1;   -- 只回滚这条，事务继续
insert into payments(order_id, method, amount) values ('...', 'alipay', 99);
commit;

易错 语句报错后事务进入 aborted 状态，必须 rollback（或 to savepoint）才能继续执行后续语句--不回滚就接着写会一路报错。

场景 你没写 begin，为什么每条语句也「像个事务」？

讲解 psql 里不显式写 begin，每条语句被自动包一层单语句事务、立即提交。反过来，显式事务里的 DDL 也能回滚--PG 的 DDL 是事务性的，建表、加列、建索引都能 rollback；MySQL 的 DDL 会隐式提交、不可回滚。

begin;
create table t_test (id int);
rollback;
-- t_test 不存在了：DDL 也被回滚（PG 特性，MySQL 不行）

易错 「DDL 不能回滚」是 MySQL 的规矩，当通用结论背会在 PG 面试里翻车。

场景 周五下午全站变慢，查出来是一个开了两小时没提交的报表查询。

讲解 长事务三宗罪：① 它的快照让 vacuum 不敢清理之后产生的死元组 -> 表膨胀；② 事务 ID 有回卷风险，老事务拖住全局；③ 持有的锁一直不放，别人排队。排查入口：pg_stat_activity 看 xact_start 最老的几个。

select pid, state, xact_start, now() - xact_start as 事务年龄,
       left(query, 60) as query
from pg_stat_activity
where xact_start is not null
order by xact_start
limit 5;

-- select pg_terminate_backend(pid);   -- 处决最老的那个

易错 「开了忘提交」的最大来源是应用连接池--不是有人真的在跑两小时，是一个连接借出去没还干净。

场景 两个会话同时操作一份数据，能出什么幺蛾子？全家桶一共四种。

讲解 ① 脏读：读到别人还没提交的修改（他一回滚，你读的就是从没存在过的数据）；② 不可重复读：同一事务里两次读同一行，值变了（别人提交了 update）；③ 幻读：两次执行同一条件查询，行集变了（别人提交了 insert/delete）；④ 丢失更新：两个事务都「读-改-写」同一行，后提交的覆盖先提交的。

-- 丢失更新现场（两个窗口都执行）：
-- 窗口A                          -- 窗口B
begin;                            begin;
select stock from products
  where id = 1;      -- 100
                                  select stock from products where id = 1;  -- 100
update products set stock = 99
  where id = 1;
commit;                           update products set stock = 0 where id = 1;
                                  commit;   -- A 的 99 被 B 的 0 覆盖

易错 脏读和不可重复读的区别就一个字：「没提交的」和「提交了的」。幻读盯的是行集不是单行值。

场景 隔离级别就是「愿意容忍哪种怪事」的档位旋钮。

讲解 从松到紧：读未提交（啥都不防）/ 读已提交（防脏读）/ 可重复读（再防不可重复读和丢失更新）/ 串行化（全防，并发最低）。档位越紧，并发性能越差--隔离级别本质是正确性和并发度的交易。

show transaction_isolation;    -- PG 默认 read committed
set transaction isolation level repeatable read;   -- 当前事务用 RR
set default_transaction_isolation = 'repeatable read';  -- 会话级默认

易错 矩阵别死背：先记住四种异常是什么，级别能防谁自然推导出来。

场景 你在 PG 里设 read uncommitted，结果脏读复现不了。

讲解 PG 的 MVCC 架构下不存在脏读：未提交的行版本对别人天然不可见，读未提交这个档位在 PG 里被静默升级成读已提交。所以「PG 支持四种隔离级别」的说法不严谨--能设四个名字，行为只有三档。

set transaction isolation level read uncommitted;
show transaction isolation;   -- 显示 read uncommitted
-- 但脏读照样发生不了：MVCC 保证了未提交不可见

易错 面试说「PG 支持脏读」直接露馅；标准矩阵是理论，PG 的实现是现实，两边都要会讲。

场景 为什么 PG 读不加锁也不脏读？答案写在每一行里。

讲解 每行藏着两个系统列：xmin（创建它的事务 id）、xmax（删除/更新它的事务 id）。修改不改原行，而是插入新版本。读时拿一个「快照」（记着哪些事务已提交），按规则判断每个版本可见：xmin 已提交且在快照内、且 xmax 未提交或不存在 -> 可见。读不阻塞写、写不阻塞读，就是这么来的。

select xmin, xmax, id, stock from products where id = 1;
-- update 后再看：旧行 xmax 被填上，新行是新的 xmin
-- pg 把旧版本留给并发读者，稍后 vacuum 清理

易错 MVCC 的账单是死元组：更新越多、垃圾越多，vacuum 是系统的清道夫（W5 D33 长事务拖累的就是它）。

场景 面试最爱：「PG 和 MySQL 的可重复读有什么区别？」

讲解 PG 的 RR 靠快照：整个事务用同一份快照，别人提交的插入和删除都不可见，幻读天然不存在。MySQL InnoDB 的 RR 靠锁（间隙锁挡住区间内的插入）防幻读，但两个事务都「读-改-写」同一行时仍可能互相覆盖，要靠 CAS 式更新防丢失。同名 RR，两套世界。

-- PG RR 下复现不了幻读：
-- 窗口A（RR）                     -- 窗口B
begin isolation level repeatable read;
select count(*) from t;  -- 10
                                  insert into t values (...); commit;
select count(*) from t;  -- 还是 10：快照没变，看不见 B 的插入

易错 标准矩阵说「RR 防不了幻读」--PG 是例外。答这题的正确姿势：「标准这么说，但 PG 的实现是快照隔离，实际防住了」。

场景 锁不是一把锁：行锁表锁、几种模式、谁跟谁犯冲，得有张地图。

讲解 PG 的行锁只在 UPDATE / DELETE / SELECT FOR UPDATE 时出现，不阻塞读（读走 MVCC，根本不碰锁）。表锁大多是 DDL（alter / drop）自动加的，跟一切犯冲。日常关心的其实就一个矩阵：两个写操作争同一行 -> 后到的等；写操作和 DDL 争同一张表 -> 全队等。

-- pg_locks 是锁的总账本（含表锁、事务锁、咨询锁等）
select locktype, relation::regclass, mode, granted
from pg_locks
where relation is not null;

易错 PG 的行锁信息在内存里、不落盘，崩溃恢复不需要修补行锁--跟「锁存在数据页里」的数据库不同，冷知识但面试官爱听。

场景 秒杀预演的核心武器，四个修饰词各有分工。

讲解 select ... for update：选中的行加行锁，别的事务想改会等，锁持续到事务结束；for share：共享锁（只挡写不挡读）；nowait：拿不到锁立即报错而不是排队；skip locked：跳过被锁的行只处理没锁的--多消费者任务队列的完美原语。

-- 任务队列：多 worker 并发取任务不撞车
begin;
select id from tasks
where status = 'pending'
order by id
limit 10
for update skip locked;     -- 被别的 worker 锁着的直接跳过
update tasks set status = 'running' where id in (...);
commit;

易错 for update 必须在事务里才有意义--没有事务，语句结束锁就释放了，等于没锁。

场景 两个事务互相等待：A 锁了 1 号行等 2 号，B 锁了 2 号行等 1 号。

讲解 PG 的等待图里出现环，死锁检测器（默认每 1s 醒一次）发现后杀掉其中一个事务、报 deadlock detected。被杀的收到错误，另一个正常提交。预防三板斧：① 多行操作按相同顺序（比如都按 id 升序）；② 缩短事务（锁持有时间 = 事务长度）；③ 一次锁齐所需行，别分批加锁。

-- 复现模板（两个窗口交错执行）：
-- 窗口A                    -- 窗口B
begin;                      begin;
select * from p where id=1 for update;
                            select * from p where id=2 for update;
select * from p where id=2 for update;  -- 等待 B
                            select * from p where id=1 for update;
                            -- B 先拿到锁，A 被杀：deadlock detected

易错 死锁没有「报错给双方」：一个失败一个成功。应用必须把被杀的事务重试，否则等于丢了一次更新。

场景 生产上一条 UPDATE 卡了 10 分钟--它在等谁？

讲解 一行 SQL 找出阻塞链：pg_blocking_pids(pid) 返回「正在阻塞我」的连接。配上 pg_stat_activity 的 query 字段，谁堵谁、堵在哪条语句，一目了然。这是线上「全站突然卡住」的第一反应动作。

select pid,
       pg_blocking_pids(pid) as 被谁阻塞,
       wait_event_type, wait_event,
       state,
       now() - query_start as 已运行,
       left(query, 60) as query
from pg_stat_activity
where state <> 'idle'
order by query_start;

易错 先处理「被谁阻塞」非空且最老的那个连接（看是不是忘提交的事务），再考虑杀谁。