database-design-patterns

数据库设计模式

关系型数据库架构设计专家。涵盖规范化决策、索引选择、迁移安全性和连接池——这些结构性基础决定了数据库能否在大规模场景下保持良好性能，还是会成为维护负担。

适用场景

适用于：

• 设计新架构或重构现有架构
• 针对特定查询模式决定是否规范化或反规范化
• 选择索引类型（B-tree、GIN、GiST、hash、partial、covering）
• 规划不会中断正在运行的生产系统的迁移
• 实现软删除、多态关联或复合键
• 配置 PgBouncer 或 Prisma 连接池

不适用于：

• 运行 EXPLAIN ANALYZE 或阅读查询计划 → 使用 postgresql-optimization
• 文档模型设计（MongoDB、DynamoDB）→ 使用 NoSQL 技能
• 数据库配置、只读副本或基础设施 → 使用云端／基础设施技能
• ORM 专属代码生成 → 使用相应的 ORM 技能

---

规范化与反规范化决策树

flowchart TD
    A[New data or query performance problem?] --> B{New data design?}
    B -->|Yes| C[Start normalized: 3NF]
    B -->|No — query too slow| D{Measured with EXPLAIN?}
    D -->|No| E[Measure first. Never guess.]
    D -->|Yes — proven join bottleneck| F{Read-heavy or write-heavy?}
    F -->|Read-heavy, joins are the bottleneck| G[Controlled denormalization:\nmaterialized view or cached column]
    F -->|Write-heavy or balanced| H[Keep normalized.\nOptimize query or add index first.]
    C --> I{Any repeated groups in a row?}
    I -->|Yes| J[1NF: Move to child table]
    I -->|No| K{Non-key cols depend on part of PK?}
    K -->|Yes| L[2NF: Extract to separate table]
    K -->|No| M{Transitive dependencies?}
    M -->|Yes| N[3NF: Extract lookup table]
    M -->|No| O[Schema is 3NF — ship it]

规则：从 3NF 开始。只有经过测量后，才对可证明过慢的特定联接进行反规范化。切勿凭猜测进行反规范化。

---

索引选择决策树

flowchart TD
    A[Which index type?] --> B{Data type and query pattern}
    B -->|Equality or range on scalar| C[B-tree — default choice]
    B -->|Full-text search, arrays, JSONB| D[GIN — inverted index]
    B -->|Geometric / PostGIS types| E[GiST — generalized search]
    B -->|Exact equality only, very high cardinality| F[Hash — rare, limited utility]
    C --> G{Subset of rows frequently queried?}
    G -->|Yes, e.g. status = 'active'| H[Partial index:\nWHERE status = 'active']
    G -->|No| I{Query selects only indexed columns?}
    I -->|Yes| J[Covering index:\nINCLUDE additional columns]
    I -->|No| K[Standard B-tree index]

始终创建索引：

• 每个外键列（防止联接时进行全表扫描）
• 常见查询中出现在 WHERE、ORDER BY 或 JOIN ON 子句中的列
• 复合索引：将选择性最高的列放在最前面

选择 partial 索引还是 covering 索引，或调整多列索引中的列顺序时，请参阅 references/indexing-strategies.md。

---

迁移安全性决策树

flowchart TD
    A[Schema change needed] --> B{Breaking change?}
    B -->|No: add nullable column, add index| C[Single migration, safe to run]
    B -->|Yes: rename column, change type, drop column| D[Expand-Contract pattern]
    D --> E[Phase 1 — Expand:\nAdd new column/table, keep old]
    E --> F[Deploy app: write to both old and new]
    F --> G[Backfill existing rows to new column]
    G --> H[Phase 2 — Contract:\nRemove old column once all reads use new]
    H --> I[Deploy app: read only from new]
    I --> J[Drop old column in final migration]
    C --> K{Large table?}
    K -->|Yes| L[CREATE INDEX CONCURRENTLY\nALTER TABLE with minimal lock]
    K -->|No| M[Standard migration]

有关扩展-收缩模板、锁超时设置和回滚策略，请参阅 references/migration-patterns.md。

---

规范化参考

1NF — 不含重复组

每列只存放一个值。列中不得存放逗号分隔的列表。

-- Bad: tags stored as CSV
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  tags TEXT  -- "sql,indexing,performance"
);

-- Good: normalized to child table
CREATE TABLE article_tags (
  article_id INT REFERENCES articles(id),
  tag TEXT NOT NULL,
  PRIMARY KEY (article_id, tag)
);

2NF — 不含部分依赖（仅适用于复合主键）

每个非键列都依赖于整个主键，而非仅依赖其中一部分。

-- Bad: product_name depends only on product_id, not on (order_id, product_id)
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  product_name TEXT,  -- should be in products table
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

3NF — 不含传递依赖

非键列只依赖于主键，而不依赖于其他非键列。

-- Bad: zip_code determines city/state (transitive)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  zip_code TEXT,
  city TEXT,    -- derivable from zip_code
  state TEXT    -- derivable from zip_code
);

-- Good: extract lookup table
CREATE TABLE zip_codes (
  zip TEXT PRIMARY KEY,
  city TEXT,
  state TEXT
);

---

关键设计决策

代理键与复合键

在以下情况下使用代理键（serial/UUID）：

• 自然键包含多列，且会作为外键出现在子表中
• 自然键可能发生变化（电子邮件地址、用户名）
• 该表会被许多其他表引用

在以下情况下使用复合主键：

