Versioned Schema Migrations, Expand/Contract Patterns, and Backward Compatibility

Learning outcomes

ServiceHub now deploys multiple application instances, so a schema change cannot be treated as a single instantaneous event. Old code may still be processing requests while new code starts, a backfill may run for a long period, and rollback may require the old representation to remain usable. The practical engineering problem is therefore release compatibility, not merely writing a syntactically valid ALTER TABLE.

Versioned migrations are release artifacts

A schema migration moves the database from one known structural state to another. A reliable migration is ordered and immutable after application. If an operator edits an already-applied file, its checksum changes and the release system should report drift rather than silently rewriting history. This is the same reason source-control commits and container images are identified precisely: rollback and diagnosis depend on knowing what actually ran.

DROP DATABASE IF EXISTS servicehub_change_lab;CREATE DATABASE servicehub_change_lab  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;USE servicehub_change_lab;CREATE TABLE schema_migrations (  version_no BIGINT PRIMARY KEY,  migration_name VARCHAR(180) NOT NULL,  checksum_sha256 CHAR(64) NOT NULL,  owner_name VARCHAR(100) NOT NULL,  status ENUM('applying','applied','failed') NOT NULL,  started_at DATETIME(6) NOT NULL,  finished_at DATETIME(6) NULL,  UNIQUE KEY uq_migration_name (migration_name)) ENGINE=InnoDB;CREATE TABLE work_orders (  work_order_id BIGINT PRIMARY KEY,  tenant_id BIGINT NOT NULL,  customer_id BIGINT NOT NULL,  status VARCHAR(24) NOT NULL,  summary VARCHAR(240) NOT NULL,  opened_at DATETIME(6) NOT NULL,  KEY ix_tenant_opened (tenant_id, opened_at, work_order_id)) ENGINE=InnoDB;INSERT INTO work_orders VALUES(1001,17,501,'open','Pump vibration inspection','2026-08-15 08:10:00.000000'),(1002,17,502,'waiting','Replace pressure sensor','2026-08-15 09:15:00.000000');

The ledger is deliberately simple so the mechanism is visible. Real migration frameworks usually maintain an equivalent metadata table. A failed migration is not repaired by editing history; create a new corrective migration and preserve the old evidence.

Expand first so old code can ignore the new capability

ServiceHub wants a human-facing public_reference. Renaming the primary key or immediately adding a required non-null column would force every application instance to switch at the same moment. The expand phase adds capability while preserving the old contract.

SET @version = 2026081601;SET @checksum = '4d1130d0f5e69f5be3a0b975dc943246d2615fc74da1fe2042ef1d21f9a92d0e';INSERT INTO schema_migrations(version_no,migration_name,checksum_sha256,owner_name,status,started_at)VALUES (@version,'add_public_reference',@checksum,'servicehub-platform','applying',NOW(6));ALTER TABLE work_orders  ADD COLUMN public_reference VARCHAR(40) NULL,  ALGORITHM=INSTANT;UPDATE schema_migrationsSET status='applied', finished_at=NOW(6)WHERE version_no=@version;SHOW CREATE TABLE work_orders\GSELECT * FROM schema_migrations ORDER BY version_no;

Using ALGORITHM=INSTANT is a fail-fast availability contract on this baseline. If the operation cannot use that exact algorithm, MySQL rejects it rather than silently choosing a more disruptive alternative. The old application still sees all columns it expects.

Old and new application versions overlap during deployment

During the overlap, old code inserts without the new field. New code writes it when available. Then a bounded backfill fills historical rows. This makes compatibility observable rather than theoretical.

-- Old application shape.INSERT INTO work_orders(work_order_id,tenant_id,customer_id,status,summary,opened_at)VALUES (1003,17,503,'open','Legacy client request',NOW(6));-- New application shape.INSERT INTO work_orders(work_order_id,tenant_id,customer_id,status,summary,opened_at,public_reference)VALUES (1004,17,504,'open','New client request',NOW(6),'WO-001004');-- Repeat in bounded batches on a production-sized table.UPDATE work_ordersSET public_reference = CONCAT('WO-', LPAD(work_order_id,6,'0'))WHERE public_reference IS NULLORDER BY work_order_idLIMIT 100;SELECT work_order_id,public_reference,summaryFROM work_orders ORDER BY work_order_id;

