Zero-downtime database migrations: expand and contract

A zero-downtime database migration changes your schema, or moves your data to a new server, while the application keeps serving traffic. It rests on two habits: split every breaking change into backward-compatible steps (expand, migrate, contract) so old and new code can run side by side, and never let a migration hold a heavy lock for long. In PostgreSQL, that means setting lock_timeout, building indexes concurrently, adding constraints as NOT VALID and backfilling in small batches.

Why migrations cause downtime

Most schema changes are quick to execute. The downtime comes from locks and from code that doesn't match the schema:

• Heavy locks. Most forms of ALTER TABLE take an ACCESS EXCLUSIVE lock, which conflicts with every other lock mode. PostgreSQL's documentation notes that it is the only lock mode that blocks a plain SELECT.
  
  
• The lock queue. Postgres grants conflicting lock requests in order of arrival. If your ALTER TABLE is waiting behind a three-minute report query, every query that arrives after it waits too. A statement that needs its lock for a millisecond can freeze the table for three minutes.
  
  
• Table rewrites and full scans. Some changes rewrite the whole table and its indexes, or scan every row to validate a constraint, while holding that lock.
  
  
• Code and schema out of step. During a rolling deploy, old and new application versions run together. Rename or drop a column that old code still uses, and those requests fail.
  
  
• Huge single-transaction updates. Updating 50 lakh rows in one statement holds row locks for its whole duration, bloats the table and floods replicas with changes.
  
  

Expand and contract

Expand and contract, sometimes called parallel change, turns a breaking change into a sequence of safe ones. Take renaming students.phone to mobile_number on a live platform:

It's more deploys than a single RENAME COLUMN, but at every step both the running code and the one before it work against the schema, which is what makes rolling deploys and rollbacks safe. The same pattern handles splitting a table, changing a column's type, and moving a primary key from integer to bigint, where a trigger keeps the new column in sync during the backfill. Our guide to zero-downtime deployment covers the application side.

Operations that lock Postgres tables, and safer alternatives

Two of these deserve detail. CREATE INDEX CONCURRENTLY scans the table twice and waits for existing transactions, so it's slower, but reads and writes carry on. It can't run inside a transaction block, and if it fails it leaves an invalid index behind, which you drop before retrying. PostgreSQL 18 also lets you add a NOT NULL constraint as NOT VALID directly, but the CHECK route above works on every supported version. Our guide to database indexing covers choosing the index itself.

Whatever the operation, run it with a short lock timeout, so a blocked migration gives up quickly instead of queueing every query behind it, and retry a few seconds later:

SET lock_timeout = '3s';
SET statement_timeout = '15min';

ALTER TABLE test_attempts
  ADD CONSTRAINT attempts_student_fk
  FOREIGN KEY (student_id) REFERENCES students (id) NOT VALID;

-- later, as a separate step; this takes a weaker lock and doesn't block writes
ALTER TABLE test_attempts VALIDATE CONSTRAINT attempts_student_fk;

CREATE INDEX CONCURRENTLY attempts_student_submitted_idx
  ON test_attempts (student_id, submitted_at);

The PostgreSQL documentation advises against setting lock_timeout or statement_timeout in postgresql.conf, because that would affect every session; set them for the migration session only.

Backfilling in batches

A backfill copies or computes data for existing rows. Done as one giant UPDATE, it holds row locks for its whole run, keeps a long transaction open that stops vacuum cleaning up, and sends a burst of changes to replicas. Do it in small, committed batches instead, walking the primary key:

last_id, batch = 0, 5000
while True:
    ids = list(Student.objects.filter(id__gt=last_id, mobile_number__isnull=True)
               .order_by("id").values_list("id", flat=True)[:batch])
    if not ids:
        break
    Student.objects.filter(id__in=ids).update(mobile_number=F("phone"))
    last_id = ids[-1]
    time.sleep(0.2)    # leave room for real traffic and for replicas

This is safe to stop and restart, because it only touches rows still missing a value, and each batch commits on its own. Run it as a background job rather than inside a migration. Watch replica lag while it runs, using replay_lag in pg_stat_replication on the primary, and slow down if lag grows. An illustrative sizing: 1.2 crore rows in batches of 5,000 is 2,400 batches; at about half a second per batch including the pause, that's around 20 minutes, with no moment where students notice.

Django migrations, safely

Django is convenient, but its defaults need care on a busy database:

