Schema Migrations Without Downtime

In production, schema changes run against tables that are in use. Some operations take strong locks; if one waits behind a long query, every other query waits behind it, and the site stalls. Always set a lock_timeout in migrations so a blocked change fails fast and can be retried.

Make big changes in small safe steps (expand and contract): add a new nullable column; backfill it in batches; add a constraint as NOT VALID (instant) and then VALIDATE CONSTRAINT (checks existing rows without blocking writes); only then make it NOT NULL. Create indexes CONCURRENTLY.

Track which migrations have run in a table such as schema_migrations, give files increasing numbers, and never edit a migration that has already run in production — write a new one instead. Tools like Flyway, Alembic or Prisma do this bookkeeping for you.

003_payment_reference.sqlSQL

CREATE TABLE IF NOT EXISTS schema_migrations (
    version    text PRIMARY KEY,
    applied_at timestamptz NOT NULL DEFAULT now()
);

-- Fail fast instead of queueing behind long transactions
SET lock_timeout = '5s';

-- Step 1 (expand): a nullable column is a quick metadata-only change
ALTER TABLE payments ADD COLUMN IF NOT EXISTS reference text;

-- Step 2: backfill in batches (repeat until 0 rows are updated)
UPDATE payments SET reference = 'LEGACY-' || id
WHERE id IN (SELECT id FROM payments WHERE reference IS NULL LIMIT 1000);

-- Step 3: add the rule without scanning, then validate without blocking writes
ALTER TABLE payments ADD CONSTRAINT payments_reference_not_null CHECK (reference IS NOT NULL) NOT VALID;
ALTER TABLE payments VALIDATE CONSTRAINT payments_reference_not_null;

-- Step 4 (contract): now SET NOT NULL can use the validated check, and the check can go
ALTER TABLE payments ALTER COLUMN reference SET NOT NULL;
ALTER TABLE payments DROP CONSTRAINT payments_reference_not_null;

INSERT INTO schema_migrations (version) VALUES ('003_payment_reference')
ON CONFLICT (version) DO NOTHING;

RESET lock_timeout;

SELECT version, applied_at::date FROM schema_migrations;
SELECT id, reference FROM payments ORDER BY id LIMIT 3;

Runs in your browser · PostgreSQL

Key points

Set lock_timeout so blocked schema changes fail fast instead of stalling the site.
Expand and contract: nullable column → backfill in batches → NOT VALID + VALIDATE → NOT NULL.
Number migrations, record them in a table, and never edit one that already ran.

Exercise

Write migration 004_students_admission_number.sql that adds a required, unique admission_number to students safely: add it nullable, give it a DEFAULT from a sequence so new students are numbered automatically, backfill existing rows with values like 'ADM-2026-0001' from the id, build a unique index concurrently, then make it NOT NULL. Record it in schema_migrations.

Show solution

Try the exercise yourself first — then compare your approach with this one.

The column starts nullable so adding it is instant, and its DEFAULT (from a sequence that starts after the existing ids) numbers new students automatically. The backfill uses lpad for zero-padded numbers, the unique index is built concurrently (outside a transaction block) so writes continue, and only then does the column become NOT NULL.

004_students_admission_number.sqlSQL

SET lock_timeout = '5s';

-- New students get the next number automatically; start after the existing ids
CREATE SEQUENCE IF NOT EXISTS admission_number_seq;
SELECT setval('admission_number_seq', (SELECT max(id) FROM students));

ALTER TABLE students ADD COLUMN IF NOT EXISTS admission_number text;
ALTER TABLE students ALTER COLUMN admission_number
    SET DEFAULT 'ADM-2026-' || lpad(nextval('admission_number_seq')::text, 4, '0');

-- Backfill existing students (in batches on a big table)
UPDATE students
SET admission_number = 'ADM-2026-' || lpad(id::text, 4, '0')
WHERE admission_number IS NULL;

CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS idx_students_admission_number
    ON students (admission_number);

ALTER TABLE students ALTER COLUMN admission_number SET NOT NULL;

INSERT INTO schema_migrations (version) VALUES ('004_students_admission_number')
ON CONFLICT (version) DO NOTHING;

RESET lock_timeout;

INSERT INTO students (full_name, form, stream, gender) VALUES ('New Student', 1, 'A', 'F')
RETURNING id, admission_number;        -- numbered automatically

SELECT id, full_name, admission_number FROM students ORDER BY id LIMIT 3;

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding