skip to content

Schema Evolution & Migrations

21 roadmaps10 questions

Changing a schema that's already serving traffic: versioned migration discipline and zero-downtime change patterns. Interviewers ask 'how do you rename a column in production?' to separate people who have only worked on greenfield databases.

part ofRelational database conceptsoverview, primer and where to startread it →
on this pageshow

explore

questions

10

A team keeps its database schema in version-controlled migration scripts run by a tool such as Flyway or Liquibase. How does that tool know which scripts have already been applied to a given database, and what is recorded for each one?

level: juniorimportance: must knowfreq 68%

answer

  1. flyway_schema_history / DATABASECHANGELOG
  2. one row per applied script
  3. version + checksum + success
  4. diff disk vs table, apply the gap
  5. baseline for pre-existing databases

basics

~20 s

A history table inside the same database (Flyway's flyway_schema_history, Liquibase's DATABASECHANGELOG) holds one row per applied script: version or id, description, checksum, timestamp, success flag. Each run diffs the scripts on disk against those rows and applies only the missing ones.

solid answer

~50 s

Versioned migrations are ordered, immutable scripts checked into the repository next to the application code. The tool bootstraps a bookkeeping table in the target database — `flyway_schema_history` for Flyway, `DATABASECHANGELOG` plus a lock table for Liquibase. Each successfully applied script inserts a row holding its version (or id/author/filename), a description, a checksum of the script text, who applied it, when, how long it took, and whether it succeeded. On every run the tool reads that table, compares it with the scripts it discovers on disk or the classpath, and executes only those with no row, in version order — each inside its own transaction where the engine supports transactional DDL. The checksum additionally detects that an already-applied script was edited afterwards, and fails the run rather than silently letting environments diverge. Because the state lives in the database itself, every environment — laptop, CI, staging, production — converges on the same schema starting from whatever version it currently sits at.

code

text · 4 lines
text
installed_rank | version | description        | script                      | checksum   | installed_on        | success
1              | 1       | init schema        | V1__init_schema.sql         | 1839902744 | 2026-01-04 10:02:11 | true
2              | 2       | add orders         | V2__add_orders.sql          | -884213097 | 2026-01-11 09:44:02 | true
3              | 3       | index orders email | V3__index_orders_email.sql  | 2011774310 | 2026-02-02 18:15:30 | true

go deeper

for a junior

Name the history table, say it holds one row per applied script, and explain that the tool runs only the scripts with no row. That is the core recall.

for a middle

Add the checksum and validation step, the ordering rule, per-migration transactions, and the difference between Flyway's version key and Liquibase's id/author/filename triple.

for a senior

Bring in locking during rolling deploys, baselining an existing database, non-transactional DDL leaving a failed row, and shared-database pitfalls.

for a principal

Frame it as making the schema a reproducible derived artifact, discuss ownership boundaries when many services touch one database, and the audit value of installed_by/installed_on for change control.

