postgresql-table-design

PostgreSQL Table Design 是一套针对 PostgreSQL 的 schema 设计规则与决策要点，用于设计新的 PostgreSQL schema，或在 schema 上线前进行审查。

适用场景

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

核心规则

为参考表（如 users、orders 等）定义主键；时序/事件/日志数据不一定需要。使用时优先选择 BIGINT GENERATED ALWAYS AS IDENTITY；仅在需要全局唯一性或不可见性时使用 UUID。
先规范化（至 3NF）以消除数据冗余和更新异常；仅在有实测且高回报的读取场景、且连接性能被证明有问题时才反规范化。
在语义上必要处一律添加 NOT NULL；为常见值设置 DEFAULT。
为实际查询的访问路径创建索引：主键/唯一（自动）、外键列（需手动！）、频繁过滤/排序、连接键。
事件时间优先使用 TIMESTAMPTZ；金额使用 NUMERIC；字符串使用 TEXT；整数使用 BIGINT；浮点数使用 DOUBLE PRECISION（或需要精确小数运算时用 NUMERIC）。

PostgreSQL 特有注意事项

标识符：不加引号会被转为小写。避免使用带引号/混合大小写的名称，使用 snake_case。
UNIQUE 与 NULL：UNIQUE 允许多个 NULL。PG15+ 可用 UNIQUE NULLS NOT DISTINCT (...) 限制为仅一个 NULL。
外键索引：PostgreSQL 不会自动为外键列建索引，需要手动添加。
无静默强制转换：长度/精度溢出会直接报错（不截断）。向 NUMERIC(2,0) 插入 999 会失败，不像某些数据库会静默截断或四舍五入。
序列/标识存在间隙（正常现象，不要“修复”）。回滚、崩溃和并发事务都会留下间隙（1, 2, 5, 6...）。
堆存储：默认没有聚簇主键；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

约束

主键：隐含 UNIQUE + NOT NULL；创建 B-tree 索引。
外键：指定 ON DELETE/UPDATE（CASCADE、RESTRICT、SET NULL、SET DEFAULT）。为引用列建索引。循环依赖可用 DEFERRABLE INITIALLY DEFERRED 在提交时检查。
UNIQUE：创建 B-tree 索引；除非使用 NULLS NOT DISTINCT（PG15+），否则允许多个 NULL。除非确实需要重复 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+）会自动创建。
优先使用声明式分区或 hypertable。不要使用表继承。
限制：没有全局 UNIQUE 约束——需将分区键纳入主键/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 属性带生成的可索引标量 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 设计、安全的 schema 演进）；生成列与扩展（pg_trgm、citext、timescaledb、postgis、pgvector 等）；JSONB 索引策略，包括 jsonb_path_ops 和抽取出的 B-tree 列。

技能介绍