I've not interacted with mysql a whole lot, but when i did I was regularly surprised that it didn't have stuff I was missing from Postgres. Off the top of my mind:
- Query planner is much worse (just yesterday I had to USE INDEX to sped up a query by 300x, I'm near-certain postgres would just have gotten it right)
- Indexes are much more limited: no GIST, no GIN
- No transactional lock (`pg_advisory_xact_lock` in postgres). This one was very surprising, it's a really useful thing and I had to implement it myself as a lock table
At least you have access to USE INDEX on MySQL. On Postgres it's not rare to have a query suddenly perform awful in production because some switch flipped in the planner and now it's picking some random index
Good news is that they just added a plan stability feature in pg19. It's actually full planner hints, so you can edit the plan to whatever you want if you have no fear, but the main motivation is exactly index stability.
Recent MySQL comes with a rewritten optimizer (which they call "hypergraph") that will soon become the default, but you can already use if you want to.
One application I'm working on (CRUD, but with fairly complicated business logic) is seeing large performance improvements, especially on reports which join many rows from 10+ tables.
I regularly test the same dataset on both PostgreSQL and MySQL -- we support both, and this new optimizer puts them pretty close.
The current default optimizer has some pathological cases where reports run 8-10 times slower on MySQL compared to PostgreSQL. Not with this one.
- Query planner is much worse (just yesterday I had to USE INDEX to sped up a query by 300x, I'm near-certain postgres would just have gotten it right) - Indexes are much more limited: no GIST, no GIN - No transactional lock (`pg_advisory_xact_lock` in postgres). This one was very surprising, it's a really useful thing and I had to implement it myself as a lock table