skill分享 - PostgreSQL 表设计（postgresql）

简介

这是一个面向 PostgreSQL 的表结构设计 skill，强调可维护性、性能与演进安全。适合在建模、重构或评审表结构时使用，避免常见数据库设计错误。

核心价值

• ✅ 数据类型统一：字段类型选择更稳健
• ✅ 约束完整：避免脏数据与不可控写入
• ✅ 索引有据可依：基于查询路径设计索引
• ✅ 演进可控：兼顾扩展与回滚策略

适用场景

• 新建业务表或重构核心表
• 设计高并发或大数据量表
• PostgreSQL 迁移与规范化
• 统一团队数据库设计规范

如何使用

方法一：直接复制使用（推荐）

将下面的完整内容复制，发送给 Codex，告诉它“请帮我制作成 skill”。

方法二：手动创建 skill

创建 skill 并命名为 postgresql-table-design，将下方内容作为配置即可。

---

Skill 完整内容

---
name: postgresql-table-design
description: Design a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features
---

# PostgreSQL Table Design

## Core Rules

- Define a **PRIMARY KEY** for reference tables (users, orders, etc.). Not always needed for time-series/event/log data. When used, prefer `BIGINT GENERATED ALWAYS AS IDENTITY`; use `UUID` only when global uniqueness/opacity is needed.
- **Normalize first (to 3NF)** to eliminate data redundancy and update anomalies; denormalize **only** for measured, high-ROI reads where join performance is proven problematic. Premature denormalization creates maintenance burden.
- Add **NOT NULL** everywhere it’s semantically required; use **DEFAULT**s for common values.
- Create **indexes for access paths you actually query**: PK/unique (auto), **FK columns (manual!)**, frequent filters/sorts, and join keys.
- Prefer **TIMESTAMPTZ** for event time; **NUMERIC** for money; **TEXT** for strings; **BIGINT** for integer values, **DOUBLE PRECISION** for floats (or `NUMERIC` for exact decimal arithmetic).

## PostgreSQL “Gotchas”

