Uber traced write amplification, verbose replication, a corruption bug and painful upgrades back to one Postgres design choice — indexes pointing at physical disk locations — and moved to MySQL/InnoDB.

Early Uber was a monolithic Python backend using Postgres for data persistence — the 9.2 release series, whose on-disk design hadn’t changed significantly since Postgres 8.3, roughly ten years earlier.

Postgres stores rows as immutable tuples, each identified by a ctid that conceptually represents its physical location on disk. Indexes are B-trees mapping index fields to a ctid payload. So an index in Postgres answers the question “which disk offset holds this row?” — and that single design fact is where everything that follows comes from.

Take a users table with a primary key and two secondary indexes — one on name, one on birth year. Update just the birth year, and because the row tuple is immutable, Postgres writes a brand-new tuple with a new ctid. The old tuple stays until autovacuum reclaims it.

That one logical change forces at least four physical updates: the new row tuple, plus every index — including the primary key index and the name index, neither of which contains any data that changed. They must be updated anyway, because they have to point at the new ctid. On a table with a dozen indexes, changing a field covered by one of them propagates into all twelve. Uber calls this write amplification, and all of it also goes into the write-ahead log, so the total on disk is larger still.

Because Postgres replicates by streaming that WAL, the amplification becomes replication amplification. Instead of shipping “change the birth year for row 4 to 770,” Postgres ships all four physical writes as byte-level disk offsets. Uber ran a master in a West Coast colocation space and replicas in an East Coast one for disaster recovery, and the verbosity became a bandwidth problem: cross-country links with local-interconnect bandwidth are expensive and often simply unavailable. During peak traffic, their bandwidth to the WAL archival storage service wasn’t fast enough to keep up with the rate WALs were being written.

Then, during a routine master promotion to add capacity, Uber hit a Postgres 9.2 bug. Replicas followed timeline switches incorrectly and misapplied some WAL records, so records that should have been marked inactive by the versioning mechanism weren’t. A plain SELECT * FROM users WHERE id = 4 came back with two rows: the old version and the new one.

The corrupted rows were different on different replicas — row X bad here, good there — and Uber couldn’t easily tell how many rows were affected, or even whether the master was among them. Application logic failed in a number of places, and they resorted to defensive programming statements to detect the condition on tables known to have the problem. The repair was to resync every replica from a fresh master snapshot, a laborious process made slower because they could only pull a few replicas out of the load balancer at a time.

The same physical-replication choice explains two other things that hurt. Replicas apply WAL updates to produce a byte-identical copy of the master, so they cannot hold a different view of the data — meaning no true replica MVCC. If a streaming replica has an open transaction touching rows an incoming update affects, Postgres pauses the WAL application thread; if that goes on too long, Postgres kills the transaction. Replicas routinely lag seconds behind master, and it is easy to write code that gets killed — a developer whose ORM quietly holds a transaction open while sending a receipt email will never see it coming.

And because replication records are physical, you cannot replicate between different Postgres GA releases. Upgrading meant shutting down the master, running pg_upgrade in place for many hours with no traffic served, snapshotting the whole database (many more hours), wiping every replica and restoring them. Uber did it once, from 9.1 to 9.2. The process took so long they could not afford to do it again — so their legacy Postgres instances still ran 9.2 at a time when the current GA release was 9.5.

Uber moved the bulk of its data onto MySQL with the InnoDB storage engine, usually through Schemaless — a database sharding layer it built on top of MySQL — with NoSQL databases like Cassandra in some specialized cases.

Uber is measured about it: Postgres served them well in the early days, and they still have legacy Postgres instances. They report being generally quite happy with MySQL.

If your database’s indexes point at where a row lives rather than what it is called, then every write, every replica and every upgrade is quietly paying for that address.