Upgrading Uber's MySQL Fleet

Tbf, either can be made to be fast, or slow. MySQL used to have a huge advantage for range queries due to its clustering index (assuming you have designed a schema to exploit that capability), but as time has gone on that gap has narrowed [0] (despite the title, it's not just about inserts). My own benchmarks for strictly read-only queries have also borne that out.

The biggest difference, IMO, is if you _aren't_ aware of the clustering index, and so design your schema in a way that is suboptimal. For example, given a table with orders or something similar, with a PK of `(user_id, created_at)`, a query like `SELECT... FROM... WHERE user_id = ? ORDER BY created_at DESC LIMIT 1` can be quite fast in MySQL. With a monotonic integer as the PK, and a secondary index on those two columns, it can still be quite fast, depending on insert order. If however you invert the PK to `(created_at, user_id)`, while MySQL 8+ will still be able to use the index, it's not nearly as efficient – in my tests, I saw query speed go from 0.1 msec --> 10 msec.

In contrast, while there is a small difference in Postgres with all of those differences, it's just not that big of a difference, since it stores tuples in a heap. Again, in my tests, query speed went from 0.1 msec --> 0.7 msec. This is a point query, of course; with range queries (`WHERE created_at < ...`) Postgres suffered more, jumping up to ~25 - 50 msec.

[0]: http://smalldatum.blogspot.com/2024/09/mysql-and-postgres-vs...