postgresql

PostgreSQL 表设计

使用此技能的场景

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

不要使用此技能的场景

• 目标数据库不是 PostgreSQL
• 只需优化查询，无需更改架构
• 需要与数据库无关的数据建模指南

说明

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

安全

• 未做好备份并制定回滚计划前，避免在生产环境执行破坏性 DDL。
• 应用架构更改前，使用迁移并在暂存环境中验证。

核心规则

• 为引用表（用户、订单等）定义 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 连续。
• 堆存储：默认不会按主键聚簇（与 SQL Server/MySQL InnoDB 不同）；CLUSTER 只会执行一次重组，后续插入时不会持续维护。堆的物理顺序无法保证，并且会随着更新和维护而改变；查询结果顺序必须通过 ORDER BY 指定。
• 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。TOAST 决策取决于元组总大小、可压缩性和存储设置，而不是无条件地按每个值 2KB 作为阈值。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。支持网络运算符（<<、>>、&&）。
• 几何类型：二维空间数据使用 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。

表类型

• 常规：默认类型；完全持久化，并记录日志。
• TEMPORARY：通常限定于会话，并在会话结束时删除；ON COMMIT 可以改变其生命周期，且数据不会写入 WAL 日志。适用于临时操作，速度更快。
• 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())。current_user_id() 函数是特定于应用的占位符，并非内置函数。请定义可信的身份函数，以非所有者角色测试策略，并考虑所有者/BYPASSRLS 绕过以及 FORCE ROW LEVEL SECURITY。

约束

• PK：隐式包含 UNIQUE + NOT NULL；会创建 B-tree 索引。
• FK：指定 ON DELETE/UPDATE 操作（CASCADE、RESTRICT、SET NULL、SET DEFAULT）。在引用列上显式添加索引——可加快联接，并避免删除/更新父行时出现锁问题。对于循环 FK 依赖，使用 DEFERRABLE INITIALLY DEFERRED 在事务结束时检查。
• UNIQUE：会创建 B-tree 索引；除非使用 NULLS NOT DISTINCT（PG15+），否则允许多个 NULL。标准行为：允许 (1, NULL) 和 (1, NULL) 同时存在。使用 NULLS NOT DISTINCT 时，只允许一个 (1, NULL)。除非确实需要重复的 NULL，否则优先使用 NULLS NOT DISTINCT。
• CHECK：行级约束；NULL 值会通过检查（三值逻辑）。例如：CHECK (price > 0) 允许 price 为 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) 上的索引；而仅针对 b 的谓词仍可能使用扫描或依赖版本的跳跃扫描；请检查实际执行计划）。将选择性最高/最常用于筛选的列放在前面。
• 覆盖索引：CREATE INDEX ON tbl (id) INCLUDE (name, email) - 包含非键列，以支持仅索引扫描；只有在 MVCC 可见性允许时，才能避免访问堆表。
• 部分索引：适用于频繁访问的子集（WHERE status = 'active' → CREATE INDEX ON tbl (user_id) WHERE status = 'active'）。规划器必须证明查询谓词蕴含索引谓词；通用参数化计划和成本选择可能会阻止索引被使用。
• 表达式索引：适用于计算后的搜索键（CREATE INDEX ON tbl (LOWER(email))）。WHERE 子句中的表达式必须完全匹配：WHERE LOWER(email) = '[email protected]'。
• GIN：适用于 JSONB 包含/存在查询、数组（@>、?）和全文搜索（@@）
• GiST：适用于范围、几何数据和排斥约束
• BRIN：适用于超大型且自然有序的数据（时间序列）——存储开销极小。当磁盘上的行顺序与索引列相关时（按插入顺序排列，或在执行 CLUSTER 后），效果最佳。

分区

• 适用于非常大的表（>100M 行），且查询始终按分区键筛选的场景（通常是时间／日期）。
• 另一种用途：适用于数据维护任务有特定要求的表，例如定期清理或批量替换数据。
• 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 约束，是一种独立机制。
• 优先使用声明式分区或超表。不要使用表继承。
• 限制：不支持全局 UNIQUE 约束，需在 PK／UNIQUE 中包含分区键。现代 PostgreSQL 支持在分区表上使用外键；应检查目标版本和约束规则，而不是用临时触发器替代完整性约束。

特殊注意事项

更新频繁的表

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

插入频繁的工作负载

• 尽量减少索引——只创建查询所需的索引；每个索引都会拖慢插入。
• 使用 COPY 或多行 INSERT，而不是逐行插入。
• 对可重建的暂存数据使用 UNLOGGED 表——写入速度快得多。
• 批量加载时延后创建索引——先删除索引，加载数据，再重新创建索引。
• 按时间／哈希分区以分散负载。TimescaleDB 可自动对插入频繁的数据进行分区和压缩。
• 使用自然键作为主键，例如 (timestamp, device_id)，前提是需要强制全局唯一性。许多插入频繁的表根本不需要主键。
• 如果确实需要代理键，优先使用 BIGINT GENERATED ALWAYS AS IDENTITY，而不是 UUID。

适合 Upsert 的设计

• 冲突目标列上必须有 UNIQUE 索引——ON CONFLICT (col1, col2) 需要完全匹配的唯一索引（当冲突目标包含兼容的 WHERE 谓词时，可推断部分唯一索引）。
• 使用 EXCLUDED.column 引用原本要插入的值；只更新实际发生变化的列，以减少写入开销。
• 不需要实际更新时，DO NOTHING 比 DO UPDATE 快。

安全的架构演进

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

生成列

• 使用 ... 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 列和文本列建立 GIN 索引）。
• hstore：键值对；大多已被 JSONB 取代，但适用于简单的字符串映射。
• timescaledb：时序数据必备——自动分区、保留策略、压缩、连续聚合。
• postgis：提供超越基本几何类型的全面地理空间支持，是基于位置的应用程序必备组件。
• pgvector：用于嵌入向量的相似性搜索。
• pgaudit：记录所有数据库活动的审计日志。

JSONB 指南

• 优先使用带有 GIN 索引的 JSONB。
• 默认：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);
  • 权衡：不再支持键存在性查询（?、?|、?&）；它也支持受支持的 JSONPath 运算符 @? 和 @@，但不支持键存在性运算符
• 对特定标量字段进行相等/范围查询：提取该字段并使用 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);

局限性

设计默认值是权衡取舍，并非对类型的普遍禁用：不带时区的 timestamp 可以表示有意设定的墙上时间，varchar 也可以限制长度。索引、分区和扩展需要根据工作负载和版本进行验证，而非依据固定行数阈值或强制使用供应商扩展。pgAudit 日志取决于配置，并不保证记录所有活动。电子邮件正则表达式示例有意保持不完整；不要用它们来证明邮件可送达。应在锁定预算和备份条件下演练迁移；回滚无法撤销所有外部影响。