Zero-Downtime Database Migrations: A Practical Guide for PostgreSQL

Database migrations are one of the few operations that can take a perfectly healthy production application offline in seconds. You deploy a one-line schema change, and suddenly your API is returning 500 errors, your connection pool is exhausted, and your users are staring at a loading spinner. The worst part is that the migration itself might only take three seconds — but the damage lasts far longer.

This guide explains why migrations break production PostgreSQL databases, how to prevent it using the Expand and Contract pattern, which tools actually help, and what your ORM is doing behind your back. Everything here is based on documented PostgreSQL behavior, open-source tooling, and real outage stories. If you deploy to production, this is required reading.

1. Why Migrations Break Production

The core problem is simple: every DDL statement in PostgreSQL acquires a lock on the affected table. When you run ALTER TABLE, CREATE INDEX, DROP COLUMN, or any other schema change, PostgreSQL must ensure that no other transaction is reading or writing the table in a way that would conflict with your change. It does this by acquiring locks — and some of those locks are exclusive, meaning nothing else can touch the table until the migration finishes.

Here is a real story that illustrates how badly this can go wrong.

This is the fundamental trap. The migration does not need to be slow to cause an outage. It just needs to wait for a lock, and while it waits, it blocks everything behind it. PostgreSQL's lock queue is a first-come, first-served line. Your ALTER TABLE gets in line behind the analytics query. Every new SELECT, INSERT, and UPDATE that arrives after your ALTER TABLE gets in line behind it. The table is effectively frozen.

Which Operations Are Dangerous?

Not all migrations are equally risky. Here is a breakdown of common operations and their lock behavior:

The pattern is clear. Any operation that requires an AccessExclusiveLock is dangerous in production because it blocks all reads and writes. Operations that rewrite the table — adding a column with a default value on PostgreSQL versions before 11, changing a column type, reorganizing tables, changing primary keys — can hold that lock for minutes or even hours on large tables.

Even "safe" operations like adding a nullable column still acquire AccessExclusiveLock briefly. If that brief moment coincides with a long-running transaction holding a conflicting lock, you get the 45-minute outage scenario described above.

The Lock Queue Problem

The most counterintuitive aspect of PostgreSQL locking is the queue effect. When your DDL statement requests a lock it cannot immediately acquire, it enters a wait queue. But here is the critical detail: while it is waiting, it blocks all subsequent lock requests that conflict with the lock it is requesting — even though it has not acquired the lock yet. So a simple ALTER TABLE that is waiting behind one slow query will block every other query on that table. The damage cascates exponentially.

This is why setting lock_timeout before running migrations is essential. If your migration cannot acquire a lock within a few seconds, it is better to fail fast and retry later than to sit in the queue blocking everything.

2. The Expand and Contract Pattern

The Expand and Contract pattern is the gold standard for zero-downtime migrations. The idea is simple: never make a breaking change in one step. Instead, break every migration into two phases.

Expand phase: Add the new schema alongside the old one. Both versions coexist. The application is updated to work with both schemas. No data is removed, no columns are dropped, nothing breaks.

Contract phase: Once all application instances are using the new schema and the old schema is no longer needed, remove the old schema. Clean up the leftover columns, tables, or constraints.

Here is what the full lifecycle looks like for a common scenario — renaming a column from email to email_address:

The key properties of this pattern are:

• Backwards compatible. At every step, the old version of the application can still function. If you need to roll back a deployment, the old code still works because the old schema is still there.
• Reversible. If something goes wrong during the expand phase, you simply drop the new column and you are back to where you started. No data loss.
• No downtime. The expand phase adds a nullable column (instant in PostgreSQL). The backfill runs as a background operation. The contract phase drops a column that nothing reads anymore. At no point is the table locked for more than milliseconds.

Expand and Contract for Column Type Changes

Changing a column type (for example, from INTEGER to BIGINT) is one of the most dangerous migrations because PostgreSQL must rewrite the entire table. The Expand and Contract pattern handles this with a dual-write strategy:

This is more work than a single ALTER TABLE orders ALTER COLUMN amount TYPE BIGINT, but that single command would lock your table for the entire rewrite — which could be hours for a table with millions of rows. The dual-write approach breaks it into steps that each take milliseconds of lock time.

3. Tool Comparison: pgroll vs Alembic vs Prisma

Several tools exist to help you run safe migrations. They differ significantly in philosophy, automation level, and what guardrails they provide. Here is an honest comparison of the most relevant options.

pgroll (by Xata)

pgroll is an open-source CLI tool that implements the Expand and Contract pattern automatically. It is the most opinionated and safety-focused tool in this comparison.

When you run a migration with pgroll, it does not simply execute your SQL. Instead, it:

1. Creates a new column alongside the old one
2. Sets up triggers so that writes to the old column are automatically copied to the new column (and vice versa)
3. Backfills existing rows in the background
4. Serves multiple schema versions simultaneously using PostgreSQL schemas — old application instances see the old schema, new instances see the new schema
5. Once you confirm the migration is complete, it drops the old column and removes the triggers

