MariaDB Hidden Gem: Online Schema Change without pt-osc

When people hear “online schema change” in the MySQL and MariaDB world, many immediately think about pt-online-schema-change. And for good reasons: for years, changing a large table in production was one of those tasks that could ruin your day.

Recently, I discovered that MariaDB includes a very useful hidden gem I completely missed: the ability to update a table schema without blocking concurrent DML statements for the duration of the schema change. [1]

As you probably already know, MariaDB Server supports multiple schema change algorithms.

This is an overview summary:

Clause	Meaning	Concurrency impact	Typical use / comment
ALGORITHM=INSTANT	Metadata-only change. No table rebuild, no row copy.	Usually the best case; very fast. Pair with LOCK=NONE when supported.	Adding some columns, changing defaults, renaming columns, some metadata-only changes.
ALGORITHM=NOCOPY	Changes the table without copying all rows into a new table.	Can often be used with LOCK=NONE, allowing concurrent reads and writes.	Good for many online DDL operations, especially index-related changes.
ALGORITHM=INPLACE	Operation is performed “in place,” but may still rebuild internal structures.	Depends on the exact operation. Some support LOCK=NONE; others may need stricter locks.	Useful, but the name can be misleading: “in place” does not always mean “instant.”
ALGORITHM=COPY	Traditional table-copy style operation.	Historically the most intrusive, but MariaDB 11.2+ can do many COPY operations with LOCK=NONE.	Still the most expensive algorithm; test carefully on large tables.
LOCK=NONE	Non-locking strategy.	Allows concurrent SELECT, INSERT, UPDATE, and DELETE. If not possible, the statement fails.	Best production choice when you require online writes.
LOCK=SHARED	Read-lock strategy.	Allows concurrent reads, but blocks writes.	Acceptable for maintenance windows where reads must continue but writes can wait.
LOCK=EXCLUSIVE	Exclusive-lock strategy.	Blocks reads and writes.	Most restrictive; avoid on busy production tables unless planned.
no explicit LOCK / ALGORITHM	Let MariaDB choose.	Convenient, but less predictable.	Fine for testing; in production, be explicit so surprises fail early.

So, as you can see above, usually DBAs were not very happy when they had to perform a DDL requiring the use of the COPY algorithm, and that’s when pt-online-schema-change was useful.

But since MariaDB 11.2, ALTER TABLE can do most operations with ALGORITHM=COPY, LOCK=NONE;

This means that even when a copy is needed, MariaDB’s newer online schema change can still allow concurrent DML in many cases.

The solution

So what is that online schema change inside MariaDB?

Conceptually, this is similar to what pt-online-schema-change does: create a new version of the table, copy rows, capture concurrent changes, and switch to the new definition. The difference is that MariaDB Server now performs this natively inside the server, using an internal online change buffer instead of user-visible triggers.

MariaDB’s documentation describes exactly this mechanism: LOCK=NONE adds an extra step to the COPY algorithm by introducing an internal online change buffer. A new table is created from the old table as it looked at the start of the ALTER; for InnoDB and other transactional engines, this copy uses REPEATABLE READ isolation. Meanwhile, concurrent changes are written to the old table and duplicated into the online change buffer, then the accumulated changes are applied. [1]

Step by Step

1. MariaDB creates a new table

Because this is ALGORITHM=COPY, MariaDB Server needs a new physical table with the target structure.

For example, if the old table’s definition is:

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'new',
    PRIMARY KEY (id),
    KEY idx_customer_id (customer_id)
) ENGINE=InnoDB;

and you run:

ALTER TABLE orders
  MODIFY COLUMN status VARCHAR(50) NOT NULL DEFAULT 'new',
  ALGORITHM=COPY,
  LOCK=NONE;

MariaDB creates an internal temporary copy of the table with the new definition.
For explanation purposes, we can call it orders_new, but that is not the real
user-visible table name:

orders_new(id, customer_id, status VARCHAR(50) NOT NULL)

The internal intermediate table is not exposed as a normal user table. Internally, MariaDB uses temporary names with the #sql... prefix. Depending on the code path, names such as #sql-alter... or #sql-shadow-<thread_id>-<table_name> may be used. For example, one MariaDB source function builds shadow table names from the temporary prefix, the word shadow, the connection thread id, and the original table name.

2. MariaDB copies rows from the old table

The existing rows are copied from the original table into the new table.

The key difference from a traditional blocking copy is that the copy is based on the old table content as it existed at the beginning of the ALTER. For transactional engines like InnoDB, MariaDB’s docs describe this as copying in REPEATABLE READ isolation.

So, while the copy is happening, the server is effectively saying:

I am building the new table from a consistent starting point.

3. Application writes continue on the original table

Because you requested LOCK=NONE, concurrent DML can continue:

INSERT INTO orders ...;
UPDATE orders SET ...;
DELETE FROM orders WHERE ...;

MariaDB’s Online Schema Change page says that the feature means updating a schema without blocking concurrent DML for the duration of the schema change, and that with LOCK=NONE, DML such as INSERT, UPDATE, and DELETE can continue.

That is the “online” part.

4. Concurrent changes are captured in the online change buffer

This is the magic part.

While rows are being copied, new changes still go to the old table, because that is the table the application is using. But MariaDB also duplicates those changes into an online change buffer.

So if this happens during the copy:

UPDATE orders
SET status = 'paid'
WHERE id = 123;

MariaDB must later make sure that the copied row in the new table also ends up with status = 'paid'.

