Core Code
Core Code Modern Dev Handbook
DATABASE-ADVANCED Contents
Storage & Index Internals
DB Page Layouts B-Tree Internals Hash, GIN, and GiST Indexes Selectivity & Cardinality Index Maintenance & Bloat Covering & Partial Indexes WAL & Write Amplification MVCC and HOT Updates
Query Planner & Execution
Query Lifecycle Cost-Based Optimizer Basics Reading EXPLAIN ANALYZE Join Strategies Index Scan vs Seq Scan Statistics & Misestimation Plan Regressions Query Optimization Checklist
Transactions & Isolation
ACID Realities Isolation Levels Deep Dive Phantoms, Lost Updates, and Anomalies Deadlocks: Detection and Retries Locking Mechanisms Long-Running Transactions Optimistic vs Pessimistic Concurrency Transaction Safety Checklist
Replication & High Availability
Replication Types Postgres Replication Model MySQL Replication Model Replica Lag and Read-After-Write Failover Strategies Backup Strategies Point-in-Time Recovery HA Architecture Patterns
Sharding & Horizontal Scale
Vertical vs Horizontal Scaling Sharding Patterns Shard Key Design Cross-Shard Queries Rebalancing and Resharding Multi-Region Data Architectures Global Consistency Tradeoffs Sharding Operational Checklist
Postgres vs MySQL Tradeoffs
Storage Engine Differences JSON Support Comparison Indexing Capability Differences Replication Differences Ecosystem and Extensions Interpreting Benchmarks Choosing Between Postgres and MySQL Migration Surface Area
Caching & Redis Strategies
Caching Patterns Redis Data Structures in Production Cache Invalidation Cache Stampede and Thundering Herd TTL Design Strategies Read-Heavy Optimization Cache Consistency Models Cache Observability Checklist
Data Modeling at Scale
Normalization vs Denormalization Soft Delete Patterns Event-Driven Modeling Time-Series Modeling Multi-Tenant Schema Design Audit Trail Design Schema Migration Strategies Data Modeling Checklist

Migration Surface Area

Hidden migration costs: SQL dialects, ORMs, behavior differences, and rollout strategy.
On this page

Migration Surface Area: Postgres ↔ MySQL Is Not a Simple Swap

Database migration is rarely blocked by data copy. It is blocked by behavioral differences: SQL dialect, data types, indexing, query planner behavior, and transaction semantics.

Production migration fails when teams underestimate the surface area and treat the database like an interchangeable component.

1) SQL Dialect and Feature Gaps

Even “standard SQL” features can differ in details. Common friction points:

  • Upsert syntax and behavior
  • CTEs and optimization differences
  • Window functions availability and performance characteristics
  • DDL capabilities and locking semantics
  • JSON operators/functions and indexing patterns

Production implication: application queries must be audited and rewritten, not only tested.

2) Data Type Mapping

Type differences cause silent bugs if you assume equivalence:

  • Boolean vs tinyint patterns
  • Timestamp/timezone semantics
  • Text and collation differences
  • JSON vs JSONB and indexing implications
  • UUID storage and ordering characteristics

Migration requires explicit type mapping and a data validation plan.

3) Collation, Sorting, and Case Sensitivity

String comparison rules can change:

  • Case sensitivity differences
  • Accent/locale sorting differences
  • Index usage changes due to collation behavior

Failure mode: unique constraints behave differently or query results sort differently, breaking pagination.

4) Index Strategy Must Be Redesigned

You cannot “copy indexes” blindly.

  • InnoDB clustered PK affects secondary index cost and locality.
  • Postgres heap + index-only scans depend on vacuum and visibility maps.
  • Special index types (GIN/GiST/BRIN) do not translate directly.
  • Generated columns vs expression indexes differ in ergonomics.

Production rule: re-derive indexes from query patterns in the target engine.

5) Query Planner Regressions

Even if queries are identical, plans can differ due to:

  • Different cost models
  • Different statistics collection behavior
  • Different join algorithms and heuristics
  • Different index selectivity assumptions

Migration risk: a small subset of queries becomes catastrophically slower in production.

6) Transaction and Locking Semantics

Both systems support isolation levels, but their behavior and defaults differ. Migration risks include:

  • Lost update/phantom assumptions changing
  • Deadlock frequency changing
  • Long transaction impact manifesting differently (vacuum vs purge/undo pressure)

Production rule: validate critical workflows under concurrency, not only functional correctness.

7) Replication and HA Model Differences

Migrating engines often means migrating HA approach:

  • Postgres: WAL streaming, slots, timelines after promotion
  • MySQL: binlog, GTID, relay log apply behavior

Failover runbooks must be rewritten. Monitoring and alerting thresholds also change.

8) Tooling and Operational Workflow Changes

Operational tooling is part of the migration:

  • Backup/PITR pipeline
  • Schema migration tooling and DDL strategy
  • Monitoring dashboards and alerts
  • Performance troubleshooting playbooks

Failure mode: the database is migrated but on-call lacks operational visibility and procedures.

9) Data Copy Is the Easy Part (But Still Has Traps)

Data copy risks include:

  • Encoding and charset conversion bugs
  • Time zone and timestamp conversion errors
  • Large table copy time exceeding maintenance window
  • Inconsistent snapshot if copy is not transaction-consistent

Production rule: data copy must be repeatable and validated, not a one-shot event.

10) Migration Rollout Strategies

Safer migration approaches include:

  • Shadow reads: read from new DB in parallel and compare results.
  • Dual-write: write to both systems (hard, but enables gradual cutover).
  • CDC-based replication: stream changes from old to new for near-zero downtime.
  • Incremental cutover: move subsets of tenants or traffic segments first.

Each strategy has failure modes and requires strong observability.

Failure Modes in Production

  • Silent correctness drift: collation/type differences cause subtle bugs.
  • Query plan meltdown: a few hot queries regress massively under new planner.
  • DDL outage: schema change locks behave differently, causing downtime.
  • Replication pipeline gaps: CDC/dual-write misses updates.
  • Operational blind spot: monitoring and alerts not adapted to new engine.
  • Rollback impossible: cutover done without a safe fallback window.

Migration Checklist (Production)

  • Inventory all queries; categorize by criticality and performance sensitivity.
  • Define explicit type mapping and collation strategy; validate with sample datasets.
  • Redesign indexes for the target engine using real query patterns.
  • Benchmark with realistic data size, skew, and durability settings.
  • Run concurrency tests on critical workflows (transactions, locks, retries).
  • Rebuild HA/replication runbooks; update monitoring and alerting.
  • Choose rollout method (shadow read, CDC, dual-write) and implement verification.
  • Automate consistency checks (counts, checksums, invariants) during migration.
  • Plan rollback and keep a reversible window before committing fully.
  • Do a full restore drill on the new system before cutover.
  • Document operational differences for on-call and incident response.
  • After cutover, monitor plan regressions and error rates aggressively.

Summary

Postgres↔MySQL migration surface area includes SQL dialect differences, type and collation mapping, index redesign, query planner regressions, transaction/locking behavior, and HA/operational workflow changes. Safe migration requires staged rollout, strong verification, and rewritten runbooks. Treat migration as a production engineering project, not a data copy task.