PostgreSQL migrations with zero downtime

← Back to the section

Changing a database schema without taking production down is a discipline of its own. Let's look at why plain ALTER TABLE statements are dangerous under live traffic, and how to roll out changes correctly.

Why ALTER TABLE blocks everything

Imagine you add a column to a table with tens of millions of rows. You write ALTER TABLE orders ADD COLUMN priority integer — the change itself takes milliseconds, and production freezes for several minutes.

The reason: for most schema changes PostgreSQL takes an ACCESS EXCLUSIVE lock — the strictest one of all, it lets no one in: not SELECT, not INSERT, not UPDATE. Worse still, it queues behind all current queries. If a long report is in flight when the migration starts, the ALTER TABLE waits for it, and a queue of all new queries piles up behind it.

Hence the first rule: every migration starts with SET LOCAL lock_timeout = '3s'. If the lock can't be acquired within 3 seconds, the migration fails with an error instead of hanging production.

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN priority integer;
COMMIT;

Breaking change and N-1 compatibility

Not all schema changes are equally dangerous.

Safe changes (no table rewrite):

• ADD COLUMN ... NULL — a column without the NOT NULL constraint
• ADD COLUMN ... NOT NULL DEFAULT 'x' (PostgreSQL 11+, a constant value)
• CREATE TABLE
• ADD CONSTRAINT ... NOT VALID

Dangerous changes (require a special approach):

• DROP COLUMN, RENAME COLUMN
• Changing a column's type (ALTER TYPE)
• ADD COLUMN NOT NULL without a default value on a large table
• Removing a value from an enum type

Dangerous changes are also called a breaking change — they can break code that is already running. The problem is not only the lock, but the timing of the deploy: first the migration is applied, then the new version of the application starts. Between them there is a window where the old code runs against the new schema. If the schema changed incompatibly, the old code crashes.

This rule is called N-1 compatibility: a migration must be compatible with the previous version of the code.

Expand-Contract: one change across several releases

To make a dangerous change safely, you split it across several releases. This pattern is called Expand-Contract (expand — migrate — contract).

Each release can be rolled back independently. If something goes wrong in release 2, you can return to release 1 without losing data.

The mechanics show up in a tiny program: a table row is a set of "column to value" pairs.

live example

import java.util.LinkedHashMap;
import java.util.Map;

public class RenameDemo {

    static Map<String, Long> schema(String... columns) {
        Map<String, Long> row = new LinkedHashMap<>();
        for (String column : columns) {
            row.put(column, 1200L);
        }
        return row;
    }

    static String read(Map<String, Long> row, String column) {
        Long value = row.get(column);
        return value == null ? "crashed: no column " + column : "read " + value;
    }

    public static void main(String[] args) {
        Map<String, Long> renamed = schema("total_amount");
        Map<String, Long> expanded = schema("amount", "total_amount");

        System.out.println("RENAME in one release, old code: " + read(renamed, "amount"));
        System.out.println("releases 1-3, old code:          " + read(expanded, "amount"));
        System.out.println("releases 1-3, new code:          " + read(expanded, "total_amount"));
        System.out.println("release 4, new code:             " + read(renamed, "total_amount"));
    }
}

The first line is a plain RENAME: the schema is new, and the old code looks for a column that is gone. While both columns live side by side, both versions work; the old one goes away once no old code is left.

How to add a NOT NULL column

PostgreSQL 11 and newer add a column with a constant default instantly — the value is stored "virtually" in the metadata instead of being written into every row:

-- Instant even on 100M rows
ALTER TABLE orders ADD COLUMN priority integer NOT NULL DEFAULT 0;

But a dynamic default (NOW(), gen_random_uuid()) can't be stored virtually, and PostgreSQL goes off to rewrite every row. Here you need expand-contract:

1. Add the column without NOT NULL
2. Fill existing rows in batches (see the section on backfill)
3. Set the constraint the safe way:

-- Step 3a: add a CHECK constraint without validating old rows
ALTER TABLE orders ADD CONSTRAINT ck_orders_priority_not_null
    CHECK (priority IS NOT NULL) NOT VALID;

