Appearance

PostgreSQL 商业场景训练营 ​

这页用商业项目训练 PostgreSQL，而不是写“博客实战”。你要通过这些练习真正理解：为什么 PostgreSQL 不是 MySQL 的语法替代品，为什么它适合复杂 SQL、JSONB、GIS、扩展索引和强约束，为什么生产上必须关注 VACUUM、表膨胀、执行计划估算、WAL、锁和长事务。

训练目标：每个场景都要能写 SQL、看 EXPLAIN (ANALYZE, BUFFERS)、解释 MVCC/VACUUM/WAL 原理、说明不这样做的后果，并能转成面试标准回答。

训练总流程 ​

mermaid
flowchart TD
    A["业务建模"] --> B["选择数据类型和约束"]
    B --> C["选择索引类型"]
    C --> D["写业务 SQL"]
    D --> E["执行 EXPLAIN ANALYZE"]
    E --> F["判断扫描方式和 Buffers"]
    F --> G["观察 MVCC 和锁"]
    G --> H["分析 VACUUM / WAL / 膨胀"]
    H --> I["整理面试回答"]

PostgreSQL 的训练重点和 MySQL 不完全一样：

训练点PostgreSQL 重点如果按 MySQL 惯性理解会怎样
JSON 字段jsonb + GIN 可直接做部分查询以为 JSON 只能存字符串
MVCC更新产生新 tuple，旧版本后续 VACUUM误以为旧版本都在 undo log
覆盖索引Index Only Scan 依赖 Visibility Map误以为字段在索引里就一定不访问表
执行计划EXPLAIN ANALYZE 看真实行数和 Buffers只看是否用了索引
大表分区、BRIN、归档、VACUUM 都重要只想着加普通 B-tree
长事务阻止 VACUUM 清理 dead tuple只关注锁，不关注版本膨胀

训练一：医疗资产表和 JSONB 扩展字段 ​

场景 ​

医疗资产平台里，不同设备的扩展属性不同。CT 设备可能有球管次数，监护仪可能有床位号，网络设备可能有 IP 和端口。如果每个扩展字段都加到主表，表会越来越宽；如果全部放字符串，又很难查询。PostgreSQL 常用 jsonb 保存灵活属性，并配合 GIN 索引。

建表 Demo ​

sql
drop table if exists medical_asset;

create table medical_asset (
  id bigserial primary key,
  asset_no varchar(64) not null,
  hospital_id bigint not null,
  department_id bigint not null,
  asset_name varchar(128) not null,
  status varchar(20) not null,
  attrs jsonb not null default '{}',
  created_at timestamp not null default now(),
  updated_at timestamp not null default now(),
  constraint uk_medical_asset_no unique (asset_no),
  constraint ck_medical_asset_status
    check (status in ('IDLE', 'USED', 'REPAIR', 'SCRAPPED'))
);

create index idx_asset_hospital_status_time
on medical_asset(hospital_id, status, created_at desc);

create index idx_asset_attrs_gin
on medical_asset using gin(attrs);

插入数据：

sql
insert into medical_asset(asset_no, hospital_id, department_id, asset_name, status, attrs)
values
('A-CT-001', 1, 10, 'CT 设备', 'USED', '{"deviceType":"CT","tubeCount":12000,"vendor":"GE"}'),
('A-MON-001', 1, 11, '监护仪', 'IDLE', '{"deviceType":"Monitor","bedNo":"12A","vendor":"Mindray"}'),
('A-NET-001', 1, 12, '网络采集盒', 'USED', '{"deviceType":"Gateway","ip":"10.1.2.3","port":8080}');

查询 CT 设备：

sql
explain (analyze, buffers)
select id, asset_no, asset_name
from medical_asset
where attrs @> '{"deviceType":"CT"}';

原理解释 ​

jsonb 会把 JSON 转成二进制结构，方便比较、索引和查询。GIN 索引不是按整行排序的 B-tree，而是倒排思路：把 JSON 内部的 key/value 拆成可检索项，查询某个属性时能快速定位包含该属性的行。

