Database migration for systems analyst loops

Why interviewers love this question

At Stripe, Notion, Linear, Airbnb, and every other company that runs a real database in production, one bad migration can take the product down for hours. That is why database migration is standard fare in systems analyst loops — not as a trivia question, but as a forcing function to see whether you understand schema change as a deploy artifact.

The prompt usually comes in one of three flavors: "How would you add a column to a 500 GB table without downtime?", "Walk me through renaming a column referenced by ten services", or "Your team needs to change user_id from INT to BIGINT — how do you ship it?". All three are the same question. The interviewer wants you to reason about expand-contract, backwards-compatibility windows, and the difference between DDL that takes a lightweight lock and DDL that rewrites the whole table.

Good candidates do not start with the SQL. They start with the rollout plan — which services read the old shape, which write the new, how long the dual-write window is, and what the rollback looks like at each step. The SQL is the easy part.

Schema versioning the right way

Schema is code. That line is older than most engineers in the room, but interviewers still ask it because half the candidates show up assuming the DBA owns the schema and they just write SELECTs against it.

Every change to the database lives in a versioned file in git, applied in order, tracked in a metadata table. That is the contract. Standard migration runners all enforce the same pattern:

Each migration is an incremental file with a strict naming convention:

V001__create_users.sql
V002__add_email_index.sql
V003__add_phone_column.sql
V004__backfill_phone_from_profile.sql

The runner records applied versions in a metadata table (commonly schema_migrations or flyway_schema_history) and refuses to re-apply or skip. This is what candidates miss when they sketch a "just SSH in and ALTER" plan — no audit trail, no rollback story, no way to reproduce staging.

The expand-contract pattern

If you remember one pattern from this article, make it expand-contract. It answers almost every "change schema X without downtime" question because it decouples the schema change from the code change.

The mental model has three phases. Expand — add the new structure (column, table, index) while leaving the old in place. Old code keeps working. Migrate — update application code to dual-write and backfill historical rows so the new structure matches reality. Contract — once every reader has switched and dual-writes have stabilized, drop the old structure.

Canonical example: renaming column name to full_name on a users table read by twelve services and written to by four.

-- Step 1 (expand): add the new column, nullable, no default rewrite
ALTER TABLE users ADD COLUMN full_name TEXT;

-- Step 2 (backfill, in batches to avoid long locks)
UPDATE users
SET full_name = name
WHERE full_name IS NULL
  AND id BETWEEN 0 AND 100000;
-- repeat in chunks via a job

-- Step 3 (migrate): app dual-writes, then switches reads to full_name
-- (this is a code deploy, not SQL)

-- Step 4 (contract): drop the old column once nothing reads it
ALTER TABLE users DROP COLUMN name;

Each step is an independent deploy. Rollback at any step is safe because the previous state is still valid. If step 3 surfaces a bug where one service still reads name, you redeploy that service — you do not roll back the schema.

This pattern shows up in every senior loop because it forces the candidate to talk about the application layer, not just the database. You cannot rename a column atomically across a fleet of running pods — you stage the change so any instant in time has a working interpretation of the data.

Backwards-compatible vs unsafe DDL

Half the interview answer is knowing which DDL is cheap and which rewrites the table. Dialect-specific — what's safe in Postgres 14 may be unsafe in MySQL 5.7 — but categories generalize.

The trap is conflating "this DDL doesn't rewrite the table" with "this change is safe to deploy". Renaming a column is metadata-only in both Postgres and MySQL — the table never moves a byte. But every service that still references the old name breaks the instant the rename commits. The database is happy. Your readers are not.

The fix is SET lock_timeout = '2s' at the start of every migration with retry-and-backoff. If you can't acquire the lock in two seconds, abort and try later — better a failed migration than a 30-minute stall.

Zero-downtime techniques

Once a table is over 100 GB or 1 billion rows, even "safe" DDL like adding an index can take hours. Native ALTER TABLE is out — you need an online schema change tool that copies data in the background while reads and writes keep flowing.

The three tools interviewers expect you to name are pt-online-schema-change (Percona, MySQL), gh-ost (GitHub, MySQL, binlog-based instead of triggers), and pg_repack (Postgres, rewrites without ACCESS EXCLUSIVE). All three work the same way: create a shadow table with the new schema, copy data in small batches, replay changes that landed during the copy, swap atomically.

Beyond table-rewrite tools, there is the read-replica failover approach: apply the slow migration on a replica, let it catch up, promote it, then re-apply on the old primary. Works when the migration is locking but doesn't require a coordinated app change.

