Uber PostgreSQL to MySQL Migration
Uber migrated its core trip and driver data from PostgreSQL to MySQL in 2016 after discovering that PostgreSQL's MVCC model and WAL-based replication produced unsustainable write amplification at the scale of hundreds of millions of trips: every index update required a full tuple write to the WAL, not just the changed columns.
Uber's primary data stores ran on PostgreSQL through early rapid growth. By 2016, engineering teams observed that PostgreSQL's heap-based storage and MVCC implementation were causing significant write amplification at their scale: an UPDATE to a single non-indexed column required writing a full new row version to the heap, updating every secondary index (since index entries point to heap tuple locations, not logical row IDs), and recording the full row image in the WAL for replication. For tables with many secondary indexes: which trip data required for driver matching, fare lookup, and analytics: every write touched multiple disk locations and saturated WAL throughput. MySQL's InnoDB uses a clustered index model where secondary indexes store the primary key value rather than a heap address; updating a non-indexed column requires no secondary index page updates and produces a more compact binlog entry. Uber documented the migration publicly and it became one of the most-discussed database engineering decisions in the industry.
Scale at Decision Point
Users
~40M monthly active riders; ~1M active drivers at the time of migration (2016)
Data Volume
Billions of trip records; multiple tables with 10+ secondary indexes
Request Rate
Not disclosed; write throughput was the binding constraint
PostgreSQL primaries with streaming replication replicas; transition to MySQL with row-based binlog replication
Architecture Evolution
Initial Architecture
PostgreSQL as the primary data store for trip, driver, and fare data. Standard PostgreSQL MVCC with heap-based row storage. Streaming replication sending the full WAL stream to read replicas. Multiple secondary indexes on trip tables to support diverse query patterns (driver lookup, fare lookup, geospatial queries, analytics aggregations).
- Every UPDATE creates a new row version in the heap; the old version becomes a dead tuple. VACUUM must periodically reclaim dead tuples, consuming I/O and CPU on active tables: VACUUM cannot keep pace under sustained high write rates.
- All secondary indexes store ctid (heap tuple location) rather than a logical row identifier. When a row is updated, every secondary index must be updated to point to the new tuple location: even if the indexed column itself did not change. A table with 8 secondary indexes requires 8 index page writes per row update.
- PostgreSQL streaming replication sends the entire WAL, which includes full row images for every heap and index modification. At high write rates, WAL volume grows proportionally to the number of secondary indexes, saturating replication bandwidth and causing replica lag.
- HOT (Heap Only Tuple) optimization reduces this amplification only when the updated column is not covered by any index and the new tuple fits in the same page : conditions that rarely hold for heavily indexed trip tables.
Evolved Architecture
MySQL with InnoDB storage engine as the primary data store. InnoDB uses a clustered primary index (rows are stored in primary key order within B-tree pages), and secondary indexes store the primary key value rather than a physical row address. Updating a non-indexed column does not touch secondary index pages. Row-based MySQL binlog records before/after column values rather than physical WAL entries, producing smaller replication payloads under column-selective updates. Schemaless (Uber's custom MySQL-backed document store) layered on top for services requiring schema flexibility.
- MySQL's clustered primary index means secondary index range scans are efficient only when the primary key is in the access path; queries that cannot use the clustered index scan more pages than PostgreSQL's heap model would for the same query.
- MySQL's DDL operations (adding columns, modifying indexes) historically required full table rebuilds, causing operational complexity during schema evolution. Online DDL was introduced but carries its own gotchas under active write load.
- Row-based replication requires more binlog space than statement-based for bulk operations, and binlog parsing for downstream CDC consumers adds operational complexity.
Key Transitions
Trigger
Write amplification from PostgreSQL's MVCC and WAL model was becoming the primary scaling bottleneck. WAL replication bandwidth was saturating, replica lag was growing, and VACUUM could not keep pace with dead tuple accumulation on high-write trip tables. The team evaluated the architectural root cause: PostgreSQL's heap tuple model requires secondary index updates on every row version creation: and determined that MySQL's InnoDB clustered index model eliminated the structural cause.
Before
PostgreSQL heap storage with streaming WAL replication; growing WAL amplification
After
MySQL InnoDB clustered index with row-based binlog replication; reduced write amplification
Outcome
WAL/binlog replication bandwidth reduced significantly for update-heavy workloads. Replica lag stabilized under sustained write load. Dead tuple accumulation eliminated. InnoDB's undo log model handles MVCC differently, deferring cleanup without requiring a blocking VACUUM on the primary. Migration was executed as a long-running project with online data migration tooling to avoid downtime.
Lessons
- PostgreSQL's MVCC model trades write amplification for multi-version isolation. This tradeoff is acceptable for most workloads but becomes a structural bottleneck on tables with many secondary indexes under sustained high write rates.
- Secondary index design is a first-order concern in write-heavy systems. Each additional index on a high-write table multiplies WAL volume (PostgreSQL) or write I/O (any system). Audit indexes regularly and remove any not serving active query patterns.
- Database engine choice is often irreversible at scale. The migration required building bespoke online migration tooling and ran for months. Evaluate write amplification characteristics of the chosen storage model before scale, not after.
Key Lessons
MVCC implementation details: not the MVCC concept itself: determine write amplification at scale
Both PostgreSQL and MySQL/InnoDB implement MVCC for snapshot isolation. PostgreSQL stores old row versions in the heap itself (requiring VACUUM to reclaim them) and updates all secondary index entries on each row version creation. InnoDB stores old versions in a separate undo log space and secondary indexes store the primary key, making secondary index updates conditional on primary key changes. These implementation differences produce dramatically different write profiles under high-frequency updates to tables with many secondary indexes.
Applicable when: You are selecting a database for a write-heavy workload on tables with many secondary indexes; or you are observing unexpectedly high WAL volume or replica lag on PostgreSQL under update-heavy traffic.
Read replica replication bandwidth is proportional to write amplification, not just write rate
PostgreSQL streaming replication ships the full WAL stream. On tables with 8 secondary indexes, a single UPDATE generates WAL entries for the new heap tuple plus all 8 index pages touched. Replica bandwidth consumption is 8–10× the logical update rate. At Uber's write volume, this saturated replication links and caused lag that degraded read-from-replica features. The root fix was reducing the WAL volume at its source, not increasing replication bandwidth.
Applicable when: Your read replicas are falling behind despite sufficient network bandwidth; or you observe that WAL/binlog volume is far higher than the logical write rate would suggest. Audit secondary index count and check PostgreSQL-specific write amplification.
Online schema migration tooling is a prerequisite for database migrations at scale, not an afterthought
At Uber's data volume, a naive table copy would take days and require downtime. gh-ost (GitHub's online schema change tool) and custom migration infrastructure allowed the team to copy data while the old system continued serving traffic, then cut over atomically. Building this tooling was a significant part of the migration project. Without it, migration risk would have been unacceptably high.
Applicable when: You are planning a major storage migration on a table that is live and cannot afford downtime. Budget for migration tooling as a first-class engineering deliverable.
Technologies
Patterns
Failure Modes Encountered
Related Scenarios
Sources
2 sources are pending verification and have been hidden until a followable citation is available.