古法编程

数据库性能与可靠性的核心在于索引策略、安全迁移和分析型查询优化。本文提供 PostgreSQL 索引类型速查与反模式检测、基于 expand-contract 模式的零停机迁移实战、以及 ClickHouse MergeTree 引擎选型与物化视图构建的详细步骤，帮助开发者借助 Everything Claude Code 的 postgres-patterns、database-migrations 和 clickhouse-io Skill，在 AI 编程助手中高效落地生产级数据库最佳实践。

数据库最佳实践：PostgreSQL 索引优化、零停机迁移与 ClickHouse 数据工程

在 AI 辅助编程中，让 Claude Code 等助手直接操作生产数据库，需要严谨的工程模式作为护栏。本指南将通过三个核心 Skill 的实战步骤，为你构建完整的数据库优化、迁移与分析能力体系。

一、 PostgreSQL 索引、数据类型与安全速查

postgres-patterns Skill 提供了即时的模式建议，避免开发者凭记忆编码。以下是从其核心逻辑中提炼的关键操作指南。

Step 1：建立索引与数据类型决策表 根据查询模式选择正确的索引和数据类型，是优化的起点。Skill 内置了速查逻辑，可以基于查询条件自动建议。

  • 索引类型速查：对于 WHERE col = value 的等值查询，使用默认的 B-tree 索引 (CREATE INDEX idx ON t (col);)。对于 jsonb 字段内的键值检索，使用 GIN 索引 (CREATE INDEX idx ON t USING gin (col);)。复合索引遵循“等值字段在前，范围字段在后”原则，例如 CREATE INDEX idx ON orders (status, created_at); 以优化 WHERE status = 'pending' AND created_at > '2024-01-01' 查询。
  • 数据类型选型：Skill 的内置表格明确推荐使用 bigint 作为主键，timestamptz 存储带时区时间，numeric(10,2) 存储金额，避免 int、timestamp、float 带来的溢出或精度问题。
  • 覆盖索引：当查询只需要特定字段时，使用 INCLUDE 子句避免回表，例如 CREATE INDEX idx ON users (email) INCLUDE (name, created_at);。

Step 2：执行反模式检测 Skill 提供了现成的 SQL 片段，用于主动发现性能隐患。你可以直接在数据库中运行这些诊断查询。

  • 检测未索引的外键：运行源码中提供的查询，找出所有没有索引的外键约束，这通常是导致连接查询性能低下的原因。
  • 分析慢查询与表膨胀：通过 pg_stat_statements 视图查找平均执行时间超过 100ms 的查询。通过 pg_stat_user_tables 视图检查 n_dead_tup（死元组数）超过 1000 的表，确认是否需要进行 VACUUM。

Step 3：应用安全与性能配置模板 Skill 提供了经过验证的生产环境配置建议，可直接套用。

  • 安全加固：首先撤销 public schema 的默认权限，防止未授权访问 (REVOKE ALL ON SCHEMA public FROM public;)。
  • 参数调优：设置合理的连接数 (max_connections = 100)、工作内存 (work_mem = '8MB') 以及事务和语句超时 (idle_in_transaction_session_timeout = '30s', statement_timeout = '30s')。
  • 启用监控：务必安装并启用 pg_stat_statements 扩展，为后续的慢查询分析提供数据基础。

错误表现与验证：如果查询性能未提升，检查是否遗漏了索引或使用了错误的索引类型。使用 EXPLAIN ANALYZE 验证查询计划是否命中索引。验证安全配置时，尝试用低权限用户访问 restricted schema，应收到“permission denied”错误。

二、 零停机数据库迁移实践

database-migrations Skill 的核心价值在于强制推行安全的迁移流程，尤其适用于需要支持 多 Agent 并行协作 的复杂项目。它内置了对 Prisma、Drizzle、Kysely、Django、golang-migrate 等主流工具链的支持。

Step 1：遵循 Expand-Contract（扩展-收缩）模式 这是实现零停机 schema 变更的黄金法则。Skill 会为每一步生成明确的脚本。

  • 扩展阶段（无锁添加）：添加新的、可为空的列或表。例如，重命名字段时，第一步是添加新列：ALTER TABLE users ADD COLUMN display_name TEXT;。
  • 迁移阶段（数据回填）：编写数据迁移脚本，将旧列数据批量复制到新列。对于大表，必须使用批量处理模式。源码中提供了一个使用 FOR UPDATE SKIP LOCKED 和 LIMIT 的 PL/pgSQL 循环示例，它能以 10000 行为一批进行更新，避免长事务锁表。
  • 收缩阶段（清理旧物）：在确认所有应用代码都已切换到读写新列后，最后再删除旧列：ALTER TABLE users DROP COLUMN username;。

