[sgarland]

I have been saying this for years. The opinion has become even firmer since moving into DB specialization – the horror show of schemata and queries that even dedicated backend teams dream up is unreal. I don’t even really blame them; relational databases are hard. Yes, anyone can install Postgres (or spin up a managed service) and get decent results for quite some time, but at scale you absolutely have to know what you’re doing. There is a reason that DBAs were a thing, and the SaaS industry is slowly realizing that they shouldn’t have abandoned that role.

[rrr_oh_man]

> at scale you absolutely have to know what you’re doing

There's a lot of runway you hit that magic scale: https://news.ycombinator.com/item?id=39276069

[sgarland]

Yes, but the runway is a good deal shorter if you’re doing things as most dev teams without RDBMS knowledge do. The biggest three issues unfortunately build on each other:

Using JSON when you really should be properly normalizing your schema (or using a DB better suited for JSON).

Using UUIDs – especially UUIDv4 – for any indexed columns, especially as PKs.

Using Postgres for no actual reason other than it’s popular.

MySQL / InnoDB is a clustering index RDBMS, which can have massive performance improvements over others IFF you build your schema around it. If you use anything non-k-sortable (e.g. UUIDv4) as your PK, it suffers massive performance degradation instead. So you use Postgres instead, and think you’re immune. Wrong – its MVCC implementation means you’ll have approximately 7x read amplification for Visibility Map lookups under the same circumstances, and huge amounts of WAL bloat, which can also lead to increased network latency.

JSON[B] in Postgres is frequently TOASTed due to its size. The overhead from TOAST/DETOAST isn’t small, and you also have to deal with difficulties indexing the column, and you can’t as of yet do a partial update to a JSON column. MySQL can do partial updates, doesn’t have TOAST, and has better (still annoying) strategies for extracting scalars from them to be indexed as a quasi-sidecar lookup, but it lacks GIN/GiST index types.

I’ve personally ran a single MySQL instance with over 100K QPS, and it had the plenty of runway, and a ton of vertical scaling left. This was with very careful tuning, and a suboptimal schema. I’m sure it could go far higher if everything was done correctly from the start.