-- Step 3b: validate existing rows (does not block writes)
ALTER TABLE orders VALIDATE CONSTRAINT ck_orders_priority_not_null;

-- Step 3c: SET NOT NULL is cheap — PostgreSQL 12+ trusts the validated CHECK
ALTER TABLE orders ALTER COLUMN priority SET NOT NULL;

-- Step 3d: drop the temporary CHECK
ALTER TABLE orders DROP CONSTRAINT ck_orders_priority_not_null;

CHECK NOT VALID executes instantly. VALIDATE takes a gentle SHARE UPDATE EXCLUSIVE lock, which does not interfere with normal queries.

How to rename a column

RENAME COLUMN can't be done in a single release: the old code breaks at once, because it references a column that no longer exists.

The safe sequence:

1. Add the new column (ADD COLUMN new_name <type>)
2. Code starts writing to both columns
3. Move existing data in batches
4. Code starts reading from the new column
5. Code stops writing to the old one
6. Drop the old column (DROP COLUMN old_name)

For read-only scenarios there is a simpler option — put a view where the table used to be. The old code queries orders and nobody will rename that, so the name has to keep working: the table itself moves aside, and a view with the old columns takes its place:

ALTER TABLE orders RENAME TO orders_tbl;
ALTER TABLE orders_tbl RENAME COLUMN old_name TO new_name;
CREATE VIEW orders AS SELECT *, new_name AS old_name FROM orders_tbl;

The old code keeps reading orders and sees the familiar name, the new code works against orders_tbl. Once no old code is left, the view is dropped and the table takes its name back. Writing through such a view takes extra rules, so the trick is for reads.

How to change a column's type

ALTER COLUMN ... SET DATA TYPE with a cast rewrites the whole table — the same as ALTER TABLE on millions of rows, with ACCESS EXCLUSIVE held for a long time.

Exceptions that work instantly (without a rewrite):

• varchar → text (widening without a cast)
• varchar(50) → varchar(100) (widening only)

For everything else (for example, integer → bigint) you need expand-contract: add a shadow column of the new type, fill it with data, switch the code over.

How to add a foreign key

A plain ADD FOREIGN KEY locks both tables and validates all existing rows. On large tables, that's slow.

The safe way is two-step:

-- Step 1: create the FK without validating existing rows (instant)
ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_order_id
  FOREIGN KEY (order_id) REFERENCES orders(id)
  NOT VALID;

-- Step 2: validate existing rows (does not block writes)
ALTER TABLE order_items VALIDATE CONSTRAINT fk_order_items_order_id;

NOT VALID means: new rows will be validated immediately, old ones at VALIDATE time.

How to add an index

CREATE INDEX without extra keywords takes a SHARE lock, which blocks INSERT/UPDATE/DELETE. On a large table, that's several minutes with no writes.

The solution is CREATE INDEX CONCURRENTLY. It builds the index in several passes without locking:

CREATE INDEX CONCURRENTLY ix_orders_status ON orders (status);

DROP INDEX also takes a hard lock — use DROP INDEX CONCURRENTLY.

If CREATE INDEX CONCURRENTLY was interrupted, it leaves an INVALID index behind — drop it and create it again:

DROP INDEX CONCURRENTLY IF EXISTS ix_orders_status;
CREATE INDEX CONCURRENTLY ix_orders_status ON orders (status);

An important detail for migration tools (Liquibase, Flyway): CONCURRENTLY can't run inside a transaction. Changesets with indexes must have runInTransaction="false":

<changeSet id="20260507-add-status-index" runInTransaction="false">
    <sql>CREATE INDEX CONCURRENTLY ix_orders_status ON orders (status);</sql>
    <rollback>DROP INDEX CONCURRENTLY IF EXISTS ix_orders_status;</rollback>
</changeSet>

How to remove a value from an enum

PostgreSQL has no REMOVE VALUE FROM ENUM command: natively a value can't be removed.

The only way: create a new type without the unwanted value and switch the column to it:

