What Is a Schema Migration?

A schema migration is a versioned script that changes the structure of a database: adding a column, creating an index, changing a constraint. Migrations run in a fixed order and are recorded in a history table inside the database, so every environment, from a laptop to production, converges on the same schema. Writing one is easy. Running one against a live system without downtime or data loss is the actual skill.

How does a migration tool know which migrations have already run?

A migration tool records each applied migration in a table inside the database it manages. Flyway uses flyway_schema_history, Rails uses schema_migrations and Liquibase uses DATABASECHANGELOG. On every run, the tool compares the migration files in your repository with that table and applies only the missing ones, in version order.

Flyway also stores a checksum of each versioned migration, so it can detect a file that was edited after it ran. Its documentation advises against changing a migration once it has been applied to a permanent downstream environment. Write a new migration and roll forward instead.

That rule exists because environments that already ran the original will never run the edited copy, so they drift apart from environments that run the new version. Flyway's checksum is there to catch exactly that.

What is the difference between up and down migrations?

An up migration applies a change and a down migration reverses it. A migration with both is called reversible, because a tool can step the schema back to the previous version. Some changes cannot truly be reversed: once an up migration drops a column, no down migration can bring back the values that were in it without a backup.

The common tools handle the down direction differently:

Data changes need the most care in every tool. Django's RunPython operation only runs backwards if you give it a second function, and reversing an irreversible operation raises IrreversibleError (see the Django migrations documentation). Rails lets you raise ActiveRecord::IrreversibleMigration in a down method to block a destructive rollback on purpose, and Flyway's undo cannot reverse repeatable migrations at all.

Should you roll back or roll forward a bad migration in production?

Rolling forward is the safer default in production: fix a bad migration with a new, corrective migration instead of running its down script. A down migration that drops a column or table destroys whatever was written since the change went live, and Prisma's documentation notes that rolling back reverts the schema only, not your data or application code.

Down migrations still earn their place in development and continuous integration. Running up, then down, then up again on every new migration proves it is reversible before it reaches production, and gives you a tested escape hatch for the rare case where rolling back really is safe.

Tooling here is still moving. Prisma ORM 8 replaces migrate dev and migrate deploy with a new migration workflow; its documentation says planning, editing, applying and rolling back work today, but there is no squash command yet. Check which version your project is on before following an older guide.

What is the expand and contract pattern?

Expand and contract, also called parallel change, splits a backward-incompatible change into three phases. First expand the schema so it supports the old and new shapes at once, then migrate every reader and writer to the new shape, then contract by removing the old shape. Danilo Sato described the pattern on Martin Fowler's site in 2014.

Sato notes that most database refactorings follow it. Renaming a column is the classic case, spread over four releases:

-- Release 1: EXPAND. Add the new column. Old code ignores it.
ALTER TABLE customers ADD COLUMN email_address text;

-- Release 2: the application writes to both columns.
-- Backfill existing rows in small batches, outside the deploy.
UPDATE customers
SET    email_address = email
WHERE  email_address IS NULL
  AND  id BETWEEN 1 AND 10000;

-- Release 3: the application reads email_address only.

-- Release 4: CONTRACT. Nothing reads the old column any more.
ALTER TABLE customers DROP COLUMN email;

Renaming the column in one step would break every running copy of the old application the moment the migration commits. Spread across four releases, every intermediate state works for both the code being replaced and the code replacing it, which is what lets a rolling deploy finish without downtime. The cost is time: the old column stays until nothing reads it.

How do you change a PostgreSQL schema without downtime?

Avoid statements that hold a strong lock while they rewrite or scan a large table. In PostgreSQL, many ALTER TABLE forms take an ACCESS EXCLUSIVE lock, the only lock mode that blocks plain SELECT queries. The safe patterns add constraints as NOT VALID, build indexes concurrently and set a lock timeout so a stuck migration fails fast.

The rules below come from the PostgreSQL ALTER TABLE and CREATE INDEX documentation:

• Adding a column with no default, or with a non-volatile default, does not rewrite the table. A volatile default such as clock_timestamp() rewrites the table and its indexes.
• Changing a column type normally rewrites the table and its indexes. On a large table, add a new column and use expand and contract instead.
• Adding a check or foreign key constraint with NOT VALID skips the scan of existing rows. A later VALIDATE CONSTRAINT checks them while holding only a SHARE UPDATE EXCLUSIVE lock, which does not lock out concurrent updates.
• Creating an index with CREATE INDEX CONCURRENTLY does not block inserts, updates or deletes. It cannot run inside a transaction block, and a failed build leaves an INVALID index to drop and create again.
• Waiting for a lock has no limit by default, because lock_timeout defaults to zero. Set it for the migration session so a blocked statement gives up instead of waiting.

-- Give up after 5 seconds instead of waiting indefinitely for a lock
SET lock_timeout = '5s';

-- Add a constraint without scanning existing rows under a strong lock
ALTER TABLE orders
  ADD CONSTRAINT orders_total_non_negative CHECK (total_cents >= 0) NOT VALID;

-- Check existing rows later; concurrent writes continue
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_non_negative;

-- Build an index without blocking writes (must run outside a transaction)
CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);

Migrations stay simpler when the schema starts out sound. See how to design a Postgres schema for a marketplace, what database normalization is and Postgres performance optimization.

If you are designing a new schema rather than changing an existing one, Kozo is the Jishu Labs tool for that step. Describe your app in plain English and Kozo designs a normalized, indexed, migration-ready Postgres schema with a live ER diagram in seconds. Its Pro plan exports reversible up and down migrations; applying them safely is still the job of the practices above.