---

name: postgresql description: "设计 PostgreSQL 专用模式。涵盖最佳实践、数据类型、索引、约束、性能模式和高级特性" risk: unknown source: community date_added: "2026-02-27"

PostgreSQL 表设计

适用场景

• 为 PostgreSQL 设计模式
• 选择数据类型和约束
• 规划索引、分区或 RLS 策略
• 审查表的可扩展性和可维护性

不适用场景

• 目标数据库不是 PostgreSQL
• 只需要查询调优而不涉及模式变更
• 需要数据库无关的建模指南

操作指南

1. 捕获实体、访问模式和规模目标（行数、QPS、保留期）。
2. 选择强制不变量的数据类型和约束。
3. 为实际查询路径添加索引，并使用 EXPLAIN 验证。
4. 在规模或访问控制需要时规划分区或 RLS。
5. 审查迁移影响并安全地应用变更。

安全性

• 在没有备份和回滚计划的情况下，避免在生产环境执行破坏性 DDL。
• 在应用模式变更前使用迁移和暂存环境验证。

核心规则

• 为引用表（users、orders 等）定义 PRIMARY KEY。时间序列/事件/日志数据不一定需要。使用时优先选择 BIGINT GENERATED ALWAYS AS IDENTITY；仅在需要全局唯一性/不透明性时使用 UUID。
• 先规范化（到3NF） 以消除数据冗余和更新异常；仅在经过测量的、高投资回报率的读取场景中反规范化，且需证明连接性能确实存在问题。过早反规范化会增加维护负担。
• 在语义上需要的地方添加 NOT NULL；为常见值使用 DEFAULT。
• 为实际查询的访问路径创建索引：PK/唯一（自动）、FK列（手动！）、频繁的过滤/排序和连接键。
• 事件时间优先使用 TIMESTAMPTZ；金额使用 NUMERIC；字符串使用 TEXT；整数值使用 BIGINT；浮点数使用 DOUBLE PRECISION（或精确十进制运算使用 NUMERIC）。

PostgreSQL "陷阱"

• 标识符：未加引号 → 转为小写。避免使用引号/混合大小写名称。约定：表/列名使用 snake_case。
• 唯一 + NULL：UNIQUE 允许多个 NULL。使用 UNIQUE (...) NULLS NOT DISTINCT（PG15+）限制为一个 NULL。
• FK 索引：PostgreSQL 不会自动为 FK 列创建索引。需要手动添加。
• 无静默强制转换：长度/精度溢出会报错（不会截断）。例如：向 NUMERIC(2,0) 插入 999 会失败并报错，不像某些数据库会静默截断或四舍五入。
• 序列/标识有间隙（正常；不要"修复"）。回滚、崩溃和并发事务会在 ID 序列中产生间隙（1, 2, 5, 6...）。这是预期行为——不要试图使 ID 连续。
• 堆存储：默认没有聚集 PK（不同于 SQL Server/MySQL InnoDB）；CLUSTER 是一次性重组，后续插入不会维护。磁盘上的行顺序是插入顺序，除非显式聚集。
• MVCC：更新/删除会留下死元组；vacuum 处理它们——设计时避免热宽行频繁变动。

数据类型

• ID：优先使用 BIGINT GENERATED ALWAYS AS IDENTITY（GENERATED BY DEFAULT 也可以）；在合并/联邦/分布式系统中或需要不透明 ID 时使用 UUID。使用 uuidv7()（PG18+ 优先）或 gen_random_uuid()（旧版 PG）生成。
• 整数：除非存储空间紧张，优先使用 BIGINT；较小范围使用 INTEGER；除非受限否则避免 SMALLINT。
• 浮点数：除非存储空间紧张，优先使用 DOUBLE PRECISION 而非 REAL。精确十进制运算使用 NUMERIC。
• 字符串：优先使用 TEXT；如需长度限制，使用 CHECK (LENGTH(col) <= n) 而非 VARCHAR(n)；避免 CHAR(n)。二进制数据使用 BYTEA。大字符串/二进制（>2KB 默认阈值）自动使用 TOAST 压缩存储。TOAST 存储策略：PLAIN（无 TOAST）、EXTENDED（压缩 + 行外）、EXTERNAL（行外，不压缩）、MAIN（压缩，尽量保持行内）。默认 EXTENDED 通常最优。大小写不敏感：对于区域/重音处理使用非确定性排序规则；对于纯 ASCII 使用 LOWER(col) 表达式索引（优先）或 CITEXT。
• 金额：NUMERIC(p,s)（永远不用浮点数）。
• 时间：时间戳使用 TIMESTAMPTZ；仅日期使用 DATE；持续时间使用 INTERVAL。避免 TIMESTAMP（不带时区）。事务开始时间使用 now()，当前墙钟时间使用 clock_timestamp()。
• 布尔值：BOOLEAN 加 NOT NULL 约束，除非需要三态值。
• 枚举：小型稳定集合（如美国州、星期几）使用 CREATE TYPE ... AS ENUM。业务逻辑驱动的可演变值（如订单状态）使用查找表。