Back to Details

database-management

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Database Management Skill

Database Management Skill

Purpose

目的

This skill provides guidelines for managing database schemas and migrations in production environments. It focuses on safe, incremental schema changes that maintain data integrity and support zero-downtime deployments.
本Skill提供生产环境中管理database schemas与migrations的指南，重点关注安全、增量式的架构变更，以维护数据完整性并支持零停机部署。

Operating Mode Decision Flow

操作模式决策流程

When touching database schema or migrations, first determine the operating mode:
  1. Greenfield, before production deployment, no real shared data
    • Start from an empty database.
    • Build the schema exclusively through the initial migration.
    • Fold schema changes back into the initial migration instead of accumulating throwaway migrations.
  2. Brownfield, existing database without a reliable migration history
    • Do not pretend the database is empty.
    • Choose and document one adoption strategy: baseline, schema capture, or parallel database/schema with gradual migration.
    • From that point onward, make schema changes reproducible from source control.
  3. Production, already deployed, or any real shared data exists
    • Treat applied migrations as immutable.
    • Create a new focused incremental migration for each schema change.
    • Prefer backward-compatible changes and expand/contract rollout for risky changes.
Then classify the change:
  • Additive changes: new tables, nullable columns, indexes, or constraints that existing data already satisfies are usually the safest.
  • Destructive changes: dropping columns/tables, changing types, or tightening constraints should be staged and usually need expand/contract.
  • Large data changes: prefer a separate, batched, resumable backfill instead of one long blocking migration.
当涉及database schema或migrations时，首先确定操作模式：
  1. 全新项目（Greenfield），生产部署前，无真实共享数据
    • 从空数据库开始。
    • 仅通过初始migration构建架构。
    • 将架构变更合并回初始migration，而非累积一次性migrations。
  2. 遗留项目（Brownfield），现有数据库无可靠迁移历史
    • 不要假设数据库是空的。
    • 选择并记录一种适配策略：基线（baseline）、架构捕获（schema capture），或并行数据库/架构逐步迁移。
    • 从该点开始，确保架构变更可从版本控制中复现。
  3. 生产环境，已部署或存在真实共享数据
    • 将已应用的migrations视为不可变。
    • 为每个架构变更创建新的增量式migration。
    • 优先选择向后兼容的变更，对高风险变更采用扩展/收缩（expand/contract）发布流程。
然后对变更进行分类：
  • 新增型变更：新表、可为空列、索引，或现有数据已满足的约束，通常是最安全的。
  • 破坏性变更：删除列/表、修改类型或收紧约束，应分阶段进行，通常需要扩展/收缩流程。
  • 大数据变更：优先选择单独的、分批的、可恢复的回填（backfill），而非单次长时间阻塞的migration。

Core Principles

核心原则

1. Pre-migration State (CRITICAL)

1. 迁移前状态（CRITICAL）

Greenfield services should start from an empty database and build the schema exclusively through migrations. This ensures consistent environments and reliable onboarding.
Brownfield systems (existing databases) require an adoption strategy. Do not pretend the database is empty. Choose one approach and document it:
  • Baseline: mark an existing schema version as the starting point, then apply incremental migrations going forward
  • Schema capture: capture the current schema as an initial migration (often generated), then proceed incrementally
  • Parallel database/schema: create a new database/schema and migrate traffic/data gradually
Key requirement: for a given deployed unit, schema changes must be reproducible from source control and consistently applied across environments.
全新项目应从空数据库开始，仅通过migrations构建架构。 这确保环境一致，且新成员可可靠上手。
遗留系统（现有数据库）需要适配策略。 不要假设数据库是空的。选择一种方法并记录：
  • 基线（baseline）：将现有架构版本标记为起点，之后应用增量式migrations
  • 架构捕获（schema capture）：将当前架构捕获为初始migration（通常自动生成），之后逐步推进
  • 并行数据库/架构：创建新数据库/架构，逐步迁移流量与数据
关键要求：对于给定的部署单元，架构变更必须可从版本控制中复现，并在所有环境中一致应用。

2. First Migration is Self-Contained (CRITICAL)

2. 初始Migration独立完备（CRITICAL）

The first migration must be sufficient to bootstrap a new environment. In a greenfield service, this typically means creating the initial schema (tables, indexes, constraints) needed for the application to start.
Guidelines:
  • Keep the initial migration understandable; it is the foundation for all environments
  • Avoid dialect-specific features unless you explicitly document them as required prerequisites
  • Be careful with “automatic” fields (e.g., 
    updated_at
    ): the database will not update them automatically unless you implement that behavior (via application code or database mechanisms)
