postgresql

PostgreSQL 表设计

何时使用本技能

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

何时不要使用本技能

• 你的目标是非 PostgreSQL 数据库
• 你只需要查询调优，无需更改 schema
• 你需要与数据库无关的建模指南

操作说明

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

安全

• 在没有备份和回滚计划的情况下，避免对生产环境执行破坏性 DDL。
• 在应用 schema 更改之前，使用迁移和预发环境验证。

核心规则

• 为参考表（用户、订单等）定义 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 存在空洞（正常；不要“修复”）。回滚、崩溃和并发事务会在 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 通常是最优的。使用 ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy 控制，并使用 ALTER TABLE tbl SET (toast_tuple_target = 4096) 设置阈值。大小写不敏感：对于区域设置/重音处理，使用非确定性排序规则；对于纯 ASCII，使用基于 LOWER(col) 的表达式索引（除非列需要大小写不敏感的 PK/FK/UNIQUE，否则首选）或 CITEXT。
• 货币：NUMERIC(p,s)（绝不使用浮点数）。
• 时间：时间戳使用 TIMESTAMPTZ；仅日期使用 DATE；持续时间使用 INTERVAL。避免使用 TIMESTAMP（不带时区）。事务开始时间使用 now()，当前实际时间使用 clock_timestamp()。
• 布尔值：除非需要三态值，否则使用带 NOT NULL 约束的 BOOLEAN。
• 枚举：对于小型、稳定的集合（例如美国各州、星期几），使用 CREATE TYPE ... AS ENUM。对于由业务逻辑驱动且会变化的值（例如订单状态）→ 使用 TEXT（或 INT）+ CHECK 或查找表。
• 数组：TEXT[]、INTEGER[] 等。用于需要查询元素的有序列表。使用 GIN 索引以支持包含（@>、<@）和重叠（&&）查询。访问：arr[1]（从 1 开始索引）、arr[1:3]（切片）。适合标签、分类；避免用于关系——改用联结表。字面量语法：'{val1,val2}' 或 ARRAY[val1,val2]。
• 范围类型：用于区间的 daterange、numrange、tstzrange。支持重叠（&&）、包含（@>）和运算符。使用 GiST 索引。适合调度、版本控制、数值范围。选择一种边界方案并一致使用；默认优先使用 [)（包含/排除）。
• 网络类型：IP 地址使用 INET，网络范围使用 CIDR，MAC 地址使用 MACADDR。支持网络运算符（<<、>>、&&）。
• 几何类型：用于 2D 空间数据的 POINT、LINE、POLYGON、CIRCLE。使用 GiST 索引。考虑使用 PostGIS 获得高级空间功能。
• 文本搜索：全文搜索文档使用 TSVECTOR，搜索查询使用 TSQUERY。使用 GIN 索引 tsvector。始终指定语言：to_tsvector('english', col) 和 to_tsquery('english', 'query')。绝不使用单参数版本。这适用于索引表达式和查询。
• 域类型：CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$') 用于带验证的可复用自定义类型。跨表强制执行约束。
• 复合类型：CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT) 用于列内的结构化数据。使用 (col).field 语法访问。
• JSONB：优于 JSON；使用 GIN 索引。仅用于可选/半结构化属性。仅当内容原始顺序必须保留时才使用 JSON。
• 向量类型：pgvector 提供的 vector 类型，用于嵌入的向量相似性搜索。

不要使用以下数据类型

• 不要使用 timestamp（不带时区）；请改用 timestamptz。
• 不要使用 char(n) 或 varchar(n)；请改用 text。
• 不要使用 money 类型；请改用 numeric。
• 不要使用 timetz 类型；请改用 timestamptz。
• 不要使用 timestamptz(0) 或任何其他精度指定；请改用 timestamptz
• 不要使用 serial 类型；请改用 generated always as identity。

表类型

• Regular：默认；完全持久，有日志记录。
• TEMPORARY：会话级作用域，自动删除，不记录日志。用于临时工作更快。
• UNLOGGED：持久但不具有崩溃安全性。写入更快；适合缓存/暂存。

