Schema Migrations

Your schema will change. A column gets added, a type gets widened, a table gets split. The question is whether those changes happen as a reviewed, repeatable process, or as someone pasting SQL into a production console at 6 p.m. on a Friday.

A migration is a small, ordered, versioned script that moves the schema from one state to the next. Run the same migrations in the same order against any database and you get the same schema.

What a migration system does#

 migrations/
   V001__create_shop_schema.sql
   V002__add_loyalty_points.sql
   V003__index_orders_customer.sql

 database table: schema_migrations
   version | applied_at
   001     | 2024-05-01 10:02
   002     | 2024-05-12 09:41

A migration tool (Flyway, Liquibase, Alembic, Django and Rails migrations, Prisma Migrate, golang-migrate, sqitch) does three jobs: it keeps a table of which versions have run, it applies the missing ones in order, and it refuses to run out of order or twice.

Whatever tool you choose, a few rules hold.

Never edit a migration that has already been applied anywhere shared. Other databases ran the old version. If it was wrong, write a new migration that corrects it.

One logical change per migration. Small ones are reviewable and easier to reason about when one fails.

Commit migrations alongside the code that needs them. The application version and the schema version must agree.

Test them on a copy of production data. A migration that takes 40 ms on your laptop can take 40 minutes on 200 million rows.

Prefer rolling forward over rolling back. "Down" migrations are rarely tested and cannot restore dropped data. If a change goes wrong, ship a fix, and keep backups for the real disasters.

What makes a migration dangerous#

On a quiet development database every ALTER TABLE looks the same. On a busy production table two things decide whether it is safe: how long it holds a lock, and who it blocks while waiting for it.

Most ALTER TABLE forms need an ACCESS EXCLUSIVE lock, which blocks every read and write on the table. The lock itself is not the problem if the change is instant. The problem is a slow change (a table rewrite), or a long queue.

The lock queue#

 1. A long-running report holds a lock on orders.
 2. Your migration requests ACCESS EXCLUSIVE, so it waits for the report.
 3. Every new query on orders queues up behind your migration.
 4. The application stalls, even though nothing has "run" yet.

The defense is a lock timeout, so your migration gives up quickly instead of taking the site down while it waits:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN coupon_code TEXT;

If it cannot get the lock in three seconds it fails, you retry in a quieter moment, and nobody noticed.

Safe and unsafe operations in PostgreSQL#

NOT VALID, then VALIDATE#

Adding a constraint normally checks every existing row while holding a lock that blocks writes. Split it into two steps:

ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_product
  FOREIGN KEY (product_id) REFERENCES products (product_id) NOT VALID;

ALTER TABLE order_items VALIDATE CONSTRAINT fk_order_items_product;

The first statement is instant and enforces the rule for all new rows. The second scans existing rows, but under a much lighter lock that lets reads and writes continue.

The same trick makes NOT NULL safe on a big table:

ALTER TABLE customers ADD CONSTRAINT customers_phone_nn CHECK (phone IS NOT NULL) NOT VALID;
ALTER TABLE customers VALIDATE CONSTRAINT customers_phone_nn;
ALTER TABLE customers ALTER COLUMN phone SET NOT NULL;   -- instant: uses the validated check (PostgreSQL 12+)
ALTER TABLE customers DROP CONSTRAINT customers_phone_nn;

On the course data the VALIDATE step fails, because three customers have no phone. That is the feature, not a bug: validation tells you which rows break the rule before the rule goes live. Fix the data, then validate again.

CONCURRENTLY#

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

It takes longer and needs two table passes, but does not block writes. It cannot run inside a transaction block, which matters because many migration tools wrap each file in a transaction. Configure the tool to run that migration without one. If a concurrent build fails, it leaves an invalid index behind. Drop it and retry.

Expand and contract#

Renaming a column in one step breaks the application. While you deploy, old versions of the code are still running and still reference the old name. The fix is to change the schema in compatible stages. This is the expand and contract pattern, shown here renaming products.name to title.

 1. EXPAND    add products.title (nullable); deploy code that writes BOTH columns
 2. BACKFILL  copy name into title for existing rows, in batches
 3. MIGRATE   deploy code that reads title
 4. TIGHTEN   add NOT NULL on title (via the validated-check recipe)
 5. CONTRACT  deploy code that stops writing name; then DROP COLUMN name

At every step, both the old and the new version of the code work against the schema. No step needs downtime, and you can pause or roll back between any two.

Backfilling in batches#

One UPDATE over a hundred-million-row table runs for a long time, holds locks, writes enormous WAL and bloats the table. Work in chunks and let other traffic through:

UPDATE products
SET title = name
WHERE product_id IN (
    SELECT product_id FROM products
    WHERE title IS NULL
    ORDER BY product_id
    LIMIT 5000
);
-- repeat until it reports UPDATE 0, pausing briefly between rounds

Run it from a script or a procedure with a commit per batch.

Changing a column type#

ALTER COLUMN ... TYPE often rewrites the table under an exclusive lock. For a large table, use the same staged approach: add a new column with the new type, backfill, keep the two in sync (in code or with a trigger), switch the reads, then drop the old column. For example, widening INTEGER to BIGINT on a table nearing two billion rows.

Data migrations#

Sometimes a migration moves data, not structure. Keep data changes separate from schema changes (different files), make them idempotent where you can (WHERE new_col IS NULL), and remember they run in the same ordered history, so they will run again on every new environment. Seed data for development belongs in a seed script, not in the migration history.

Common mistakes#

Editing an applied migration. Environments drift apart.

CREATE INDEX without CONCURRENTLY on a busy table. Writes stall for the duration.

Renaming or dropping a column while old code is still deployed. Errors start the moment the migration finishes.

No lock_timeout. A single long query turns your migration into an outage.

Testing only on an empty database. The time and locking behavior is invisible.

Combining a schema change and a huge data backfill in one transaction. Long locks, a giant rollback if it fails.

Practice#

1. Write three migration files for the course schema: one that adds loyalty_points INTEGER NOT NULL DEFAULT 0 to customers, one that adds an index with CONCURRENTLY, and one that adds a foreign key using NOT VALID followed by VALIDATE.
2. In two psql sessions, open a transaction in session A that updates a row in products and leave it open. In session B run ALTER TABLE products ADD COLUMN note TEXT; and watch it wait. Check pg_stat_activity. Repeat with SET lock_timeout = '2s' in B.
3. Plan the expand and contract steps for renaming customers.city to town. Write the SQL for each step.
4. Write the batch backfill statement, run it repeatedly in a loop until it affects zero rows, and count how many rounds it takes for a 100,000-row scratch table.
5. Explain why CREATE INDEX CONCURRENTLY fails inside BEGIN ... COMMIT, and how your migration tool would be configured to run it.