What Are Common Database Refactoring Techniques?
Learn key database refactoring techniques like expand-and-contract migrations for evolving live schemas without downtime.
Expected Interview Answer
Database refactoring techniques are small, behavior-preserving changes to a schema — such as expand-and-contract column changes, splitting a table, or introducing a bridging view — applied incrementally and safely to a live database without breaking running application code.
Because a database usually cannot be taken offline for a schema change the way a code deploy can, refactoring techniques favor multi-step migrations: the expand-and-contract pattern adds a new column or table alongside the old one, dual-writes to both while application code migrates over, backfills historical data, then only drops the old structure once every reader has cut over. Other core techniques include splitting an overloaded table into related tables, merging duplicated tables, renaming a column via a temporary view or synonym, and introducing a foreign key gradually by first validating existing data before enforcing the constraint. The unifying principle is that each step is small, reversible, and never assumes every consumer of the schema deploys simultaneously.
- Allows schema evolution without downtime or a big-bang cutover
- Reduces risk because each step is small and independently reversible
- Keeps old and new consumers working simultaneously during migration
- Makes rollback straightforward if a step reveals a problem
AI Mentor Explanation
Refactoring a database schema is like resurfacing a stadium's outfield section by section during a season, rather than closing the whole ground at once. Groundskeepers relay one section, let it settle and get used, then move to the next, so matches keep being played on either the old or new surface throughout. Expand-and-contract schema changes work the same way: the old and new structures coexist while the cutover happens gradually.
Step-by-Step Explanation
Step 1
Expand
Add the new column, table, or constraint alongside the existing structure without removing anything yet.
Step 2
Dual-write and backfill
Update application code to write to both old and new structures, and backfill historical rows into the new structure.
Step 3
Migrate readers
Gradually switch each consumer to read from the new structure, verifying correctness at each step.
Step 4
Contract
Once every reader and writer has migrated, drop the old column, table, or constraint to complete the refactor.
What Interviewer Expects
- Knowledge of the expand-and-contract pattern by name or in substance
- Understanding that schema changes must support old and new consumers simultaneously
- Awareness of concrete techniques: splitting/merging tables, backfilling, validating constraints before enforcing
- Recognition that each step should be small and reversible
Common Mistakes
- Proposing a single big-bang ALTER TABLE as the whole answer
- Forgetting the backfill step for historical data
- Not mentioning that old and new schema versions must coexist during migration
- Skipping validation of existing data before enforcing a new constraint
Best Answer (HR Friendly)
“I refactor live database schemas using an expand-and-contract approach: add the new structure first, have the application dual-write to both old and new, backfill historical data, migrate every reader over, and only then drop the old structure. Breaking this into small, reversible steps means I never need a risky big-bang cutover that could take the whole system down.”
Code Example
-- Step 1: Expand — add the new column alongside the old one
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
-- Step 2: Backfill existing rows into the new column
UPDATE users SET email_address = email WHERE email_address IS NULL;
-- Step 3: Application dual-writes both columns during the transition,
-- then switches reads over to email_address once verified.
-- Step 4: Contract — drop the old column once nothing reads it anymore
ALTER TABLE users DROP COLUMN email;Follow-up Questions
- How would you add a NOT NULL constraint to a column on a large, live table with zero downtime?
- How do you safely split a table that is being actively read and written in production?
- What is the risk of skipping the dual-write phase during a schema migration?
- How would you roll back a schema refactor partway through if a problem is discovered?
MCQ Practice
1. What is the core idea of the expand-and-contract database refactoring pattern?
Expand-and-contract adds new structures alongside old ones, migrates consumers incrementally, and only removes the old structure once nothing depends on it.
2. Why is a backfill step necessary during many schema refactors?
Backfilling ensures rows created before the migration also have valid data in the new structure, not just newly written rows.
3. What is a safe way to add a NOT NULL constraint to a large, actively used table?
Checking existing data first (and often adding the constraint as NOT VALID before validating separately) avoids locking issues and unexpected failures on live tables.
Flash Cards
What is the expand-and-contract pattern? — Add the new schema structure first, migrate consumers gradually, then remove the old structure last.
Why dual-write during a migration? — So both old and new consumers keep working correctly while the cutover happens gradually.
Why backfill during a refactor? — To populate the new structure with historical data for rows written before the migration began.
Core principle of database refactoring? — Each step should be small, reversible, and never assume every consumer deploys simultaneously.