Chapter 16 · Partitioning, Large Tables, Online DDL, and Data Lifecycle
ALTER TABLE Algorithms, Lock Levels, Online DDL, and Operational Planning
Treat ALTER TABLE as an observable production operation: constrain the algorithm and lock level, measure the work, and plan for metadata locks, disk, topology, and rollback.
Learning outcomes
A schema change that is trivial on a 50 MB table can become an outage on 5 TB. MariaDB offers multiple ALTER algorithms and lock strategies, but the words online, in-place, and instant are not interchangeable. This lesson treats DDL as an operational change with measurable work, metadata-lock boundaries, disk requirements, replication effects, and abort criteria.
Explain COPY, INPLACE, NOCOPY, and INSTANT as different work guarantees rather than speed labels.
Use LOCK=NONE/SHARED/EXCLUSIVE as explicit concurrency constraints and interpret unsupported combinations as useful failures.
Demonstrate metadata-lock blocking with two sessions even for an otherwise online change.
Estimate copy/rebuild cost from measured table size and throughput instead of universal timing rules.
Write a production-like DDL runbook with prechecks, stop conditions, observability, and rollback/recovery boundaries.
From MariaDB 11.2, most ALTER TABLE operations can use
concurrent DML even when the COPY algorithm is required.
Therefore do not teach or operate from the old shortcut “COPY
= offline.” If concurrency is a requirement, request
LOCK=NONE explicitly and let MariaDB fail if the
operation cannot honor it.
1. The two axes: how data changes and who may access the table
| Control | Meaning | Why operators care |
|---|---|---|
| ALGORITHM=INSTANT | No data-file modification for supported operation | Strong guard against accidental table work. |
| ALGORITHM=NOCOPY | Avoid clustered-index rebuild | Stronger than INPLACE for rebuild avoidance. |
| ALGORITHM=INPLACE | Use engine-specific path; may still rebuild | Name does not guarantee “no copy-like work.” |
| ALGORITHM=COPY | Build/copy into new structure | Can require large temporary/storage work. |
| LOCK=NONE | Concurrent reads and writes must be allowed | Fails if operation cannot satisfy this. |
| LOCK=SHARED | Reads allowed; writes blocked | Useful only when maintenance policy accepts write pause. |
| LOCK=EXCLUSIVE | No concurrent DML | Highest application impact. |
ALGORITHM constrains the physical/change mechanism;
LOCK constrains concurrency. They answer different
questions and should be reviewed separately.
2. Create a table large enough to observe DDL mechanics
DROP DATABASE IF EXISTS servicehub16_l3;CREATE DATABASE servicehub16_l3;USE servicehub16_l3;CREATE TABLE tickets ( ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_id BIGINT NOT NULL, state VARCHAR(24) NOT NULL, opened_at DATETIME(6) NOT NULL, note VARCHAR(255) NULL, KEY ix_customer(customer_id)) ENGINE=InnoDB;INSERT INTO tickets(customer_id,state,opened_at,note)SELECT seq, IF(seq % 5=0,'closed','open'), NOW(6), RPAD('x',200,'x')FROM seq_1_to_100000;SELECT TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA='servicehub16_l3' AND TABLE_NAME='tickets';The Sequence storage engine/table syntax is available in MariaDB and is convenient for a disposable lab. If your package disables it, generate rows with a recursive CTE or client script instead. Record size before every DDL experiment.
3. Use INSTANT/NOCOPY as safety assertions, not hopeful hints
ALTER TABLE tickets ADD COLUMN source VARCHAR(24) NULL, ALGORITHM=INSTANT, LOCK=NONE;SHOW CREATE TABLE tickets;
If MariaDB cannot perform the requested operation instantly, an
explicit ALGORITHM=INSTANT request fails instead of
silently choosing heavier work. That failure is valuable in a
change window because it preserves your operational assumption.
-- Changing a column type commonly requires work that is not INSTANT.ALTER TABLE tickets MODIFY note TEXT NULL, ALGORITHM=INSTANT, LOCK=NONE;Expect an “operation not supported” style error for the requested algorithm on versions/operations that cannot satisfy it. Do not immediately remove the guard. Re-plan the operation with the exact algorithm/lock behavior your target version supports.
4. Online DDL still has metadata-lock boundaries
A metadata lock (MDL) protects table definition stability. Even an ALTER that permits concurrent DML may need to wait for an older transaction holding table metadata, and it may briefly need an exclusive metadata transition at critical phases.
USE servicehub16_l3;START TRANSACTION;SELECT * FROM tickets WHERE ticket_id=1;-- Keep this transaction open. Do not COMMIT yet.ALTER TABLE tickets ADD INDEX ix_state_opened(state,opened_at), ALGORITHM=INPLACE, LOCK=NONE;SHOW FULL PROCESSLIST;SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\GIf the ALTER waits, that is not evidence that online DDL is broken; it proves an existing transaction/metadata dependency matters. Repair the lab by committing Session A, then observe Session B proceed.
5. Current COPY behavior: concurrent DML can still mean expensive work
MariaDB 11.2+ can perform most COPY operations with
LOCK=NONE. An online change buffer captures
concurrent modifications while the new table is built. This
improves availability but does not eliminate I/O,
temporary-file, redo/binlog, CPU, or final synchronization
pressure.
| Signal to watch | What it can tell you |
|---|---|
| filesystem/free space | Whether copy/temp/change-buffer work risks exhausting disk |
| processlist / DDL progress | Whether the change is waiting or advancing |
| I/O latency and throughput | Whether the change is saturating storage |
| redo/binlog growth | How much concurrent/change traffic the operation drives |
| replica lag / wsrep flow control | Whether downstream/topology capacity is keeping up |
| application latency/error rate | Whether “online” is still violating the service objective |
6. Estimate work from measurements, not a magic “minutes per TB” number
rebuild_seconds ≈ bytes_to_read_write / measured_sustained_effective_bytes_per_secondrequired_headroom ≈ target_copy + indexes + temp/change_buffer + redo/binlog growth + safety_marginmaintenance_budget = precheck + lock_wait + main_work + final_sync + validation + rollback_marginThese are planning models, not guarantees. Measure a representative clone or subset under comparable storage, concurrency, cache, and topology conditions. Record the uncertainty explicitly. If a 2 TB rebuild benchmarked at 300 MB/s in a quiet lab, do not multiply mechanically and promise a production completion time; concurrent workload and final synchronization can dominate.
7. Production DDL runbook
| Stage | Required evidence |
|---|---|
| preflight | exact VERSION(), SHOW CREATE TABLE, size/index inventory, backups/restores, free space, replication/Galera health |
| dry run | target-version clone; explicit ALGORITHM/LOCK; measured throughput and lock behavior |
| go/no-go | traffic window, disk headroom, lag budget, application latency budget, owner and kill authority |
| execute | query id/process, DDL progress, OS I/O/free space, redo/binlog/topology lag |
| validate | schema checksum/SHOW CREATE, row/query checks, replica/cluster convergence, application smoke tests |
| abort/recovery | document what kill means for this phase/version; restore/rebuild path if post-change validation fails |
8. Production judgment and cleanup
Prefer the cheapest algorithm that satisfies the actual schema requirement, but encode critical assumptions explicitly. “Online” is an availability property, not a statement that the operation is free. Large-table DDL belongs in capacity planning and incident response, not only in schema migration code.
Prerequisites: a disposable InnoDB schema with ALTER plus DML privileges; PROCESS-level visibility may be needed to inspect other sessions depending on account/version. Online-DDL guarantees are operation-, engine-, and version-specific; production work requires filesystem/OS and topology telemetry in addition to SQL.
Check your understanding
- Why are ALGORITHM and LOCK separate controls?
- Why is ALGORITHM=INPLACE not a guarantee that no rebuild occurs?
- How can an online ALTER still wait before doing useful work?
- What changed in MariaDB 11.2 regarding COPY and LOCK=NONE?
- Which measurements belong in a DDL go/no-go decision?
Review the answers
ALGORITHM constrains the change mechanism while LOCK constrains allowed concurrency. INPLACE is engine-specific and can still rebuild. Existing transactions can hold metadata locks that delay DDL. From 11.2, most COPY operations can permit concurrent DML with LOCK=NONE, but the copy remains resource-intensive. A go/no-go packet should include size, free space, measured throughput, metadata-lock exposure, application SLOs, redo/binlog growth, and replica/Galera health.
DROP DATABASE IF EXISTS servicehub16_l3;