Hacker News Reader

Comment by donatj | Hacker News Reader

Unpopular opinion on HN apparently, but MySQL

- It's less featureful, and I'd consider that a strong virtue in the YAGNI camp - less to go wrong, less mental overhead.

- Maintenance is simpler and far less necessary in my general experience.

- Replication is simpler and more reliable.

- You can tell the query optimizer what to do. When this is needed, you'll be thankful. It's a godsend.

That said, I wouldn't run Oracle MySQL. I opt for MariaDB on smaller projects and AWS Aurora MySQL for larger projects. Aurora scales insanely well, and replication lag is almost non-existent.

In my general experience MySQL was always significantly faster but it's been a number of years since I've worked with Postgres and the comments here seem to indicate that that may no longer be the case. YMMV

View on HN

> It's less featureful, and I'd consider that a strong virtue in the YAGNI camp - less to go wrong, less mental overhead.

This doesn't really hold water in my opinion.

It's not like PostgreSQL is some minefield of misfeatures and quirky behavior. Some of these features exist, but have zero impact on you unless you actually opt to use them. But if you end up needing to: they're there, and you can just start using them.

Compare this to MySQL where they simply don't exist no matter how much you may need them. Need to apply an index to the result of a function to quickly fix a performance issue in prod? Sorry, you can't. Need window functions to accurately compute some analytics in a sane period of time? Sorry, you can't. The list of things you can do in PostgreSQL that you simply can't with MySQL is massive and grows every day.

The odds that you'll want, need, or greatly benefit at least one of these features is not small. Having the flexibility of knowing these features exist should you ever have a use-case for them is massive.

Your examples regarding MySQL's features are not correct.

Need to apply an index to the result of a function? No problem, use a functional index, supported since October 2018: https://dev.mysql.com/doc/refman/8.0/en/create-index.html#cr...

Need to use a window function? No problem, supported since April 2018: https://dev.mysql.com/doc/refman/8.0/en/window-functions.htm...

Fair. I picked two instances that had long been personally painful with MySQL and didn’t exist when I finally fled to greener pastures.

There are far more examples than just those two though. :)

Sure, and there are also plenty of examples of things MySQL can do that Postgres can't.

There's no silver bullets in software or databases, no "one size fits all". Just various trade-offs between different design decisions.

I mean, for most mature ecosystems / technology stacks I think the “no one size fits all, just strategic design trade-offs” theory largely holds true as an objectively accurate account of the “choosing the best tool“ situation ….

but IMO, I think it’s worth noting that occasionally, some rare unique and functionally superlative technology comes along that in practice transcends every alternative from the onset and indefinitely going forwards, sometimes even at a more prominent scale than the MySQL / Postgres projects topic of discussion (which are not small by any means).

something maybe like Git, most immediately comes to mind, as an example of the de-facto standard for distributed VCS basically since … 2005* when Linus decided to create it?

edit: not 1995

> something maybe like Git, most immediately comes to mind, as an example of the de-facto standard for distributed VCS basically since … 1995 when Linus decided to create it?

hot take. might be the most popular, maybe even by a large margin, but I think you'll find a good chunk of people who have actually tried different VCSs don't think it's the best.

Yah, I think that’s probably true. But that’s also hard to reconcile with the reality of the adoption trending consistently away from any alternative and only towards Git. And “large margins” are indeed pretty objectively the case (from the largest developer surveys the breakdown 10 years ago was like 70% Git to everything, growing to ~95% in 2022). Usually the phenomenon you’re describing, leads to other alternatives becoming more popular not less (even if the most popular standard continues to eclipse the field. Here is would seem these highly likable alternatives for those who took the plunge are nevertheless dwindling into irrelevance…

I suspect the die-hard proponents of Mercurial, or SVN, or whatever else, these few pagan heretics that might exist out there wherever they’re hiding, have found themselves in a camp different to the Git standard likely on the basis of electing to be intentionally contrarian / anti-normative as the general catalyst, and rather not, as a function of struggling with Git to the point of being so disillusioned they call it quits and head out looking for greener pastures. I think in practice the most common result of encountering problems with Git is, fix the problems. And functionally I think that’s resulted only in furthering it’s supremacy over alternatives, despite there existing a handful of cultish weirdos who are _really_ into Mercurial and prefer not to fux with Git as a personal lifestyle choice haha)

