Schema Migrations Without Downtime

Applications change, and so do the databases behind them. New features need new columns, old fields need renaming, tables need splitting and indexes need adding as data grows. Each change is a schema migration. Done carelessly, a migration can lock a busy table, break the running version of the application or leave data half-converted. Teams that deploy often need schema migrations that run while the application stays online. The core technique is to break risky changes into small, backward-compatible steps, often called expand and contract, combined with careful backfills, safe index builds and tested rollback plans. This guide explains how to apply these ideas in practice, with PostgreSQL examples.

Why schema migrations cause downtime

Schema changes can cause outages in a few ways:

• Locks: some operations block reads or writes on a table while they run
• Incompatibility: the new schema breaks the old application code still running during deployment
• Long-running data changes: large updates hold locks or overload the database
• Failed migrations: a change fails halfway and leaves the schema in an unexpected state

During a typical deployment, old and new versions of the application may run side by side for a while. Every migration must work with both.

Expand and contract

Martin Fowler describes parallel change, also known as expand and contract, as a way to make backward-incompatible changes safely by breaking them into three phases: expand, migrate and contract (Martin Fowler: ParallelChange). For databases this usually means:

1. Expand: add the new structure alongside the old, without removing anything
2. Migrate: update the application to write to both, backfill existing data, then switch reads to the new structure
3. Contract: once nothing uses the old structure, remove it

Each step is deployed separately, so the application keeps working at every point.

Example: renaming a column safely

Renaming a column directly breaks any running code that uses the old name. With expand and contract:

1. Add the new column, allowing nulls
2. Deploy code that writes to both old and new columns
3. Backfill the new column from the old one in batches
4. Deploy code that reads from the new column
5. Deploy code that stops writing to the old column
6. Drop the old column in a later release

It takes more steps, but none of them requires downtime or breaks the running application.

Backfill data in batches

Updating millions of rows in one statement can hold locks for a long time and strain the database. Instead, backfill in batches:

• Process a limited number of rows at a time, ordered by primary key
• Pause briefly between batches to let other work proceed
• Make the backfill restartable, so it can resume after interruption
• Monitor database load and replication lag while it runs

Build indexes without blocking writes

In PostgreSQL, a standard index build locks out writes, though not reads, on the table until it finishes. The PostgreSQL documentation explains that with the CONCURRENTLY option, the index is built without taking locks that prevent concurrent inserts, updates or deletes (PostgreSQL: CREATE INDEX). The trade-offs, also documented, are that a concurrent build performs two scans of the table, takes longer, cannot run inside a transaction block and may leave an invalid index if it fails, which must then be dropped and rebuilt.

Worked example: schema migration steps

The simplified, untested SQL below shows the expand and contract steps for replacing a free-text status column with a reference to a status table. Each block would run as a separate migration, deployed alongside the matching application change.

-- Simplified, untested example

-- Release 1 (expand): add new structure without removing anything
CREATE TABLE order_status (id smallint PRIMARY KEY, name text NOT NULL UNIQUE);
ALTER TABLE orders ADD COLUMN status_id smallint REFERENCES order_status (id);

-- Release 1 (separate migration, outside a transaction): index without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_status_id ON orders (status_id);

-- Release 2 (migrate): backfill in batches, repeated until no rows remain
UPDATE orders o
SET status_id = s.id
FROM order_status s
WHERE o.status = s.name
  AND o.status_id IS NULL
  AND o.id IN (SELECT id FROM orders WHERE status_id IS NULL ORDER BY id LIMIT 5000);

-- Release 4 (contract): only after no code reads or writes the old column
ALTER TABLE orders DROP COLUMN status;

Between these releases, the application is updated to write both columns, then to read the new one, then to stop using the old one. Before the backfill, make sure every existing status value has a matching row in the new table; otherwise unmatched rows would never be filled and the batch loop would not finish. Check for remaining nulls before moving to the contract step.

Watch for locking operations

Some schema operations take strong locks or rewrite tables, depending on the database and version. Before running a migration on a large table, check the documentation for the specific operation, test on a copy of production data and measure how long it takes. Setting a lock timeout for migration sessions helps a migration fail quickly rather than queue behind long-running queries and block other traffic.

Add constraints in stages

New constraints, such as making a column required, can fail or lock tables if existing data does not comply. Add them in stages: first make sure the application always writes valid values, then backfill and clean existing rows, then add the constraint. Some databases offer ways to add constraints without immediately checking every existing row and validate them separately later; check your database documentation for the options available in your version.

Make migrations repeatable and versioned

Use a migration tool that applies numbered or timestamped migrations in order and records which have run. Keep migrations in version control with the application code. Never edit a migration that has already run in production; add a new one instead.

Plan rollbacks

Decide in advance how to reverse each step:

• Expand steps are usually easy to reverse, because nothing was removed
• Application changes can be rolled back if the schema still supports the old version
• Contract steps, such as dropping columns, are hard to reverse, so delay them until you are confident

Take backups before significant migrations and confirm you can restore them. Our guide to Kubernetes zero downtime deployment covers how application rollouts and rollbacks fit with database changes.

Test migrations realistically

Run migrations against a recent, anonymised copy of production data to measure duration and locking. Test the application's old and new versions against the intermediate schema, so you know both work during deployment.

Coordinate with data migrations

Schema changes are different from moving data between systems, but they often happen together, for example when consolidating data from a legacy system. Our guide to ERP data migration covers planning and validation for larger data moves.

Monitor during and after

Watch database load, lock waits, error rates and application performance while migrations and backfills run. Be ready to pause a backfill if load rises, and confirm afterwards that row counts and sample values match expectations.

Frequently Asked Questions

What is a schema migration?

It is a change to a database's structure, such as adding, renaming or removing tables, columns, constraints or indexes.

How do you run schema migrations without downtime?

Break changes into backward-compatible expand, migrate and contract steps, backfill data in batches, build indexes without blocking writes and deploy each step separately.

What does CREATE INDEX CONCURRENTLY do in PostgreSQL?

It builds an index without locks that prevent inserts, updates or deletes, at the cost of a longer build, two table scans and not running inside a transaction block.

When should old columns be dropped?

Only after no running code reads or writes them, ideally a release or more after the switch, because dropping is hard to reverse.

Change your database with confidence

We plan and run schema migrations, backfills and index changes that keep your application online. See our custom app development services, or talk to us about schema migrations.

About the Author

Manav Sharma is the founder of Insyrge and a CRM solutions architect. He works across Zoho, Salesforce, HubSpot, Odoo and more, designing the CRM setups, integrations and automations that keep sales and operations teams working from the same data.