How to Run Database Migrations in Production Safely

# How to Run Database Migrations in Production Safely

Application rollbacks are easy — you redeploy the previous version. Database migrations are not, because data has state and history. A careless migration can lock a table for minutes, break running code mid-deploy, or destroy data you can't get back. This guide covers doing it safely.

Why migrations are uniquely dangerous

Three properties make database changes harder than code changes:

1. 1They're stateful — you can't just "redeploy" data back into existence.
2. 2They run alongside live code — during a rolling deploy, old and new app versions hit the same schema simultaneously.
3. 3Some operations lock — certain DDL statements block reads or writes on large tables, causing an effective outage.

The golden rule: backward compatibility

During any zero-downtime deploy, old and new code coexist. Therefore every migration must be compatible with both the currently-running code and the new code. This rules out doing a rename or a drop in a single step. The solution is the expand/contract (also called parallel change) pattern.

Example: renaming a column safely

You want to rename users.name to users.full_name. The unsafe way is ALTER TABLE users RENAME COLUMN name TO full_name — the instant it runs, old code reading name breaks. Do this instead:

Phase 1 — Expand (deploy 1):

ALTER TABLE users ADD COLUMN full_name TEXT;

Deploy code that writes to both columns and reads from full_name with a fallback to name.

Phase 2 — Backfill:

UPDATE users SET full_name = name WHERE full_name IS NULL;

Do this in batches on large tables (see below).

Phase 3 — Contract (deploy 2, after all instances run new code):

ALTER TABLE users DROP COLUMN name;

Each step is independently safe, and at no point is there a version of the code that can't function against the current schema.

Avoiding locks on large tables

The operations that cause outages are the ones that take long locks. Know your database's behavior:

Use non-blocking variants where available:

-- Build the index without blocking writes
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- Add NOT NULL safely: validate as a separate, non-blocking step
ALTER TABLE users ADD CONSTRAINT users_email_nn
 CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_nn;

Backfilling in batches

A single UPDATE across millions of rows holds a long transaction and can bloat your database. Batch it.

-- Repeat until zero rows affected
UPDATE users SET full_name = name
WHERE id IN (
 SELECT id FROM users WHERE full_name IS NULL LIMIT 5000
);

Run batches with a short pause between them so you don't saturate I/O or block other queries.

When and how to run migrations during deploy

There are two main strategies for *when* migrations execute:

• Pre-deploy (release phase) — run the migration before new code rolls out. Works well for additive (expand) migrations that the old code tolerates.
• Separate migration step — run migrations as a distinct, observable job rather than inside app startup.

Avoid running migrations from inside multiple app instances on boot — you'll get race conditions where several pods try to migrate at once. Use a single migration runner (most frameworks lock a migrations table to prevent concurrent runs, but a dedicated job is cleaner).

# A one-shot migration job, separate from the long-running app
npx sequelize-cli db:migrate

Always have a rollback plan

For every migration, know how you'd undo it. Additive changes (new nullable column) are trivially reversible. Destructive ones (drop column) are not — which is exactly why the contract step comes *after* you're confident the new code is stable. Take a backup before any irreversible migration.

Migrations on PandaStack

PandaStack provides managed PostgreSQL (14.x, 16.x), MySQL (5.7, 8.x), MongoDB, and Redis via KubeBlocks, with scheduled and manual backups — so you have a recovery point before risky changes. The platform manages the database; the *migration discipline* is yours, because only your application knows its schema semantics.

A clean pattern on PandaStack: run migrations as a cron job or a one-shot task (also a first-class app type) that connects using the auto-injected DATABASE_URL, separate from your long-running web service. That keeps migrations observable in their own logs and prevents every web pod from racing to migrate on startup. Combine that with the expand/contract pattern and the platform's rolling deploys, and you get migrations that don't take your app down. Take a manual backup before any contract (destructive) step.

Safe-migration checklist

• ✅ Every change is backward-compatible (expand/contract)
• ✅ No long-locking DDL on large tables (CONCURRENTLY, NOT VALID)
• ✅ Backfills run in batches
• ✅ Migrations run from a single runner, not every pod
• ✅ Backup taken before destructive steps
• ✅ Rollback plan known in advance

References

• [PostgreSQL: ALTER TABLE locking](https://www.postgresql.org/docs/current/sql-altertable.html)
• [Martin Fowler: Evolutionary Database Design](https://martinfowler.com/articles/evodb.html)
• [PostgreSQL: CREATE INDEX CONCURRENTLY](https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY)
• [The Twelve-Factor App: Admin processes](https://12factor.net/admin-processes)

Get managed databases with backups and run migrations safely on PandaStack — start free at [dashboard.pandastack.io](https://dashboard.pandastack.io).