JobCannon
全部技能

Database Migration Strategies

⬢ 層級 3技術
+$30–50k
薪資影響
5 個月
學習時長
中等
難度
2
職業
一覽

Database migrations are how teams safely evolve production schemas. Expert patterns: expand-contract (add column, backfill, drop), dual-write (shadow traffic), blue-green (switch databases), online DDL tools (gh-ost, pt-online-schema-change). Career path: Junior (basic migrations, Liquibase, 4-6 months) → Senior (zero-downtime orchestration, $135-185k). Certification: AWS DBS Specialty, Liquibase certified, Flyway docs.

什麼是 Database Migration Strategies

Database migrations are controlled schema and data evolution: adding columns, changing types, creating indices, backfilling data, and dropping old schema, all without downtime. The stakes are high: a bad migration can take production down, corrupt data, or cause prolonged recovery. Strategies span offline (downtime-required, simple rollback) to online (continuous traffic, complex tooling), frameworks (Liquibase, Flyway, Prisma Migrate), and deployment patterns (expand-contract, blue-green databases, shadow writes). Large-scale systems (100GB+ tables, billions of rows) require specialized tools (gh-ost, pt-online-schema-change, AWS DMS) and careful orchestration. Backend engineers who master migration safety command +$30–50k premium over those who cause production incidents. The salary impact is moderate (+$10–20k) but concentrated in senior/staff engineer roles where schema management defines infrastructure reliability. At scale (>$1B revenue), a single poorly-planned migration affecting 10k+ users = customer churn, SLA violation, and C-suite stress. Companies now hire "data platform" or "database reliability" engineers specifically to design safe migration frameworks. Stripe, Uber, Airbnb published case studies on zero-downtime migration patterns; mastering these patterns is expected for infrastructure-track engineers.

🔧 工具與生態
LiquibaseFlywayPrisma migrateDrizzle migrategh-ostpt-online-schema-changeAWS DMSAzure DMSStriimDebeziumFivetranAirbyteSchemachange

💰 各地區薪資

地區初級中級高級
USA$105,000$155,000$210,000
UK£65,000£95,000£140,000
EU€75,000€110,000€155,000
CANADACA$115,000CA$165,000CA$225,000

🎯 使用 Database Migration Strategies 的職業

Backend Developer
Database Administrator

❓ 常見問題

Online vs offline migrations, what's the difference?
Offline: downtime required, lock tables, simple rollback. Online: serves traffic during migration, uses triggers/dual-write/shadow reads, more complex but zero-downtime. For large tables (> 100GB): always online. Tools: gh-ost, pt-osc (MySQL), Liquibase online config. Offline works for small tables or during maintenance windows. AWS DMS handles large cross-database migrations online with CDC (Change Data Capture).
How do dual-write patterns work?
Write new schema + old schema in parallel for 1-2 days, read from old schema. Verify new schema data matches. Flip reads to new schema. Drop old schema. Catches data inconsistencies before cutover. Risk: code complexity, schema mismatch bugs. Alternative: expand-contract (safer, slower).
Blue-green migration strategy, when should I use it?
Set up identical database clusters (blue=live, green=standby). Migrate green while blue serves traffic. Test green thoroughly. Flip traffic to green. Keep blue as rollback for 24h. Best for: cross-database migrations (Postgres→MySQL), major version upgrades, schema overhauls. Cost: 2x database cost during migration. Trade-off: safest approach, highest cost.
What's the expand-contract pattern?
Phase 1: ADD COLUMN new_column. Phase 2: backfill new_column from old_column via batch jobs. Phase 3: flip code to read from new_column. Phase 4: DROP COLUMN old_column. Each phase deployable independently. Slowest approach but safest, zero-downtime, reversible at each step. Used by Google, Facebook for massive tables.
Large table migrations (1TB+), tactics?
Never LOCK TABLE. Use gh-ost (online DDL, triggers, shadow table) or AWS DMS (CDC + replication). Chunk data with row IDs in batches (10k rows/sec). Monitor replication lag. For index changes: rebuild online or drop+recreate in parallel. Test on production-sized replica first. Budget: hours to days depending on table size.
Rollback strategies for failed migrations
Test rollback plan BEFORE migrating: reverse migration scripts, backup-restore window, CDC rewind (Debezium). Keep last 7 days snapshots. For expand-contract: rollback = revert code, old column still there. For blue-green: instant (flip traffic back). For online DDL: rollback = abort tool, old table preserved. Document rollback SLO per migration type.
Cross-cloud migrations (AWS Postgres to Azure)
Use AWS DMS (source) + replication → Azure. Or Fivetran/Airbyte for data sync + custom schema migration. Schema-first approach: migrate DDL via Liquibase, then data. Test on non-prod first. Network latency matters: direct connection > VPN. 1TB takes 8-24h depending on latency. Plan cutover window. Verify foreign keys, sequences, triggers exist on target.

不確定這項技能適不適合你？

做一下職業匹配，我們會推薦合適的方向。

找到最適合我的技能 →

找到理想的職業方向

在 2,521 個職業中進行技能匹配。免費。

免費做職業匹配 →