ALTER TABLE Improvements in MariaDB Server 10.3

MariaDB Server 10.3.7 (the first Generally Available release in the series) includes some ALTER TABLE improvements that are worth mentioning. Last October, I wrote about the Instant ADD COLUMN feature that was introduced in the 10.3.2 alpha release. The effort to support instant ALTER TABLE in MariaDB comes from a collaboration with ServiceNow. The first part of that, instant ADD COLUMN, was brainstormed in April 2017 by engineers from MariaDB Corporation, Alibaba and Tencent. A prototype was first developed by Vin Chen (陈福荣) from the Tencent Game DBA Team and was later refined by our team for the MariaDB version.

Part of the original plan was to introduce syntax for ALTER TABLE…ALGORITHM=INSTANT in order to be able to give a guarantee that the requested operation will be performed instantly, or not at all. This was finally implemented in MariaDB Server 10.3.7. We also introduced the keyword ALGORITHM=NOCOPY, which will refuse an operation if the table would be rebuilt.

The example illustrates a new configuration parameter alter_algorithm. A DBA could set it globally in the MariaDB Server configuration to NOCOPY in order to prevent expensive ALTER TABLE statements from being executed by mistake.

The ALGORITHM=INPLACE syntax, which was added in MariaDB Server 10.0, can misleadingly suggest that no copying ever takes place. However, until the efforts to implement instant schema changes (instant DROP COLUMN and others being worked on for upcoming MariaDB Server releases), the inplace operation often did involve copying, potentially allocating quite a bit of extra space, not only for the new copy of the table, but also for pre-sorting the data and for logging concurrent modifications to the table (ALTER ONLINE TABLE). With the ALGORITHM=INSTANT and ALGORITHM=NOCOPY clauses, which represent proper subsets of ALGORITHM=INPLACE, we are clarifying the situation.