Reduced operational downtime with new ALTER TABLE features

Reduced operational downtime with new Online Schema Change

We recently released MariaDB Enterprise Server 11.4 as part of the MariaDB Enterprise Platform 2025 major release. One key update in this release are the improvements made to ALTER TABLE, both the Online Schema Change feature and the Optimistic ALTER TABLE for replication feature. In this blog, we will dig deeper into what these changes are and show you how you can benefit from them.

Online Schema Change

ALTER TABLE operations have long been problematic in both MySQL and MariaDB. The way ALTER TABLE operations have traditionally been done is the following:

1. Create a new table with the new structure (even if the structure is hardly changed at all)
2. Lock the existing table
3. Copy all data from the original table to the new structure
4. When done, delete the old table and rename the new table to the original name
5. Unlock the table.

A table lock restricts write access while maintaining read access. In a typical OLTP scenario, tables are subject to frequent writes. This raises the concern of how long will a table remain locked? The duration of a lock is largely dependent on the size of the table. Because larger tables require more time to copy their data, they need longer lock periods. As an extreme we at MariaDB we have customers with very large tables where an ALTER TABLE can take days to execute.

Throughout the years there have been attempts to mitigate this potentially lengthy locking time, such as InnoDB’s support for INSTANT operations. For INSTANT ALTER operations the changes are not applied when the ALTER TABLE command is executed, but rather they are applied on subsequent rows operations. However such solutions are not without drawbacks and are not universally applicable.

There are also third party tools, such as gh-ost or pt-online-schema-change that offer alternative solutions by creating complex procedures to track changes to the original table while a new table with the new structure is being created. These third party tools are, however,  not completely fail-proof. Because they are not part of the database server’s internal operations and rely on external components, failures during the operation can lead to messy cleanup.

There is now a universal solution to the problem: the new Online Schema Change feature, available in MariaDB Enterprise Server. This built-in capability significantly reduces the lock duration that previously blocked data manipulation language (DML) operations during ALTER TABLE executions. The new ALTER TABLE process is the following:

1. Create a new table with the new structure (even if the structure is hardly changed at all)
2. Lock the existing table
3. Create a change buffer
4. Unlock the table
5. Copy all data from the original table to the new structure. At the same time all changes to the original table are captured in the change buffer.
6. When done, lock the table
7. Apply the change buffer to the data in the new table structure
8. Delete the old table and rename the new table to the original name
9. Unlock the table

The locking is now split into two phases: a short lock is taken when the change buffer is set up and a second lock is taken when applying the changes from the change buffer. Typically, copying data is by far the slowest part of the operation, and by not locking the table during that process the table is online and usable most of the time. This is a significant improvement for most use cases.

While this new approach offers substantial advantages, it’s not universally superior. Certain edge cases exist where the new process is not preferable, for example if:

1. Your table is very small – In such cases, copying the data doesn’t take long, reducing the benefits of changing the algorithm
2. There is an extremely high rate of changes to the table – While this is uncommon, an exceptionally high rate of DML operations can cause the change buffer to grow nearly as fast as the data is copied. In this use case, the traditional ALTER TABLE approach may be more efficient.

For cases like this it is still possible to explicitly use the old ALTER TABLE way of processing the tables by adding the LOCK=SHARED qualifier to the statement. The default behavior can also be reverted to the older method using the –old-mode=LOCK_ALTER_TABLE_COPY option. While the new Online Schema Change is now the default approach, the diagram below illustrates the difference between the two different algorithms:

The benefits of this new feature are highly dependent on the specific use case. To illustrate its potential impact, consider this simple example involving a 10MB InnoDB table. One connection executes an ALTER TABLE while another connection attempts to execute a set of single row updates (based on the Primary Key). This was tested using both the traditional ALTER TABLE method and the new Online Schema Change feature:

These results demonstrate that while the new Online Schema Change feature makes the ALTER TABLE marginally slower, it has the significant advantage of allowing concurrent DML operations. In this specific test, with this table and these types of queries, the performance scales linearly as the table size increases. For example, with a 100MB version of this table, the traditional ALTER TABLE method took approximately 250 seconds, while the new OSC feature took around 280 seconds. It’s important to note that with a different table structure and different DML queries the results are likely to be different.

Optimistic ALTER TABLE for Replication

How does the Optimistic ALTER TABLE interact with replication?

This feature aims at fixing a different problem: replication lag. Because MariaDB’s native replication is asynchronous (or semi synchronous, if that feature is used), there is inherent lag when you use replication. A transaction is committed on the primary server before being replicated to the replica, where it is then executed when the replica is able to do so. With short transactions and a non-overloaded replica, the lag is typically minimal. However, longer transactions directly increase the minimum possible lag.

Because ALTER TABLE operations can take hours, the resulting lag can also take hours. While the OSC feature helps on the primary server by allowing concurrent DML operations, it does not directly address replication lag in this scenario.Only replication operations that are committed in the same group commit can be run in parallel. Therefore, while DML operations committed concurrently with the Online ALTER TABLE could be executed in parallel on the replica, all the DML operations (that were done in parallel on the primary) will arrive and be executed  on the replica before the ALTER TABLE operation itself.

The new optimistic ALTER TABLE for replication feature addresses this issue by  dividing the ALTER TABLE operation into two phases. When the ALTER TABLE statement is initiated on the primary server, a BEGIN ALTER command is sent to the replica. The replica then begins its own ALTER TABLE process. Combined with the OSC feature, the table remains available for DML on the primary during this phase.

Upon completion of the ALTER TABLE operation on the primary, a COMMIT ALTER command is sent to the replica, at which point the schema change takes effect immediately on the replica.  If the ALTER TABLE operation fails on the primary, a ROLLBACK ALTER command is sent to the replica, preventing the ALTER TABLE operation from being committed.  The following diagram illustrates the difference between the two approaches:

The gains in inherent lag are pretty much the duration of the ALTER operation which depends on the table size but can be up to several hours. Another great feature that improves the operational performance of MariaDB.

This new feature significantly reduces inherent replication lag, potentially by the duration of the ALTER TABLE operation itself, which can range from minutes to several hours depending on the table. This is just one of the many exciting new features in MariaDB Enterprise Server 11.4 that enhance operational performance.

Next steps

• Customers can download MariaDB Enterprise Server 11.4 here
• Users can try these new features by downloading MariaDB Community Server 11.4 or later and contacting us to upgrade
• Learn more about MariaDB Enterprise Platform 2025