There is also feature-flagging the read path. The schema change happens behind a flag — old code reads the old column, new code reads the new, and you gradually shift traffic. Teams that ship migrations daily have built this into their framework.

Tools by stack

Pick the right toolbox for your audience. At a Python shop, naming Alembic over Flyway shows you know the stack; at Stripe-scale, you should be talking about gh-ost and feature flags, not Liquibase XML.

For application schema migrations: Flyway and Liquibase dominate Java; Alembic dominates Python; golang-migrate and dbmate are lightweight cross-language picks; Django and Rails ship runners most teams adopt by default.

For online table rewrites: gh-ost and pt-online-schema-change on MySQL; pg_repack and pg_squeeze on Postgres.

For the warehouse, dbt owns the layer where schema evolves through model refactors, snapshots, and tests rather than ALTER statements. Snowflake and BigQuery handle most type changes without a rewrite, so the DWH conversation focuses on contract testing and schema evolution more than locking.

Common pitfalls

The most common pitfall is deploying the migration and the code change in the same commit. It feels efficient. In practice there is no instant where you can roll back code without reverting schema, and reverting schema is what causes outages. Ship the schema change in one deploy, watch it settle, then ship the code that depends on it.

A second trap is forgetting that defaults can rewrite the table. In Postgres before 11, ADD COLUMN ... DEFAULT 'x' rewrote every row. After 11, only volatile defaults rewrite. Candidates who say "I just add a default" without naming the version trip up senior interviewers. Add the column nullable, backfill in batches, then add the default and NOT NULL in a separate migration.

A third pitfall is letting the backfill run as one giant UPDATE. On a billion-row table, that one statement holds locks, fills WAL, and blocks autovacuum. Break it into batches of 10,000 to 100,000 rows with a sleep between batches, commit after each batch, and track progress in a side table so the job is restartable. This is the kind of detail that separates a candidate who has done one migration from one who has done fifty.

A fourth and underrated pitfall is migration ordering across services. If service A writes to the new column and service B still reads only the old one, the data is fine in the database but user-visible behavior is broken. Publish the migration plan as a sequence: which service deploys first, what each one writes and reads at each step, and what the rollback target is.

Finally, claiming "zero downtime" without defining it. Zero application downtime is achievable; zero query downtime — including the brief table-swap pause from gh-ost — is not. The honest answer is sub-second swap with no failed user requests, not literal zero.

Related reading

• ACID isolation levels for systems analyst interviews
• API contract testing for systems analyst interviews
• CI/CD pipelines for systems analyst interviews
• Capacity planning for systems analyst interviews
• Background jobs for systems analyst interviews

To drill schema-change scenarios like this with realistic case prompts, NAILDD ships 500+ systems analyst questions across exactly these patterns.

FAQ

What is the difference between a migration and a deploy?

A deploy ships code; a migration ships a schema change. In a healthy workflow they are decoupled — the migration goes out first, sits in production for at least one deploy cycle, then the code that depends on it ships. Coupling them is convenient in dev but dangerous in prod.

When should I use expand-contract vs a direct DDL change?

Use expand-contract whenever more than one service touches the table, or whenever the table is large enough that a direct change holds locks longer than your application can tolerate. For internal admin tables with a single writer and a few hundred rows, a direct ALTER TABLE inside a maintenance window is fine. For any customer-facing table, expand-contract is the default.

How do I rollback a migration in production?

You don't rollback the SQL — you roll forward with a new migration that undoes the damage. Rollback scripts work in development because the data is throwaway. In production they are dangerous because the data has moved on; reversing a column drop does not restore the data. Write a new migration that fixes the previous one, and treat the deploy pipeline as your time machine.

Are ORM-generated migrations safe?

They are safe for adding tables, columns, and most indexes. They are unsafe for renames, type changes, and constraint additions on large tables because the ORM generates the naive DDL, not the expand-contract version. Senior teams let the ORM autogenerate the migration as a starting point, then a human edits it for production-safe phasing before it merges.

How does this question differ from a data engineer interview?

A systems analyst interview focuses on the rollout plan and service coordination — who reads the column, what the dual-write window looks like, how the change appears in a design doc. A data engineer interview on the same topic goes deeper into lock semantics, vacuum pressure, replication lag, and WAL growth. Both should know expand-contract, but the SA conversation lives one layer above the storage engine.

What should I say if asked "have you done a real migration in production"?

Tell it as a story: what the change was, the table size, the tool, the dual-write window, what almost went wrong, what you would do differently. Interviewers prefer one detailed war story over a textbook recitation. The numbers — table size, lock time, batch size, backfill duration — are what make it credible.