Uber: why it switched from Postgres to MySQL

01 · CONTEXT

From Postgres to MySQL: what Uber reported

Uber's initial architecture was a monolithic Python backend that used Postgres to store data. Later the architecture moved to microservices and new data platforms, and in many of the cases where Postgres had been used, Schemaless was adopted, a database sharding layer built on top of MySQL.

The engineering article that explains the switch lists five Postgres limitations: inefficient architecture for writes, inefficient data replication, table corruption issues, poor MVCC support on replicas, and difficulty upgrading to newer versions.

The text analyzes these limitations by comparing how Postgres and MySQL, with the InnoDB storage engine, represent tables and indexes on disk.

02 · ON-DISK FORMAT

Where the index points

A central aspect of Postgres's design is immutable rows, called tuples and identified by a ctid, which conceptually represents the tuple's location on disk. A single record can have several ctids, for example when there are multiple versions of it. The indexes, primary and secondary, point directly to those locations.

To update a record, Postgres adds a new tuple to the table and stores in each tuple a version field and a pointer to the previous tuple. While both versions exist, the indexes need entries for both.

InnoDB, MySQL's storage engine, also supports MVCC and mutable data, but its secondary index records hold a pointer to the primary key value, not to the row's location on disk.

The cost of InnoDB's design is one extra lookup: a query through a secondary index first looks up the primary key and then queries the primary index to find the row's location. The text treats this as a slight disadvantage compared to Postgres for that kind of query.

03 · COST OF WRITES

One small update, many writes

In the article's example, the table has three indexes: the primary key and two secondary ones. Updating the birth year of a single row required at least four physical writes in Postgres: the new tuple and a new entry in each index, including those that cover fields that did not change.

Each of these writes also has to be reflected in the WAL, so the total number of disk writes is even higher. On a table with a dozen indexes, an update to a field covered by a single index has to be propagated to all twelve.

In InnoDB the same update changes the field in place, copies the old row to the rollback segment, and updates only the indexes that actually cover the changed field; the primary key index and the name index do not need to be touched.

Cleanup of old versions also changes. In InnoDB, the rows eligible for cleanup are directly in the rollback segment, whereas Postgres's autovacuum needs to scan entire tables to identify deleted rows.

04 · REPLICATION

Physical WAL versus logical log

Postgres implements streaming replication by sending the master's WAL to the replicas. Since the WAL was designed for crash recovery, it carries low-level information about on-disk updates, at the level of tuples and their offsets.

Uber started with physical servers in a West Coast colocation and, for disaster recovery, added servers in a second colocation on the East Coast. The Postgres master, with its replicas, sat in the West data center, and a set of replicas in the East.

1. The application writes to the Postgres master, which keeps a change log (WAL) used for crash recovery.

2. The master sends the WAL to the replicas; the WAL contents describe the tuples and their offsets on disk.

3. A small logical change becomes several physical writes, and the WAL entries for all of them propagate over the network.

4. With replicas in another data center, this volume weighs on the link between the two sites.

5. The WAL was also archived to a storage service, and at the initial peak the bandwidth to it could not keep up.

According to the text, the verbosity of the replication protocol can generate an overwhelming volume of data for databases with many indexes, and it is not possible to get a cross-coast link with the same bandwidth as a local interconnect. WAL archiving also suffered: at the initial peak, the bandwidth to the storage service did not keep up with the WAL write rate.

MySQL offers statement-based, row-based, and mixed replication. Statement-based replication is usually the most compact, but it can force replicas to execute expensive statements; row-based is more verbose, but more predictable and efficient on replicas.

Because only the primary index points to row offsets, MySQL's replication stream needs to contain only logical updates, and the replicas infer index changes on their own. The text concludes that MySQL's replication binary log is much more compact than Postgres's WAL stream.

1. The update is normally applied in place and the old version of the row goes to the rollback segment.

2. The replication stream carries only logical row updates; the replicas infer index changes on their own.

3. Because the stream is logical, the replica can have true MVCC semantics and reads on it do not block replication.

4. For a version upgrade, you update one replica at a time and, at the end, promote one of them to be the new master.

05 · REPLICAS, CORRUPTION, AND UPGRADES

When physical replication sends the bill

Postgres has no real MVCC support on replicas: since they apply the WAL, they have an on-disk copy of the data identical to the master's. If a replica has an open transaction, updates that affect the rows it holds are blocked and WAL application is paused until the transaction finishes. Since the replica could fall far behind, Postgres applies a timeout and terminates the blocking transaction.

The text says replicas can routinely have a atraso of seconds relative to the master, and that it is therefore easy to write code whose transactions get killed. One example is an implicit transaction that stays open until a receipt email has finished sending.

In MySQL, because the replication stream carries logical updates, replicas can have true MVCC semantics, and read queries on them do not block replication.

Uber reported a Postgres bug: during a routine master promotion, the replicas incorrectly followed timeline switches and misapplied some WAL records, and records that should have been marked inactive were not. A query by a key would return two records, and the duplicate results made application logic fail in several cases.

Because the bug affected all servers, the corrupted rows differed from replica to replica, and the team was not sure how many replicas, or whether the master, were affected. The fix for the replicas was to resynchronize them from a new snapshot of the master, a laborious process, because there was only capacity to take a few replicas out of the load-balancing pool at a time.

According to the text, the bug affected only certain Postgres versions and was fixed long ago, but the authors still find it worrying that this class of bug can happen: given how replication works, the problem could spread to all databases in a replication hierarchy.

In MySQL, replication is logical, so an operation like rebalancing a B-tree never corrupts an index. A typical problem is a statement being skipped or, less commonly, applied twice, which can leave data missing or invalid, but does not cause a database outage.

Physical replication also makes version upgrades harder: according to the text, it is not possible to replicate between different GA releases of Postgres. The procedure included running pg_upgrade on the master, which can take many hours on a large database without serving traffic, and then wiping each replica and restoring it from a new snapshot.

Uber completed the upgrade from Postgres 9.1 to 9.2, but the process took so many hours that it could not be repeated, and the legacy instances were still on 9.2 at the time of the text. For those on Postgres 9.4 or newer, the text cites pglogical, which allows replicating between different releases, but notes that it is not integrated into the main Postgres tree.

In MySQL, according to the text, it is trivial to replicate between different releases. The typical way to upgrade is to apply the change to one replica at a time and, at the end, promote one of them to be the new master, with almost no downtime.

06 · OTHER DIFFERENCES AND CONCLUSION

Cache, connections, and Uber's bottom line

Postgres lets the kernel cache recent data in the page cache, and reading data this way requires system calls to locate it, each with a context switch, which is more expensive than accessing main memory. InnoDB implements its own LRU, the buffer pool, which results in fewer context switches.

MySQL creates a thread per connection, and Postgres uses a process per connection, which is much more expensive. Uber reported trouble scaling Postgres beyond a few hundred active connections; the documentation strongly recommends an out-of-process connection pool, and pgbouncer was generally successful, but application bugs that opened too many connections caused prolonged outages.

The text concludes that Postgres served Uber well in the beginning, but significant problems appeared when scaling with growth. Some legacy Postgres instances remained, and most of the databases came to be built on MySQL, typically with Schemaless, or, in specialized cases, on NoSQL databases such as Cassandra.