初始migration必须足以引导新环境启动。 在全新项目中，这通常意味着创建应用启动所需的初始架构（表、索引、约束）。
指南：
  • 保持初始migration易懂，它是所有环境的基础
  • 除非明确记录为必需前提，否则避免使用特定数据库方言的特性
  • 注意“自动”字段（如
    updated_at
    ）：除非通过应用代码或数据库机制实现该行为，否则数据库不会自动更新它们

3. Incremental Changes After Production (CRITICAL)

3. 生产部署后的增量变更（CRITICAL）

After production deployment, all new migrations must be incremental changes only. Each migration should make a single, focused change to the schema. Never recreate the entire schema as part of production schema evolution.
Guidelines:
  • Keep each migration small and focused
  • Prefer schema changes that are safe to apply while the application is running
  • Never edit, reorder, delete, or squash migrations that may already have run in a shared environment
  • Do not drop/recreate production tables as part of “schema evolution”
Primary keys (CRITICAL):
  • Prefer UUIDv7 for primary keys when supported (for better index locality and write performance characteristics than fully random UUIDs)
  • Use UUIDv4 when UUIDv7 is not available
  • Only use auto-incrementing integers when you have a clear reason and have evaluated trade-offs (predictability, sharding/merging difficulty, cross-system uniqueness)
生产部署后，所有新migrations必须仅为增量式变更。 每个migration应对架构做出单一、聚焦的变更。在生产架构演进中，绝不能重新创建整个架构。
指南：
  • 保持每个migration小而聚焦
  • 优先选择可在应用运行时安全应用的架构变更
  • 切勿编辑、重排、删除或压缩可能已在共享环境中运行过的migrations
  • 不要在“架构演进”中删除/重建生产表
主键（CRITICAL）：
  • 若支持，优先选择UUIDv7作为主键（相比完全随机的UUID，具有更好的索引局部性和写入性能）
  • 当UUIDv7不可用时，使用UUIDv4
  • 仅在有明确理由并评估过权衡（可预测性、分片/合并难度、跨系统唯一性）后，才使用自增整数

4. Development Phase: Single Migration Strategy (CRITICAL)

4. 开发阶段：单一Migration策略（CRITICAL）

During the development phase (before production deployment), do not create additional migration files. Instead, merge all schema changes into the initial schema migration. This keeps the migration history clean and avoids accumulating throwaway migrations that will never run incrementally in production.
Guidelines:
  • When a schema change is needed, modify the initial migration file directly to include the new tables, columns, or indexes
  • Delete any extra migration files that were created
  • Reset the migration version tracker in the development database to point to the initial migration
  • Only start creating incremental migrations once the service has been deployed to production and real data exists
This rule does not apply once the service is in production. After production deployment, follow the incremental changes principle (section 3).
开发阶段（生产部署前），不要创建额外的migration文件。 相反，将所有架构变更合并到初始架构migration中。这保持迁移历史整洁，避免累积不会在生产中增量运行的一次性migrations。
指南：
  • 当需要架构变更时，直接修改初始migration文件以包含新表、列或索引
  • 删除所有已创建的额外migration文件
  • 将开发数据库中的迁移版本跟踪器重置为指向初始migration
  • 仅在服务部署到生产且存在真实数据后，才开始创建增量式migrations
此规则不适用于服务进入生产后。 生产部署后，请遵循增量变更原则（第3节）。

5. Production Migration Strategy (Recommended Default)

5. 生产Migration策略（推荐默认）

For production systems, treat schema changes as a multi-deploy process. The safe default is the expand/contract pattern:
  1. Expand: introduce new schema elements in a backward-compatible way (new nullable columns, new tables, new indexes)
  2. Dual compatibility: deploy application code that can work with both old and new schema (feature flags help)
  3. Backfill: migrate existing data in a controlled way (often outside a single transaction; in batches)
  4. Switch reads/writes: move the application to the new schema
  5. Contract: remove old schema elements only after verifying they are no longer used
These are strong recommendations, not universal hard rules. Database engines, migration tools, table sizes, traffic patterns, and operational windows differ. If a project uses a different safe approach, follow the local standard and document the reason.
Operational recommendations:
  • Check the database engine and version before assuming DDL lock behavior or transactional DDL support.
  • Prefer online/non-blocking variants where the database supports them, such as concurrent index creation.
  • For populated tables, prefer staged constraint changes: add nullable or non-validated shape first, backfill/validate, then enforce.
  • For large tables, avoid long-running single-shot updates; prefer batched, resumable, observable backfills.
  • Keep migrations idempotent where practical, or rely on the migration tool’s exactly-once guarantees.
  • Apply migrations through the established migration tool, not by ad hoc production SQL.
