4 ms·

I got into programming in 2000 and right when I did Tim Perdue wrote a very important article comparing MySQL and Postgres. He concluded that Postgres was the b

I got into programming in 2000 and right when I did Tim Perdue wrote a very important article comparing MySQL and Postgres. He concluded that Postgres was the better database. [edit] Thanks to DocTomoe for finding this: https://phpbuilder.com/mysql-and-postgresql-compared/ https://phpbuilder.com/mysql-and-postgresql-compared/ Some of the discussion of the article survives: https://www.postgresql.org/message-id/39635C4A.1A4486D9%40alumni.caltech.edu https://www.postgresql.org/message-id/39635C4A.1A4486D9%40al... The article was a very big deal at the time, because it was so well researched and so devastating for MySQL. I figured that article was the death knell for MySQL, but then MySQL surprised me by just going and going and going. It was helped along back then by having a very close relationship with the PHP language. Nowadays, you can use any database you want with PHP, but in 2000 PHP was very much biased in favor of MySQL, and every article written about PHP was written with the assumption you'd be using MySQL as the database. I suspect that MySQL would have died except for the massive life support it got from PHP during those years. The moral I take from all of this is that sometimes you can have two technologies, and one is clearly better, yet the one that is clearly better can remain under-utilized for 21 straight years. I know a bunch of HN people will now show up and defend MySQL, or defend a lazy style of programming that accepts defaults even when that means using a poor cousin of something good. But we should stop for a moment and really think about the implications of this. Because it really is remarkable that people have known of the superiority of Postgres for 21 years and yet people still use MySQL.

tomstuart 5y ago
You might find this (from 1989) interesting: https://dreamsongs.com/RiseOfWorseIsBetter.html https://dreamsongs.com/RiseOfWorseIsBetter.html
- lkrubner 5y ago
  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.
DocTomoe 5y ago
I am fairly sure you are referring to https://phpbuilder.com/mysql-and-postgresql-compared/ https://phpbuilder.com/mysql-and-postgresql-compared/
- nayuki 5y ago
  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.phpbuilder.com/columns/tim20000705.php3 https://web.archive.org/web/20170422031621/http://www.phpbui...
ayushnix 5y ago
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.
bruce343434 5y ago
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.
- SOLAR_FIELDS 5y ago
  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.
  - lkrubner 5y ago
    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.
    - xdfgh1112 5y ago
      Strict mode has fixed this for over a decade.
  - 5y ago
PedroBatista 5y ago
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.
- e12e 5y ago
  > 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.
cakoose 5y ago
"Some opinionated thoughts on SQL databases" (2021): https://blog.nelhage.com/post/some-opinionated-sql-takes/ 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.
normalocity 5y ago
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".
scotty79 5y ago
> 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.

tomstuart 5y ago

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

lkrubner 5y ago
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.

DocTomoe 5y ago

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

nayuki 5y ago
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.phpbuilder.com/columns/tim20000705.php3 https://web.archive.org/web/20170422031621/http://www.phpbui...

ayushnix 5y ago

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.

bruce343434 5y ago

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.

SOLAR_FIELDS 5y ago
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.
- lkrubner 5y ago
  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.
  - xdfgh1112 5y ago
    Strict mode has fixed this for over a decade.
- 5y ago

PedroBatista 5y ago

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.

e12e 5y ago
> 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.

cakoose 5y ago

"Some opinionated thoughts on SQL databases" (2021): https://blog.nelhage.com/post/some-opinionated-sql-takes/ 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.

normalocity 5y ago

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".

scotty79 5y ago

> 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.