Step 2：自动生成迁移文件与安全审计清单 当使用如 Prisma 的 ORM 时，运行 npx prisma migrate dev --name add_user_avatar，Skill 会介入审查生成的 SQL。

  • 推荐安全操作：对于添加列，推荐使用 nullable 字段或带默认值的列，因为在 PostgreSQL 11+ 中，这种添加是瞬时操作，无需重写表。
  • 强制使用并发索引：对于索引创建，Skill 会强制推荐 CREATE INDEX CONCURRENTLY idx_users_email ON users (email);，以避免在创建期间锁定整个表。
  • 标记不可逆操作：任何没有对应 DOWN 脚本或无法安全回滚的操作，都会被明确标记警告。

Step 3：测试与上线

  • 本地/预发布环境回归：Skill 强烈建议在拥有生产规模数据量的副本上测试迁移。一个在 100 行表上瞬间完成的 ALTER TABLE，可能在 1000 万行表上导致长时间锁表。
  • 生产上线与回滚：上线时，使用 npx prisma migrate deploy（或对应工具的 deploy 命令）。Skill 会提供检查生产环境 schema 是否与版本库一致的 SQL，防止“环境漂移”。回滚策略是创建一个新的 forward migration 来修复问题，而不是直接撤销已上线的迁移。

错误表现与验证：如果迁移在生产环境卡住，立即检查 pg_stat_activity 中的锁等待。验证迁移是否成功，除了查看 ORM 的应用状态，应直接查询系统表 information_schema.columns 或 \d table_name 确认列已存在且类型正确。

三、 ClickHouse 数据工程与分析优化

clickhouse-io Skill 专注于 ClickHouse 的 OLAP 特性，帮助你设计出高性能的数据管道和分析查询。

Step 1：选择正确的表引擎与设计 表引擎决定了数据的存储、更新和查询方式。

  • MergeTree：适用于纯追加的日志和事件数据。设计时，ORDER BY 子句至关重要，它定义了数据的物理排序和主键索引。PARTITION BY toYYYYMM(date) 是按月分区的常见实践。
  • ReplacingMergeTree：需要对同一主键行进行去重时选择此引擎，它在后台合并时保留最新版本。
  • AggregatingMergeTree：用于存储预聚合的中间状态，通常与物化视图配合使用，以实现对海量数据的实时汇总。

Step 2：实施高效批量数据导入 ClickHouse 性能的关键之一是批量写入。

  • 批量插入：避免单行循环插入。应将数据收集在应用层（如使用 TypeScript 或 Python 代码），然后拼接成一个大的 INSERT ... VALUES 语句一次性发送。源码中的 TypeScript 示例展示了如何将对象数组格式化为 SQL 值列表。
  • 使用合适的数据格式：对于超大批量导入，考虑使用 TabSeparated、CSV 或 Native 格式，它们比 JSON 更高效。

Step 3：优化分析查询与构建物化视图

  • 查询优化：充分利用 ClickHouse 的列式存储和向量化执行引擎。使用 uniq() 函数进行近似去重统计，使用 countIf() 等条件聚合函数替代子查询，以优化漏斗分析等复杂逻辑。
  • 物化视图实现实时聚合：创建物化视图，将源表的增量数据自动聚合到目标表。例如，创建一个每小时聚合交易量和独立用户数的视图。查询时，需要使用 -State 和 -Merge 组合函数，如 sumState(amount) 用于插入，而查询聚合结果时使用 sumMerge(amount)。

Step 4：监控与诊断性能问题 Skill 提供了查询 system.query_log 和 system.parts 的模板。

  • 定位慢查询：查询 system.query_log，筛选 query_duration_ms > 1000 且最近一小时内的记录，找出耗时最长的查询。
  • 分析表部件状态：通过 system.parts 表可以查看表的分区、部件数量、行数及大小，帮助判断是否存在部件过多（合并延迟）或数据倾斜问题。

错误表现与验证：查询变慢时，首先检查是否命中了分区剪枝（在 WHERE 子句中使用了分区键）。通过 EXPLAIN 查看查询计划。验证物化视图是否工作，可以向源表插入测试数据，然后立即查询物化视图的目标表，看是否有预聚合结果生成。

FAQ

Q: postgres-patterns Skill 推荐的索引策略需要为每个查询都建立索引吗？ A: 不需要。索引会消耗存储和写入性能。Skill 的建议基于通用模式，你应该优先为高频率、高选择性（返回行数少）的查询条件创建索引。可以通过 pg_stat_user_tables 中的 idx_scan 字段来观察索引的实际使用情况。

Q: 在 ClickHouse 中，如何选择分区键和排序键（ORDER BY）？ A: 分区键通常选择时间字段（如 toYYYYMM(event_date)），用于数据管理和高效裁剪时间范围查询。排序键（ORDER BY）应按照查询中常见的 WHERE 过滤条件和 GROUP BY 列的顺序来设计，把最常用于等值过滤和基数高的列放在前面，这能极大加速查询和聚合。