What is the difference between addColumn with a default value and a nullable column in Liquibase for large tables?

Adding a column to a large table is a common migration, but the choice between nullable (no default) and non-nullable (with a default value) has dramatic performance differences depending on the database engine, and Liquibase's generated SQL directly reflects this.

Adding a nullable column with no default — This is the safest and fastest operation on nearly all databases. Because existing rows need no update (NULL is a valid default), most modern databases (PostgreSQL 11+, MySQL 8.0+) complete this as an instant metadata-only operation. No rows are touched.

<changeSet id="add-nullable-notes" author="nina">
    <addColumn tableName="order">
        <column name="notes" type="TEXT"/>  <!-- nullable, no default -->
    </addColumn>
</changeSet>

Adding a NOT NULL column with a constant default value — On PostgreSQL 11+ this is also instant (the default is stored as a catalog entry and applied lazily). On older PostgreSQL (before 11), MySQL (before 8.0), and most other databases, this rewrites every row in the table, acquiring a full table lock for the duration. On a 500 million row table, this is minutes of downtime.

<changeSet id="add-status-with-default" author="nina">
    <addColumn tableName="order">
        <column name="status" type="VARCHAR(20)" defaultValue="PENDING">
            <constraints nullable="false"/>
        </column>
    </addColumn>
</changeSet>

The safest pattern for large tables across all database versions is the expand/contract approach: add the column as nullable → backfill in batches using the application or a chunked UPDATE → add NOT NULL constraint once all rows are populated. This avoids any single operation that locks the full table.