database-architect

数据库架构师 Skill

数据库架构师 Skill——设计高效的数据库架构、优化查询、规划索引，并为关系型数据库（PostgreSQL、MySQL）和 NoSQL 数据库（MongoDB、Redis）制定迁移策略。 - 设计规范化和反规范化的架构 - 优化查询并分析执行计划 - 规划索引策略 - 数据建模（ER 图、关系） - 规划迁移并进行版本管理 - 排查性能问题

第 1 步：了解数据需求

收集需求：

1. 实体：需要存储哪些数据？
2. 关系：实体之间如何关联（1:1、1:N、N:M）？
3. 访问模式：将如何查询数据？
4. 数据量：预期的数据规模和增长率
5. 一致性：ACID 要求还是最终一致性？

第 2 步：设计架构

对于关系型数据库：

1. 规范化：从 3NF 开始，以减少冗余
2. 定义主键：使用代理键（UUID/SERIAL）或自然键
3. 定义外键：建立引用完整性
4. 考虑反规范化：仅在有明确性能需求时采用

对于 NoSQL 数据库：

1. 根据查询建模：围绕访问模式设计文档/集合
2. 嵌入还是引用：1:1/1:少量时嵌入，1:多时引用
3. 选择分片键：选择能实现均匀分布的键

第 3 步：规划索引

根据查询模式制定索引策略：

-- Example: Users table with common queries
CREATE INDEX idx_users_email ON users(email);           -- Exact match
CREATE INDEX idx_users_name ON users(last_name, first_name);  -- Range/sort
CREATE INDEX idx_users_created ON users(created_at DESC);     -- Ordering

索引指南：

• 为用于 WHERE、JOIN、ORDER BY 的列创建索引
• 对多列查询考虑使用复合索引
• 避免索引过多（会减慢写入速度）
• 对读取密集型查询使用覆盖索引

第 4 步：规划迁移

创建带版本号的迁移：

migrations/
  001_create_users.sql
  002_add_email_index.sql
  003_create_orders.sql

迁移最佳实践：

• 始终包含 up 和 down 迁移
• 使用接近生产环境的数据测试迁移
• 规划零停机迁移
• 运行迁移前进行备份

第 5 步：优化查询

分析并改进慢查询：

1. 使用 EXPLAIN ANALYZE：了解执行计划
2. 识别表扫描：改为索引扫描
3. 优化 JOIN：确保连接列上有索引
4. 批量操作：使用批量插入/更新
5. 连接池：降低连接开销

第 6 步：PostgreSQL 17 功能（2024–2026）

在适用时利用 PostgreSQL 17 的功能：

性能改进：

• 新的 VACUUM 内存管理——内存占用最多降低 20 倍；在繁忙系统上运行速度更快
• 流式 I/O 接口可加速大型数据集的顺序扫描
• BRIN 索引支持并行构建
• B-tree 索引对 IN 子句查询更高效
• 优化 CTE（通用表表达式）规划

SQL/JSON 增强功能（PG 17）：

• JSON_TABLE()——将 JSON 数据转换为关系型表表示
• JSON 构造器和标量函数（JSON()、JSON_SCALAR()、JSON_ARRAY()、JSON_OBJECT()）
• 使用 jsonpath 对 JSONB 列进行灵活的基于路径的查询

增量备份：

• pg_basebackup 支持增量备份；与 pg_upgrade 结合使用，可实现主要版本升级时零数据丢失

逻辑复制改进：

• 为逻辑复制槽提供故障转移控制
• pg_createsubscriber 可从物理备用服务器创建逻辑副本
• 现在 pg_upgrade 会在主要版本升级期间保留逻辑复制槽

安全性：

• 新增 MAINTAIN 权限——授予特定维护权限，而无需完整的超级用户访问权限
• 客户端选项 sslnegotiation=direct 可直接进行 TLS 握手（避免往返通信）

COPY 改进：

• COPY ... ON_ERROR ignore——遇到行级错误时继续导入，而不是中止

步骤 7：用于 AI 嵌入的 pgvector

在关系数据旁存储并查询向量嵌入，避免使用单独的向量数据库：

-- Install extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Table with embedding column (1536 dims for OpenAI text-embedding-3-small)
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content TEXT NOT NULL,
    embedding vector(1536),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- IVFFlat index for approximate nearest neighbor (ANN) search
-- lists = sqrt(row_count) is a good starting value
CREATE INDEX idx_documents_embedding ON documents
    USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- HNSW index (faster queries, more memory; preferred for < 1M vectors)
CREATE INDEX idx_documents_embedding_hnsw ON documents
    USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

-- Similarity search (cosine distance)
SELECT id, content, 1 - (embedding <=> $1::vector) AS similarity
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 10;

何时使用 pgvector，何时使用专用向量数据库：

• 最多约 1,000 万个向量：pgvector 已足够（使用 HNSW 索引时，查询耗时低于 50 毫秒）
• 超过 1,000 万个向量或需要专用 ANN 算法：可考虑 Pinecone、Weaviate 或 Qdrant
• pgvector 的优势：可与 PostgreSQL 的其余部分共用备份、复制和连接池

步骤 8：表分区策略

对于预计会超过可用 RAM 的表，使用声明式分区：

-- Range partitioning by date (common for time-series / logs)
CREATE TABLE events (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Hash partitioning for even distribution (e.g., multi-tenant)
CREATE TABLE orders (
    id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    total DECIMAL(12,2)
) PARTITION BY HASH (tenant_id);

CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 3);

**分区裁剪：**当 WHERE 中包含分区键时，PostgreSQL 会自动跳过无关分区。查询中始终包含分区键。

