How would you perform a complex migration (adding a mandatory column to a table with 300 million records) using Liquibase?

Adding a mandatory (NOT NULL) column to a table with 300 million records is a task that requires careful approach to avoid locking the table and causing long downtimes.

The approach via Liquibase can be broken down into several steps:

1. Add the new column as NULLABLE

   • This is quick and does not lock the table.
   • In Liquibase: <addColumn> with the attribute nullable="true".

2. Fill the column with default or computed values

   • Do this in batches to avoid overloading the database.
   • You can use scripts or separate changes in Liquibase with <update>.

3. Check that all records are filled

   • Ensure there are no NULLs.

4. Change the column to NOT NULL

   • In Liquibase: <modifyDataType> or <addNotNullConstraint>.
   • This may take time, but now without NULL.

5. Add index or constraints if needed

Example Liquibase changelog:

<changeSet id="1" author="me">
  <addColumn tableName="big_table">
    <column name="new_column" type="varchar(255)" nullable="true"/>
  </addColumn>
</changeSet>

<changeSet id="2" author="me">
  <update tableName="big_table">
    <column name="new_column" value="default_value"/>
  </update>
</changeSet>

<changeSet id="3" author="me">
  <addNotNullConstraint tableName="big_table" columnName="new_column" columnDataType="varchar(255)"/>
</changeSet>

If the database supports it, online migrations can be used (for example, PostgreSQL with ALTER TABLE ... ALTER COLUMN SET NOT NULL after filling), or tools for online migrations (gh-ost, pt-online-schema-change for MySQL).

Important:

• Perform migration during low load hours.
• Test on a copy of the database.
• Make backups before changes.