Safe Schema Migrations

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Safe Schema Migrations
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

Migration files belong in version control and run consistently across environments. Break a rename or drop into expand → dual-read/write → backfill → validate constraints → contract. Some DDL takes strong locks, so inspect table size and set timeouts before production execution.

Connect it to a real scenario

To add required `summary`, first add it nullable, backfill in batches, validate a `CHECK ... NOT VALID`, then enforce NOT NULL. Decide whether rollback is safe or a forward fix is required when data transformation is irreversible.

Version-control migrations
Use expand/contract compatibility
Add lock timeouts and observability

Try the working example

sql

SET lock_timeout = '2s';
ALTER TABLE app.tutorials ADD COLUMN summary text;

-- Backfill in controlled batches from the application/job.
UPDATE app.tutorials
SET summary = title
WHERE summary IS NULL AND tutorial_id BETWEEN 1 AND 1000;

ALTER TABLE app.tutorials
  ADD CONSTRAINT tutorials_summary_present
  CHECK (summary IS NOT NULL) NOT VALID;
ALTER TABLE app.tutorials VALIDATE CONSTRAINT tutorials_summary_present;
ALTER TABLE app.tutorials ALTER COLUMN summary SET NOT NULL;

You should see

You can introduce a required column in controlled, compatible stages.

5-minute try-it

Write a multi-deploy plan to safely rename `display_name` to `full_name`.

One important caution

Do not judge migration success only by command exit status; monitor lock waits, replica lag, and application errors.

PostgreSQL — ALTER TABLE — PostgreSQL Global Development Group

Easy traps

Do not judge migration success only by command exit status; monitor lock waits, replica lag, and application errors.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Write a multi-deploy plan to safely rename `display_name` to `full_name`.

You'll know it worked when: You can introduce a required column in controlled, compatible stages.