mermaid
flowchart TD
    A["attrs jsonb"] --> B["拆出 key/value"]
    B --> C["写入 GIN 索引"]
    C --> D["查询 attrs @> 条件"]
    D --> E["通过 GIN 找候选行"]
    E --> F["回表校验并返回"]

不这样会怎样 ​

错误做法后果
所有扩展字段都放主表字段越来越多，很多列为空，维护困难
JSON 存成普通 text数据库无法高效按属性查询
JSON 无约束乱写同一字段可能出现 deviceType、type、device_type 多种写法
所有查询都查 JSON高频固定条件不如抽成普通列

商业建议 ​

稳定、高频、参与 Join 或排序的字段放普通列，例如 hospital_id、department_id、status。灵活、低频、设备类型差异大的字段放 jsonb，例如 tubeCount、bedNo、firmwareVersion。

训练二：用 returning 做状态流转 ​

场景 ​

采集任务只能从 WAITING 改成 RUNNING，多个执行器并发抢任务时，不能抢到同一条。

建表 ​

sql
drop table if exists collect_task;

create table collect_task (
  id bigserial primary key,
  task_no varchar(64) not null unique,
  hospital_id bigint not null,
  status varchar(20) not null,
  priority int not null default 0,
  created_at timestamp not null default now(),
  started_at timestamp null,
  finished_at timestamp null,
  constraint ck_collect_task_status
    check (status in ('WAITING', 'RUNNING', 'SUCCESS', 'FAILED'))
);

create index idx_task_status_priority_time
on collect_task(status, priority desc, created_at);

插入任务：

sql
insert into collect_task(task_no, hospital_id, status, priority)
values
('T001', 1, 'WAITING', 10),
('T002', 1, 'WAITING', 5),
('T003', 2, 'WAITING', 8);

抢任务：

sql
with picked as (
  select id
  from collect_task
  where status = 'WAITING'
  order by priority desc, created_at
  limit 1
  for update skip locked
)
update collect_task t
set status = 'RUNNING',
    started_at = now()
from picked
where t.id = picked.id
returning t.id, t.task_no, t.hospital_id;

原理解释 ​

for update 会锁住被选中的行，skip locked 会让其他并发事务跳过已经被锁住的任务，不等待。returning 可以把更新后的任务直接返回给执行器，减少一次查询。

mermaid
flowchart TD
    A["执行器请求任务"] --> B["查询 WAITING 任务"]
    B --> C["for update 加行锁"]
    C --> D["skip locked 跳过别人已锁任务"]
    D --> E["更新为 RUNNING"]
    E --> F["returning 返回任务"]

不这样会怎样 ​

如果先 select 再 update：

sql
select id from collect_task where status = 'WAITING' limit 1;
update collect_task set status = 'RUNNING' where id = ?;

多个执行器可能先后读到同一条 WAITING 任务，导致重复执行。靠应用内存锁也不可靠，因为执行器可能多实例部署。

面试标准回答 ​

text
PostgreSQL 可以用 for update skip locked 实现多执行器抢任务。for update 锁住候选行，skip locked 让其他事务跳过已锁记录，不会阻塞在同一条任务上。配合 update ... returning 可以把状态流转和返回任务放在一个 SQL 中完成，避免先查后改带来的重复抢占。

原理跳转：事务与锁、核心全过程原理。

训练三：Index Only Scan 为什么仍可能访问 heap ​

场景 ​

很多人把 MySQL 覆盖索引的经验套到 PostgreSQL，认为查询字段都在索引里就一定不访问表。但 PostgreSQL 要判断 tuple 对当前事务快照是否可见，所以还要依赖 Visibility Map。

建表和索引 ​

sql
drop table if exists asset_event;

create table asset_event (
  id bigserial primary key,
  hospital_id bigint not null,
  event_type varchar(32) not null,
  asset_no varchar(64) not null,
  created_at timestamp not null default now(),
  detail text not null
);

create index idx_event_cover
on asset_event(hospital_id, event_type, created_at desc)
include (asset_no);

查询：

sql
explain (analyze, buffers)
select asset_no, created_at
from asset_event
where hospital_id = 1
  and event_type = 'OFFLINE'
