Schema migration
A versioned script that changes your database structure, committed to git and run in order, so every environment matches.
See it
What it is
A schema migration is a small, versioned script that changes database structure: add a column, create an index, rename a table. It lives in git next to your code, runs in order, and gets recorded in a migrations table so every environment ends up at the same version. Prisma Migrate, Drizzle Kit, Rails migrations, Django migrations, Alembic, and Flyway all do the same job with different syntax.
The pattern that keeps production up is expand and contract. To rename a column: add the new one (expand), backfill it, write to both for a release, switch reads over, then drop the old one (contract). Three boring deploys instead of one exciting one. The rule underneath is that old code and new schema must be able to coexist for a while, because during a rolling deploy they will.
Gotcha: never edit a migration that already ran in production. It ran; rewriting the file just means your laptop and prod now disagree about history. Write a new migration instead. Second gotcha: some 'one-line' changes take a lock on the whole table and stall every query behind them. Know which is which. Adding a column with a constant, non-volatile default is metadata-only on modern Postgres and rewrites nothing, but a volatile default like gen_random_uuid() rewrites every row, and so does most type conversion. Validating a new constraint or building an index non-concurrently scans the whole table while holding a lock. Even the metadata-only change still needs a brief exclusive lock, which queues behind one long-running query, and then everything else queues behind the lock. Set a short lock_timeout, retry, and build indexes CONCURRENTLY.
Ask AI for it
Write a zero-downtime schema migration for this change using the expand and contract pattern, split into separate migration files: (1) add the new nullable column, then create any index with CREATE INDEX CONCURRENTLY in a migration that does not run inside a transaction, (2) backfill existing rows in batches with a sleep between batches, (3) enforce non-null without a long lock by adding CHECK (column IS NOT NULL) NOT VALID, running ALTER TABLE ... VALIDATE CONSTRAINT as a separate statement, then SET NOT NULL and dropping the temporary check, (4) drop the old column in a later release. Set a short lock_timeout at the top of each with a retry around it, avoid table rewrites and volatile defaults, and note which application deploy has to ship between each step.