PostgreSQL 设计与使用助手
触发条件
当用户出现以下意图时激活本 Skill：
- 设计 PostgreSQL 表结构 / Schema
- PostgreSQL 索引优化 / 查询优化
- 分区方案 / 分表策略
- 逻辑复制 / 主从架构
- JSONB 半结构化数据设计
- "如何设计 xxx 的数据库"
设计流程（Agent 执行路径）
0. 版本检查 → 加载 references/version-major.md 对比用户版本，识别废弃项和重大变更。同时加载所有 version-X.Y.md（X.Y ≤ 用户目标版本），后续设计过程中 Agent 从已加载的上下文中自主匹配深度特性
1. 需求分析 → 理解业务实体和关系
2. 概念设计 → ER模型，识别实体/关系/属性
3. 规范约束 → 加载 references/design-spec.md，确保命名/字段/索引符合规范
4. 逻辑设计 → 表结构DDL，含索引（B-tree/GIN/GiST/BRIN）、约束、注释
5. 物理设计 → 表空间/分区/填充因子/VACUUM策略
6. 使用指引 → 加载 references/usage-guide.md，给出场景化操作
7. 优化建议 → 加载 references/best-practices.md，给出性能建议
8. 模板参考 → 加载 references/patterns.md，匹配业务模板
快速参考
必备三字段
每张表必须包含：id (BIGINT GENERATED ALWAYS AS IDENTITY) / created_at (TIMESTAMPTZ NOT NULL DEFAULT now()) / updated_at (TIMESTAMPTZ NOT NULL DEFAULT now())
命名铁律
| 对象 | 规范 | 示例 |
|---|---|---|
| 表名 | 小写+下划线，复数 | orders、users |
| 字段名 | 小写+下划线 | user_name |
| 主键 | <表名>_id |
order_id |
| 唯一约束 | uq_<表>_<字段> |
uq_users_email |
| 普通索引 | idx_<表>_<字段> |
idx_orders_created_at |
| 外键 | fk_<表>_<引用表> |
fk_orders_users |
| 检查约束 | ck_<表>_<规则> |
ck_orders_total_positive |
| 布尔字段 | is_xxx / has_xxx |
is_active、has_subscription |
| 时间字段 | _at 后缀 |
created_at、deleted_at |
| JSON字段 | 后缀 _jsonb 或 attrs/metadata |
metadata |
数据类型速查
| 场景 | 推荐 | 禁止 |
|---|---|---|
| 主键/ID | BIGINT GENERATED ALWAYS AS IDENTITY | SERIAL（不推荐，推荐 IDENTITY） |
| 金额 | NUMERIC(18,2) | FLOAT / DOUBLE / MONEY |
| 字符串 | TEXT + CHECK (LENGTH(col) <= n) | VARCHAR(n) 无长度约束（TEXT 和 VARCHAR 在 PG 中等价） |
| 长文本 | TEXT | 超长 VARCHAR |
| 布尔 | BOOLEAN NOT NULL | 三态布尔除非有意 |
| 时间 | TIMESTAMPTZ | 无时区 TIMESTAMP（除非语义上确为本地时间） |
| JSON | JSONB + GIN 索引 | JSON（除非需保序） |
| 有穷枚举 | CREATE TYPE ... AS ENUM | 业务枚举（应查表） |
| UUID | UUID DEFAULT gen_random_uuid() | UUID 做聚簇主键 |
PostgreSQL 特有须知
- 外键列不自动建索引 — 必须手动
CREATE INDEX，否则 DELETE/UPDATE 父表会锁子表全表 - 没有聚簇主键 — 行以插入顺序存储（堆表），
CLUSTER是一次性操作 - MVCC 产生死元组 — UPDATE/DELETE 留下死行，需设计 VACUUM 策略
- 不自建截断 — 插入
999到NUMERIC(2,0)抛出错误，不会静默截断 - 自增 ID 有间隙 — 回滚/崩溃导致序列间隙是正常行为，不要尝试消除
- UNIQUE 允许多 NULL — 除非使用
NULLS NOT DISTINCT（PG15+）
索引类型速查
| 类型 | 使用场景 | 示例 |
|---|---|---|
| B-tree（默认） | 等值/范围查询、ORDER BY，覆盖 90%+ | idx_orders_status |
| Composite B-tree | 多列过滤，区分度高的列在前 | idx_orders_user_status(user_id, status) |
| Covering（INCLUDE） | 索引覆盖扫描，不回表 | idx_users_id_name(id) INCLUDE (name) |
| Partial | 只索引热点子集 | WHERE status = 'active' |
| Expression | 索引计算结果 | LOWER(email) |
| GIN | JSONB 查询、数组、全文搜索 | USING GIN (metadata) |
| GiST | 范围类型、几何、排他约束 | USING GIST (booking_period) |
| BRIN | 超大表、自然有序字段（时序） | USING BRIN (created_at) |
渐进式加载
详细内容按需加载 references/：
| 主题 | 文件 | 何时加载 |
|---|---|---|
| Schema 设计规范（命名/字段/索引/分区） | references/design-spec.md |
Step 3 规范约束 |
| 场景化操作指南（建表/DDL/优化/迁移/分区） | references/usage-guide.md |
Step 6 使用指引 |
| 最佳实践（索引/事务/分区/主从/运维） | references/best-practices.md |
Step 7 优化建议 |
| 业务 Schema 模板（6类业务完整DDL） | references/patterns.md |
Step 8 模板参考 |
| 重大版本特性（废弃/依赖变更/新模块） | references/version-major.md |
Step 0 版本检查（模块激活时即加载） |
| 深度版本特性 — 17.x（JSON_TABLE/增量备份/MERGE增强） | references/version-17.md |
Step 0 版本检查时自动加载（版本 ≤ 用户目标版本时） |
| 深度版本特性 — 16.x / 15.x（逻辑复制/行过滤/并行应用） | references/version-16.md |
Step 0 版本检查时自动加载（版本 ≤ 用户目标版本时） |
| 深度版本特性 — 14.x（JSONB下标/多范围/LZ4/管道模式） | references/version-14.md |
Step 0 版本检查时自动加载（版本 ≤ 用户目标版本时） |
版本参考文档启用规则
- 模块激活时 — 始终加载
references/version-major.md，Agent 需主动对比用户使用的 PostgreSQL 版本，若存在废弃项或重大变更，立即提示用户 - 深度特性 Step 0 自动加载 — 版本检查时加载所有 version-X.Y.md（X.Y ≤ 用户目标版本）。设计过程中 Agent 从已加载的上下文中自主检索匹配相关特性，用户无需指定版本号
- 深度特性按大版本拆分，小版本持续追加 — 如
version-17.md包含该大版本下所有小版本的设计级特性。更新时在文件中新增## X.Y.Z版本节即可，不单开文件
硬规则
- 设计规范内容来自权威资料（PostgreSQL官方文档/社区最佳实践），不编造
- 所有外键列必须有索引 — PostgreSQL 不会自动创建
- 所有表必须有主键，使用 BIGINT GENERATED ALWAYS AS IDENTITY
- 优先使用软删除（
deleted_at），物理删除需评估 GDPR/合规要求 - 小数禁止用 FLOAT/DOUBLE，必须 NUMERIC
- 时间必须用 TIMESTAMPTZ，不使用无时区的 TIMESTAMP
- 表必须有
created_at和updated_at - JSON 数据必须使用 JSONB（非 JSON），高频查询字段上 GIN 索引
- 单表超过 2000 万行或 10GB 时考虑分区（经验阈值，具体依查询模式调整）
- 不推荐 SERIAL，使用 GENERATED ALWAYS AS IDENTITY