## The problem being solved Schemas change constantly: new tables, new columns, new indexes, new constraints, data backfills. Without a disciplined mechanism each environment drifts — a developer applies a change by hand on their laptop, someone else runs a slightly different statement on staging, and production ends up with a schema nobody can reproduce. Versioned migrations make the schema a *derived artifact*: the sequence of scripts in the repository is the source of truth, and any database can be rebuilt by replaying them in order. ## The changelog / history table The tool cannot store its state in a file, because the state belongs to a particular database, not to a particular checkout. So it stores it *in the database it manages*. On first run it creates a bookkeeping table, then wraps every later run around it. - **Flyway** creates `flyway_schema_history` with columns roughly: `installed_rank`, `version`, `description`, `type` (SQL/JDBC), `script` (file name), `checksum`, `installed_by`, `installed_on`, `execution_time`, `success`. - **Liquibase** creates `DATABASECHANGELOG` with `ID`, `AUTHOR`, `FILENAME` (the triple that identifies a changeset), `DATEEXECUTED`, `ORDEREXECUTED`, `EXECTYPE`, `MD5SUM`, `DESCRIPTION`, `COMMENTS`, `TAG`, plus a separate `DATABASECHANGELOGLOCK` table used as a mutex. The identity differs — Flyway keys on a monotonic *version* parsed from the filename (`V37__add_orders_index.sql`), Liquibase on the `id + author + changelog file path` triple — but the role is identical: one row means "this unit of change has been applied here". ## The run algorithm 1. Acquire a lock (a lock table, or a session/advisory lock) so two processes cannot migrate the same database simultaneously. 2. Read every row from the history table. 3. Discover the migration scripts available in this build. 4. **Validate**: for every script that already has a row, recompute its checksum and compare. A mismatch means an applied script was edited — the run aborts. Also flag scripts that have a history row but no longer exist on disk ("missing"), and, for Flyway, a *new* script whose version sorts below the highest applied version ("out of order"). 5. Apply the pending scripts in order, each ideally inside a transaction, inserting its history row in the same transaction so that state and effect commit together. 6. Release the lock. The transactional coupling matters: on engines with transactional DDL (PostgreSQL, SQL Server) a failed script rolls back completely and leaves no row, so a retry is clean. On engines where DDL commits implicitly (MySQL, Oracle) a failure can leave the schema half-changed; Flyway records a `success = false` row and demands a manual repair, which is exactly why one migration should do one thing. ## What each field buys you - **Version/id** — determines ordering and identity. - **Checksum** — enforces immutability of applied scripts, the single most important guarantee (see the follow-up below). - **Success flag / execution time** — post-mortem material when a deploy stalls; a 40-minute `ALTER TABLE` shows up here. - **installed_by / installed_on** — audit: who ran what against production, and when. ## Adopting the tool on a database that already exists A live database that predates the tool has no history table and a schema the scripts would try to create from scratch. The answer is **baselining**: point the tool at the existing database and tell it "treat everything up to version N as already applied" (`flyway baseline`, or Liquibase's `changelogSync`). It writes the history rows without executing anything, so only genuinely new scripts run afterwards. ## Repeatable migrations Both tools also support scripts that are *not* run-once. Flyway's `R__` prefix and Liquibase's `runOnChange` re-execute whenever the file's checksum changes, and always after the versioned ones. They suit objects that are declarative and idempotent — `CREATE OR REPLACE VIEW`, functions, stored procedures, reference-data seeds — where re-stating the whole definition is cleaner than diffing it. ## Common failure modes - Someone runs a manual `ALTER` in production; the schema now differs from what the scripts describe, and the next migration fails on an unexpected state. The history table is only as truthful as the discipline around it. - Two applications share one database and each runs its own migrations with the default history table name; they overwrite each other's understanding. Fix: separate schemas or distinct history table names. - A tool version upgrade changes checksum computation, invalidating old rows — hence `flyway repair`, which recomputes checksums for already-applied scripts.

  • What happens the first time you point a migration tool at a long-lived database that already has 200 tables?
    The tool sees an empty (or absent) history table and would try to run every script from version 1, which fails immediately because the objects exist. The correct move is to baseline: record the existing schema as already applied up to some version, so the tool writes history rows without executing anything. From then on only newer scripts run. Liquibase calls the equivalent operation changelog sync.
  • Why do most tools keep a separate lock table or take an advisory lock before migrating?
    Because in a rolling deploy several application instances start at once and each would try to apply the same pending scripts. Without mutual exclusion two instances can run the same DDL concurrently, producing duplicate objects, deadlocks, or a corrupted history. The lock makes exactly one process migrate while the others block and then find nothing pending.
  • How would you handle two services that share one database?
    Give each service its own schema and its own history table, or at minimum configure distinct history table names and non-overlapping object ownership. Sharing one history table across two independently deployed codebases means each deploy sees the other's scripts as missing or out of order, and validation fails.

It is git for the schema, except the commit log lives inside the working copy: the database carries its own list of which patches it has already absorbed, so any tool can pick up from where that database is.

