postgresql

# PostgreSQL 表设计 ## 何时使用此技能 - 为 PostgreSQL 设计模式 (Schema) - 选择数据类型和约束 - 规划索引、分区或 RLS 策略 - 审查表的扩展性和可维护性 ## 何时不使用此技能 - 目标数据库不是 PostgreSQL - 仅需查询调优而无需更改模式 - 需要数据库无关的建模指南 ## 指令 1. 捕获实体、访问模式和规模目标（行数、QPS、保留策略）。 2. 选择能够强制执行不变性的数据类型和约束。 3. 为实际查询路径添加索引，并使用 `EXPLAIN` 进行验证。 4. 在规模或访问控制需要时规划分区或 RLS。 5. 审查迁移影响并安全地应用更改。 ## 安全性 - 在没有备份和回滚计划的情况下，避免在生产环境中进行破坏性的 DDL 操作。 - 在应用模式更改之前，使用迁移工具和暂存环境进行验证。 ## 核心规则 - 为引用表（用户、订单等）定义 **PRIMARY KEY**。对于时序/事件/日志数据不一定需要。使用时，优先选择 `BIGINT GENERATED ALWAYS AS IDENTITY`；仅在需要全局唯一性/不透明性时使用 `UUID`。 - **优先规范化（至 3NF）** 以消除数据冗余和更新异常；**仅**在已验证、高投资回报率的读取场景且连接性能确实存在问题时进行反规范化。过早的反规范化会增加维护负担。 - 在语义要求的地方添加 **NOT NULL**；对常见值使用 **DEFAULT**。 - 为**实际查询的访问路径**创建索引：主键/唯一键（自动）、**外键列（手动！）**、频繁的过滤/排序字段以及连接键。 - 事件时间优先使用 **TIMESTAMPTZ**；金额使用 **NUMERIC**；字符串使用 **TEXT**；整数值使用 **BIGINT**，浮点数使用 **DOUBLE PRECISION**（或精确十进制运算使用 `NUMERIC`）。 ## PostgreSQL “陷阱” - **标识符**：未加引号的标识符会被转换为小写。避免使用加引号/混合大小写的名称。约定：表名/列名使用 `snake_case`。 - **Unique + NULLs**：UNIQUE 允许存在多个 NULL。使用 `UNIQUE (...) NULLS NOT DISTINCT` (PG15+) 将其限制为一个 NULL。 - **外键索引**：PostgreSQL **不会**自动为外键列创建索引。请手动添加。 - **无静默强制转换**：长度/精度溢出会报错（不会截断）。例如：将 999 插入 `NUMERIC(2,0)` 会报错，不像某些数据库会静默截断或四舍五入。 - **序列/标识符存在间隙**（正常现象；不要“修复”它）。回滚、崩溃和并发事务会在 ID 序列中产生间隙。