Are you serious? Anyone who disagrees with you about what VCS is best must be intentionally contrarian?

Haha. That’s a pretty aggressive and surface level read of my comment . For starters it was mostly a joke, and explicitly a conjecture. I was saying, probably most users of a different system do so on for some reason other than, Git being objectively inferior for their purposes (and as others in the thread pointed out, the latter actual is the case at the large scale end of the spectrum for big companies concerned with scaling, not choosing Git).

Secondly, no one has disagreed with me on the matter, as I haven’t put forth a personal opinion, I’m simply impartially making referencing to the fact that Git is the de-facto standard. And from an intellectual perspective, was hoping someone might elucidate more into why that is the case, given my conception is an inferential deduction. at best).

While I’m personally relatively familiar with Git internals, by no means an expert, its the only distributed VCS I’ve ever used and I don’t know anything substantive enough about the alternatives to credibly make a relative value comparison here. They could be the bees knees for all I know, but it seems unlikely given the position Git holds as far as consensus standard choice.

Giant companies like Google and Meta use alternatives to Git for their internal / closed-source code, because Git couldn't scale to their needs. If I recall correctly, Google transitioned from Perforce to an in-house system, and Meta transitioned from using Mercurial to a custom derivative of it.

Again, no one size fits all...

What are the “plenty of examples of things MySQL can do that Postgres can’t”? I honestly can’t think of one, much less “plenty”.

Yeah I can’t think of any. I mean if I had to come up with something, I would talk about tooling. MySQL Workbench (when it works) is by far the easiest to use and most feature-rich of all RDBMS.. MS. It runs on any OS too, which is a ding on SQL Server and not Postgres since Postgres’ is a web app- which has the limitations of a web app (no right click for example- which I know is possible in a web app, but last I checked not implemented for pgadmin).

These are just a few random ones off the top of my head!

