3 ms·

The philosophy of postgresql was to do the best possible job the rightest way. Not necessarily the easiest to use nor fastest. The philosophy of mysql was to

by VLM 3y ago

The philosophy of postgresql was to do the best possible job the rightest way. Not necessarily the easiest to use nor fastest. The philosophy of mysql was to do the best it can as fast as it can for as many users as it can. Not necessarily the 'right' or 'best' way. Its not like either philosophy is formally documented or mandatory but "generally" matches up pretty closely to real world behavior. The biggest problem you'll have evaluating them is both products are incredibly old and have changed over the decades so you'll find plenty of web search results DEMANDING that mysql can't do transactions, for example, because that was written 25 years ago and transactions were pretty much a solved problem some decades ago on mysql. Similar issues exist for tooling, library support, clustering, I wouldn't trust a many years old blog post for either product LOL.

idoubtit 3y ago
I also believe these were the original philosophies, since MySQL emerged from practical issues and Postgresql has more academic origins. But nowadays, I'm not sure MySQL is generally faster. In my experience, the main differences are that many simple things are simpler with MySQL, but Postgres is much more rigorous and complete. For instance, a major upgrade of a MySQL server is usually painless (Debian even upgrades automatically). My last upgrade from Pg12 to 14 was not that easy. Replication has been easier with MySQL, though I've heard Pg has recently matured on this side. The last time I used a Pg DB that was queried by a web app, PgPool was installed because Pg could not handle many concurrent connections, but I've also heard this may not be required with recent Pg. Client side, in many cases, a "poor man's search" with LIKE is enough or even suitable. With MySQL, I can declare `description TEXT collate utf8mb4_general_ci`, and then `LIKE '%de toutes façons%'` will match `De toutes facons`. Obtaining the same result with Postgres requires much more work. Now for the bad side of MySQL: it has so many footguns, like the various compatibility modes (silent truncation when inserting is still the default behavior, I think), or the transactions breaking when a DDL starts. Support of complex types (arrays, JSON, specialized types like ISBN...) is also years ahead in Postgresql, and so are many advanced features.
- porker 3y ago
  > silent truncation when inserting is still the default behavior, I think Hasn't been the default since MySQL 5.6. MySQL 5.7 was released in 2015 and some distros may have altered the defaults, but I doubt it.
- evanelias 3y ago
  > silent truncation when inserting is still the default behavior, I think MySQL 5.7, released in Oct 2015, changed the default to enable strict sql_mode. All prior versions have hit end-of-life for support years ago, so there is no modern version of MySQL with this silent truncation behavior. That said, there's still a major problem, but it's not MySQL's fault; rather, it's Amazon and their nonstandard defaults. AWS's managed database offerings inexplicably change the default sql_mode to disable strict-mode, both for regular RDS as well as Aurora. This is a huge problem, and it also definitely perpetuates this "MySQL still silently truncates!" perception.
bluGill 3y ago
25 years ago the people behind MySQL said that you didn't need transactions, quit asking for them. They didn't understand any of ACID really. Since then the project has got the important features, but I still have a bad taste in my mouth from the at the time leaders. 25 years ago postgresql was clearly a slow DB, but they didn't claim you didn't need performance, just that you had to measure first to ensure you really did (nobody did that measure). Things have changed. However my career ended up not needing much of a DB ever and so I've never had any need to see how much. I just like to watch the fights from the sidelines.
flashgordon 3y ago
What I liked about postgres (even as long time ago as 5-10 years ago) was that it tried to do things in an explainable and standards compliant way even if it mostly did not beat MySQL on latency. This never bothered me because my view had been to scale horizontally when needed and a db that led itself to better control plane development was more important than aggregate single node performance. Just my 2c.

by VLM 3y ago

idoubtit 3y ago

I also believe these were the original philosophies, since MySQL emerged from practical issues and Postgresql has more academic origins. But nowadays, I'm not sure MySQL is generally faster. In my experience, the main differences are that many simple things are simpler with MySQL, but Postgres is much more rigorous and complete. For instance, a major upgrade of a MySQL server is usually painless (Debian even upgrades automatically). My last upgrade from Pg12 to 14 was not that easy. Replication has been easier with MySQL, though I've heard Pg has recently matured on this side. The last time I used a Pg DB that was queried by a web app, PgPool was installed because Pg could not handle many concurrent connections, but I've also heard this may not be required with recent Pg. Client side, in many cases, a "poor man's search" with LIKE is enough or even suitable. With MySQL, I can declare `description TEXT collate utf8mb4_general_ci`, and then `LIKE '%de toutes façons%'` will match `De toutes facons`. Obtaining the same result with Postgres requires much more work. Now for the bad side of MySQL: it has so many footguns, like the various compatibility modes (silent truncation when inserting is still the default behavior, I think), or the transactions breaking when a DDL starts. Support of complex types (arrays, JSON, specialized types like ISBN...) is also years ahead in Postgresql, and so are many advanced features.

porker 3y ago
> silent truncation when inserting is still the default behavior, I think Hasn't been the default since MySQL 5.6. MySQL 5.7 was released in 2015 and some distros may have altered the defaults, but I doubt it.
evanelias 3y ago
> silent truncation when inserting is still the default behavior, I think MySQL 5.7, released in Oct 2015, changed the default to enable strict sql_mode. All prior versions have hit end-of-life for support years ago, so there is no modern version of MySQL with this silent truncation behavior. That said, there's still a major problem, but it's not MySQL's fault; rather, it's Amazon and their nonstandard defaults. AWS's managed database offerings inexplicably change the default sql_mode to disable strict-mode, both for regular RDS as well as Aurora. This is a huge problem, and it also definitely perpetuates this "MySQL still silently truncates!" perception.

bluGill 3y ago

25 years ago the people behind MySQL said that you didn't need transactions, quit asking for them. They didn't understand any of ACID really. Since then the project has got the important features, but I still have a bad taste in my mouth from the at the time leaders. 25 years ago postgresql was clearly a slow DB, but they didn't claim you didn't need performance, just that you had to measure first to ensure you really did (nobody did that measure). Things have changed. However my career ended up not needing much of a DB ever and so I've never had any need to see how much. I just like to watch the fights from the sidelines.

flashgordon 3y ago

What I liked about postgres (even as long time ago as 5-10 years ago) was that it tried to do things in an explainable and standards compliant way even if it mostly did not beat MySQL on latency. This never bothered me because my view had been to scale horizontally when needed and a db that led itself to better control plane development was more important than aggregate single node performance. Just my 2c.