行级安全性

使用 ALTER TABLE tbl ENABLE ROW LEVEL SECURITY 启用。创建策略：CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id())。行级别的内置基于用户的访问控制。

约束

• PK：隐式 UNIQUE + NOT NULL；创建 B-tree 索引。
• FK：指定 ON DELETE/UPDATE 动作（CASCADE、RESTRICT、SET NULL、SET DEFAULT）。在引用列上添加显式索引——可加快连接速度并防止父级删除/更新时的锁定问题。对于在事务结束时检查的循环 FK 依赖，请使用 DEFERRABLE INITIALLY DEFERRED。
• UNIQUE：创建 B-tree 索引；允许多个 NULL，除非使用 NULLS NOT DISTINCT（PG15+）。标准行为：允许 (1, NULL) 和 (1, NULL)。使用 NULLS NOT DISTINCT 时：只允许一个 (1, NULL)。除非你特别需要重复的 NULL，否则优先使用 NULLS NOT DISTINCT。
• CHECK：行本地约束；NULL 值通过检查（三值逻辑）。例如：CHECK (price > 0) 允许 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）的默认选择
• Composite：顺序很重要——如果最左前缀上有等值条件，则使用索引（WHERE a = ? AND b > ? 使用 (a,b) 上的索引，但 WHERE b = ? 不使用）。将选择性最高/最常过滤的列放在前面。
• Covering：CREATE INDEX ON tbl (id) INCLUDE (name, email) - 包含非键列，以便进行仅索引扫描而无需访问表。
• Partial：用于热点子集（WHERE status = 'active' → CREATE INDEX ON tbl (user_id) WHERE status = 'active'）。任何带有 status = 'active' 的查询都可以使用此索引。
• Expression：用于计算搜索键（CREATE INDEX ON tbl (LOWER(email))）。表达式必须在 WHERE 子句中完全匹配：WHERE LOWER(email) = '[email protected]'。
• GIN：JSONB 包含/存在、数组（@>、?）、全文搜索（@@）
• GiST：范围、几何、排他约束
• BRIN：非常大的、自然有序的数据（时间序列）——存储开销极小。当磁盘上的行顺序与索引列相关时（插入顺序或 CLUSTER 之后）有效。

分区

• 适用于超大表（>1 亿行），且查询始终按分区键（通常是时间/日期）过滤的场景。
• 另一种用途：适用于数据维护任务要求定期清理或批量替换数据的表。
• RANGE：常见于时间序列（PARTITION BY RANGE (created_at)）。创建分区：CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')。TimescaleDB 通过保留策略和压缩实现基于时间或基于 ID 的分区自动化。
• LIST：用于离散值（PARTITION BY LIST (region)）。示例：FOR VALUES IN ('us-east', 'us-west')。
• HASH：在没有自然键时用于均匀分布（PARTITION BY HASH (user_id)）。按模数创建 N 个分区。
• 约束排除：需要分区上存在 CHECK 约束，查询规划器才能进行剪枝。声明式分区（PG10+）会自动创建。
• 优先使用声明式分区或超表。不要使用表继承。
• 限制：没有全局 UNIQUE 约束——需将分区键包含在 PK/UNIQUE 中。不支持来自分区表的外键；请使用触发器。

特殊注意事项

更新密集的表

• 分离热/冷列——将频繁更新的列放入单独的表，以尽量减少膨胀。
• 使用 fillfactor=90 为可避免索引维护的 HOT 更新留出空间。
• 避免更新已建索引的列——这会阻止有益的 HOT 更新。
• 按更新模式分区——将频繁更新的行放入与稳定数据不同的分区。

插入密集型工作负载

• 尽量减少索引——只创建查询所需的索引；每个索引都会拖慢插入。
• 使用 COPY 或多行 INSERT 代替单行插入。
• UNLOGGED 表用于可重建的暂存数据——写入快得多。
• 延迟创建索引用于批量加载——删除索引、加载数据、重建索引。
• 按时间/哈希分区以分散负载。TimescaleDB 可自动完成插入密集型数据的分区和压缩。
• 使用自然键作为主键，例如 (timestamp, device_id)，如果强制全局唯一性很重要——许多插入密集型表根本不需要主键。
• 如果确实需要代理键，优先使用 BIGINT GENERATED ALWAYS AS IDENTITY 而非 UUID。

