第 4 章 · 约束 · 视图 · 序列

EXCLUDE 约束让会议室不冲突；物化视图与 REFRESH 的「快门」动画

会议室预订系统：EXCLUDE 约束实时检测

每行是一个会议室，已有预订显示为彩色块。下方填写「申请」时段并提交，系统会模拟 PG 的 EXCLUDE USING gist (room WITH =, period WITH &&) 约束，冲突会被拒绝。

新预订申请

房间 起 (小时) 止 预订人
尚未提交

等价 SQL

-- 选好参数后再点提交
底层机制：PG 在 INSERT 时通过 btree_gist 索引查找 「同 room 且 period && 新 period」的已有行；只要找到一个，就抛 exclusion_violation。 O(log N) 的索引查询足以支撑高并发预订场景。

普通视图 vs 物化视图：刷新过程动画

速度

📋 基础表 sales (实时写入)

行数: 0
最近写入: --
实时聚合
↘

👁 普通视图 v_sales

每次查询: SELECT 时聚合
永远最新 (但慢)

📋 同一基础表

行数: 0
REFRESH ⏬

📦 物化视图 mv_sales

行数: 0
已过期: 0 秒

事件时间轴

蓝块 = 业务写入绿块 = REFRESH 刷新
观察要点：
  • 普通视图行数永远跟基础表一致 (没显示是因为它每次现算)
  • 物化视图行数会「卡住」直到下一次 REFRESH
  • 越久没 REFRESH，物化视图越「过期」
  • 生产用 pg_cron 周期性触发 REFRESH MATERIALIZED VIEW CONCURRENTLY

SERIAL vs IDENTITY ALWAYS vs IDENTITY BY DEFAULT 行为对比

SERIAL

--

IDENTITY ALWAYS

--

IDENTITY BY DEFAULT

--
结论：IDENTITY ALWAYS 在「手工塞 id」时直接拒绝，避免了 SERIAL 那种「序列与表脱节」的隐患。 数据导入时用 OVERRIDING SYSTEM VALUE 显式标注意图，比直接 INSERT 安全得多。

约束 / 视图 / 序列 速查

6 大约束

约束含义典型 SQL
NOT NULL列不能为空name text NOT NULL
PRIMARY KEYNOT NULL + UNIQUE，且每表唯一id bigint PRIMARY KEY
UNIQUE列值唯一 (NULL 默认互不冲突)email text UNIQUE
FOREIGN KEY引用必须存在customer_id REFERENCES customers(id) ON DELETE CASCADE
CHECK任意确定性表达式CHECK (price > 0)
EXCLUDEPG 独有！关系性排他EXCLUDE USING gist (room WITH =, period WITH &&)

视图 vs 物化视图

维度VIEWMATERIALIZED VIEW
数据存在哪不存（只存 SQL 定义）真实落到磁盘
查询性能每次跑底层 SQL查表速度 + 可加索引
数据新鲜度永远实时取决于上次 REFRESH
可索引？否是
可写入？满足条件可更新否
刷新无需REFRESH MATERIALIZED VIEW [CONCURRENTLY]

SERIAL vs IDENTITY

维度SERIALIDENTITY (PG 10+ 推荐)
SQL 标准❌ PG 特有✅ SQL:2003
语法id serial PRIMARY KEYid bigint GENERATED ALWAYS AS IDENTITY
手工塞 id总是允许 (易踩坑)ALWAYS 拒绝；BY DEFAULT 允许
权限序列单独 GRANT跟随列权限
类型修改繁琐SQL 标准 ALTER 干净