The online change buffer is not purely memory; it is implemented as a temporary file on the filesystem, on a per-table basis. It is typically stored under the directory defined by TMPDIR, or the operating system default temporary path.

That is a very important operational detail for your post: online copy can consume temporary disk space.

5. MariaDB applies the buffered changes

After the initial copy has caught up, MariaDB Server applies the changes accumulated in the online change buffer to the new table.

Conceptually:

Initial copy:
  old table snapshot -> new table

Then catch up:
  online change buffer -> new table

At the end of this step, the new table should contain:

1. all rows from the original snapshot;
2. plus all committed changes that happened while the copy was running;
3. transformed according to the new schema.

6. MariaDB briefly synchronizes and switches tables

Even with LOCK=NONE, there is still a short critical section.

MariaDB’s documentation says that while copying and applying online changes happens without blocking concurrent DML, ALTER TABLE acquires an EXCLUSIVE lock for a short time to synchronize with parallel operations that have not finished.

This is a key sentence for the blog:

LOCK=NONE does not mean “no locks ever.” It means the long copy phase does not block concurrent DML. A short exclusive synchronization is still required at the end.

After that synchronization, MariaDB Server switches the table definition so the new table becomes the real table.

We can see the LOCKs differences in the illustration below between the traditional COPY algorithm and the new one with LOCK=NONE:

Difference from pt-online-schema-change

The process is conceptually similar in that both involve a new version of the table and a catch-up mechanism.

But the implementation is different:

Aspect	pt-online-schema-change	MariaDB ALGORITHM=COPY, LOCK=NONE
Who manages it?	External tool	MariaDB server
New table?	Yes	Yes
Copy rows?	Yes	Yes
Capture concurrent writes?	Via triggers	Via internal online change buffer
Final switch?	Rename/swap tables	Server-managed switch
Extra application-visible triggers?	Yes	No
Temp/resource concern	Shadow table + triggers	New table + online change buffer temp file

MariaDB’s online copy is like a native, server-integrated online schema change. It still copies the table, but the change capture is internal instead of trigger-based.

One hidden gem can lead to another

There is also an important replication benefit, but it is worth distinguishing between two different mechanisms.

ALTER TABLE ... ALGORITHM=COPY, LOCK=NONE makes the schema change online on the server where it is executed. The table is copied, but concurrent INSERT, UPDATE, and DELETE operations can continue while MariaDB records those changes in the online change buffer and applies them before the final switch.

For replication timing, MariaDB has another useful option:

SET SESSION binlog_alter_two_phase = ON;

When this variable is enabled, a long-running ALTER TABLE can be written to the binary log in two phases. Instead of waiting until the primary has completely finished the ALTER, MariaDB can first write a START ALTER event. Replicas can then start executing the same ALTER TABLE while the primary is still working. When the primary finishes, it writes either COMMIT ALTER or ROLLBACK ALTER.

That’s another hidden gem!

This means the schema change can progress in parallel across the topology rather than being serialized as “primary first, replicas later.”

Pay attention: this is not enabled by default, so it must be enabled explicitly for the session or configured globally if you want this behavior more broadly.

Without two-phase binary logging, the old mental model often looked like this:

Primary:
  run ALTER for 2 hours
  then write ALTER to the binary log

Replica:
  waits 2 hours
  receives ALTER
  runs ALTER for 2 more hours
  also has to process the writes replicated from the primary

Total time until the replica is fully caught up:
  approximately primary ALTER time + replica ALTER time + extra lag

With binlog_alter_two_phase=ON, the model is much better:

Primary:
  starts ALTER
  writes START ALTER to the binary log
  continues the online copy

Replica:
  receives START ALTER early
  starts its own online ALTER
  runs roughly in parallel with the primary

Primary:
  finishes ALTER
  writes COMMIT ALTER

Replica:
  finalizes the ALTER after receiving COMMIT ALTER

So the total time until the replica is fully caught up can be much closer to the duration of the ALTER itself, instead of the primary duration plus the replica duration.

A typical example would be:

SET SESSION binlog_alter_two_phase = ON;

ALTER TABLE orders
  MODIFY COLUMN status VARCHAR(50) NOT NULL DEFAULT 'new',
  ALGORITHM=COPY,
  LOCK=NONE;

In this example, ALGORITHM=COPY, LOCK=NONE makes the copy-style alter online, while binlog_alter_two_phase=ON makes the replication of that long-running alter more efficient.

This is a big deal for large tables.

The replica still needs to perform the schema change locally, and it still needs enough CPU, I/O, and temporary disk space to do so. This does not make the operation free. But it avoids the worst-case pattern where the primary spends hours altering the table, only for the replicas to begin doing the same expensive work.

In short:

ALGORITHM=COPY, LOCK=NONE
  -> online schema change on each server

binlog_alter_two_phase=ON
  -> replicas can start the long ALTER earlier

Together, they make large schema changes much friendlier for replicated environments.

This illustration below compares both scenarios:

Conclusion

The important lesson is not that pt-online-schema-change is obsolete. It is not… but it should no longer be the automatic first reflex. With modern MariaDB, native online DDL should be the first thing to check.

For many changes, MariaDB can perform the operation with LOCK=NONE, allowing the application to continue reading and writing while the schema change is running. By explicitly specifying the algorithm and lock level, we also ensure the operation fails safely rather than accidentally accepting a more intrusive schema change.

That is exactly the kind of feature I like to call a MariaDB Hidden Gem: small syntax, big operational impact.