The change was trivial: widen coupon_code from VARCHAR(32) to VARCHAR(64) on the orders table, ahead of a promo launch. The DBA ran it at 14:20 on a Tuesday, it did not complete, and by 14:23 the entire checkout flow was timing out. The postmortem took longer than the incident. An analytics session had opened a transaction at 14:05 that touched orders and never committed; the ALTER needed an exclusive metadata lock on the table and queued behind that transaction; and because MariaDB's metadata lock queue is fair, every subsequent query touching orders — every cart load, every payment insert — queued behind the ALTER. Three hundred connections piled into Waiting for table metadata lock in four minutes, the pool exhausted, and the application fell over. The VARCHAR widening itself, once we killed the analytics transaction, finished in under a second. The schema change was never the problem. The queue was.
ALTER TABLE is the operation where MariaDB's three eras of DDL machinery — full table copies, InnoDB online DDL, and instant schema changes — all still coexist, and choosing wrong between them is a production outage wearing a maintenance task's clothes. This is the decision framework we run every schema change through now.
Which alterations are INSTANT, which are INPLACE, and which copy?
MariaDB answers this per operation and per engine, and the answer changed across releases, so the only honest source is the server in front of you — request the algorithm explicitly and let the server refuse, rather than assume. INSTANT, available since 10.3 for ADD COLUMN and extended in later releases, changes only metadata: milliseconds regardless of table size. INPLACE rebuilds indexes or reorganizes rows inside InnoDB without a full server-side copy and, with LOCK=NONE, permits concurrent reads and writes for most of the run. COPY — the fallback and the default for anything the faster paths decline — builds a shadow table and blocks writes the whole way. The declaration that saves you:
-- never let the server choose silently; make it refuse loudly
ALTER TABLE orders
MODIFY COLUMN coupon_code VARCHAR(64) NOT NULL,
ALGORITHM=INSTANT;
-- ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported
-- for this operation. Try ALGORITHM=NOCOPY.
-- INPLACE with concurrent DML, when INSTANT declines
ALTER TABLE orders
ADD INDEX idx_created (created_at),
ALGORITHM=INPLACE, LOCK=NONE;
-- what is running, and how far along is it?
SELECT ID, USER, TIME, STATE, INFO
FROM information_schema.PROCESSLIST
WHERE INFO LIKE 'ALTER TABLE%';The version-specific facts that survive consulting: widening a VARCHAR within the same byte-length class is INSTANT on modern releases, but crossing the 255-to-256 length-byte boundary — VARCHAR(255) to VARCHAR(256) — forces more work; ADD COLUMN at the end of the table has been INSTANT since 10.3, while adding columns mid-table or reordering came later; and DROP COLUMN became INSTANT in 10.4 only in the limited sense that the column vanishes from the dictionary while space reclamation waits. The instant add column happy path gets its own treatment in the instant add column notes; this article is about everything outside that happy path. When you are unsure, run the ALTER against a restored copy of the table with ALGORITHM=INSTANT and read which error the server gives you — ERROR 1846 is the server telling you exactly which slower algorithm to try next.
How does a queued ALTER freeze a table it never touches?
Through metadata lock fairness: an ALTER needs an exclusive MDL on the table, and while it waits for current holders — any open transaction that has touched the table — every new statement requesting a shared MDL on that table queues behind the waiting exclusive request rather than jumping past it. The sequence is always the same. A long transaction, usually analytics or an ORM session someone left uncommitted, holds a shared lock. The ALTER queues. Within seconds, the application's normal traffic stacks behind the ALTER. The ALTER has modified nothing and blocked everything, and SHOW PROCESSLIST fills with the tell-tale state:
-- the classic pile-up: one idle transaction, one waiting ALTER,
-- hundreds of victims
SELECT ID, USER, HOST, TIME, STATE
FROM information_schema.PROCESSLIST
WHERE STATE LIKE '%metadata lock%'
ORDER BY TIME DESC;
-- who is holding the lock the ALTER wants? (10.5+)
SELECT * FROM sys.schema_table_lock_waitsG
-- or: find long-open transactions before you start
SELECT * FROM information_schema.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;The defensive playbook is short and non-negotiable on hot tables. Before any non-INSTANT ALTER, check INNODB_TRX and kill nothing yet — just confirm no transaction older than your ALTER's expected lock wait exists, or reschedule. Set lock_wait_timeout for your session to something small, tens of seconds, so a blocked ALTER fails fast and releases the queue instead of stacking the application behind it for the default of a day; an ALTER that fails in thirty seconds is a retry, while an ALTER that waits for hours is an outage. And run the statement from a session you can kill cleanly — killing a mid-copy ALTER rolls back the copy, which on a big table can take longer than the copy did. The interaction with strict-mode data errors matters too: an ALTER that must rewrite rows can fail late on data the new definition rejects, which is the same ERROR 1366 class from the sql_mode notes, arriving after an hour of copying.
When do you reach for pt-online-schema-change instead?
When the operation cannot be INSTANT, the table is hot enough that even LOCK=NONE's brief MDL phases or INPLACE's load are unacceptable, or the table is simply large enough that any rebuild is an hours-long event you want throttled, monitored, and killable — which on our fleet means the top twenty tables by write rate and anything over 50 GB. Percona Toolkit's pt-online-schema-change builds a shadow table, syncs it with triggers, and swaps it in with a brief rename; it turns the alteration into a background process with --max-load and --max-lag guards that pause the copy when the server or the replicas suffer. The honest cost list: triggers add write overhead for the duration, the table needs the disk space to exist twice, foreign-key tables need careful --alter-foreign-keys-method handling, and the tool's lock discipline is only as good as your flag choices. gh-ost is the trigger-free alternative that tails the binary log instead, but its operational focus is MySQL, and I have seen it need coaxing against MariaDB-specific binlog quirks; on MariaDB I default to pt-osc and treat gh-ost as the exception that needs a rehearsal.
The decision tree we laminate: INSTANT-eligible per a rehearsal on a copy → run it directly, any time. Small or cold table → native ALTER with ALGORITHM=INPLACE, LOCK=NONE, session lock_wait_timeout set, off-peak. Hot or huge → pt-online-schema-change in a traffic trough with --max-lag pointed at the replica set, which also keeps the parallel-replication lag math from the parallel replication notes honest during the copy. Whatever the path, the ALTER goes through the change-management checklist that grew out of the upgrade field notes: rehearsed on a restored copy, timed, and reversible — or at least its blast radius understood — before it touches production.
Where MonPG fits
The signals worth trending are the queue and the throughput: sessions in Waiting for table metadata lock as a first-class alert, INNODB_TRX age maxima so the blocking transaction pages before the ALTER ever queues, and replica lag during any online schema change. Full disclosure, as in every article of 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 lock-wait visibility is core to what it is being built around, because the PostgreSQL version of this exact incident is what it already catches. Until that ships, the processlist queries above belong in your runbook. If PostgreSQL is also in your fleet, that monitoring is live today — see the PostgreSQL overview, or browse more field notes on the blog.