Migrations Without Downtime

Schema changes are the riskiest database operation. Most can be done with no downtime if you know which operations lock tables.

Migrations as code

PandaDev uses Alembic through Flask-Migrate:

flask db migrate -m "add super_admin to users"   # generate from model changes
# REVIEW the generated file: autogenerate misses things
flask db upgrade                                  # apply
flask db downgrade                                # roll back one step

Every migration is committed to Git and runs in the same order everywhere. Never edit a production schema by hand.

Operations and their locks

Always set a lock timeout

A migration waiting for a lock blocks every query queued behind it. Fail fast instead:

SET lock_timeout = '5s';

Adding NOT NULL safely

ALTER TABLE users ADD CONSTRAINT users_country_nn CHECK (country IS NOT NULL) NOT VALID;
-- backfill in batches, then:
ALTER TABLE users VALIDATE CONSTRAINT users_country_nn;   -- no write lock
ALTER TABLE users ALTER COLUMN country SET NOT NULL;      -- PG 12+ uses the valid constraint, no scan
ALTER TABLE users DROP CONSTRAINT users_country_nn;

Expand and contract

To rename fullname to display_name without breaking the running app:

1. Expand: add display_name (nullable). Deploy code that writes both columns.
2. Backfill: copy old values in batches of a few thousand rows.
3. Switch reads: deploy code that reads display_name.
4. Contract: once nothing uses fullname, drop it in a later release.

Each step is backward compatible with the code running beside it, so deploys and rollbacks are always safe.

Batch backfills

UPDATE users SET display_name = fullname
WHERE id IN (SELECT id FROM users WHERE display_name IS NULL LIMIT 5000);

Repeat until zero rows are updated, pausing briefly between batches. A single huge UPDATE holds locks and creates massive bloat.

Before every production migration

• [ ] Tested on a recent copy of production data
• [ ] Timed: you know how long it runs
• [ ] lock_timeout set
• [ ] Backup or PITR point confirmed
• [ ] Rollback plan written

Check yourself

• Why is a lock timeout essential?
• Describe the four steps of expand and contract.
• Why backfill in batches?