order by created_at desc
limit 20;

观察重点 ​

执行计划里如果出现 Index Only Scan，还要看：

字段含义
Heap Fetches需要访问 heap 表检查可见性的次数
Buffers: shared hit/read命中的缓存页和读取页
actual rows实际返回行数
rows估算行数

原理解释 ​

PostgreSQL 的索引项通常不直接保存 MVCC 可见性所需的全部信息。它要知道某个 heap page 上的 tuple 是否对所有事务都可见。如果 Visibility Map 标记这个 page 是 all-visible，Index Only Scan 可以不访问 heap；如果没有标记，就要回 heap 检查 tuple 的 xmin/xmax。

mermaid
flowchart TD
    A["Index Only Scan"] --> B{"Visibility Map<br/>是否 all-visible"}
    B -- "是" --> C["直接从索引返回字段"]
    B -- "否" --> D["访问 heap page"]
    D --> E["检查 xmin / xmax 可见性"]
    E --> F["返回可见 tuple"]

为什么 VACUUM 相关 ​

VACUUM 清理 dead tuple 后，会维护 Visibility Map。长事务、频繁更新、VACUUM 不及时都会让 Heap Fetches 变多，Index Only Scan 的收益下降。

原理跳转：MVCC、VACUUM 与 WAL 原理、索引与优化器。

训练四：长事务导致表膨胀 ​

场景 ​

PostgreSQL 更新不会原地覆盖旧 tuple，而是生成新 tuple。旧 tuple 不能马上删，因为可能还有老事务需要看到它。长事务会阻止 VACUUM 清理旧版本。

会话 A：开启长事务 ​

sql
begin;

select count(*) from medical_asset;

-- 不要 commit，保持事务打开

会话 B：大量更新 ​

sql
update medical_asset
set updated_at = now()
where hospital_id = 1;

查看长事务 ​

sql
select pid,
       state,
       now() - xact_start as xact_age,
       query
from pg_stat_activity
where xact_start is not null
order by xact_start;

查看 dead tuple ​

sql
select relname,
       n_live_tup,
       n_dead_tup,
       last_vacuum,
       last_autovacuum
from pg_stat_user_tables
where relname = 'medical_asset';

原理图 ​

mermaid
flowchart TD
    A["事务A持有旧快照"] --> B["事务B更新数据"]
    B --> C["生成新 tuple"]
    C --> D["旧 tuple 变成 dead 候选"]
    D --> E{"事务A是否还可能需要旧版本"}
    E -- "是" --> F["VACUUM 不能清理"]
    E -- "否" --> G["VACUUM 清理 dead tuple"]

不这样会怎样 ​

问题后果
长事务不提交dead tuple 不能清理，表越来越膨胀
VACUUM 跟不上扫描页变多，查询越来越慢
只加索引索引也可能膨胀，不能根治
不看 pg_stat_activity找不到阻止清理的老事务

面试标准回答 ​

text
PostgreSQL 的 MVCC 通过 heap tuple 多版本实现，更新会生成新 tuple，旧 tuple 等没有事务需要时再由 VACUUM 清理。长事务持有很老的快照，数据库必须保留它可能看到的旧版本，因此 VACUUM 不能清理这些 dead tuple，最终导致表膨胀、索引膨胀、扫描页数增加和查询变慢。排查要看 pg_stat_activity 的 xact_start、pg_stat_user_tables 的 n_dead_tup 和 autovacuum 情况。

训练五：用 EXPLAIN ANALYZE 定位慢 SQL ​

场景 ​

查询某医院最近离线事件很慢。不要只问“有没有索引”，要看真实执行计划。

sql
explain (analyze, buffers)
select asset_no, created_at
from asset_event
where hospital_id = 1
  and event_type = 'OFFLINE'
order by created_at desc
limit 50;

怎么看 ​

观察点说明可能问题
Seq Scan顺序扫描表没有合适索引或选择性太低
Index Scan走索引再访问 heap回表多、随机 IO 多
Bitmap Heap Scan先位图定位，再批量访问 heap命中行多时常见
Index Only Scan尽量只读索引Heap Fetches 多会变慢
actual rows vs rows真实行数和估算行数统计信息不准
Buffers read从磁盘读取页缓存未命中或扫描太多页

