ALTER TABLE improvements in MariaDB Server

ALTER TABLE improvements in MariaDB Server

FOSDEM 2020

ALTER TABLE in MySQL used to copy the table contents row by row. We can do much better; in the best case, allow instantaneous schema changes, even for nontrivial changes, such as ADD COLUMN…AFTER and DROP COLUMN. This talk describes how ALTER TABLE has been improved over the years for the InnoDB storage engine in MySQL 5.1, 5.5, 5.6, 5.7, and MariaDB Server 10.2, 10.3, 10.4, 10.5, mostly by the presenter.

The talk enumerates different classes of ALTER TABLE operations:

1. operations not involving other than metadata
2. operations that can be performed instantly by introducing a backward-compatible data file format change and 'faking' the operation (ADD or DROP COLUMN)
3. operations that can avoid rebuilding a table
4. operations that must rebuild the table
5. variations of the previous 2 classes that allow concurrent modifications to the table

We also show how ALTER TABLE can be executed concurrently on multiple nodes in statement-based replication. Finally, we cover some theoretical limits of what kind of ALTER TABLE operations can be supported without rebuilding the table, by introducing an optional validation step and on-demand conversion of records in previous schema versions of the table.

Speakers: Marko Mäkelä