Evolving a MySQL Schema Safely — From Development to Release

Adding a column sounds like the simplest change you can make to a database. It becomes dangerous the moment the code that reads the new column and the code that doesn't are running at the same time — which, in a rolling deployment, is every release.

This article walks one simplified schema through a realistic evolution, adding fields one at a time, and shows what has to happen on the development side and the release side for each one to be safe.

The example — a small e-commerce orders table:

CREATE TABLE orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL
);

Two rules everything else follows

Backward compatibility is the whole game, and it reduces to two rules:

1. Old and new code run simultaneously. A rolling deploy means version N and version N+1 of your service are live at the same time. Every schema change has to keep both working.
2. A change is backward compatible if old code still works on the new schema. If old code runs INSERT INTO orders (id, user_id, ...) without naming your new column, that column has to be nullable or have a default — otherwise the old INSERT breaks.

From those two rules the classification follows:

If it's in the "yes" column, ship the schema and the code in whatever order is convenient. If it's in the "no" column, you need the expand-contract dance from the section after next.

Adding a field safely (the common case)

Most schema evolution is just "add a field," and most of the time it's safe if you do it in the right order. Business need: record a coupon code on an order.

ALTER TABLE orders
  ADD COLUMN coupon_code VARCHAR(50) NULL,
  ALGORITHM=INSTANT, LOCK=NONE;

The NULL is the part that matters. Old code that doesn't know about coupon_code inserts without it, the column fills with NULL, and nothing breaks. A DEFAULT works the same way.

The ALGORITHM=INSTANT, LOCK=NONE is about not blocking the table while it changes. MySQL picks an algorithm automatically, but it can silently fall back to a COPY that locks the table and copies every row — which on a large table is an outage. Specifying the algorithm and lock explicitly makes MySQL fail fast instead of silently degrading. For adding a nullable column, INSTANT is metadata-only and finishes immediately regardless of table size.

The release-side order is simple: schema first, then code. Ship the migration, then ship the code that reads coupon_code. There's never a moment where code references a column that doesn't exist yet.

When the field can't just be added (expand-contract)

The interesting case is when you need to change something that already exists rather than add something new. Renaming a column, changing its type, or replacing one field with another is a breaking change — and breaking changes have to be decomposed into backward-compatible steps.

Business need: status is a free-form VARCHAR, and the team wants it as a controlled integer so the application can't write garbage. A direct ALTER TABLE ... MODIFY status TINYINT is off the table — old code is still writing 'PENDING' and 'PAID' while new code would write 0 and 1.

The change runs in three phases.

Phase 1 — Expand. Add the new column alongside the old one, in a way old code tolerates:

ALTER TABLE orders
  ADD COLUMN status_code TINYINT NOT NULL DEFAULT 0;

The DEFAULT 0 is what keeps old INSERTs working: they don't name status_code, so it fills with the default. New code starts writing both status and status_code.

Phase 2 — Backfill. Copy existing data from old to new in batches, so you neither lock the table nor flood the binary log:

UPDATE orders
   SET status_code = CASE status
       WHEN 'PENDING' THEN 0
       WHEN 'PAID'    THEN 1
       WHEN 'SHIPPED' THEN 2
       ELSE 0 END
 WHERE status_code = 0
 LIMIT 10000;

Run it repeatedly until no rows change. During the transition window, both columns stay in sync — either by the application dual-writing, or by a trigger.

Phase 3 — Contract. Only after every service instance runs the new code do you drop the old column:

ALTER TABLE orders DROP COLUMN status;

The trap that makes this necessary is subtle. If you drop status while any old instance is still deployed, that instance fails the moment it tries to write status. The contract step can only happen when the rollout is 100% complete — which means you have to know when that is. That's why the schema change and the code change are two separate deploys, never one.

The rule to internalize: a rename is an add followed by a delete, and the delete waits for the deployment to finish.

Development-side discipline

Most of the safety is decided before anything reaches production, in how the migration is written and reviewed.

• Version your migrations and run them through a tool — Flyway or Liquibase. Hand-run ALTER TABLE statements leave no record of what changed and won't run consistently across environments.
• Make migrations backward compatible by construction. Add as nullable or with a default first. Never rename or drop in the same change that adds.
• Test against both code versions. Run the old code against the new schema and confirm old INSERTs and SELECTs still work. Run the new code against the old schema too. If either direction breaks, the change isn't compatible.
• Rehearse on a production-sized copy. An ALTER that takes milliseconds on a dev table can take hours on a table with millions of rows. The behavior of online DDL changes completely with scale, so test at scale.

Release-side discipline (keeping it online)

On the deployment side, the concerns are about the table itself and about not blocking traffic.

• Prefer INSTANT/INPLACE. For "add a nullable column," INSTANT is instant. Reserve heavier approaches for heavier changes.
• Reach for gh-ost or pt-online-schema-change when the change is large. For a type change or a table rebuild on a big table, native online DDL can still cause replication lag and hold metadata locks long enough to matter. These tools build a shadow table, copy in small throttled chunks, sync ongoing writes, then atomically swap. gh-ost reads the binary log instead of using triggers, which makes it safer on high-write tables. Both throttle on replica lag.
• Watch replication lag during the change. The migration has to complete on the primary before it runs on replicas; if replicas fall behind, your read replicas serve stale data.
• Keep rollback cheap. Catching a bad migration early usually means renaming back before the contract phase. Once the old column is dropped, there's no cheap rollback — another reason the contract phase belongs at the very end.

One detail worth knowing if you do a lot of these: MySQL 8.0 caps instant DDL changes at 64 versions per table. If you're constantly adding columns with INSTANT, check TOTAL_ROW_VERSIONS in INFORMATION_SCHEMA.INNODB_TABLES and run OPTIMIZE TABLE as you approach ~50.

A checklist to keep it straight

For any new field or schema change, in order:

1. Classify it — safe (add nullable / default / index) or breaking (rename / drop / type change).
2. Safe → write the migration, ship schema first, then code.
3. Breaking → expand (add alongside, with default) → backfill in batches → deploy new code fully → contract (drop old).
4. Large table → use gh-ost / pt-osc, throttle on replica lag, rehearse at production scale.
5. Never rename or drop in a single deployment.

The Bottom Line

Adding fields to a live database is safe as long as you remember that two versions of your code always overlap. Everything hard about schema evolution comes from that overlap.

Add fields as nullable or with defaults so old code keeps working. Break destructive changes into expand-contract so nothing is removed while old code still references it. Treat the "drop the old column" step as the last thing you do, only after the rollout is fully complete.

The discipline is mostly a refusal to take shortcuts: don't rename in one step, don't ship code that references a column before the schema exists, and don't let the table lock while you change it.