Wiederherstellbare Datenbankmigrationen

Database Migrations

Principles

• Every schema change is a migration file in the repo's tool. Never change production by hand.
• In production, migrations only move forward: undoing one is a new migration. Down steps are for development and staging.
• Schema changes and data changes are separate migrations.
• A migration that ran anywhere shared is never edited; add a new one.
• Test against production-sized data: what is instant on 100 rows can lock 10 million.

Model the data

• Name each new table's or field's owner, life cycle and access rules.
• The tightest correct type; constraints (unique, foreign key, check) for rules that must always hold.
• Data shared by several customers carries its tenant key, in its unique constraints and indexes.
• Money as integer minor units or decimal, timestamps in UTC.

Safe on a live database

Old and new code run side by side during a deploy.

• New columns are nullable or have a default. Adding NOT NULL to existing data: add nullable, backfill, then add the constraint.
• Indexes on existing large tables are built without blocking writes (CREATE INDEX CONCURRENTLY in Postgres, online DDL elsewhere). These usually cannot run inside a transaction, so give them their own migration and plan for a half-applied state.
• Rename or retype in stages (expand, migrate, contract):
  1. Expand: add the new column or table; deploy code that writes both.
  2. Backfill the existing rows.
  3. Migrate: deploy code that reads the new one and still writes both; verify the data matches.
  4. Contract: deploy code that uses only the new one; drop the old one in a later migration.
• Dropping a column: remove every use from the code, deploy, then drop it in the next release.
• Backfill large tables in batches (1,000–10,000 rows), committing each batch, skipping locked rows, resumable from where it stopped, with progress output and a pause when the database is busy. Never one UPDATE over the whole table.
• Set a lock timeout so a blocked migration fails instead of stalling traffic.

Reversible and recoverable

• Each migration has a down step or a note that it cannot be reversed, and the rollback plan is written down.
• Destructive steps (DROP, DELETE, lossy type changes) can only be undone from a backup. Before one runs on real data, confirm a recent, restorable backup and get explicit approval. Never run one on shared or production data on your own.
• Run up, down and up again locally on realistic data.

Indexes and queries

• Index for the real queries: equality columns first, then the range or sort column. Confirm with EXPLAIN on realistic data; an index that does not change the plan goes.
• Every index slows writes. Avoid N+1 queries; paginate large sets by key, not deep offsets.
• Prevent read-modify-write races with conditional updates, SELECT ... FOR UPDATE or the right isolation level; keep transactions short, with no network calls inside.

Anti-patterns

Before you finish

• Safe while old and new code run together.
• Large-table operations neither lock nor run in one huge transaction.
• Up and down ran locally; irreversible steps marked and approved.
• Indexes checked against the real queries.
• Models, schema files and seeds updated with the migration.