Expected state: every row has a reference after the backfill, while the old write shape still succeeded. At this point new code can switch reads to the new column, but contract is not yet justified merely because the data is ready.

Wrong approach: destructive rename or drop in the same release

A tempting one-step deployment renames/drops the legacy column and immediately deploys new binaries. If old instances still serve traffic, they fail with “unknown column.” The DDL can succeed perfectly while the release fails. The repair is staged compatibility: expand, deploy compatible code, backfill, verify no old version remains, then contract.

SELECT COUNT(*) AS rows_missing_referenceFROM work_orders WHERE public_reference IS NULL;SELECT version_no,migration_name,checksum_sha256,statusFROM schema_migrations ORDER BY version_no;-- Deliberately deferred until fleet and rollback gates pass:-- ALTER TABLE work_orders DROP COLUMN legacy_column, ALGORITHM=INSTANT;

Migration acceptance matrix

Store migration version, application version, and change-window annotations in deployment telemetry. The operator should be able to answer which application version expects which schema state without reconstructing it from memory.

What MySQL is doing during expand and backfill

The expand DDL changes the table definition that MySQL stores in its transactional data dictionary. For an operation that truly qualifies for ALGORITHM=INSTANT, InnoDB does not rewrite every existing record simply to add the nullable column. That is why the operation can be fast even on a large table. The database still needs metadata locks around the definition change, however, and future row versions must understand the new definition. The important production distinction is therefore row rewrite cost versus metadata coordination; eliminating the first does not eliminate the second.

The backfill is ordinary transactional DML. Each batch generates undo needed for transaction rollback and multiversion concurrency control (MVCC), redo needed for crash recovery, and—when binary logging is enabled—binary-log events needed for replication and point-in-time recovery. A single enormous update can therefore create long transactions, history-list growth, lock pressure, redo bursts, and replica lag. Batching is not magic: it is a way to place explicit boundaries around that work so operators can observe progress, pause, retry, and avoid monopolizing resources.

Detect migration drift before applying anything new

A migration ledger is valuable only if the release process compares the recorded checksum with the artifact it is about to trust. The following lab simulates drift without editing production history. It does not change the stored checksum; it merely proves that a mismatch can be detected and converted into a hard deployment failure.

SET @artifact_checksum = '0000000000000000000000000000000000000000000000000000000000000000';SELECT version_no, migration_name, checksum_sha256 AS recorded_checksum,       @artifact_checksum AS artifact_checksum,       checksum_sha256 = @artifact_checksum AS checksum_matchesFROM schema_migrationsWHERE version_no = 2026081601;

Expected result: checksum_matches is 0. That result does not tell you which copy is correct; it tells you the release history and artifact disagree. The safe action is to stop, recover the original artifact from source control/build storage, and investigate. Updating the ledger to make the mismatch disappear destroys evidence.

Production rollout sequence and monitoring signals

A mature expand/contract release has explicit gates between phases. During expand, watch metadata-lock waits and DDL errors. During dual-compatible deployment, compare error rates between old and new application versions and verify both write shapes. During backfill, record rows completed, remaining rows, transaction duration, redo/binlog growth, replica lag, lock waits, and host I/O. During contract, verify old-version instance count is zero and that rollback no longer requires the legacy representation.

Do not use a universal batch size or deployment delay. The correct batch size depends on row width, indexes, storage latency, transaction concurrency, replication topology, and the service-level objective. Start conservatively, measure, and change one variable at a time.

Hands-on verification before moving to Lesson 2

Re-run the old insert shape after the expand migration, verify the new insert shape, ensure the backfill has no remaining null references, and compare the stored migration checksum with the artifact checksum. Then open a second session and confirm ordinary reads still work while no long backfill transaction remains. Record the exact server version and schema definition with SELECT @@version and SHOW CREATE TABLE. The lab is successful only when the evidence shows compatibility, not merely when every command returned “Query OK.”

Authoritative references

• MySQL 8.4 — Upgrading MySQL
• MySQL 8.4 — Upgrade Paths
• MySQL 8.4 — Upgrade Best Practices
• MySQL Shell 8.4 — Upgrade Checker Utility
• MySQL 8.4 — InnoDB and Online DDL
• MySQL 8.4 — Upgrading Group Replication
• MySQL 8.4 — Downgrading MySQL
• MySQL 8.4 — Native Authentication Plugin
• MySQL Community Server 8.4 Downloads
• MySQL Shell 8.4 Downloads