# Postgresql

> 设计 PostgreSQL 专用模式。涵盖最佳实践、数据类型、索引、约束、性能模式和高级特性。触发词：PostgreSQL模式设计、PG表设计、PostgreSQL数据类型、PG索引、PostgreSQL分区、RLS策略、PG约束、PostgreSQL性能、PG JSONB、PostgreSQL扩展

- Skill: `kscz0000/postgresql` (Agent Skill)
- Install (CLI): `npx skillmds@latest add kscz0000/postgresql`
- Raw SKILL.md: https://api.skillmd.com/api/skills/kscz0000/postgresql/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: kscz0000 (https://skillmd.com/u/kscz0000)
- Updated: 2026-09-21
- Page: https://skillmd.com/skills/kscz0000/postgresql

---


# PostgreSQL 表设计

## 使用此技能的场景

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

## 不要使用此技能的场景

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

## 指南

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

## 安全

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

## 核心规则

- 为引用表（用户、订单等）定义 **PRIMARY KEY**。时序/事件/日志数据并非总是需要主键。使用时优先选择 `BIGINT GENERATED ALWAYS AS IDENTITY`；仅在需要全局唯一性/不透明性时使用 `UUID`。
- **先规范化（至 3NF）** 以消除数据冗余和更新异常；**仅**在已度量且高 ROI 的读取场景中，当连接性能被证实存在问题时才反规范化。过早反规范化会造成维护负担。
- 在语义要求的地方添加 **NOT NULL**；为常见值使用 **DEFAULT**。
- 为**实际查询的访问路径**创建索引：主键/唯一键（自动）、**外键列（需手动！）**、频繁过滤/排序列和连接键。
- 事件时间优先使用 **TIMESTAMPTZ**；金额使用 **NUMERIC**；字符串使用 **TEXT**；整数值使用 **BIGINT**，浮点数使用 **DOUBLE PRECISION**（精确小数运算使用 `NUMERIC`）。

## PostgreSQL "陷阱"

- **标识符**：未加引号 → 自动转为小写。避免使用加引号/大小写混合的名称。约定：表名/列名使用 `snake_case`。
- **唯一约束 + NULL**：UNIQUE 允许多个 NULL。使用 `UNIQUE (...) NULLS NOT DISTINCT`（PG15+）来限制只允许一个 NULL。
- **外键索引**：PostgreSQL **不会**自动为外键列创建索引。需要手动添加。
- **无静默强制转换**：长度/精度溢出会报错（不会截断）。示例：向 `NUMERIC(2,0)` 插入 999 会报错，不像某些数据库会静默截断或四舍五入。
- **序列/标识列会有间隔**（正常现象，不要"修复"）。回滚、崩溃和并发事务会在 ID 序列中产生间隔（1, 2, 5, 6...）。这是预期行为——不要试图让 ID 连续。
- **堆存储**：默认没有聚集主键（不同于 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)` 上的表达式索引（除非列需要大小写不敏感的主键/外键/唯一约束，否则优先）或 `CITEXT`。
- **金额**：`NUMERIC(p,s)`（绝不使用浮点数）。
- **时间**：时间戳使用 `TIMESTAMPTZ`；仅日期使用 `DATE`；持续时间使用 `INTERVAL`。避免使用 `TIMESTAMP`（无时区）。事务开始时间使用 `now()`，当前挂钟时间使用 `clock_timestamp()`。
- **布尔值**：`BOOLEAN` 配合 `NOT NULL` 约束，除非需要三态值。
- **枚举**：小型、稳定的集合（如美国州、星期几）使用 `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** 索引。适合调度、版本控制、数值范围。选择一种边界方案并保持一致；默认优先使用 `[)`（含左不含右）。
- **网络类型**：`INET` 用于 IP 地址，`CIDR` 用于网络范围，`MACADDR` 用于 MAC 地址。支持网络运算符（`<<`、`>>`、`&&`）。
- **几何类型**：`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`。


## 表类型

- **常规表**：默认；完全持久化，有 WAL 日志。
- **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`）。在引用列上添加显式索引——加速连接并防止父表删除/更新时的锁问题。循环外键依赖使用 `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`）
- **复合索引**：顺序很重要——仅在最左前缀上使用等值条件时索引生效（`WHERE a = ? AND b > ?` 使用 `(a,b)` 上的索引，但 `WHERE b = ?` 不会）。将选择性最高/最常过滤的列放在前面。
- **覆盖索引**：`CREATE INDEX ON tbl (id) INCLUDE (name, email)` - 包含非键列以实现仅索引扫描，无需访问表。
- **部分索引**：用于热点子集（`WHERE status = 'active'` → `CREATE INDEX ON tbl (user_id) WHERE status = 'active'`）。任何带 `status = 'active'` 的查询都可以使用此索引。
- **表达式索引**：用于计算搜索键（`CREATE INDEX ON tbl (LOWER(email))`）。表达式必须与 WHERE 子句完全匹配：`WHERE LOWER(email) = 'user@example.com'`。
- **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` 使用不同参数会创建重载而非替换。如不需要重载则删除旧版本。

## 生成列

- `... 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 指南

- 优先使用 `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')`


## 示例

### 用户表

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

### 订单表

```sql
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

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