The Labs / Data
DataWorking~5h
Add a column without taking the site down
A safe schema migration for a live table: you add a new nullable column, backfill it in batches without locking the table or blocking writes, then prove that both an 'old' reader (unaware of the column) and a 'new' reader (using the column) work correctly at every point during the migration — including mid-backfill.
它证明了什么
你可以在面试中捍卫的一句话。
简历亮点
Executed a zero-downtime schema migration (expand/backfill/contract) on a live table — batched backfill, dual-read compatibility, and a test suite proving old and new code paths both work throughout the rollout.
- Understands why 'just add a NOT NULL column' breaks production at scale
- Can sequence a migration so two versions of the app stay compatible mid-deploy
- Batches a backfill instead of running one table-locking UPDATE
简介
你逐步构建的内容。
- 01Phase 1 (expand): add the new column as nullable, with no default that forces a table rewrite.
- 02Phase 2 (backfill): populate the new column in small batches (e.g. 1,000 rows at a time) with a short pause between batches, never holding a long-running transaction or table lock.
- 03During backfill, an 'old' reader (SELECT without the new column) must keep working unmodified.
- 04During backfill, a 'new' reader (SELECT that treats NULL-in-new-column as 'not yet backfilled' and falls back) must return correct results for both backfilled and not-yet-backfilled rows.
- 05Phase 3 (contract): once 100% backfilled and verified, add the NOT NULL constraint (or equivalent) and drop the fallback path.
- 06Every phase must be independently revertible without data loss.
证明
当这些通过时，即完成。
Running the old query against the table mid-backfill returns the same row count and columns as before the migration started
automated test
Running the new query mid-backfill returns correct values for backfilled rows and a defined fallback for not-yet-backfilled rows (no NULLs leak into consumer logic)
automated test
The backfill runs in bounded batches — no single query touches more than the configured batch size
automated test
After Phase 3, the NOT NULL constraint holds and 100% of rows have a non-null value
automated test
技术栈
PostgreSQLPython or SQL migration scriptsa migration tool (Alembic/Flyway-style, or hand-rolled)
Sage Method
frame → map → decide → prove
你将保留
A phased migration script set (expand/backfill/contract) + a dual-reader compatibility test suite