对于生产系统，将架构变更视为多部署流程。安全的默认模式是**扩展/收缩（expand/contract）**模式：
  1. 扩展（Expand）：以向后兼容的方式引入新架构元素（新的可为空列、新表、新索引）
  2. 双兼容（Dual compatibility）：部署可同时适配新旧架构的应用代码（功能标志会有所帮助）
  3. 回填（Backfill）：以受控方式迁移现有数据（通常在单个事务外；分批进行）
  4. 切换读写（Switch reads/writes）：将应用切换到新架构
  5. 收缩（Contract）：仅在确认旧架构元素不再被使用后，再移除它们
这些是强烈推荐的方案，而非通用硬性规则。数据库引擎、迁移工具、表大小、流量模式和操作窗口各不相同。如果项目使用其他安全方法，请遵循本地标准并记录原因。
操作建议：
  • 在假设DDL锁行为或事务性DDL支持之前，检查数据库引擎和版本。
  • 优先选择数据库支持的在线/非阻塞变体，例如并发索引创建。
  • 对于已填充数据的表，优先选择分阶段的约束变更：先添加可为空或未验证的约束，再回填/验证，最后强制执行。
  • 对于大表，避免长时间运行的单次更新；优先选择分批、可恢复、可观察的回填。
  • 尽可能保持migrations幂等，或依赖迁移工具的精确一次执行保证。
  • 通过既定的迁移工具应用migrations，而非临时的生产SQL。

6. Migration File Naming and Organization

6. Migration文件命名与组织

Choose one canonical migrations location per deployable service and enforce it. The exact folder name and placement should follow your migration framework and repository layout (especially in monorepos).
Examples (all valid depending on framework/project):
  • migrations/
    at the service root
  • db/migrate/
    (common in some ecosystems)
  • src/<app>/migrations/
    (framework-driven layouts)
Rules:
  • There must be exactly one authoritative migrations location for a deployed unit.
  • Do not scatter migrations for the same service across multiple folders.
  • Configure the migration tool and CI/CD to run migrations from the canonical location.
  • In monorepos, the canonical location is typically at the service root, not necessarily the repository root.
  • Follow the migration tool’s naming and ordering convention consistently.
Acceptable common patterns include:
  • Timestamp-based: 
    YYYYMMDDHHMMSS_description.sql
  • Sequential: 
    0001_description.sql
  • Tool-generated identifiers (when the framework enforces ordering)
为每个可部署服务选择一个规范的migrations存放位置并强制执行。 具体文件夹名称和位置应遵循迁移框架和仓库布局（尤其是在单体仓库中）。
示例（根据框架/项目不同均有效）：
  • 服务根目录下的
    migrations/
  • db/migrate/
    （在某些生态系统中常见）
  • src/<app>/migrations/
    （框架驱动的布局）
规则：
  • 每个部署单元必须有且仅有一个权威的migrations存放位置。
  • 不要将同一服务的migrations分散到多个文件夹中。
  • 配置迁移工具和CI/CD从规范位置运行migrations。
  • 在单体仓库中，规范位置通常在服务根目录，而非仓库根目录。
  • 始终遵循迁移工具的命名和排序约定。
可接受的常见模式包括：
  • 基于时间戳：
    YYYYMMDDHHMMSS_description.sql
  • 顺序编号：
    0001_description.sql
  • 工具生成的标识符（当框架强制排序时）

7. Rollback / Reversal Strategy (CRITICAL)

7. 回滚/反转策略（CRITICAL）

Every migration must have a documented reversal strategy, but not every migration should be rolled back via “down” SQL.
Choose the safest option for the change:
  • Down migration (safe for additive changes like new tables/columns/indexes when no data is lost)
  • Forward-fix (preferred when rollback risks data loss; revert application behavior via feature flags and ship a corrective migration)
  • Backup/restore (for catastrophic cases; ensure you can restore and recover)
  • Expand/contract reversal (switch application back to old schema path, then clean up later)
Guidelines:
  • Require a documented reversal strategy for every migration.
  • Prefer down migrations only when they are demonstrably safe (typically additive changes where no data is lost).
  • Prefer forward-fix when rollback risks data loss or inconsistency (revert application behavior via feature flags, then ship a corrective migration).
  • Ensure you have a backup/restore plan for catastrophic recovery.
每个migration必须有记录的反转策略，但并非每个migration都应通过“down” SQL回滚。
为变更选择最安全的选项：
  • Down migration（适用于新增型变更，如新表/列/索引，且无数据丢失风险时）
  • 向前修复（Forward-fix）（当回滚有数据丢失风险时优先选择；通过功能标志恢复应用行为，然后发布修正性migration）
  • 备份/恢复（Backup/restore）（用于灾难性情况；确保可恢复）
  • 扩展/收缩反转（Expand/contract reversal）（将应用切换回旧架构路径，之后再清理）
