Hacker News Reader

Comment by bigiain | Hacker News Reader

> I swear people that cut their teeth on MySQL and have never used Postgres just don't know what they are missing

I seriously trialled and compared Postgres vs MySQL at the start of a major major project, and MySQL had a few clear wins for us (mainly replication) while the features Postgres had weren't in our current roadmap's requirements (the biggest regret that caused me was not having stored procedures). That was in 1998/1999. I now seem to be stuck on a career path where everything is MySQL and switching to Postgres anywhere seems to have huge back pressure from everyone I work with - even though at least half of them know damned well (like I do) that MySQL hasn't been the right choice for a couple of decades almost.

:shrug:

View on HN

I could tell a similar story from 2015. Worked at a MySQL shop where we had several big clusters with bi-directional replication between two "masters", and used failover between them if anything died. Also replicated to a 3rd node which was read only (used for huge queries, backups etc). Sounds ok, but our developers were not good at writing SQL or designing schemas, and our ops team didn't have any DBAs. So all of the problems were our own fault. But a new senior engineering hire told us that Postgres would fix everything because MySQL was a shitshow. He demonstrated amazing benchmarks on a specific database we were having issues with. But OMG once we tried to get replication working... Bloody awful. Major internal fighting ensued. Another engineer decided to build a clean MySQL and tune it using best practices, and demonstrated comparable performance to the Postgres demo. That was it for Postgres, and a major project kicked off to fix the MySQL problems (switch to innodb, turn on GTIDs, fix bad SQL that was breaking replication, use row based replication, better indexes, monitoring, actual metrics from the underlying hardware etc). Who'd have thought you had to spend time on your databases? Crazy right?

Yeah. Interesting to see the replication results came out the same 15 years apart.

And I feel seen on the devs with no database skills thing. Every single database problem I can recall having to step in and help fix in the last ~5 years has been Java devs leaving everything up the the ORM to deal with, resulting in unindexed full table scans and similar brain damage. I "fixed" a production problem with a query that'd crept up to over 30 seconds without anybody noticing, by adding a single index to a single table. (And then sent the devs to work out how to give Hibernate the hints it needed to create the schema properly and set up more monitoring so once-weekly reporting SQL queries taking 10+ seconds wouldn't be hidden in 95 and 99 percentile alerting...)

Just sticking with your old horse, has its problems, but you wont get surprised by new ones

And the fact that the community and tooling surrounding MySQL is absolutely bonkers massive. It's not that the grass has been trodden down to a path; no there's a 6 lane highway with a rest area and a Cracker Barrel.

LOL amazing metaphor.

The one thing that I've learned recently which surprised me- if you want to cluster MySQL (via partitioned tables or PlanetScale, etc) you cannot use foreign keys.

Some of the Postgres compatible tooling (Citus, Cockroach, etc) appears to support foreign keys across partitions / shards.

I was quite surprised to see this. Makes sense that not allowing FKs makes the data easier to shard but I never imagined that would be a thing you would have to contend with.

I also ran into this recently when looking at SingleStore! I was surprised when they told me I couldn't use foreign keys, and was even more surprised that they were surprised that I do use foreign keys. They acted like I was the only one. And to be fair, given their clientele, I might be the only one!

At "scale" you just can't afford to use FK, it's indeed slowing down everything and preventing good and fast clustering / replication. For some use cases you just have to live with it

Yup for sure, that's what they were saying. It makes sense, but I was still surprised by it. I've never had to manage something at this scale before, so it was news to me.

For reference, we're at about 250 million rows, so not even proper "scale" scale!

citus has some rather severe limitations around foreign keys. Namely, you can’t use them from any distributed tables. See https://docs.citusdata.com/en/v10.2/develop/reference_ddl.ht...

Reply on news.ycombinator.com