Why Uber dropped PostgreSQL

The rivalry between database management systems is often somewhat heated, with fans of one system often loudly proclaiming that a competitor "sucks" or similar. And the competition between MySQL and PostgreSQL in the open-source world has certainly been heated at times, which makes a recent discussion of the pros and cons of the two databases rather enlightening. While it involved technical criticism of the design decisions made by both, it lacked heat and instead focused on sober analysis of the differences and their implications.

The transportation company Uber had long used PostgreSQL as the storage back-end for a monolithic Python application, but that changed over the last year or two. Uber switched to using its own Schemaless sharding layer atop MySQL and on July 26 published a blog post by Evan Klitzke that set out to explain why the switch was made. There were a number of reasons behind it, but the main problem the company encountered involved rapid updates to a table with a large number of indexes. PostgreSQL did not handle that workload particularly well.

There were effectively two facets to the problem with the table: changing any indexed field would result in write amplification because each index also needed to be updated and all of those writes need to be replicated across multiple servers. One advantage that MySQL has, Klitzke said, is that it uses a level of indirection in the InnoDB storage engine so that an update to an indexed field does not require updating the unrelated indexes.

There were some other problems as well. Uber encountered a bug in PostgreSQL 9.2 that led to data corruption, which rightly caused a lot of consternation. The bug was fixed quickly, but some corrupted data did end up being replicated, which made things worse. However, the blog post seems to imply that this kind of problem is somehow PostgreSQL-specific and does not really acknowledge that bugs will occur in all database systems (really, all software, of course), including MySQL:

Another problem that Uber encountered was in upgrading to new PostgreSQL releases. The process it used was time-consuming and required quite a bit of downtime, which it could not afford. MySQL supports both binary replication (which is what PostgreSQL uses) and statement-level replication. The latter allows MySQL to be more easily upgraded in place, without significant downtime, Klitzke said.

Joshua D. Drake posted a pointer to the blog post to the pgsql-hackers mailing list: "It is a very good read and I encourage our hackers to do so with an open mind." And it seems that's just what they did. While there was disagreement with some of the statements and implications in the blog post, there was also acknowledgment that some of the problems are real.

Josh Berkus restated the main write-amplification problem in a more concrete way. If you have a large enough table for indexes to make sense and use that table in JOIN statements throughout the application, indexing most of the columns may make sense from a performance standpoint. But if that table is updated frequently ("500 times per second"), there is a problem:

Bruce Momjian amended that slightly: "Or, basically, we don't have an answer to without making something else worse." Tom Lane, though, saw it as more of an annoyance, rather than "a time-for-a-new-database kind of problem". But both Berkus and Robert Haas disagreed; Haas said that he has "seen multiple cases where this kind of thing causes a sufficiently large performance regression that the system just can't keep up". Berkus called it "considerably more than an annoyance for the people who suffer from it", but agreed that it is not something that should, by itself, cause a database switch.

Stephen Frost took issue with some of the high-level criticisms in the blog post that were quoted by Drake. In particular, the table-corruption problem is hardly PostgreSQL-specific: "The implication that MySQL doesn't have similar bugs is entirely incorrect, as is the idea that logical replication would avoid data corruption issues (in practice, it actually tends to be quite a bit worse)." In addition, there are ways to make upgrading to newer versions much less painful:

He also wondered if Uber truly understood the write amplification problem and its implications. That is a theme that was taken up by Markus Winand in a blog post on the switch. In it, he agreed that MySQL might be the best choice for Uber's use case, but felt like there were some missing pieces in the explanation of the company's problems. The source of the problem is that Uber has an update-heavy workload that is evidently updating one or more of the indexed columns; otherwise PostgreSQL already has a solution:

He wondered if all of the indexes were actually needed, but that cannot be determined from the information in Uber's blog post. That post mentions InnoDB's indirection as an advantage, though, and downplays the penalty that comes from that indirection. Winand calls it the "clustered index penalty" and suggested that it can be substantial if queries are made using the secondary keys. The fact that Uber downplayed that penalty makes it appear that most of the queries use the primary index, which doesn't suffer from the penalty.

In the end, Winand concluded—in a somewhat snarky way—that what Uber is looking for is a key/value store with a SQL front-end. Since InnoDB is a "pretty solid and popular key/value store" and that MySQL (and MariaDB) provide SQL on top of it, it makes sense that it works well for the company.

Simon Riggs also addressed Klitzke's post with a blog post of his own. He welcomed Uber raising the points that it did, but was concerned that "a number of important technical points are either not correct or not wholly correct because they overlook many optimizations in PostgreSQL that were added specifically to address the cases discussed". He noted the penalty for using indirect indexing on secondary keys, as MySQL does, but also pointed out that PostgreSQL could use indirect indexes some day as well:

Riggs also said that statement-level replication has performance and corner-case problems that make it unsuitable for PostgreSQL. It does save bandwidth, as Klitzke pointed out, but can lead to hard-to-diagnose replication problems.

The discussion on the mailing list was largely even-tempered and focused on the problems at hand, much like all three of the blog posts mentioned above. Some solutions were considered and might become PostgreSQL features down the road. In the end, publicly losing a big user like Uber is perhaps a little unfortunate—and it does seem like there may have been other factors in play, such as new pro-MySQL CTO—but it is in no way a condemnation of PostgreSQL as a whole. In fact, as Berkus put it: "Even if they switched off, it's still a nice testimonial that they once ran their entire worldwide fleet off a single Postgres cluster."

Overall, the "incident" demonstrates a sensible approach to criticism of a project: find the pieces that are truly problems and look at how to solve them. In the case of Uber, it may well be that it is best served by MySQL, but it is also likely that others with different needs will see things differently. Having several open-source software choices means that everyone can choose the right tool for their job.