Why Does MariaDB 10.6.11 Copy Data When Adding a New Column (NULL Default) to the End of a Table?

In MariaDB versions prior to 10.6.11, adding a new column to an existing table with the InnoDB storage engine typically involved a process known as a "silent table rebuild." This rebuild involved:

1. Creating a temporary table: MariaDB would create a temporary table in the background with the same structure as the original table, but including the new column.
2. Copying data: Each row from the original table was copied into the temporary table, with the new column filled with the default value (NULL in your case).
3. Dropping the original table: Once the copy was complete, MariaDB would drop the original table.
4. Renaming the temporary table: Finally, the temporary table, now containing the data with the added column, would be renamed to the original table's name.

This approach had some limitations:

• Downtime: While the rebuild was happening, the table would be temporarily unavailable for writes (inserts and updates).
• Resource consumption: Copying a large table could be time-consuming and resource-intensive, especially on systems with limited disk space or memory.

Changes in MariaDB 10.6.11

MariaDB 10.6.11 introduced an optimization for certain scenarios when adding a column to the end of an InnoDB table:

• "Instant ADD COLUMN" for NULL defaults: If the new column has a default value of NULL (like in your case), and certain other conditions are met (such as not having a FULLTEXT index), MariaDB can perform an "instant ADD COLUMN" operation.
• No temporary table: In this case, MariaDB doesn't need to create a temporary table or copy the data. Instead, it updates the table structure in-place, adding the new column definition and setting its default value for existing rows.
• Faster and less disruptive: This "instant ADD COLUMN" approach is significantly faster and less disruptive compared to the traditional rebuild method.

Conditions for "Instant ADD COLUMN"

For the "instant ADD COLUMN" optimization to work, the following conditions must generally be true:

• The table uses the InnoDB storage engine.
• The new column is added at the end of the table (not in the middle).
• The new column has a default value of NULL.
• The table doesn't have a FULLTEXT index.

If any of these conditions aren't met, MariaDB might still have to resort to the traditional rebuild method, which would involve copying to a temporary table.

-- Assuming you have a table named `customers` with existing columns

ALTER TABLE customers
ADD COLUMN new_column VARCHAR(255) DEFAULT NULL;

In this case, if the customers table uses InnoDB, the new new_column will be added at the end with a default value of NULL, potentially using the "instant ADD COLUMN" method (assuming other conditions are met).

Scenario 2: Traditional Rebuild (MariaDB versions before 10.6.11 or when "Instant ADD COLUMN" is not applicable)

-- Assuming you have a table named `products` with existing columns

ALTER TABLE products
ADD COLUMN discount DECIMAL(5,2) DEFAULT 0.00;  -- Not NULL default for illustration

-- This might involve a silent table rebuild in older MariaDB versions.

Here, the products table might undergo a temporary rebuild because the default value isn't NULL (for demonstration purposes). This could involve copying data to a temporary table.

This method involves manually handling the data migration and involves more steps:

• Create a New Table: Define a new table with the desired structure, including the new column.
• Copy Data (Potentially with Transformation): Use a SELECT statement to extract data from the original table, optionally performing any necessary transformations for the new column. Insert the data with the new column into the new table.
• Drop the Original Table (Optional): Once the data has been migrated, you can drop the original table if it's no longer needed.
• Rename the New Table (Optional): If you want to keep the original table name, you can rename the new table to match it.

Drawbacks:

• More complex and error-prone compared to ALTER TABLE.
• Can be significantly slower and resource-intensive for large tables.

Partitioning (Advanced):

If you're using MariaDB with table partitioning enabled, you can potentially add columns to individual partitions instead of the entire table at once. This can be helpful for reducing downtime when dealing with very large tables. However, partitioning is an advanced feature and requires careful planning and understanding.

Recommendation:

In most cases, the ALTER TABLE statement with the "instant ADD COLUMN" optimization (if applicable) is the most efficient and straightforward approach. However, if you have specific requirements or limitations, the manual data manipulation method or partitioning (for advanced users) might be considered as alternatives.