postgresql-table-design

PostgreSQL 表设计

何时使用

• 设计新的 PostgreSQL 模式，或在发布前审查现有模式。
• 专门为 PostgreSQL 选择列类型、键、约束或索引。
• 决定是否以及如何对大表进行分区，或如何存储半结构化数据。
• 规划在线数据库上的模式变更，且不造成停机。

PostgreSQL 模式的规则和决策要点。完整的数据类型目录、工作负载模式（更新密集、插入密集、upsert、模式演进）、扩展、JSONB 索引以及完整的 DDL 示例见 references/details.md；当下面某节指向该文件时，请打开它。

核心规则

• 为参考表（用户、订单等）定义 PRIMARY KEY。时序/事件/日志数据不一定需要。使用时，优先选择 BIGINT GENERATED ALWAYS AS IDENTITY；仅在需要全局唯一性/不可见性时使用 UUID。
• 先规范化（到 3NF） 以消除数据冗余和更新异常；仅在读取经过实测、高 ROI 且已证明连接性能有问题时才反规范化。
• 在所有语义上需要的地方添加 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 列建立索引。请手动添加。
• 无静默转换：长度/精度溢出会报错（不截断）。将 999 插入 NUMERIC(2,0) 会失败，不像那些会静默截断或舍入的数据库。
• 序列/identity 存在间隙（正常；不要“修复”）。回滚、崩溃和并发事务会留下间隙（1, 2, 5, 6...）。
• 堆存储：默认没有聚簇 PK；CLUSTER 是一次性重组，后续插入不会维持。
• MVCC：更新/删除会留下死元组；vacuum 负责处理它们——设计时要避免热点宽行频繁变动。

数据类型

• ID：BIGINT GENERATED ALWAYS AS IDENTITY；分布式或不透明 ID 使用 UUID，用 uuidv7()（PG18+）或 gen_random_uuid() 生成。
• 数字：除非存储空间至关重要，否则使用 BIGINT；优先使用 DOUBLE PRECISION 而非 REAL；金额和精确小数使用 NUMERIC(p,s)。
• 字符串：TEXT，需要限制时使用 CHECK (LENGTH(col) <= n)；二进制使用 BYTEA。不区分大小写的查找：在 LOWER(col) 上建立表达式索引，或当约束必须不区分大小写时使用 CITEXT。
• 时间：TIMESTAMPTZ、DATE、INTERVAL。now() 是事务开始时间；clock_timestamp() 是墙上时钟。
• 布尔值：除非需要三态，否则使用 BOOLEAN NOT NULL。
• 枚举：CREATE TYPE ... AS ENUM 仅用于小型、稳定的集合；会演进的业务值使用 TEXT + CHECK 或查找表。
• JSONB 优于 JSON，使用 GIN 索引，仅用于可选/半结构化属性。
• 数组、范围、网络、几何、全文、域、复合和向量类型，以及 TOAST 存储和排序规则控制：见 references/details.md。

应避免的类型

避免	改用
timestamp（不带时区）	timestamptz
char(n)、varchar(n)	text（+ 如需要，对长度加 CHECK）
money	numeric
timetz	timestamptz
timestamptz(0) 或任何精度	timestamptz
serial	generated always as identity

约束

• PK：隐式 UNIQUE + NOT NULL；创建 B-tree 索引。
• FK：指定 ON DELETE/UPDATE（CASCADE、RESTRICT、SET NULL、SET DEFAULT）。为引用列建立索引。对于在提交时检查的循环依赖，使用 DEFERRABLE INITIALLY DEFERRED。
• UNIQUE：创建 B-tree 索引；允许多个 NULL，除非使用 NULLS NOT DISTINCT（PG15+）。除非确实需要重复 NULL，否则优先使用 NULLS NOT DISTINCT。
• CHECK：行内局部；NULL 通过（三值逻辑）。与 NOT NULL 结合使用：price NUMERIC NOT NULL CHECK (price > 0)。
• EXCLUDE：使用运算符防止重叠，例如 EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) 可防止重复预订。需要支持 GiST 的类型。

索引

• B-tree：等值/范围查询的默认选择（=、<、>、BETWEEN、ORDER BY）。
• 复合索引：最左前缀规则（WHERE a = ? AND b > ? 使用 (a,b)；WHERE b = ? 则不使用）。最具选择性的列放在最前面。
• 覆盖索引：CREATE INDEX ON tbl (id) INCLUDE (name, email) 用于仅索引扫描。
• 部分索引：热点子集，CREATE INDEX ON tbl (user_id) WHERE status = 'active'。
• 表达式索引：CREATE INDEX ON tbl (LOWER(email))；查询必须使用相同的表达式。
• GIN：JSONB 包含/存在、数组、全文搜索。GiST：范围、几何、排他约束。
• BRIN：大型、自然有序的数据（时间序列），存储成本极低；当磁盘顺序与索引列相关时有效。

分区

• 适用于大型表（>1 亿行），其查询始终按分区键过滤，或维护操作（修剪、批量替换）遵循某个键。
• RANGE 用于时间序列（PARTITION BY RANGE (created_at)；TimescaleDB 通过保留和压缩实现自动化），LIST 用于离散值，HASH 用于没有自然键的均匀分布。
• 约束排除：规划器通过分区的 CHECK 约束修剪分区；声明式分区（PG10+）会自动创建它们。
• 优先使用声明式分区或超表。不要使用表继承。
• 限制：没有全局 UNIQUE 约束——在 PK/UNIQUE 中包含分区键。分区表的外键需要 PG11+，引用分区表的外键需要 PG12+；在更早的版本上，使用触发器。

示例

CREATE TABLE users (
  user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
CREATE INDEX ON users (created_at);

CREATE TABLE orders (
  order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(user_id),
  status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),
  total NUMERIC(10,2) NOT NULL CHECK (total > 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (created_at);

-- JSONB attributes with a generated, indexable scalar
CREATE TABLE profiles (
  user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
  attrs JSONB NOT NULL DEFAULT '{}',
  theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED
);
CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);

深入了解

references/details.md 包含本文件仅提及名称的材料：

• 完整的数据类型目录：TOAST 存储、排序规则、数组、范围、网络、几何、文本搜索、域、复合类型、向量。
• 表类型（TEMPORARY、UNLOGGED）和行级安全性。
• 约束和索引说明，以及 RANGE、LIST 和 HASH 的分区 DDL。
• 工作负载模式：更新密集、插入密集、upsert 设计、安全模式演进。
• 生成列和扩展（pg_trgm、citext、timescaledb、postgis、pgvector 等）。
• JSONB 索引策略，包括 jsonb_path_ops 和提取的 B-tree 列。