友好的 Upsert 设计

• 冲突目标列上需要 UNIQUE 索引——ON CONFLICT (col1, col2) 需要精确匹配的唯一索引（部分索引无效）。
• 使用 EXCLUDED.column 引用本应插入的值；仅更新实际发生变化的列，以减少写入开销。
• DO NOTHING 更快于 DO UPDATE，当不需要实际更新时。

安全的架构演进

• 事务性 DDL：大多数 DDL 操作可在事务中运行并回滚——BEGIN; ALTER TABLE...; ROLLBACK; 可安全测试。
• 并发创建索引：CREATE INDEX CONCURRENTLY 避免阻塞写入，但不能在事务中运行。
• 易变默认值会导致重写：添加带有易变默认值（例如 now()、gen_random_uuid()）的 NOT NULL 列会重写整个表。非易变默认值则很快。
• 先删除约束再删除列：先 ALTER TABLE DROP CONSTRAINT 再 DROP COLUMN，以避免依赖问题。
• 函数签名变更：使用不同参数执行 CREATE OR REPLACE 会创建重载，而非替换。如果不需要重载，请 DROP 旧版本。

生成列

• ... GENERATED ALWAYS AS (<expr>) STORED 用于可计算、可索引的字段。PG18+ 新增 VIRTUAL 列（读取时计算，不存储）。

扩展

• pgcrypto：使用 crypt() 进行密码哈希。
• uuid-ossp：提供替代的 UUID 函数；新项目建议优先使用 pgcrypto。
• pg_trgm：使用 % 运算符和 similarity() 函数进行模糊文本搜索。使用 GIN 索引可加速 LIKE '%pattern%'。
• citext：大小写不敏感的文本类型。除非需要大小写不敏感的约束，否则建议优先在 LOWER(col) 上使用表达式索引。
• btree_gin/btree_gist：启用混合类型索引（例如，同时对 JSONB 和 text 列建立 GIN 索引）。
• hstore：键值对；大部分场景已被 JSONB 取代，但在简单的字符串映射场景中仍然有用。
• timescaledb：时序数据的必备扩展——自动化分区、保留策略、压缩、连续聚合。
• postgis：在基础几何类型之外提供全面的地理空间支持——对于基于位置的应用必不可少。
• pgvector：用于嵌入向量的向量相似度搜索。
• pgaudit：为所有数据库活动提供审计日志。

JSONB 指南

• 优先使用 JSONB 并配合 GIN 索引。
• 默认写法：CREATE INDEX ON tbl USING GIN (jsonb_col); → 可加速：
  • 包含查询 jsonb_col @> '{"k":"v"}'
  • 键存在性 jsonb_col ? 'k'、任意/所有键 ?\|、?&
  • 嵌套文档上的路径包含查询
  • 析取查询 jsonb_col @> ANY(ARRAY['{"status":"active"}', '{"status":"pending"}'])
• @> 负载较重时：可考虑使用操作符类 jsonb_path_ops，获得更小、更快的仅包含查询索引：
  • CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);
  • 取舍：不再支持键存在性（?、?|、?&）查询——仅支持包含查询（@>）
• 针对特定标量字段的等值/范围查询：提取该字段并使用 B-tree 索引（生成列或表达式）：
  • ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;
  • CREATE INDEX ON tbl (price);
  • 建议使用 WHERE price BETWEEN 100 AND 500（走 B-tree），而不是没有索引的 WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500。
• JSONB 内的数组：使用 GIN + @> 进行包含查询（例如标签）。如果只做包含查询，可考虑 jsonb_path_ops。
• 核心关系保留在表中；JSONB 用于可选/可变的属性。
• 使用约束限制列中允许的 JSONB 值，例如 config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')

示例

用户

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);