How do you handle large table migrations in Liquibase without causing downtime?

Large table migrations — adding a non-nullable column to a 500 million row table, back-filling data, adding an index on a frequently queried column — are among the most challenging database operations to perform without application downtime. The raw DDL that Liquibase would generate often acquires table locks that block reads and writes for the duration.

The key principle is the expand/contract pattern (also called multi-phase migration or online schema change):

Phase 1 — Expand (backward compatible): Add the new column as nullable with no default, or create the new index concurrently. At this point, both old and new code can coexist with the schema.

<changeSet id="phase1-add-nullable-status" author="jane">
    <addColumn tableName="order">
        <column name="status_v2" type="VARCHAR(30)"/>  <!-- nullable initially -->
    </addColumn>
</changeSet>

Phase 2 — Backfill in batches (via Eventing/app code or chunked SQL): Update existing rows in batches — not in a single UPDATE that would lock the entire table.

<changeSet id="phase2-backfill-status" author="jane" runInTransaction="false">
    <sql>
        DO $$
        DECLARE batch_size INT := 10000;
        BEGIN
            LOOP
                UPDATE "order" SET status_v2 = status WHERE status_v2 IS NULL
                LIMIT batch_size;
                EXIT WHEN NOT FOUND;
                PERFORM pg_sleep(0.01); -- brief pause to avoid lock contention
            END LOOP;
        END $$;
    </sql>
</changeSet>

Phase 3 — Contract (after all code using old column is retired): Add NOT NULL constraint, drop the old column, rename if needed. By this point no active code references the old column.

For index creation on large tables in PostgreSQL, always use CREATE INDEX CONCURRENTLY in a changeSet with runInTransaction="false" to build the index without a full table lock.