How do you change a database schema without downtime?

A database migration becomes dangerous when one deployment assumes the old schema has already disappeared. During a rolling release, old and new application instances can run at the same time. A queued job may also wake up with code from the previous version. The schema has to support both until the rollout and rollback window are over.

For a breaking change, use an expand-and-contract sequence: add the new structure first, move traffic in observable steps, and remove the old structure in a later release.

Make the first change additive

Add the new column or table without renaming or deleting the old one. Keep the new column nullable while existing rows are empty. If it needs an index, understand the database's locking behavior before running the migration.

PostgreSQL documents that many ALTER TABLE forms acquire an ACCESS EXCLUSIVE lock. It also provides CREATE INDEX CONCURRENTLY to build an index without blocking writes, with extra scans and caveats. Other engines behave differently, so this part is vendor-specific.

An illustrative migration

Assume an orders table stores total_cents as a 32-bit integer. The application needs a 64-bit total_minor_units column before larger values can be accepted. The names and numbers here are illustrative.

1. Add nullable total_minor_units bigint. Do not alter or drop total_cents.
2. Deploy code that writes both columns in the same transaction. Reads still use total_cents.
3. Backfill old rows in bounded batches. Record the last processed key so the job can resume.
4. Compare both columns. Require zero nulls and zero mismatches before changing the read path.
5. Deploy reads from total_minor_units, while continuing to write both columns.
6. After the rollback window, stop writing total_cents. Remove it only after old workers, reports, and exports have been checked.

If the new read path misbehaves at step five, rollback still works because the old column remained current. Once dual writes stop, that rollback becomes unsafe. Mark that boundary in the release plan.

Treat the backfill as production traffic

A correct backfill can still hurt the system by filling the write-ahead log, increasing replication lag, holding row locks, or evicting useful pages from cache. Use small batches, pause between them when needed, and watch database load and replica delay. The right batch size comes from the live system, not from how quickly the script finishes on a laptop.

Make the job idempotent. Re-running a batch should produce the same value, and rows changed by normal traffic should not be overwritten with stale data. A common approach is to update only rows where the new column is still null, then let the application's dual write own later changes.

Verify behavior before enforcing structure

Check row counts, null counts, mismatches, application errors, and query latency. Sample records across old and recent data. If both columns represent the same value, compare them directly in a query and keep the result visible during rollout.

Add the final constraint only after the data passes. PostgreSQL can add supported constraints as NOT VALID and validate them separately, which can reduce the disruptive part of the change. Confirm the exact constraint type and lock behavior for the database version in use.

Contract after compatibility expires

Cleanup is a separate production change. Search application code, background workers, dashboards, data exports, and ad hoc jobs for the old field. Confirm that no old application version can return. Then drop the compatibility code and, later, the old column.

The migration is ready when each step has an observable success condition and a rollback that still has valid data behind it. If a step removes that option, it should happen late and on purpose.