1. CREATE TYPE order_status_v2 AS ENUM ('NEW', 'PAID', 'SHIPPED') (without the value being removed)
2. ADD COLUMN status_v2 order_status_v2 NULL
3. Move data in batches: UPDATE orders SET status_v2 = status::text::order_status_v2
4. Switch the code over to reading from and writing to status_v2
5. DROP COLUMN status, RENAME COLUMN status_v2 TO status, DROP TYPE order_status

Adding a value (ADD VALUE) is the opposite — instant, the type is not rewritten. Since PostgreSQL 12 it may run inside a transaction block, but the new value cannot be used before that transaction commits, so it belongs in its own changeset.

Moving data in batches

A large UPDATE in a single transaction is a bad idea: it holds locks the whole time, accumulates WAL and interferes with autovacuum.

The right approach: small chunks with a commit between them:

DO $$
DECLARE rows_updated integer := 1;
BEGIN
    WHILE rows_updated > 0 LOOP
        UPDATE orders SET priority = 0
        WHERE id IN (
            SELECT id FROM orders
            WHERE priority IS NULL
            LIMIT 10000
        );
        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        COMMIT;
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

COMMIT inside a DO block only works when the block is not already inside a transaction: that changeset needs runInTransaction="false" too.

For very large tables an SQL batch inside the migration still occupies a connection and holds up the deploy. A more reliable option is a background job in the application code: it runs independently of the deploy and can be stopped and restarted. Use FOR UPDATE SKIP LOCKED to avoid conflicting with user queries:

@Component
public class BackfillPriorityJob {

    private final JdbcTemplate jdbc;

    public BackfillPriorityJob(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    @Scheduled(cron = "0 * * * * *")
    public void backfillPriority() {
        int updated;
        do {
            updated = jdbc.update("""
                UPDATE orders SET priority = 0
                WHERE id IN (
                    SELECT id FROM orders
                    WHERE priority IS NULL
                    LIMIT 10000
                    FOR UPDATE SKIP LOCKED
                )
                """);
        } while (updated == 10000);
    }
}

Rolling back migrations barely works

The <rollback> section in Liquibase or Flyway creates an illusion of safety. In practice, rolling back a migration on a production database is nearly impossible:

• If a column was dropped, the data is lost
• If data integrity was violated, a rollback won't restore the state
• A single migration in an expand-contract chain can't be rolled back without breaking the whole chain

The real options when there's a problem:

• Forward-fix — write a new migration that corrects the situation
• Restore from a backup — if data was lost

This is exactly why expand-contract matters so much: each step is safe on its own and doesn't break the previous state.

squawk — a linter for migrations

squawk is a static analysis tool for SQL migrations. It catches dangerous patterns before they reach production:

• ADD COLUMN with DEFAULT on PostgreSQL below 11
• CREATE INDEX without CONCURRENTLY
• ADD FOREIGN KEY without NOT VALID
• ALTER TYPE with a table rewrite
• column and table renames

squawk db/changelog/v0042.sql

Add squawk to a pre-commit hook and to CI — dangerous migrations then don't pass review unnoticed.

In short

• ALTER TABLE takes ACCESS EXCLUSIVE and queues behind the queries already running — on a large table that is minutes with no service. Every migration therefore starts with SET LOCAL lock_timeout = '3s': better to fail fast than to freeze production.
• N-1 compatibility: between the migration and the new version, the old code runs against the fresh schema — the migration has to survive that.
• Expand-Contract splits a dangerous change over 3–4 releases: new structure beside the old, data move, read switch, removal.
• NOT NULL on an existing column — CHECK ... NOT VALID + VALIDATE + SET NOT NULL; a foreign key the same way; indexes — always CONCURRENTLY, outside a transaction (runInTransaction="false").
• An enum value cannot be removed natively — new type and shadow column; a large UPDATE goes in batches of 10 000 rows with SKIP LOCKED, more reliably as a background job.
• Rolling back a migration in production does not work: dropped data does not come back. Design for a forward-fix, keep a backup.

What to read next

• PostgreSQL locks — the lock levels and why ACCESS EXCLUSIVE lets no one in.
• Index types — what CREATE INDEX CONCURRENTLY builds.
• Enum, boolean and enumerations — why a value is easy to add and impossible to drop.
• VACUUM and bloat — what a batched data move leaves behind and who cleans it.