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.

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.