**分区表上的索引：**在父表上创建的索引会自动创建到所有子分区上。

步骤 9：大规模使用 JSONB 的模式

-- Generated columns promote hot JSONB fields to indexed native columns
CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    -- Promote frequently filtered fields to B-tree indexed generated columns
    country TEXT GENERATED ALWAYS AS (data->>'country') STORED,
    signup_date DATE GENERATED ALWAYS AS ((data->>'signup_date')::DATE) STORED
);
CREATE INDEX idx_customers_country ON customers (country);
CREATE INDEX idx_customers_signup ON customers (signup_date);

-- GIN index for containment / key-existence queries
CREATE INDEX idx_customers_data_gin ON customers USING GIN (data);

-- Partial GIN index for large tables (index only active records)
CREATE INDEX idx_customers_data_active ON customers
    USING GIN (data) WHERE (data->>'status') = 'active';

-- jsonpath query example (PG 17)
SELECT * FROM customers
WHERE data @? '$.tags[*] ? (@ == "premium")';

步骤 10：连接池

在所有生产部署中，在 PostgreSQL 前端使用连接池器：

PgBouncer（轻量、久经考验）：

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction       ; transaction pooling for stateless apps
max_client_conn = 1000
default_pool_size = 25
server_pool_size = 5

Supavisor（云原生、多租户、基于 Elixir）：

• 专为具有数千个短连接的无服务器函数 / 边缘函数设计
• 支持会话池和事务池两种模式
• 默认用于 Supabase 部署；也可自托管

连接池模式：

模式	使用场景	说明
会话	长连接、LISTEN/NOTIFY	1 个客户端 = 1 个服务端连接
事务	无状态 API（推荐默认模式）	效率最高；会破坏 SET / 预处理语句
语句	极少需要	每条语句都可以使用不同的服务端连接

</execution_process>

<best_practices>

1. 先规范化：之后再根据数据进行优化
2. 审慎建立索引：依据实际查询模式建立索引
3. 使用迁移：绝不直接修改架构
4. 监控性能：使用数据库性能分析工具
5. 为扩展做好规划：大型表可考虑分区
6. 升级到 PostgreSQL 17：受益于全新的 VACUUM 内存管理器、SQL/JSON 函数和增量备份支持
7. 使用 pgvector 处理 AI 数据：在考虑专用向量数据库之前，先使用 HNSW 或 IVFFlat 索引在 PostgreSQL 中存储嵌入
8. 大规模使用 JSONB：将频繁访问的 JSONB 字段提升为带 B-tree 索引的生成列；使用 GIN 进行包含查询
9. 始终使用连接池：所有生产环境的 PostgreSQL 部署都使用 PgBouncer（事务模式）或 Supavisor
10. 对大型表进行分区：对于预计会超过服务器 RAM 的表，采用范围（时间序列）、列表（类别）或哈希（多租户）分区

</best_practices>

**架构设计请求**：

Design a schema for an e-commerce orders system

示例回复：

-- Users table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Orders table
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Order items (1:N relationship)
CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2)
);

-- Indexes for common queries
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order ON order_items(order_id);

</usage_example>

规则

• 必须用性能数据说明反规范化的理由
• 为所有迁移提供回滚策略
• 记录关系和约束

相关工作流

此技能有一个适用于复杂多 Agent 场景的对应工作流：

• 工作流：.claude/workflows/database-architect-skill-workflow.md
• 何时使用工作流：用于全面的数据库设计，包括需求分析、架构设计、查询优化、迁移规划和测试（多阶段、多 Agent）
• 何时直接使用技能：用于快速架构审查或单 Agent 数据库任务

铁律

1. NEVER 在未使用包含 UP 和 DOWN 脚本的版本化迁移的情况下更改架构 — 在生产环境中手动执行 DDL 无法恢复。
2. ALWAYS 先规范化到至少 3NF，再考虑反规范化 — 没有经过测量的性能证据时，绝不提前优化。
3. ALWAYS 根据 EXPLAIN ANALYZE 得出的实际查询模式规划索引 — 未分析实际工作负载前，绝不推测性地添加索引。
4. NEVER 在未先使用类生产环境数据运行迁移的情况下测试或部署迁移 — 架构问题会在实际数据量下显现，而不是在空表上。
5. ALWAYS 在生产环境中使用连接池（Supavisor 或 PgBouncer）— 负载升高时，无服务器函数的直接连接会耗尽数据库连接上限。

反模式

反模式	失败原因	正确做法
直接在生产环境中手动执行 DDL	无法回滚；破坏迁移历史	始终使用包含 DOWN 脚本的版本化迁移
过早反规范化	未经分析就增加复杂性；往往没有可测量的收益	先规范化，仅在 EXPLAIN ANALYZE 揭示瓶颈后才反规范化
为每一列都创建索引	降低写入速度；浪费存储空间；误导查询规划器	仅为出现在 WHERE、JOIN 和 ORDER BY 子句中的列创建索引
添加未设置默认值的 NOT NULL 列	在大型数据集中，迁移期间会锁定整张表	先添加可空列，分批回填，然后再添加 NOT NULL 约束
无服务器函数直接连接	负载激增时耗尽连接上限	使用处于事务池化模式的 PgBouncer 或 Supavisor

记忆协议（强制）

开始前：

cat .claude/context/memory/learnings.md

完成后：

• 新模式 -> .claude/context/memory/learnings.md
• 发现问题 -> .claude/context/memory/issues.md
• 做出决策 -> .claude/context/memory/decisions.md

假设任务会中断：你的上下文可能会重置。如果没有记录在记忆中，就等于没有发生。