saying these in an interview costs you the question

  • Claiming the tool compares the live schema to the scripts and generates a diff — versioned tools track applied scripts, they do not introspect the schema
  • Thinking the applied-state is stored in a local file or in the repo, so a fresh checkout would re-run everything
  • Believing migrations can be run safely by every app instance at boot with no locking
  • Assuming a failed migration always rolls back cleanly, ignoring engines without transactional DDL

context

resolving links…

open as a page

Walk through changing the representation of an existing column — for example splitting a single full_name column into first_name and last_name — on a busy production table with no downtime. What are the phases, and what must be true of the running application at each one?

level: middleimportance: must knowfreq 58%

basics

~20 s

Expand: add the new columns and make the app write both. Migrate: backfill existing rows in batches, then switch reads to the new columns. Contract: stop writing the old column and drop it. Every step must work with the previous version still running.

open as a page

A developer fixes a typo by editing a migration script that has already been applied to production and to several teammates' local databases. What goes wrong, and what should they have done instead?

level: middleimportance: must knowfreq 62%

basics

~20 s

The stored checksum no longer matches the edited file, so validation fails on every database that already ran it, while a fresh database gets the corrected version — the two diverge. Applied migrations are immutable: revert the edit and ship a new migration containing the fix.

open as a page

Which schema changes can block reads or writes on a large, busy table, and what techniques keep the lock window short enough to be invisible to users?

level: seniorimportance: must knowfreq 52%

basics

~20 s

Anything that rewrites the table or holds an exclusive lock: type changes, most index builds, validating new constraints, some column additions. Use concurrent or online index builds, add-then-validate constraints, a short lock timeout with retries, and batched work.

open as a page

A schema migration reaches production and turns out to be wrong. When is a scripted rollback actually a viable recovery, and when do you instead forward-fix by shipping a new migration?

level: seniorimportance: must knowfreq 55%

basics

~20 s

Rollback is viable only for additive, reversible changes that no code or data depends on yet — usually within minutes. Once a drop or a data transformation ran, or new code wrote data in the new shape, undoing loses information, so you forward-fix with a new migration and, if needed, roll back the application.

open as a page

Why is adding a nullable column to a live table usually safe, while dropping or renaming an existing column is not, given that the previous version of the application is still serving traffic during a deploy?

level: juniorimportance: should knowfreq 45%

basics

~20 s

Old code queries the columns it was written against. A new nullable column is invisible to it and its inserts still succeed. A removed or renamed column breaks those queries the instant the migration lands, while old instances are still running.

open as a page

You need to populate a newly added column for roughly 500 million existing rows while the system stays fully online. How do you design, run and supervise that backfill?

level: seniorimportance: should knowfreq 45%

basics

~20 s

Run bounded batches ordered by primary key, remembering the last key processed, committing each batch, throttling on replication lag and lock waits, and written so re-running is harmless. Never one statement across the whole table.

open as a page

Two feature branches each add a migration numbered V37 and both merge in the same week. What problems does that cause, and how do teams keep migration ordering sane when many people work in parallel?

level: seniorimportance: should knowfreq 45%

basics

~20 s

Duplicate versions either abort the run or, once renumbered, apply in an order nobody tested — and databases already past V37 skip the late arrival. Fixes: allocate versions at merge time (or use timestamp versions), keep migrations independent of each other, and rebuild the schema from scratch in CI on every merge.

open as a page

For a service that deploys many identical application instances, where in the delivery pipeline should database migrations actually execute, and what stops two instances starting at once from corrupting the migration history?

level: seniorimportance: should knowfreq 48%

basics

~20 s

Run migrations once per deploy as a dedicated step — a pipeline job or init container — before the new instances take traffic, using a credential with DDL rights the app itself lacks. If instances do migrate at boot, the tool's lock table or an advisory lock serialises them so exactly one applies and the rest wait.

open as a page

In a migration where the application writes to both an old and a new representation of the same data, how do you keep the two in agreement, and how do you decide it is finally safe to stop writing the old one and remove it?

level: principalimportance: should knowfreq 34%

basics

~20 s

Write both inside one transaction, inventory every writer, and run a continuous comparison that counts disagreements. Contract only after reads have run on the new path with zero divergence for longer than your rollback and slowest-consumer window.

open as a page