Expand/Contract Schema Changes, Online DDL, Backfills, and Application Compatibility

Learning outcomes

ServiceHub must replace a legacy status string with a constrained code while multiple application versions deploy over several minutes. A one-shot rename turns deployment order into an outage risk. Expand/contract intentionally creates a compatibility window where both old and new versions work.

1. Phase model and rollback boundaries

2. Build the table and prove DDL behavior

DROP DATABASE IF EXISTS upgrade20_l4;CREATE DATABASE upgrade20_l4;USE upgrade20_l4;CREATE TABLE tickets ( ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT, status_text VARCHAR(20) NOT NULL, subject VARCHAR(160) NOT NULL, updated_at DATETIME(6) NOT NULL, INDEX ix_updated(updated_at)) ENGINE=InnoDB;INSERT INTO tickets(status_text,subject,updated_at)VALUES ('open','A',NOW(6)),('waiting','B',NOW(6)),('closed','C',NOW(6));ALTER TABLE tickets ADD COLUMN status_code TINYINT NULL, ALGORITHM=INSTANT, LOCK=NONE;SHOW CREATE TABLE tickets;

If the exact table/version cannot satisfy the requested algorithm/lock, treat rejection as useful evidence and redesign. Never remove those clauses merely to make the command “work” without understanding the fallback cost.

3. Observe metadata locking

START TRANSACTION;SELECT * FROM upgrade20_l4.tickets WHERE ticket_id=1;-- keep transaction open only in this disposable lab

ALTER TABLE upgrade20_l4.tickets ADD INDEX ix_status_code(status_code), ALGORITHM=INPLACE, LOCK=NONE;

SHOW FULL PROCESSLIST;SELECT * FROM information_schema.PROCESSLISTWHERE DB='upgrade20_l4';

Online DDL can still wait for metadata locks. The correct response is not to kill arbitrary sessions; identify the blocker, understand transaction ownership, and use a predeclared abort/kill plan.

4. Deterministic resumable backfill

UPDATE upgrade20_l4.ticketsSET status_code = CASE status_text  WHEN 'open' THEN 1 WHEN 'waiting' THEN 2 WHEN 'closed' THEN 3 ENDWHERE ticket_id > ? AND ticket_id <= ?  AND status_code IS NULL;SELECT COUNT(*) AS remainingFROM upgrade20_l4.tickets WHERE status_code IS NULL;SELECT status_text,status_code,COUNT(*)FROM upgrade20_l4.tickets GROUP BY status_text,status_code;

Persist the last completed key outside the transaction. Rate-limit from observed transaction duration, lock waits, redo/binlog volume and replica/Galera health; do not hard-code a universal chunk size.

5. Wrong approach: contract during mixed deployment

Dropping status_text while old processes still reference it creates immediate SQL errors. The repair is an explicit deployment gate: telemetry shows no supported application build references the legacy column, all data has converged, and rollback consequences are accepted.

6. Contract only after proof

ALTER TABLE upgrade20_l4.tickets MODIFY status_code TINYINT NOT NULL;-- After the compatibility window is truly closed:ALTER TABLE upgrade20_l4.tickets DROP COLUMN status_text;SHOW CREATE TABLE upgrade20_l4.tickets;DROP DATABASE upgrade20_l4;

Production judgment and bridge to Lesson 5

Schema evolution is a deployment protocol, not just DDL. Keep rollback cheap during expand/backfill and name the irreversible boundary before contract. Lesson 5 applies the same gate-based thinking to entire replicated or Galera topologies, where mixed-version periods, promotion and performance regressions must be controlled explicitly.

Expand/contract as a multi-release state machine

Backward-compatible schema evolution works because each phase narrows risk. In the expand phase, add new nullable/default-safe columns, tables, or indexes without removing the old contract. Deploy application code that can tolerate both schemas. If data must be transformed, run a resumable backfill with a durable progress key and bounded transactions. Verify counts, constraints, and query plans before switching reads. Only after every old application instance and background worker has drained should the contract phase remove the legacy object.

Dual writing can bridge incompatible representations, but it creates two sources of truth unless ownership is explicit. If both old and new fields are written, decide which value is authoritative, how discrepancies are detected, and how retries remain idempotent. Prefer deriving one representation from the other when practical. Before cutover, run a reconciliation query that proves the new representation is complete and consistent.

Online DDL is an execution property, not a guarantee of zero impact. Record the requested ALGORITHM and LOCK, verify what the target version supports, and observe metadata-lock waiting, I/O, temporary-space growth, redo/binlog generation, replica lag or Galera flow control, and application latency. A statement that allows concurrent DML can still consume enough storage or I/O to violate an SLO.

• Expand rollback: stop using the new object; usually leave the additive object in place until safe cleanup.
• Backfill rollback: pause at a durable checkpoint; design the job so restarting does not duplicate or corrupt work.
• Read cutover rollback: feature-flag reads back to the old representation while both remain populated.
• Contract boundary: after dropping old data/schema, rollback may require restore/reconstruction rather than a code redeploy.

Deployment coordination should name who owns each gate: database migration, backfill observation, application rollout, traffic shift, rollback decision, and post-change cleanup. This is where a technically valid ALTER TABLE becomes a production-safe change process.

Backfill engineering: resumable, monotonic, bounded, and observable

A backfill is production DML and should be designed like a small data-processing system. Choose a stable progress key, process a bounded key range per transaction, and persist the last successfully committed boundary outside transient process memory. On restart, resume from committed progress. Avoid pagination with changing offsets because concurrent writes can shift row positions and cause skips or repeats.

Make the transformation idempotent where possible. Updating only rows that still need conversion lets retries repeat a chunk safely. If the transformation cannot be naturally idempotent, record a migration/version marker or use a staging/reconciliation design. For each chunk measure rows examined/changed, elapsed time, lock waits, redo/binlog generation, replica lag or wsrep pressure, and application latency. Adapt pause/chunk policy from those signals rather than hard-coding a universally “safe” batch size.

Validation belongs between phases. Before switching application reads, prove that every row expected to have the new representation has it and that old/new values agree under the business rule. Before adding a NOT NULL/UNIQUE/foreign-key-like integrity requirement, pre-query for violations so the validation step does not become the first discovery of bad data. Before dropping the old representation, monitor application/query logs or schema-access evidence long enough to prove no supported code path still depends on it.

Write rollback separately for schema and data. Rolling application code back may be easy while reversing a completed backfill is expensive or lossy. If the old field remains populated during the compatibility window, rollback can switch reads back. After the contract phase deletes it, recovery may require restore or a reverse transformation. That is the real destructive boundary.

Metadata-lock rehearsal before production

Use two disposable sessions to reproduce the longest realistic transaction touching the target table, then start the planned DDL and observe whether it waits for a metadata lock. This converts an abstract risk into a measured dependency. Define a lock-wait/kill policy before the change: which session may be terminated, who owns that decision, and what application retry behavior follows. A schema change that succeeds instantly in an idle lab is not evidence that it will begin instantly under production transaction lifetimes.

Change-window stop conditions

Predeclare conditions that pause the migration: unexpected metadata-lock queues, application error spikes, replica/Galera pressure outside the agreed band, temp/disk headroom falling below the working-set estimate, or backfill reconciliation failures. A stop condition is useful only if the operator knows how to pause safely, preserve the last committed checkpoint, and decide whether to resume, roll application behavior back, or invoke restore-based recovery.

Authoritative references

• MariaDB ALTER TABLE documentation for ALGORITHM/LOCK semantics.
• MariaDB metadata locking documentation.
• MariaDB replication/Galera monitoring documentation for operational impact during schema changes.