Choosing the Right Online DDL Algorithm for MariaDB InnoDB Tables

The Problem: Schema Changes vs. Table Availability

Performing a schema change on a large production table often risks locking the table for the duration of the operation, leading to application timeouts and downtime. While MariaDB provides Online DDL (Data Definition Language) capabilities for InnoDB tables, the system does not always default to the fastest or least intrusive method. Choosing the wrong algorithm can inadvertently trigger a full table copy, consuming massive disk I/O and blocking write operations.

Decision Matrix: INSTANT vs. INPLACE vs. COPY

The choice of algorithm depends on the specific structural change and your tolerance for write locks. The following table summarizes the behavior for InnoDB tables (MariaDB 10.0+).

Algorithm	Write Availability	Disk Space Required	Primary Use Case	Risk
INSTANT	Full (No lock)	Negligible	Adding columns at the end of a table	Very limited operation support
INPLACE	Full (Concurrent)	Moderate (Log files)	Adding/dropping indexes, altering types	Can fall back to COPY if constraints fail
COPY	Blocked (Read-only)	Full table size	Complex schema overhauls	High I/O and total write lock

Engineering Trade-offs and Constraints

ALGORITHM=INSTANT is the gold standard for performance because it only modifies the metadata in the data dictionary. However, it is strictly additive. If you attempt to add a column in the middle of a table or change a column's data type, INSTANT will fail immediately with an error rather than falling back to a slower method.

ALGORITHM=INPLACE allows the table to remain available for both reads and writes by utilizing an online alter log to record changes occurring during the operation. The trade-off is the requirement for additional disk space to store this log and the potential for a brief exclusive lock at the very beginning and end of the process.

ALGORITHM=COPY is the fallback. It creates a new hidden table, copies all data from the original, and then swaps them. This is the safest method for structural changes that cannot be mapped logically (like changing a primary key), but it is the most disruptive.

Implementation and Validation

To prevent an accidental COPY operation on a multi-gigabyte table, explicitly define the algorithm in your ALTER TABLE statement. If the requested algorithm is not supported for that specific change, MariaDB will reject the query instead of executing a slow fallback.

Example: Adding a new index without blocking writes

-- Run this on the MariaDB CLI as a user with ALTER permissions
-- This ensures the operation is non-blocking; it will fail if INPLACE is unsupported
ALTER TABLE orders 
ADD INDEX idx_customer_id (customer_id), 
ALGORITHM=INPLACE, 
LOCK=NONE;

Verification Steps:

• Pre-check: Run SHOW TABLE STATUS LIKE 'orders'; and note the Update_time.
• Execution: Execute the ALTER statement. If you used ALGORITHM=INSTANT, the command should return almost immediately regardless of table size.
• Post-check: Run SHOW TABLE STATUS LIKE 'orders'; again. For INSTANT operations, the Update_time typically remains unchanged because the underlying data files were not rewritten. For COPY, the timestamp will update.
• Schema Validation: Run DESCRIBE orders; to confirm the new index or column exists.

Critical Limitations

• Non-Nullable Columns: Adding a NOT NULL column with a default value may force a COPY operation depending on the MariaDB version and storage engine configuration.
• Engine Support: Online DDL is an InnoDB feature. Tables using MyISAM or Aria do not support INSTANT or INPLACE and will always default to COPY.
• Virtual Columns: Adding indexes to generated or virtual columns may be rejected by the INSTANT algorithm.

Rollback Procedure

Since ALTER TABLE changes the state of the database schema, there is no automatic "undo" command. To roll back a change, you must execute a reverse ALTER statement:

-- To remove the index added in the previous example
ALTER TABLE orders DROP INDEX idx_customer_id, ALGORITHM=INPLACE, LOCK=NONE;