处理顺序 ​

mermaid
flowchart TD
    A["慢 SQL"] --> B["看 EXPLAIN ANALYZE"]
    B --> C{"估算和实际差距大吗"}
    C -- "大" --> D["ANALYZE 更新统计信息"]
    C -- "不大" --> E{"扫描页多吗"}
    E -- "多" --> F["调整索引/缩小范围/分区"]
    E -- "不多" --> G{"是否锁等待或 IO 慢"}
    G -- "是" --> H["查 pg_stat_activity / pg_locks / IO"]
    G -- "否" --> I["看排序、Join、函数计算"]

训练六：用 BRIN 处理时间顺序大表 ​

场景 ​

采集事件表按时间持续写入，数据量很大。查询常按时间范围查，例如最近一小时、某天。给时间列建普通 B-tree 可能很大，维护成本高。PostgreSQL 可以考虑 BRIN。

sql
create index idx_event_created_brin
on asset_event using brin(created_at);

原理 ​

BRIN 不记录每一行的具体位置，而是按一段物理页记录最小值、最大值等摘要。适合字段值和物理写入顺序高度相关的超大表，例如时间递增写入。

mermaid
flowchart TD
    A["连续写入事件表"] --> B["物理页大致按时间递增"]
    B --> C["BRIN 记录页范围摘要"]
    C --> D["按时间范围查询"]
    D --> E["跳过明显不相关页范围"]

适合与不适合 ​

场景是否适合 BRIN
时间递增写入日志表适合
超大采集流水按时间查适合
高选择性用户 ID 查询不适合，B-tree 更合适
数据物理顺序和字段无关不适合

训练七：WAL 和复制延迟 ​

场景 ​

主库写入成功，从库还查不到。PostgreSQL 流复制依赖 WAL，延迟可能出现在生成、发送、接收、重放任何环节。

mermaid
flowchart TD
    A["主库事务提交"] --> B["写 WAL"]
    B --> C["WAL Sender 发送"]
    C --> D["备库 WAL Receiver 接收"]
    D --> E["写入备库 WAL"]
    E --> F["Replay 重放"]
    F --> G["备库查询可见"]

主库查看复制状态 ​

sql
select application_name,
       state,
       sent_lsn,
       write_lsn,
       flush_lsn,
       replay_lsn,
       write_lag,
       flush_lag,
       replay_lag
from pg_stat_replication;

原理解释 ​

字段说明
sent_lsn主库已发送到哪里
write_lsn备库已写入到哪里
flush_lsn备库已刷盘到哪里
replay_lsn备库已重放到哪里
replay_lag重放延迟

写后立刻读如果读到备库，就可能读到旧数据。强一致读要读主库，或者业务层做写后短时间读主策略。

最终验收清单 ​

做完这页后，你应该能回答：

  1. jsonb 和普通 text 保存 JSON 有什么区别？
  2. GIN 索引为什么适合 JSONB 和数组？
  3. for update skip locked 为什么适合抢任务？
  4. PostgreSQL 更新为什么生成新 tuple？
  5. VACUUM 为什么不能随便清理所有 dead tuple？
  6. 长事务为什么会导致表膨胀？
  7. Index Only Scan 为什么还可能访问 heap？
  8. Visibility Map 和 VACUUM 有什么关系？
  9. EXPLAIN (ANALYZE, BUFFERS) 比普通 EXPLAIN 多看什么？
  10. BRIN 和 B-tree 分别适合什么场景？
  11. WAL 如何支持崩溃恢复和流复制？
  12. 写主读从为什么会读到旧数据？

关联知识点 ​

知识点入口
PostgreSQL 主线从零到生产级掌握
核心流程核心全过程原理
索引与优化器索引与优化器
事务与锁事务与锁
MVCC/VACUUM/WALMVCC、VACUUM 与 WAL 原理
性能排查性能排查
面试PostgreSQL 面试