- **Identifiers**: unquoted → lowercased. Avoid quoted/mixed-case names. Convention: use `snake_case` for table/column names.
- **Unique + NULLs**: UNIQUE allows multiple NULLs. Use `UNIQUE (...) NULLS NOT DISTINCT` (PG15+) to restrict to one NULL.
- **FK indexes**: PostgreSQL **does not** auto-index FK columns. Add them.
- **No silent coercions**: length/precision overflows error out (no truncation). Example: inserting 999 into `NUMERIC(2,0)` fails with error, unlike some databases that silently truncate or round.
- **Sequences/identity have gaps** (normal; don't "fix"). Rollbacks, crashes, and concurrent transactions create gaps in ID sequences (1, 2, 5, 6...). This is expected behavior—don't try to make IDs consecutive.
- **Heap storage**: no clustered PK by default (unlike SQL Server/MySQL InnoDB); `CLUSTER` is one-off reorganization, not maintained on subsequent inserts. Row order on disk is insertion order unless explicitly clustered.
- **MVCC**: updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.

## Data Types

- **IDs**: `BIGINT GENERATED ALWAYS AS IDENTITY` preferred (`GENERATED BY DEFAULT` also fine); `UUID` when merging/federating/used in a distributed system or for opaque IDs. Generate with `uuidv7()` (preferred if using PG18+) or `gen_random_uuid()` (if using an older PG version).
- **Integers**: prefer `BIGINT` unless storage space is critical; `INTEGER` for smaller ranges; avoid `SMALLINT` unless constrained.
- **Floats**: prefer `DOUBLE PRECISION` over `REAL` unless storage space is critical. Use `NUMERIC` for exact decimal arithmetic.
- **Strings**: prefer `TEXT`; if length limits needed, use `CHECK (LENGTH(col) <= n)` instead of `VARCHAR(n)`; avoid `CHAR(n)`. Use `BYTEA` for binary data. Large strings/binary (>2KB default threshold) automatically stored in TOAST with compression. TOAST storage: `PLAIN` (no TOAST), `EXTENDED` (compress + out-of-line), `EXTERNAL` (out-of-line, no compress), `MAIN` (compress, keep in-line if possible). Default `EXTENDED` usually optimal. Control with `ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy` and `ALTER TABLE tbl SET (toast_tuple_target = 4096)` for threshold. Case-insensitive: for locale/accent handling use non-deterministic collations; for plain ASCII use expression indexes on `LOWER(col)` (preferred unless column needs case-insensitive PK/FK/UNIQUE) or `CITEXT`.
- **Money**: `NUMERIC(p,s)` (never float).
- **Time**: `TIMESTAMPTZ` for timestamps; `DATE` for date-only; `INTERVAL` for durations. Avoid `TIMESTAMP` (without timezone). Use `now()` for transaction start time, `clock_timestamp()` for current wall-clock time.
- **Booleans**: `BOOLEAN` with `NOT NULL` constraint unless tri-state values are required.
- **Enums**: `CREATE TYPE ... AS ENUM` for small, stable sets (e.g. US states, days of week). For business-logic-driven and evolving values (e.g. order statuses) → use TEXT (or INT) + CHECK or lookup table.
- **Arrays**: `TEXT[]`, `INTEGER[]`, etc. Use for ordered lists where you query elements. Index with **GIN** for containment (`@>`, `<@`) and overlap (`&&`) queries. Access: `arr[1]` (1-indexed), `arr[1:3]` (slicing). Good for tags, categories; avoid for relations—use junction tables instead. Literal syntax: `'{val1,val2}'` or `ARRAY[val1,val2]`.
- **Range types**: `daterange`, `numrange`, `tstzrange` for intervals. Support overlap (`&&`), containment (`@>`), operators. Index with **GiST**. Good for scheduling, versioning, numeric ranges. Pick a bounds scheme and use it consistently; prefer `[)` (inclusive/exclusive) by default.
- **Network types**: `INET` for IP addresses, `CIDR` for network ranges, `MACADDR` for MAC addresses. Support network operators (`<<`, `>>`, `&&`).
- **Geometric types**: `POINT`, `LINE`, `POLYGON`, `CIRCLE` for 2D spatial data. Index with **GiST**. Consider **PostGIS** for advanced spatial features.
- **Text search**: `TSVECTOR` for full-text search documents, `TSQUERY` for search queries. Index `tsvector` with **GIN**. Always specify language: `to_tsvector('english', col)` and `to_tsquery('english', 'query')`. Never use single-argument versions. This applies to both index expressions and queries.
- **Domain types**: `CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$')` for reusable custom types with validation. Enforces constraints across tables.
- **Composite types**: `CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT)` for structured data within columns. Access with `(col).field` syntax.
- **JSONB**: preferred over JSON; index with **GIN**. Use only for optional/semi-structured attrs. ONLY use JSON if the original ordering of the contents MUST be preserved.
- **Vector types**: `vector` type by `pgvector` for vector similarity search for embeddings.

### Do not use the following data types

- DO NOT use `timestamp` (without time zone); DO use `timestamptz` instead.
- DO NOT use `char(n)` or `varchar(n)`; DO use `text` instead.
- DO NOT use `money` type; DO use `numeric` instead.
- DO NOT use `timetz` type; DO use `timestamptz` instead.
- DO NOT use `timestamptz(0)` or any other precision specification; DO use `timestamptz` instead
- DO NOT use `serial` type; DO use `generated always as identity` instead.

## Table Types

- **Regular**: default; fully durable, logged.
- **TEMPORARY**: session-scoped, auto-dropped, not logged. Faster for scratch work.
- **UNLOGGED**: persistent but not crash-safe. Faster writes; good for caches/staging.

## Row-Level Security

Enable with `ALTER TABLE tbl ENABLE ROW LEVEL SECURITY`. Create policies: `CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id())`. Built-in user-based access control at the row level.

## Constraints

- **PK**: implicit UNIQUE + NOT NULL; creates a B-tree index.
- **FK**: specify `ON DELETE/UPDATE` action (`CASCADE`, `RESTRICT`, `SET NULL`, `SET DEFAULT`). Add explicit index on referencing column—speeds up joins and prevents locking issues on parent deletes/updates. Use `DEFERRABLE INITIALLY DEFERRED` for circular FK dependencies checked at transaction end.
- **UNIQUE**: creates a B-tree index; allows multiple NULLs unless `NULLS NOT DISTINCT` (PG15+). Standard behavior: `(1, NULL)` and `(1, NULL)` are allowed. With `NULLS NOT DISTINCT`: only one `(1, NULL)` allowed. Prefer `NULLS NOT DISTINCT` unless you specifically need duplicate NULLs.
- **CHECK**: row-local constraints; NULL values pass the check (three-valued logic). Example: `CHECK (price > 0)` allows NULL prices. Combine with `NOT NULL` to enforce: `price NUMERIC NOT NULL CHECK (price > 0)`.
- **EXCLUDE**: prevents overlapping values using operators. `EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)` prevents double-booking rooms. Requires appropriate index type (often GiST).

## Indexing

- **B-tree**: default for equality/range queries (`=`, `<`, `>`, `BETWEEN`, `ORDER BY`)
- **Composite**: order matters—index used if equality on leftmost prefix (`WHERE a = ? AND b > ?` uses index on `(a,b)`, but `WHERE b = ?` does not). Put most selective/frequently filtered columns first.
- **Covering**: `CREATE INDEX ON tbl (id) INCLUDE (name, email)` - includes non-key columns for index-only scans without visiting table.
- **Partial**: for hot subsets (`WHERE status = 'active'` → `CREATE INDEX ON tbl (user_id) WHERE status = 'active'`). Any query with `status = 'active'` can use this index.
- **Expression**: for computed search keys (`CREATE INDEX ON tbl (LOWER(email))`). Expression must match exactly in WHERE clause: `WHERE LOWER(email) = 'user@example.com'`.
- **GIN**: JSONB containment/existence, arrays (`@>`, `?`), full-text search (`@@`)
- **GiST**: ranges, geometry, exclusion constraints
- **BRIN**: very large, naturally ordered data (time-series)—minimal storage overhead. Effective when row order on disk correlates with indexed column (insertion order or after `CLUSTER`).

## Partitioning

- Use for very large tables (>100M rows) where queries consistently filter on partition key (often time/date).
- Alternate use: use for tables where data maintenance tasks dictates e.g. data pruned or bulk replaced periodically
- **RANGE**: common for time-series (`PARTITION BY RANGE (created_at)`). Create partitions: `CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')`. **TimescaleDB** automates time-based or ID-based partitioning with retention policies and compression.
- **LIST**: for discrete values (`PARTITION BY LIST (region)`). Example: `FOR VALUES IN ('us-east', 'us-west')`.
- **HASH**: for even distribution when no natural key (`PARTITION BY HASH (user_id)`). Creates N partitions with modulus.
- **Constraint exclusion**: requires `CHECK` constraints on partitions for query planner to prune. Auto-created for declarative partitioning (PG10+).
- Prefer declarative partitioning or hypertables. Do NOT use table inheritance.
- **Limitations**: no global UNIQUE constraints—include partition key in PK/UNIQUE. FKs from partitioned tables not supported; use triggers.

## Special Considerations

### Update-Heavy Tables

- **Separate hot/cold columns**—put frequently updated columns in separate table to minimize bloat.
- **Use `fillfactor=90`** to leave space for HOT updates that avoid index maintenance.
- **Avoid updating indexed columns**—prevents beneficial HOT updates.
- **Partition by update patterns**—separate frequently updated rows in a different partition from stable data.

### Insert-Heavy Workloads

- **Minimize indexes**—only create what you query; every index slows inserts.
- **Use `COPY` or multi-row `INSERT`** instead of single-row inserts.
- **UNLOGGED tables** for rebuildable staging data—much faster writes.
- **Defer index creation** for bulk loads—>drop index, load data, recreate indexes.
- **Partition by time/hash** to distribute load. **TimescaleDB** automates partitioning and compression of insert-heavy data.
- **Use a natural key for primary key** such as a (timestamp, device_id) if enforcing global uniqueness is important many insert-heavy tables don't need a primary key at all.
- If you do need a surrogate key, **Prefer `BIGINT GENERATED ALWAYS AS IDENTITY` over `UUID`**.

### Upsert-Friendly Design

- **Requires UNIQUE index** on conflict target columns—`ON CONFLICT (col1, col2)` needs exact matching unique index (partial indexes don't work).
- **Use `EXCLUDED.column`** to reference would-be-inserted values; only update columns that actually changed to reduce write overhead.
- **`DO NOTHING` faster** than `DO UPDATE` when no actual update needed.

### Safe Schema Evolution

- **Transactional DDL**: most DDL operations can run in transactions and be rolled back—`BEGIN; ALTER TABLE...; ROLLBACK;` for safe testing.
- **Concurrent index creation**: `CREATE INDEX CONCURRENTLY` avoids blocking writes but can't run in transactions.
- **Volatile defaults cause rewrites**: adding `NOT NULL` columns with volatile defaults (e.g., `now()`, `gen_random_uuid()`) rewrites entire table. Non-volatile defaults are fast.
- **Drop constraints before columns**: `ALTER TABLE DROP CONSTRAINT` then `DROP COLUMN` to avoid dependency issues.
- **Function signature changes**: `CREATE OR REPLACE` with different arguments creates overloads, not replacements. DROP old version if no overload desired.

## Generated Columns

- `... GENERATED ALWAYS AS (<expr>) STORED` for computed, indexable fields. PG18+ adds `VIRTUAL` columns (computed on read, not stored).

## Extensions

- **`pgcrypto`**: `crypt()` for password hashing.
- **`uuid-ossp`**: alternative UUID functions; prefer `pgcrypto` for new projects.
- **`pg_trgm`**: fuzzy text search with `%` operator, `similarity()` function. Index with GIN for `LIKE '%pattern%'` acceleration.
- **`citext`**: case-insensitive text type. Prefer expression indexes on `LOWER(col)` unless you need case-insensitive constraints.
- **`btree_gin`/`btree_gist`**: enable mixed-type indexes (e.g., GIN index on both JSONB and text columns).
- **`hstore`**: key-value pairs; mostly superseded by JSONB but useful for simple string mappings.
- **`timescaledb`**: essential for time-series—automated partitioning, retention, compression, continuous aggregates.
- **`postgis`**: comprehensive geospatial support beyond basic geometric types—essential for location-based applications.
- **`pgvector`**: vector similarity search for embeddings.
- **`pgaudit`**: audit logging for all database activity.

## JSONB Guidance

- Prefer `JSONB` with **GIN** index.
- Default: `CREATE INDEX ON tbl USING GIN (jsonb_col);` → accelerates:
  - **Containment** `jsonb_col @> '{"k":"v"}'`
  - **Key existence** `jsonb_col ? 'k'`, **any/all keys** `?\|`, `?&`
  - **Path containment** on nested docs
  - **Disjunction** `jsonb_col @> ANY(ARRAY['{"status":"active"}', '{"status":"pending"}'])`
- Heavy `@>` workloads: consider opclass `jsonb_path_ops` for smaller/faster containment-only indexes:
  - `CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);`
  - **Trade-off**: loses support for key existence (`?`, `?|`, `?&`) queries—only supports containment (`@>`)
- Equality/range on a specific scalar field: extract and index with B-tree (generated column or expression):
  - `ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;`
  - `CREATE INDEX ON tbl (price);`
  - Prefer queries like `WHERE price BETWEEN 100 AND 500` (uses B-tree) over `WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500` without index.
- Arrays inside JSONB: use GIN + `@>` for containment (e.g., tags). Consider `jsonb_path_ops` if only doing containment.
- Keep core relations in tables; use JSONB for optional/variable attributes.
- Use constraints to limit allowed JSONB values in a column e.g. `config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')`

## Examples

### Users

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

### Orders

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

Skill 中文版本（翻译）

---
name: postgresql-table-design
description: 设计 PostgreSQL 专用的数据库模式。覆盖最佳实践、数据类型、索引、约束、性能模式与高级特性。
---

# PostgreSQL 表设计

## 核心规则

- 为引用型表（users、orders 等）定义 **PRIMARY KEY**。对时间序列/事件/日志类数据并非总是需要。若使用，优先 `BIGINT GENERATED ALWAYS AS IDENTITY`；只有在需要全局唯一/不可枚举时才使用 `UUID`。
- **先规范化（到 3NF）** 以消除冗余与更新异常；仅在“经过度量、ROI 高且连接性能被证明确实有问题”的读场景下再反规范化。过早反规范化会增加维护负担。
- 在语义要求的地方加 **NOT NULL**；对常见取值使用 **DEFAULT**。
- 只为 **真实查询路径** 建索引：PK/unique（自动）、**FK 列（手动！）**、高频过滤/排序列、连接键。
- 事件时间优先 **TIMESTAMPTZ**；金额用 **NUMERIC**；字符串用 **TEXT**；整数用 **BIGINT**；浮点用 **DOUBLE PRECISION**（或 `NUMERIC` 做精确小数）。

## PostgreSQL 易踩坑

- **标识符**：不加引号会自动小写。避免带引号/混合大小写。建议表/列使用 `snake_case`。
- **Unique + NULL**：UNIQUE 允许多个 NULL。使用 `UNIQUE (...) NULLS NOT DISTINCT`（PG15+）限制为一个 NULL。
- **外键索引**：PostgreSQL **不会**自动为外键列建索引，需要手动加。
- **无隐式截断**：长度/精度溢出会直接报错（不截断）。例如向 `NUMERIC(2,0)` 插入 999 会报错，而非静默截断/四舍五入。
- **序列/identity 有空洞**（正常）。回滚、崩溃、并发都会导致序列跳号（1,2,5,6...），这是预期行为，不要尝试连续化。
- **堆存储**：默认不是聚簇主键（不同于 SQL Server/MySQL InnoDB）；`CLUSTER` 仅一次性重排，后续插入不维护。磁盘行顺序默认是插入顺序，除非手动聚簇。
- **MVCC**：更新/删除会留下死元组，需 vacuum 清理；设计时避免热点宽行高频更新。

## 数据类型

- **ID**：优先 `BIGINT GENERATED ALWAYS AS IDENTITY`（`GENERATED BY DEFAULT` 也可）；当需要合并/分布式/不可枚举 ID 时用 `UUID`。可用 `uuidv7()`（PG18+）或 `gen_random_uuid()`（旧版本）。
- **整数**：优先 `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，除非需要大小写不敏感 PK/FK/UNIQUE）或 `CITEXT`。
- **金额**：`NUMERIC(p,s)`（不要用 float）。
- **时间**：时间戳用 `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** 索引。适合排期、版本、数值区间。边界建议统一为 `[)`（左闭右开）。
- **网络类型**：IP 用 `INET`，网段用 `CIDR`，MAC 用 `MACADDR`。支持网络运算符（`<<`, `>>`, `&&`）。
- **几何类型**：`POINT`、`LINE`、`POLYGON`、`CIRCLE` 用于 2D 空间；用 **GiST** 索引。高级空间需求用 **PostGIS**。
- **全文检索**：文档用 `TSVECTOR`，查询用 `TSQUERY`。`tsvector` 用 **GIN** 索引。务必指定语言：`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**：优先 JSONB；可用 **GIN** 索引。仅用于可选/半结构字段。只有当必须保留 JSON 原始顺序时才用 JSON。
- **向量类型**：`pgvector` 的 `vector` 用于向量检索/embedding。

### 不要使用以下数据类型

- 不要用 `timestamp`（无时区）；请用 `timestamptz`。
- 不要用 `char(n)` 或 `varchar(n)`；请用 `text`。
- 不要用 `money`；请用 `numeric`。
- 不要用 `timetz`；请用 `timestamptz`。
- 不要用 `timestamptz(0)` 或任何精度限定；请用 `timestamptz`。
- 不要用 `serial`；请用 `generated always as identity`。

## 表类型

- **Regular**：默认；完全持久化、写 WAL。
- **TEMPORARY**：会话级，自动删除，不写 WAL。适合临时计算。
- **UNLOGGED**：持久化但崩溃不安全。写入快，适合缓存/中间表。

## 行级安全（RLS）

使用 `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`）。对引用列**显式建索引**，可加速 JOIN 并避免父表删除/更新时锁问题。循环依赖可用 `DEFERRABLE INITIALLY DEFERRED`，在事务末尾检查。
- **UNIQUE**：创建 B-tree 索引；允许多个 NULL，除非使用 `NULLS NOT DISTINCT`（PG15+）。默认允许 `(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)`，用于 index-only scan。
- **部分索引**：热子集（`WHERE status = 'active'` → `CREATE INDEX ON tbl (user_id) WHERE 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 可自动分区/保留/压缩。
- **LIST**：离散值（`PARTITION BY LIST (region)`），例：`FOR VALUES IN ('us-east', 'us-west')`。
- **HASH**：无自然分区键时做均匀分布（`PARTITION BY HASH (user_id)`），按 modulus 生成 N 分区。
- **约束排除**：查询规划器依赖分区 CHECK 做裁剪；声明式分区（PG10+）自动生成。
- 优先使用声明式分区或 hypertable；**不要**用表继承。
- 限制：无全局 UNIQUE（需包含分区键）；分区表不支持外键（需触发器替代）。

## 特殊考虑

### 更新密集表

- 拆分热/冷字段，将频繁更新列拆到独立表以减少膨胀。
- `fillfactor=90` 留出空间以启用 HOT 更新（避免索引维护）。
- 避免更新已建索引的列（会阻断 HOT 更新）。
- 按更新模式分区，将热点行与稳定数据分开。

### 插入密集负载

- 最小化索引（只建查询需要的）；每个索引都会拖慢写入。
- 用 COPY 或多行 INSERT，避免单行插入。
- 用 UNLOGGED 表做可重建 staging，写入更快。
- 批量导入前延迟建索引（drop → load → recreate）。
- 按时间/hash 分区分摊负载；TimescaleDB 可自动分区与压缩。
- 若需要全局唯一，可用自然键（如 `(timestamp, device_id)`）；很多高写表根本不需要 PK。
- 若需要代理键，优先 `BIGINT GENERATED ALWAYS AS IDENTITY` 而非 UUID。

### Upsert 友好设计

- 冲突目标列必须有 UNIQUE 索引：`ON CONFLICT (col1, col2)` 需要精确匹配的唯一索引（部分索引不可用）。
- 使用 `EXCLUDED.column` 引用待插入值；只更新实际变化的列以减少写放大。
- 无需更新时 `DO NOTHING` 比 `DO UPDATE` 更快。

### 安全的 Schema 演进

- **DDL 可事务化**：大多数 DDL 可在事务中执行并回滚（`BEGIN; ALTER TABLE...; ROLLBACK;`）。
- **并发建索引**：`CREATE INDEX CONCURRENTLY` 避免阻塞写入，但不能在事务中运行。
- **易变默认值会重写全表**：新增 NOT NULL 且默认值为 `now()`、`gen_random_uuid()` 等会触发表重写；非易变默认值更快。
- **先删约束再删列**：`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()`），对 `LIKE '%pattern%'` 可用 GIN 加速。
- **`citext`**：大小写不敏感文本类型；除非需要大小写不敏感约束，否则优先 `LOWER(col)` 表达式索引。
- **`btree_gin`/`btree_gist`**：支持混合类型索引（例如 JSONB + text）。
- **`hstore`**：键值对；多数场景被 JSONB 取代，但简单字符串映射仍可用。
- **`timescaledb`**：时间序列关键扩展（自动分区、保留、压缩、连续聚合）。
- **`postgis`**：高级地理空间能力（位置类应用必备）。
- **`pgvector`**：向量相似度搜索（embedding）。
- **`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 + `@>` 做包含（如 tags）；若仅做包含可考虑 `jsonb_path_ops`。
- 关系型核心数据放表内；JSONB 用于可选/可变字段。
- 用约束限制 JSONB：如 `config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')`。

## 示例

### Users

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

### Orders

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

---

参考来源

• https://github.com/wshobson/agents/blob/main/plugins/database-design/skills/postgresql/SKILL.md
• https://skills.sh/wshobson/agents/postgresql-table-design