Hacker News Reader

All these analysis both on HN an outside seem to miss it one logical conclusion:

It doesn’t matter.

At least for ~90% of the projects. People use MySQL because it’s in the tutorial and it works, end of story.

> It doesn’t matter.

I think the issue is that there are so many different reasons projects need any storage. There's a reason why you need persistence, there's a reason why you need ACID, there's a reason you need some form of replication ...

Eg: a cms like WordPress would probably have been better off just storing structured data in xml files on disk (or nfs) - along with media files on disk. Or using Berkeley db/later sqlite.

Your point of sale terminals might not fare so well without central inventory.

Perhaps postgress is not really all that better. Perhaps MySQL speaks to some need that we have not measured. For me, mysql is _easy_. It just works. You don't need a phd to manage a mysql database, but it almost seems like you need one for pg. That is what makes mysql better, even though it may be technically worse. I read everywhere that mysql is supposed to be slow, but in my experience it is plenty fast. Note that I don't do any "big data" things, most I've had is a few million rows.

I am currently wrapping up a project whereupon I am adding pg support to a previously mysql-only large open source application. The footguns that I discovered when doing this (this is a very CRUD-like web app) weren't performance related, but rather the bizarre data integrity issues MySQL ships with out of the box. Did you know, for instance, that the default collation that MySQL installs with is case insensitive as well as accent insensitive? This means that data that contains é, for instance, will match the e character in queries. MySQL is in general much more forgiving about data type coercion than Postgres as well. MySQL makes assumptions about certain things regarding dates and times that Postgres doesn't.

All of the above bodes well for someone trying to quickly hack together an app that does stuff, but for long term data integrity it's much easier than pg to introduce subtle differences and flaws in the data through some application code that works at face value for CRUD operations out of the db but down the line fails due to subtle data differences.

Absolutely. And the non-standard handling of NULl and defaults. I think they might have fixed this, but for the first 15 years I interacted with MySQL, I never knew when a field would default to an empty string or a NULL.

Strict mode has fixed this for over a decade.

Does MySQL still converts `0::timestamp` into the date '0000-00-00'?

I have never seen a MySQL database that didn't have problems with dates. (But I haven't looked in a while.)

It can function like this, yes. There's a flag for the `sql_mode` that enables/disables this functionality, and I believe zero dates are disallowed by default in MySQL 8.0+ IIRC.

"Some opinionated thoughts on SQL databases" (2021): https://blog.nelhage.com/post/some-opinionated-sql-takes/

There are many factors to take into account, but this article focuses on the "operating reliably in production" factor and says that MySQL has fewer surprises:

As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it slightly wrong can utterly tank your performance or availability. In addition, because MySQL is, in my experience, more widely deployed, it’s easier to find and hire engineers with experience deploying and operating it. Postgres is a fine choice, especially if you already have expertise using it on your team, but I’ve personally been burned too many times.

> one is clearly better

For some definitions of better.

I started with MySQL. At some point I encountered project based on Postgres. It just came with its own set of quirks you had to deal with. It didn't feel like, wow, this is so much better. It was more like, yup, it's a database, it just COUNTs slowly and needs to be VACUUMed. I had more issues and questions at the time how to do things I knew how to do in MySQL, I figured them out, but those two are the impression that were left.

Then I encountered MsSql Server and it was pleasent enough. For my next project I'd probably choose Postgres but not because it created any kind of sympathy in me. The only thing I reallty liked was EXPLAIN.

MySQL has one advantage in my opinion. At least it had two decades ago. It was simple. It did things that realtionational databases are good at, in fast and easy manner. It didn't have constructs like nested queries, recursive queries, triggers or stored procedures with cursors which databases like Postgres had, because who's gonna stop them, but their performance is horrible and must be horrible because they step out of the realm of fast relational model into the realm of normal programming which algorithmically is way slower (and that's the main reason we use databases at all).

So MySQL in my opinion teaches you about what the databases are good at. It helped me immensly when dealing with slow queries later in life in Postgres, SQL Server and even Oracle.

I do prefer PostgreSQL if I have a choice, but from the practicality standpoint that many people are hitting on, I'm okay with various design decisions (i.e. take a look at some of the flags for MySQL's `sql-mode` option over the years) being phased out via the normal (warn -> deprecate -> throw error -> remove) lifecycle that things like this often go through in software. Once a technology gets wide adoption, no matter how "flawed" or not earlier versions were, you start to prioritize stability and reliability over "correctness" at some point. This leads to the understandably practical approach to many bugs in many enterprise systems where the team supporting a tech stack learns to work around the rough edges, and might even depend on certain "weird" functionality because it's simply more practical in both the short and long term than not doing it.

None of the above means that I don't see MySQL as flawed in some ways. I'm in a group of developers that I suspect make up a sizable portion of the MySQL community who didn't choose MySQL, but must support it, if for no other reason than because we see ourselves as professionals, and that's what professional do: make the employer's application work reliably.

For applications that have already survived past the point of finding product/market fit, a wholesale conversion of DBMS is rarely worth it, and conversions of this type are costly/risky even if it is worth it. I do understand many of the benefits (real and theoretical) of PostgreSQL, and if I'm around at the moment when a project's DBMS is being selected I'm going to recommend _not_ MySQL, but at some level I'm also paid to make the application that my employer is running on top of their DBMS work reliably ... and the fact is even among people who get PostgreSQL - who prefer it, would choose it if they could - many of us are also pragmatic enough not to pull the rug out from under a running application for "reasons".

You might find this (from 1989) interesting: https://dreamsongs.com/RiseOfWorseIsBetter.html

Right, it's important to keep "worse is better" in mind, but also keep in mind that the philosophy fades in importance the longer that a technology has been around. For instance:

"C is a programming language designed for writing Unix, and it was designed using the New Jersey approach. C is therefore a language for which it is easy to write a decent compiler"

Is that still true? Over time, people have wanted to fix the flaws in C, so they have added features, and nowadays writing a compiler for C is less easy.

And there are other ways to measure the declining value of "worse is better." Many people would now argue that memory safety is worth the extra effort. Many would say the world would be a better place if C was banned and everyone switched to something like Rust. C continues to lose market share to those languages that guarantee memory safety, and yet C never fully dies, which is interesting.

Over time, the tax you must pay for the "worse" begins to cost more than what you gain from the simplicity. My point, above, was how long this can take. We think of the tech industry as fast moving, and yet many core technologies have had obvious flaws for 30 or 40 years, and yet little action is taken to move to better technologies.

Unfortunately, the ecosystem of a tool matters more than the tool itself. One could come up with a technically superior alternative to an existing tool (gpg vs age and signify) but unless they have a widely recognised ecosystem, they tend to remain relatively niche products.

For example, Git can sign commits using gpg and x509 and now in version 2.34, OpenSSH keys. Although OpenSSH can be used to sign data, signify is a much better tool for this task.

I am fairly sure you are referring to https://phpbuilder.com/mysql-and-postgresql-compared/

In the live website, that link works but the links to pages 2 to 5 are broken. So here's an archive: https://web.archive.org/web/20170422031621/http://www.phpbui...