At 9:40 on a Tuesday night I ran the migration that had sailed through staging in 120 milliseconds. The statement was a plain ALTER TABLE orders ADD COLUMN risk_score DECIMAL(5,2) NULL — a nullable column appended to a 41 GB table holding about 300 million rows. Staging had done it instantly, so I ran it on production during the quiet window and watched the processlist flip to "copy to tmp table". It stayed in that state for three hours and forty-seven minutes. Writes queued, the read replica fell ninety minutes behind replaying the rebuilt table, and the on-call phone rang at 1 am.
The postmortem took ten minutes once I looked at the right column: staging was MariaDB 10.11, production was MariaDB 10.2. Same table definition, same row counts, same statement — and two completely different execution paths, because instant schema change is a versioned capability, not a MariaDB feature in the abstract. Since that night I never approve an ALTER without checking which engine version will actually run it. This is the version-by-version map I should have had.
Why was the same ALTER instant on staging but hours in production?
Because instant ADD COLUMN did not exist on the production server. MariaDB 10.3.2, released in 2018, was the first version with ALGORITHM=INSTANT for adding a column. Before that, every ADD COLUMN was a copy operation: InnoDB builds a new table with the extra column, copies every row into it, rebuilds every secondary index, and swaps the tables at the end. That is exactly what happened on my 10.2 box — the "copy to tmp table" state was InnoDB rewriting 41 GB of clustered index plus all four secondary indexes, one row at a time.
On 10.3 and later, the same statement is a metadata change. InnoDB records in the table's metadata that a column was appended, with a default value, and returns in milliseconds. Existing rows on disk are untouched; the engine fills in the default when it reads an old row. Nothing is copied, no index is rebuilt, and the wall-clock time does not grow with table size. The lesson generalizes past my incident: "the same MariaDB" is not a meaningful phrase when the two servers are a major version apart. Schema-change behavior is one of the places where the version number decides everything, and staging that does not match production's exact version is not a rehearsal — it is theater.
Which operations are instant in each MariaDB version?
The short answer: 10.3 adds columns at the end of the table, and 10.4 adds, drops, and reorders columns almost anywhere — and almost everything else still rebuilds. In 10.3, the instant path covers ADD COLUMN, but only appended at the last position. The table keeps its old on-disk layout, and the instant-added columns live behind a small piece of metadata. If your migration adds the column in the middle with AFTER some_other_column, 10.3 quietly falls back to a full copy. That fallback is exactly the trap: the statement is valid, it just costs hours instead of milliseconds.
MariaDB 10.4 reimplemented instant schema change with a new record format that stores per-record metadata — including a hidden metadata record at the start of the clustered index that describes the layout history. That is what unlocked the wider set: instant ADD COLUMN at any position including FIRST and AFTER, instant DROP COLUMN, and instant reordering of columns. The governing variable, innodb_instant_alter_column_allowed, tells the story in its allowed values: in 10.3 it accepts add_last and never; in 10.4 it gains add_drop_reorder_last as the default. What never became instant is the physically rewriting set — changing a column's type or width, changing ROW_FORMAT or ENGINE, dropping the primary key. Those rebuild the table on every version, and no release notes will change that, because the bytes on disk genuinely have to move.
-- first: which server am I actually on?
SELECT VERSION();
-- MariaDB 10.3+: say it out loud, so a non-instant path ERRORS
-- instead of silently starting a multi-hour copy:
ALTER TABLE orders ADD COLUMN risk_score DECIMAL(5,2) NULL,
ALGORITHM=INSTANT, LOCK=NONE;
-- 10.4+: mid-table placement can be instant too:
ALTER TABLE orders ADD COLUMN region_code CHAR(2) NULL AFTER country,
ALGORITHM=INSTANT, LOCK=NONE;
-- the capability switch worth checking on inherited servers:
SHOW VARIABLES LIKE 'innodb_instant_alter_column_allowed';
-- MySQL 8.0: same explicit-clause discipline, different engine rules:
ALTER TABLE orders ADD COLUMN risk_score DECIMAL(5,2) NULL,
ALGORITHM=INSTANT;
-- while any ALTER runs, watch what it is really doing:
SHOW PROCESSLIST; -- "copy to tmp table" means a full rebuildWhat are the hidden costs of instant ALTER?
The first cost is metadata that never goes away on its own. Every instant operation is recorded in the table's layout history, and that history rides along until the table is physically rebuilt. In practice it is harmless — a small per-table and per-record overhead — until you hit the second cost: the deferred rebuild. The instant operations do not eliminate the copy, they postpone it. The next statement that rebuilds the table for any reason — widening a VARCHAR, switching ROW_FORMAT, an OPTIMIZE TABLE during maintenance — must convert every row through the full layout history in one pass. A table that has accumulated a dozen instant alters takes a slightly longer rebuild when the bill comes due, and it always comes due.
The third cost is the exclusion list, which bites hardest right after upgrades. Tables using ROW_FORMAT=COMPRESSED do not qualify for the 10.4 instant set. And tables that were created — or instantly altered — under 10.3 carry the older 10.3-style metadata: after upgrading such a server to 10.4, appended ADD COLUMN keeps working instantly, but the wider instant set, particularly instant DROP COLUMN, can be refused until the table is rebuilt once. That is the "older row formats" limitation from the release notes, and it is invisible until the night you try to drop a column instantly and the statement either errors or rebuilds for three hours. My rule on any freshly upgraded fleet: the first big maintenance window includes a rebuild of the tables that were instant-altered on the old version, scheduled deliberately instead of discovered accidentally.
Replication adds one more honest line to the cost sheet. Instant DDL is instant on the writer, but the statement still has to replicate and replay. On a replica that replays instantly too, this is a non-event; on a Galera cluster, the DDL still executes under total order isolation on every node at the same logical moment, which is its own discipline — the TOI versus RSU tradeoff is covered in the replication vs Galera field notes. Instant removes the copy cost, not the coordination cost.
How do you verify what an ALTER will do before it runs?
The reliable way is to stop asking and start demanding: state ALGORITHM=INSTANT and LOCK=NONE explicitly in the statement. With the algorithm spelled out, an operation that cannot run instantly fails immediately with an error instead of silently downgrading to COPY and starting the four-hour job you were trying to avoid. This single habit converts every instant-DDL surprise from a production incident into a dry-run failure in a terminal. I pair it with SHOW PROCESSLIST discipline on anything longer than a few seconds: the State column is the ground truth, and "copy to tmp table" is the phrase that means the rebuild is happening right now.
Beyond the explicit clause, the verification checklist is short. Check VERSION() on the actual target — never the wiki page that claims what production runs. Check innodb_instant_alter_column_allowed, because someone may have set it to never as an upgrade-era safety measure and forgotten to remove it. Check the table's ROW_FORMAT with information_schema.TABLES, because COMPRESSED disqualifies the wide instant set. And for anything past a plain appended ADD COLUMN, rehearse on a restored copy of production with production row counts and time it. Ten minutes of rehearsal has saved me more nights than any amount of documentation reading, because the documentation describes what the version supports and the rehearsal tells me what this table, on this server, will actually do.
How does MySQL 8.0's instant ADD COLUMN compare?
MySQL built the same feature independently, and the details differ enough that cross-engine runbooks fail. MySQL 8.0.12 introduced instant ADD COLUMN, appended at the end of the table only — the same scope MariaDB 10.3 had. MySQL 8.0.29 widened it to adding a column at any position and to instant DROP COLUMN. So the version map is a different map: 10.3 versus 10.4 on one side, 8.0.12 versus 8.0.29 on the other, and "instant add anywhere" arrived years apart between the two ecosystems.
The implementation is different too, and so are the limits. MySQL tracks instant changes as row versions per table and caps that count at 64 — hit the ceiling and the next instant ALTER forces a rebuild. MariaDB's layout-history metadata has no equivalent counter. MySQL excludes its own list: tables with FULLTEXT indexes, tables using COMPRESSED row format, and tables living in the data dictionary tablespace cannot use the instant algorithm. Both engines record the metadata in their own way — MySQL in its transactional data dictionary, MariaDB in .frm-era table metadata and the clustered index's hidden record — so nothing about one engine's instant internals transfers to the other. The one thing that transfers is the discipline: explicit ALGORITHM=INSTANT on both, verified versions on both, rehearsal on both.
Where MonPG fits
The signals that would have caught my incident before 1 am are operational, not exotic: a processlist state that says "copy to tmp table" on a statement expected to be instant, replica lag climbing during a DDL window, and table sizes and row formats recorded somewhere a runbook can reach. Full disclosure, as in every article in this series: I work on MonPG, which monitors PostgreSQL in production today and does not monitor MariaDB yet. MariaDB support is coming soon and in active development — the /mariadb-monitoring page tracks where it stands — and schema-change visibility is on the list of things it is being built around: long-running ALTER statements surfaced with their real execution state, replica lag during migration windows, and the processlist anomalies that mean a "metadata change" turned into a full table rewrite. Until that ships, the explicit-clause habit and the queries above are the safety net. If your fleet also runs PostgreSQL, that monitoring is live today — see the PostgreSQL overview, or browse the field notes on the blog.