* Handling several thousand connections per second without needing a proxy or pool (MySQL's connection model is thread/conn vs Postgres using process/conn)

* Handle extremely high volume of primary key range scan queries (MySQL's InnoDB uses a clustered index, see https://news.ycombinator.com/item?id=35909053)

* Handle workloads that lean heavily on UPDATE or DELETE, without major MVCC pain (see https://ottertune.com/blog/the-part-of-postgresql-we-hate-th... for example)

* Semi-synchronous logical replication, for environments which cannot tolerate any data loss during failover; this ensures the statements have reached at least one replica but without the huge latency of synchronous replication

* Use index hints, to ensure random index stats changes don't cause unexpectedly negative query plan adjustments (see discussion in subthread https://news.ycombinator.com/item?id=35909340)

* Handle very high-volume OLTP workloads using direct I/O, since InnoDB's buffer pool design is completely independent of filesystem/OS caching

* Achieve best-in-industry compression by using the MyRocks storage engine (MySQL's pluggable storage engine design has a lot of tradeoffs but it is inherently what makes this even possible)

* Use UNSIGNED int types, to store twice as high max value in the same number of bytes, if you know negative numbers are not going to be present

* Use case-insensitive or accent-insensitive collations out-of-the-box without having to monkey with confusing user-defined CREATE COLLATION

* Ease-of-use commands like SHOW CREATE TABLE

* Silly cosmetic things like the ability to reorder columns in an ALTER TABLE (see https://wiki.postgresql.org/wiki/Alter_column_position)

* A tooling ecosystem which includes multiple battle-tested external online schema change tools, for safely making alterations of any type to tables with billions of rows

ratio

I would disagree on maintenance being simpler. I have never had Postgres randomly munge a table and require me to run a command to fix it.

I have not had that happen in MySQL either, at least, not with innodb. what command would that be?

I do remember getting bad tables with myisam tables a decade ago, sometimes after a bad shutdown.

I believe this was the command in question: https://dev.mysql.com/doc/refman/8.0/en/repair-table.html

My needs are meager (simple CRUD, low volume), but I haven't had that happen in MySQL either, in over 15 years of running it in production.

Not saying it can't happen, but I don't think it's a common occurrence.

WordPress may be uniquely bad.

I had the misfortune of inheriting a MySQL 5.6 database and I had to manage it for a year and a half. It wasn't very big (less than 100GB), but:

- for some reason it hang periodically and had to be restarted during the night for no apparent reason

- compared to Postgres, the tooling is garbage (both for backups and even more for general database administration)

- essential features are missing, the most important one of which, for me, was proper CSV import/export. CSV-related functionality is so broken and terribly inconvenient to use. In a specific case I had to write a program to export millions of records manually since MySQL could not generate correct CSV export due to some columns containing text with special characters, quotes, newlines. Any combination of the export parameters ("ENCLOSED BY", "ESCAPED BY" and all the other garbage options) failed in one way or another. I even tried to use non-standard characters like \x15 and \r for column and row separation but even that failed. With Postgres, "with csv header" is simple and works every time.

I also managed bigger Postgres databases (up to tens of terabytes) and never had the issues I encountered with MySQL.

> It's less featureful, and I'd consider that a strong virtue in the YAGNI camp - less to go wrong, less mental overhead.

Imagine when you actually need any of the features that PostgreSQL provides like pub/sub, logical replication, JSONB etc. With MySQL you might have to hack a solution that is much more complex or you have to set up an entirely separate tool. What I find nice with PostgreSQL is that for simple cases you can get away without a dedicated key/value store or a queue or a full text search engine. You can do a lot of these kinds of tasks with just a single database.

Correct, but as far as I know MySQL has those mentioned features but the overall management effort seems to be considerably lower.

> features that PostgreSQL provides like [...] logical replication

MySQL has offered logical replication for considerably longer than Postgres, and it's a substantially more configurable and mature implementation. MySQL's built-in replication has always been logical replication. It's a feature MySQL has had for 23 years -- built-in logical replication is literally one of the top reasons why all the biggest MySQL users chose MySQL originally!

less features, simpler admin, more compatibility, more familiarity. I agree

samesies

What's the ratio of solving DB perf issues by optimizing it and letting the planner do its work, to telling it what to do? For me it's like 1000:1.

And that one case I remember was perfectly solvable the regular way, with a little more time.

The problem is that when you you need to tell the planner what to do, you can do it in MySQL, but not postgres. Imagine you've got a production database with lots of traffic which suddenly can't handle anything because it inserted an extra row which tipped the balance and now takes seconds to process a common query.

Do you know how to fix the table statistics quickly? Do you know how to change that query to force the execution plan you want? Do you know how long the solution will last until the stats change again?

MySQL is a bit more predictable for this case and if things go really bad for some unexpected reason, one comment can fix it.

I'm looking at it from ops perspective. The ratio during development doesn't matter that much - all issues are solvable at that stage. For me it's rather "which situation would I rather be in at 3am".

There is the pg_hint_plan extension that gives most of what you would want in a hinting system.

I think a better fix lies in the direction of making queries more of a first class object with options to nail down plans, add custom logic to pick plans dependent on parameter values, etc.

I am on GCP/AWS, its not possible to use extensions. They allow only a few whitelisted extensions. If it is built into the DB, then I can use without any hassles.

I googled a bit and had no idea how many questions about Postgres query planner going nuts are out there. I just imagined this is a problem that creeps over time (giving you time to notice and act in advance, assuming you have monitoring/alerts set up) rather than suddenly tipping the scale - though it probably can happen suddenly after large data import.

Personally never ran into this with Postgres nor had anyone I know worry about it - the query planner was reliable for me in 99.99% of cases but yeah, I admit that it's a black box for me that I expect to take care of internals - hopefully it continues to do so, but I got to give it to MySQL for allowing to override it then.

Postgres query planner suddenly deciding to do something silly in the middle of the night has taken Notion down a few times. It's quite annoying, and it's very frustrating to have no recourse.

Any posts on this? Are there bulk data loads that make table stats more stale and affect plans? I’m wondering what would suddenly make a plan selection change a lot that might be a contributing factor.

> bulk data loads that make table stats more stale

This is the usual culprit (cure: "ANALYZE ((tablename))").

Collecting more samples (ALTER TABLE SET STATISTICS...) may be useful.

"Extended Statistics" covers most(?) other cases: https://www.postgresql.org/docs/current/planner-stats.html#P... https://www.postgresql.org/docs/current/multivariate-statist...

Interesting - how do you approach it when it happens and you're under time pressure to bring it back online - assuming you can't just fix query plan? I'd probably start by tweaking stats options and resetting them for problematic tables but don't have further ideas from the top of my head.

Reply on news.ycombinator.com