• 该表是纯粹的联接／关联表，不含其他属性
• 组合确实稳定且全局唯一

-- Pure join table: composite PK is correct
CREATE TABLE user_roles (
  user_id INT REFERENCES users(id),
  role_id INT REFERENCES roles(id),
  PRIMARY KEY (user_id, role_id)
);

-- Association with attributes: add surrogate key
CREATE TABLE user_project_memberships (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  project_id INT REFERENCES projects(id),
  joined_at TIMESTAMPTZ DEFAULT NOW(),
  role TEXT
);

软删除

-- Pattern: deleted_at nullable timestamp
ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ;

-- Partial index makes "active" queries fast
CREATE INDEX idx_orders_active ON orders (user_id, created_at)
WHERE deleted_at IS NULL;

-- View hides soft-deleted rows for application code
CREATE VIEW active_orders AS
  SELECT * FROM orders WHERE deleted_at IS NULL;

警告：软删除会使唯一约束变得复杂。唯一电子邮件列只允许一名已删除用户使用该电子邮件。请使用 partial 唯一索引：

CREATE UNIQUE INDEX idx_users_email_active ON users (email)
WHERE deleted_at IS NULL;

多态关联

有两种方法——避免使用简单粗糙的模式：

-- Bad: nullable FK columns for each possible parent type
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INT REFERENCES posts(id),      -- nullable
  article_id INT REFERENCES articles(id), -- nullable
  video_id INT REFERENCES videos(id),     -- nullable
  body TEXT
);

-- Good: separate association tables (referential integrity preserved)
CREATE TABLE post_comments (
  comment_id INT REFERENCES comments(id),
  post_id INT REFERENCES posts(id),
  PRIMARY KEY (comment_id, post_id)
);

-- Or: single-table inheritance with a type column + CHECK constraint
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  parent_type TEXT NOT NULL CHECK (parent_type IN ('post', 'article', 'video')),
  parent_id INT NOT NULL,
  body TEXT
);
CREATE INDEX idx_comments_parent ON comments (parent_type, parent_id);

连接池

典型 Web 应用的 PgBouncer 配置：

[pgbouncer]
pool_mode = transaction        ; Best for short-lived web requests
max_client_conn = 1000         ; Total client connections pooler accepts
default_pool_size = 20         ; DB connections per database/user pair
server_idle_timeout = 600      ; Close idle server connections after 10 min

在 PgBouncer 中使用 Prisma——在连接 URL 中设置 pgbouncer=true：

DATABASE_URL="postgresql://user:pass@host:6432/db?pgbouncer=true&connection_limit=1"

注意：PgBouncer 事务模式不支持预处理语句、SET 或 LISTEN/NOTIFY。如果 ORM 需要预处理语句且连接池大小可控，请使用会话模式。

---

反模式

反模式：过早反规范化

新手：“JOIN 很慢，所以我要把数据复制到主表中，避免 JOIN。”

专家：有索引时，JOIN 很快。复制数据会造成更新异常——同一事实存储在两个地方，可能出现不一致。正确的顺序是：先规范化，在真实负载下测量查询耗时，用 EXPLAIN ANALYZE 找出具体的 JOIN 瓶颈，然后再考虑将物化视图或单个缓存的反规范化列作为最后手段。

检测：检查 orders 表中是否存在 user_name 之类的列，同时还有指向 users 表的 user_id 外键。如果 users.name 可以更改，orders.user_name 就会逐渐与其不一致。

LLM 常见错误：训练数据中有很多教程会将过早反规范化作为一种“性能优化”。这些教程早于索引感知型 ORM 的广泛应用，并且默认需要手动编写查询。

---

反模式：外键缺少索引

新手：“数据库会知道如何 JOIN——我只需要外键约束。”

专家：外键约束可确保引用完整性，但不会创建索引。若 orders.user_id 上没有索引，执行 JOIN orders ON orders.user_id = users.id 时，每个用户都会导致对 orders 表进行一次全表顺序扫描。对于拥有数百万行的表，这会造成灾难性后果。

检测：

-- Find FK columns with no index (PostgreSQL)
SELECT
  tc.table_name,
  kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
LEFT JOIN pg_indexes pi
  ON pi.tablename = tc.table_name
  AND pi.indexdef LIKE '%' || kcu.column_name || '%'
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND pi.indexname IS NULL;

修复：始终为每个外键列添加索引；对于在线运行的表，务必使用 CONCURRENTLY。

---

反模式：在生产查询中使用 SELECT *

新手：“SELECT * 没问题——数据库只会获取我需要的数据。”

专家：SELECT * 会获取所有列，包括你并未使用的大型 TEXT、JSONB 和 BYTEA 列。它会阻止仅索引扫描（即使索引涵盖了查询，查询也必须访问堆表）。如果依赖按列位置绑定的 ORM 中新增或调整了列的顺序，这种写法就会出错。始终明确列出列名。

检测：在应用代码中搜索任何热路径查询里的 SELECT *。对于 ORM，检查 .findAll() 或等效方法是否默认选择所有列，并添加显式字段选择。

---

参考资料

• references/indexing-strategies.md — 在选择 B-tree、GIN、GiST、hash、partial 和 covering 索引时查阅；其中包括仅索引扫描的前提条件以及多列索引排序规则。
• references/migration-patterns.md — 在规划零停机迁移时查阅；涵盖扩展-收缩模式、锁超时设置、分块回填和回滚策略。