指南：
  • 要求每个migration都有记录的反转策略。
  • 仅当可证明安全时（通常是无数据丢失的新增型变更），优先选择down migrations。
  • 当回滚有数据丢失或不一致风险时，优先选择向前修复（通过功能标志恢复应用行为，然后发布修正性migration）。
  • 确保有针对灾难性恢复的备份/恢复计划。

8. Testing Migrations

8. 测试Migrations

Test according to the operating mode:
  • For greenfield services, verify a new empty database can be fully bootstrapped from migrations.
  • For production changes, test the incremental migration against production-like data.
  • Test application compatibility before and after the migration.
  • Test the documented reversal strategy where practical.
  • Verify data integrity after migration and backfill steps.
Example PostgreSQL validation queries:
sql
SELECT COUNT(*) as user_count FROM users;
SELECT column_name FROM information_schema.columns WHERE table_name = 'users';
SELECT indexname FROM pg_indexes WHERE tablename = 'users';
根据操作模式进行测试：
  • 对于全新项目，验证新的空数据库可通过migrations完全引导。
  • 对于生产变更，针对类生产数据测试增量式migration。
  • 测试迁移前后的应用兼容性。
  • 尽可能测试已记录的反转策略。
  • 验证迁移和回填步骤后的 data integrity。
示例PostgreSQL验证查询：
sql
SELECT COUNT(*) as user_count FROM users;
SELECT column_name FROM information_schema.columns WHERE table_name = 'users';
SELECT indexname FROM pg_indexes WHERE tablename = 'users';

Practical Workflow

实用工作流程

  1. Inspect the project’s migration tool, canonical migrations location, naming convention, and current migration history.
  2. Determine whether the target database is greenfield pre-production, brownfield adoption, or production/shared-data.
  3. For greenfield pre-production changes, edit the initial migration and reset only local/dev migration state as needed.
  4. For production/shared-data changes, create a new incremental migration in the canonical location.
  5. Separate schema changes from large data backfills when the data work may be long-running or operationally risky.
  6. Add or document validation queries and a reversal strategy.
  7. Test on an empty database when bootstrapping matters, and on production-like data when evolving an existing database.
  8. Apply migrations through the configured migration framework in order.
  1. 检查项目的迁移工具、规范migrations位置、命名约定和当前迁移历史。
  2. 确定目标数据库是生产前的全新项目、遗留适配，还是生产/共享数据环境。
  3. 对于生产前的全新项目变更，编辑初始migration并根据需要仅重置本地/开发迁移状态。
  4. 对于生产/共享数据变更，在规范位置创建新的增量式migration。
  5. 当数据操作可能长时间运行或存在操作风险时，将架构变更与大数据回填分离。
  6. 添加或记录验证查询和反转策略。
  7. 当涉及引导新环境时，在空数据库上测试；当演进现有数据库时，在类生产数据上测试。
  8. 通过配置的迁移框架按顺序应用migrations。

Anti-Patterns to Avoid

需避免的反模式

  1. Treating a brownfield database as if it were empty.
  2. Editing, deleting, reordering, or squashing migrations that may already have run in shared environments.
  3. Maintaining multiple source-of-truth migration folders for the same deployed unit.
  4. Bypassing the migration framework with direct production schema changes.
  5. Dropping/recreating production tables to perform ordinary schema evolution.
  6. Making a production schema change that breaks the currently deployed application version.
  7. Assuming DDL lock behavior or transactional DDL support without checking the database engine and version.
  8. Running large backfills as one unobservable, non-resumable operation.
  9. Adding or tightening constraints on populated tables without validating existing data.
  10. Forcing a timestamp or sequential naming convention when the project’s migration tool requires a different convention.
  1. 将遗留数据库视为空数据库。
  2. 编辑、删除、重排或压缩可能已在共享环境中运行过的migrations。
  3. 为同一部署单元维护多个migrations数据源。
  4. 绕过迁移框架直接修改生产架构。
  5. 通过删除/重建生产表来执行常规架构演进。
  6. 进行会破坏当前部署应用版本的生产架构变更。
  7. 未检查数据库引擎和版本就假设DDL锁行为或事务性DDL支持。
  8. 将大数据回填作为不可观察、不可恢复的单一操作运行。
  9. 在已填充数据的表上添加或收紧约束而不验证现有数据。
  10. 当项目迁移工具要求不同约定时，强制使用时间戳或顺序命名约定。