Design Database Migration Pipeline - System Design | WinJob

Problem Statement: Database Migration Pipeline

Design a database migration pipeline that applies versioned schema changes with backward compatibility, pre-flight checks, and rollback paths—patterns used by Flyway, Liquibase, and PlanetScale-style online schema workflows.

Problem framing

Migrations live in Git; CI validates SQL, checksums, and ordering before any apply
Staging runs full migration against anonymized prod-sized data; prod requires approval + lock budget
Expand-contract is default: add nullable columns first, dual-write, flip reads, then contract
Target: p99 migration orchestration API < 200ms; DDL lock wait alerts before p95 query latency degrades

Design choices

Immutable migration versions with checksum pinning—never rewrite applied scripts
Online DDL tools (gh-ost, pt-online-schema-change) for large tables; native DDL only when proven safe
Schema registry blocks deploy if app expects columns migrations have not yet expanded
Audit table records who applied what, when, and from which pipeline run id

Deep dive

For Problem Statement: Database Migration Pipeline, explain how Flyway flyway_schema_history, Liquibase DATABASECHANGELOG, and Vitess/VSchema workflows differ. Cover destructive-change gates, shadow traffic validation after DDL, and why down migrations are often disabled in prod in favor of forward repair scripts.

javaOne Dark Pro

1 public final class MigrationGate {
2   public boolean canApply(String env, int pendingDestructive, boolean lockOk) {
3     if (!lockOk) return false;
4     return !"prod".equals(env) || pendingDestructive == 0;
5   }
6 }

pythonOne Dark Pro

1 from dataclasses import dataclass
2  
3 @dataclass
4 class MigrationRun:
5     version: str
6     checksum: str
7     dry_run: bool
8  
9 def should_apply(run: MigrationRun, last: str | None) -> bool:
10     return run.version != last and not run.dry_run

typescriptOne Dark Pro

1 interface MigrationRun {
2   version: string;
3   checksum: string;
4   dryRun: boolean;
5 }
6  
7 export function shouldApply(run: MigrationRun, last: string | null): boolean {
8   return run.version !== last && !run.dryRun;
9 }

Interview positioning

State explicit RPO/RTO for schema: rollback is usually forward-fix, not down.sql on prod. Quantify lock sensitivity (metadata locks on MySQL, ACCESS EXCLUSIVE on Postgres) and how you throttle concurrent migrations per shard.

How to open this one

The framing that signals depth on a database-migration pipeline is the expand/contract pattern under zero-downtime: schema changes ship in backward-compatible steps so old and new code run against one schema during a rollout. Lead with forward-only migrations gated in CI and the failure story that proves it: a destructive ALTER shipped in lockstep with code makes rollback impossible and locks a hot table. That shows you understand migrations are about decoupling schema change from deploy, not running ALTER on deploy.

Key Highlights

•Versioned migrations are immutable after apply
•Expand-contract is the default zero-downtime pattern
•Lock budgets and online DDL tools protect prod queries
•Forward repair beats destructive down.sql in prod

1	public final class MigrationGate {
2	public boolean canApply(String env, int pendingDestructive, boolean lockOk) {
3	if (!lockOk) return false;
4	return !"prod".equals(env) \|\| pendingDestructive == 0;
5	}
6	}

1	from dataclasses import dataclass
2
3	@dataclass
4	class MigrationRun:
5	version: str
6	checksum: str
7	dry_run: bool
8
9	def should_apply(run: MigrationRun, last: str \| None) -> bool:
10	return run.version != last and not run.dry_run

1	interface MigrationRun {
2	version: string;
3	checksum: string;
4	dryRun: boolean;
5	}
6
7	export function shouldApply(run: MigrationRun, last: string \| null): boolean {
8	return run.version !== last && !run.dryRun;
9	}