5 ms·

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 o

by donatj 3y ago

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

rocho 3y ago
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.
droobles 3y ago
samesies
EamonnMR 3y ago
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.
- deleted 3y ago
  [deleted]
- jjeaff 3y ago
  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.
  - deleted 3y ago
    [deleted]
  - EamonnMR 3y ago
    I believe this was the command in question: https://dev.mysql.com/doc/refman/8.0/en/repair-table.html https://dev.mysql.com/doc/refman/8.0/en/repair-table.html
- michaelcampbell 3y ago
  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.
  - EamonnMR 3y ago
    WordPress may be uniquely bad.
stouset 3y ago
> 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.
- evanelias 3y ago
  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#create-index-functional-key-parts 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.html https://dev.mysql.com/doc/refman/8.0/en/window-functions.htm...
  - stouset 3y ago
    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. :)
    - evanelias 3y ago
      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.
pawelduda 3y ago
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.
- viraptor 3y ago
  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".
  - ants_a 3y ago
    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.
    - avinassh 3y ago
      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.
  - pawelduda 3y ago
    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.
drogus 3y ago
> 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.
- PedroBatista 3y ago
  Correct, but as far as I know MySQL has those mentioned features but the overall management effort seems to be considerably lower.
- evanelias 3y ago
  > 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!
  - deleted 3y ago
    [deleted]
tonymet 3y ago
less features, simpler admin, more compatibility, more familiarity. I agree

by donatj 3y ago

rocho 3y ago

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.

droobles 3y ago

samesies

EamonnMR 3y ago

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.

deleted 3y ago
[deleted]
jjeaff 3y ago
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.
- deleted 3y ago
  [deleted]
- EamonnMR 3y ago
  I believe this was the command in question: https://dev.mysql.com/doc/refman/8.0/en/repair-table.html https://dev.mysql.com/doc/refman/8.0/en/repair-table.html
michaelcampbell 3y ago
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.
- EamonnMR 3y ago
  WordPress may be uniquely bad.

stouset 3y ago

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

evanelias 3y ago
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#create-index-functional-key-parts 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.html https://dev.mysql.com/doc/refman/8.0/en/window-functions.htm...
- stouset 3y ago
  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. :)
  - evanelias 3y ago
    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.

pawelduda 3y ago

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.

viraptor 3y ago
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".
- ants_a 3y ago
  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.
  - avinassh 3y ago
    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.
- pawelduda 3y ago
  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.

drogus 3y ago

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

PedroBatista 3y ago
Correct, but as far as I know MySQL has those mentioned features but the overall management effort seems to be considerably lower.
evanelias 3y ago
> 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!
- deleted 3y ago
  [deleted]

tonymet 3y ago

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