The key advantage of pgroll is that it handles the entire dual-schema lifecycle for you, including the triggers that keep both columns in sync during the transition. The key limitation is that it only works with PostgreSQL and requires you to define migrations in its JSON format rather than raw SQL.

Reshape

Reshape is another open-source tool that implements Expand and Contract for PostgreSQL. It was designed with coding agents in mind — the interface is intentionally simple so that automated tools can drive it.

Reshape's philosophy is similar to pgroll but with a simpler interface. It uses TOML files for migration definitions and focuses on making the expand/contract workflow as frictionless as possible.

Alembic (SQLAlchemy)

Alembic is the migration tool for the Python SQLAlchemy ecosystem. It does not implement Expand and Contract automatically — it generates migration scripts that you can customize. The advantage is full control; the disadvantage is that safety is your responsibility.

Alembic autogenerates migrations by comparing your SQLAlchemy models to the actual database schema. It catches most changes, but it does not know about safety concerns like lock durations. You must manually review every generated migration and add safety measures yourself.

pg_repack

pg_repack is not a migration tool in the traditional sense. It reorganizes tables online without holding exclusive locks for the duration of the operation. It works by creating a shadow copy of the table, replaying any changes that happen during the copy, and then performing a brief lock swap at the end.

Use pg_repack when you need to reclaim disk space after mass deletions, rebuild bloated indexes, or change the physical order of rows (CLUSTER). These operations normally require VACUUM FULL or CLUSTER, both of which hold AccessExclusiveLock for the entire duration. pg_repack brings the exclusive lock down to a brief moment at the end of the operation.

Enterprise Options: Flyway and Liquibase

For Java/enterprise environments, two tools dominate:

• Flyway is the simpler choice. It runs versioned SQL scripts in order. No magic, no abstraction — you write the SQL, Flyway tracks which scripts have been applied. Choose Flyway when you want simplicity and full control over your SQL.
• Liquibase offers rollback guarantees. Migrations are defined in XML, YAML, or JSON, and Liquibase can auto-generate rollback scripts. Choose Liquibase when you need guaranteed rollback capability and work in a team that prefers declarative migration definitions.

Comparison Table

4. Step-by-Step Safe Migration Workflow

Regardless of which tool you use, every production migration should follow this workflow. This is a practical checklist that you can print out and tape to your monitor.

Before You Migrate

During the Migration

After the Migration

5. ORM-Specific Gotchas

Most developers do not write raw SQL migrations. They use an ORM that generates the SQL for them. This is convenient, but it means the ORM is making decisions about locking, transaction boundaries, and migration safety — and those decisions are not always correct for production. Here is what each major ORM does and what to watch for.

Django

Django's migration framework is one of the more production-friendly ORMs. On PostgreSQL 11 and later, Django's AddField operation generates safe SQL for adding columns with defaults. PostgreSQL 11 introduced the ability to add a column with a default value without rewriting the table — it stores the default in the catalog and applies it lazily. Django takes advantage of this.

However, Django does not use CREATE INDEX CONCURRENTLY by default. When you add db_index=True to a field or create an index through a migration, Django generates a regular CREATE INDEX, which blocks writes on the table. You need to use AddIndex with the concurrently=True parameter (available via django.contrib.postgres) to get non-blocking index creation.

Note the atomic = False. CREATE INDEX CONCURRENTLY cannot run inside a transaction, and Django wraps migrations in transactions by default. You must explicitly disable this.

Ruby on Rails

Rails' ActiveRecord migrations have a significant gotcha: add_index generates a regular CREATE INDEX by default, which blocks writes. You must explicitly pass algorithm: :concurrently and wrap the migration in disable_ddl_transaction!.

Rails also does not set lock_timeout by default. The strong_migrations gem is widely recommended for Rails projects — it catches dangerous migrations before they run and suggests safer alternatives.

Prisma

Prisma Migrate wraps all migrations in a transaction by default. This is a problem because CREATE INDEX CONCURRENTLY cannot run inside a transaction. If Prisma detects that it needs to create an index, it will generate a regular CREATE INDEX, which blocks writes. There is no built-in way to disable the transaction wrapper for a single migration.

The workaround is to generate the migration with prisma migrate diff, manually edit the generated SQL file to use CONCURRENTLY and remove the transaction wrapper, and then apply it with prisma db execute. This is not ideal, but it is the current state of Prisma's migration tooling for PostgreSQL.

SQLAlchemy / Alembic

Alembic gives you the most control of any ORM migration tool, but that means you are responsible for safety. By default, Alembic generates migrations that run in a transaction. For CREATE INDEX CONCURRENTLY, you need to explicitly configure the migration to run outside a transaction.

Alembic's postgresql_concurrently=True parameter generates the correct SQL. The gotcha is that you must also ensure the migration is not wrapped in a transaction, which requires configuring the migration context. Alembic's autogenerate will not add postgresql_concurrently for you — you must add it manually after reviewing the generated migration.

Summary of ORM Gotchas

The common thread across all ORMs is the same: review the generated SQL before running it in production. Every ORM makes trade-offs between convenience and safety. The ORM does not know the size of your tables, the load on your database, or whether a 30-second lock will take down your application. You do. Act on that knowledge.