1. Read the SQL first. python manage.py sqlmigrate app 0042 prints what a migration will run, so you can spot a rewrite or a blocking index before it reaches production.
   
   
2. Keep migrations small. On PostgreSQL, Django runs each migration inside one transaction by default, so every lock taken is held until the whole migration commits. One risky operation per migration keeps lock times short.
   
   
3. Use the Postgres operations. AddIndexConcurrently and RemoveIndexConcurrently from django.contrib.postgres build and drop indexes without blocking writes; because CONCURRENTLY can't run in a transaction, the migration needs atomic = False. AddConstraintNotValid and ValidateConstraint split a check constraint into its two steps.
   
   
4. Remove fields in two releases. First deploy code that no longer uses the field, removing it from Django's model state with SeparateDatabaseAndState so the column stays; drop the column in a later release.
   
   
5. Set timeouts for migrations only. Point migrate at a settings variant whose database OPTIONS include "options": "-c lock_timeout=3s", so web requests keep their normal settings.
   
   
6. Order deploys correctly. Run expanding migrations before deploying the code that needs them, and contracting migrations only after every server runs code that no longer needs the old shape.
   
   

Moving to a new database server

For a major-version upgrade or a move to a managed service, logical replication lets you copy the data while the old server keeps serving, and then cut over in seconds:

1. Create the schema on the new server with pg_dump --schema-only; logical replication copies data, not DDL.
   
   
2. Make sure every replicated table has a primary key or another replica identity; without one, updates and deletes on that table fail on the old server once it's in a publication.
   
   
3. Set wal_level = logical on the old server and create a publication; create a subscription on the new one. It copies existing rows, then streams changes.
   
   
4. Freeze schema changes until the move is done, wait for the new server to catch up, and compare row counts on key tables.
   
   
5. Cut over. If PgBouncer sits in front, PAUSE waits for in-flight transactions to finish and holds new ones. Confirm the subscriber has caught up by comparing the old server's pg_current_wal_lsn() with latest_end_lsn in the new server's pg_stat_subscription, set sequences on the new server, repoint PgBouncer and RESUME. Students see a pause of seconds, not an error.
   
   
6. Keep the old server untouched for a while as your fallback.
   
   

-- old server
CREATE PUBLICATION app_pub FOR ALL TABLES;

-- new server, after loading the schema
CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=old-db dbname=appdb user=replicator'
  PUBLICATION app_pub;

-- at cutover, for each sequence, using the old server's current value plus a margin
SELECT setval('students_id_seq', 1284573 + 1000);

Know the logical replication restrictions before you start: in PostgreSQL 18, sequence values aren't replicated, which is why the cut-over sets them by hand; large objects aren't replicated at all; and materialized views must be refreshed on the new server. Our guides to database replication and PgBouncer connection pooling cover the pieces in more depth, and before any large migration, confirm that point-in-time recovery works in case you need to undo it.

Key takeaways

• Split breaking changes into expand, migrate and contract steps so old and new code always work against the schema.
  
  
• Set a short lock_timeout on every migration and retry, so a blocked ALTER can't queue up all your traffic.
  
  
• Use CREATE INDEX CONCURRENTLY, NOT VALID constraints with separate validation, and batched backfills.
  
  
• In Django, read sqlmigrate output, keep one risky operation per migration, and remove fields over two releases.
  
  
• Move servers with logical replication and a PgBouncer pause, remembering to set sequences at cut-over.
  
  

Frequently asked questions

What is zero downtime migration?

A zero-downtime migration changes a database's schema or moves its data while the application stays fully available. Instead of taking the site offline for a maintenance window, the change is split into backward-compatible steps, run with short lock timeouts and non-blocking operations, and coordinated with application deploys so that every version of the code in service works against the schema at every moment.

How to migrate database without downtime?

To move a database to a new server or major version without downtime, replicate it continuously to the new server, typically with PostgreSQL's logical replication, while the old one keeps serving. When the copy has caught up, pause writes briefly with a connection pooler such as PgBouncer, confirm the new server is current, set sequence values, switch connections and resume. Keep the old server as a fallback.

How to migrate data without downtime?

To transform or move data inside a live database, first add the new structure alongside the old, then have the application write to both. Copy existing rows in small batches that commit separately, pausing between batches and watching replica lag. Switch reads to the new structure once the backfill is verified, and remove the old structure in a later release.

Is CREATE INDEX CONCURRENTLY safe in production?

Yes, it's designed for production. Reads and writes continue while the index builds; the cost is a slower build, because Postgres scans the table twice and waits for existing transactions. It can't run inside a transaction, so Django migrations using it need atomic = False. If the build fails, it leaves an invalid index that you should drop before trying again.