12 ms·

Serious question. I have this question for, like 20 years already. Why would anyone start a new project with MySQL? Is it really superior in anything? I'm in i

by adontz 3y ago

Serious question. I have this question for, like 20 years already. Why would anyone start a new project with MySQL? Is it really superior in anything? I'm in industry for 20+ years and as far as I remember MySQL was always the worst and most popular RDBMS at any given moment.

willvarfar 3y ago
Not up to date, but a decade ago MySQL supported pluggable storage engines and so had had some good non-default choices. It was possible to fit really big databases onto small boxes using tokudb, for example. This doesn't explain why it was so popular for starting new small projects, but people were also choosing mongodb at that time too, so ymmv :) Nowadays postgres has grown a lot of features but I believe it is still behind on built-in compression? Added: this old blog post of mine is still getting traffic 10 years later; probably still valid https://williame.github.io/post/25080396258.html https://williame.github.io/post/25080396258.html
Keyframe 3y ago
Once upon a time it was easi-ish to scale out, and was simple to use and fast. There was Percona as well. These days, who knows? Might still be true.
WJW 3y ago
- It works well enough. - It scales up fine for 99+% of companies, and for the ones that need to scale beyond that there are battle tested solutions like Vitess. - It is what people know already so they don't have to learn anything new. Same reason why people still make new websites in PHP I guess. It's not fancy but it works fine and won't bring any unwelcome surprises.
gog 3y ago
We use it, we know it and can troubleshoot it if needed, it satisfies our needs and it works. What more do you need? It also works for others, Github for example. The only thing I am missing at the moment is a native UUID type so I don't have to write functions that convert 16bit binary to textual representation and back when examining the data manually on the server.
- dijit 3y ago
  I strongly dislike how people look to github as an example, its the highest appeal to authority. I know facebook uses mysql, but I also know that it is a bastardised custom version that has known constraints and has limited use (no foreign keys for example). I spoke to the DBA who first deployed MySQL at Github and the vibe I got from him immediately was that he had doubled down on his prejudice: which is fine, but its not ok to ignore that it can be a lot of effort to work around issues with any given technology. For a great example of what I mean: most people wouldn’t choose PHP for a new project (despite it having improved majorly) - the appeal to authority there is to say “it works for Facebook” without mentioning “Hack” or the myriad of internal processes to avoid the warts of PHP. That a large headcount company can use something does not make it immune from criticism.
  - capableweb 3y ago
    > most people wouldn’t choose PHP for a new project Is this really true? I used to be a full-time PHP developer but I personally don't touch that language anymore. But it's still very popular around the world, I've seen multiple projects start this year use PHP, because that's the language the founders/most developers in the company are familiar with. Probably depends a lot on where in the world you're located. Last Stack Overflow survey had ~20% of the people answering the survey saying that they still use PHP in some capacity.
    - deleted 3y ago
      [deleted]
    - sroussey 3y ago
      The beauty of PHP is that it is stateless and the end of the run, everything is freed. It is difficult to have memory leaks. Personally, I like using Typescript/Javascript on both front end and backend, but I don’t look down at PHP backends at all. And it’s come a long way as a language. I’ve been a fan of rolling your own stdlib as the semantics there are old and weird, but vscode tells you so who cares anymore.
  - stephenr 3y ago
endorphine 3y ago
As opposed to what? Postgres? Isn't InnoDB most performant for read-heavy apps?
- dijit 3y ago
  MyISAM is actually considerably faster (than InnoDB) for read heavy apps. InnoDB is comparatively slow, but you get much better transactionality (IE; something that is much closer to ACID compliance). Row level locking is faster for inserts than table level locking, but table level locking is faster for reads than row level locking. Regardless: Both storage engines do not scale with core count as effectively as postgres due to some deadlocking on update that I have witnessed with MySQL. (not that Postgresql is the only alternative btw).
  - VintageCool 3y ago
    There is no excuse to be using MyISAM instead of InnoDB in 2023. It was a scarcely forgivable mistake in 2013. The read performance advantages of MyISAM are solved better by using SSDs instead of HDDs. MyISAM will cost you dearly when performing actions like "trying to create a new replica of an existing database".
  - menaerus 3y ago
    MyISAM is not a transactional storage engine even to begin with, so saying that you get "much better transactionality with InnoDB" or "MyISAM is actually considerably faster" is either wrong or at best comparing apples to oranges. > Both storage engines do not scale with core count as effectively as postgres due to some deadlocking on update that I have witnessed with MySQL. Strange take since a deadlock is rather an exceptional event you want never to occur so deadlocking, in algorithm design, wouldn't be considered a reason one would say that the implementation does not "scale with the core count". Whether or not the algorithm scales with the core count is for many other different reasons but not deadlocks. Considering the "scale with the core count" design problem, Postgres process-per-connection architecture makes it a much less viable option than, say, MySQL so this is wrong as well.
    - 3y ago
Topgamer7 3y ago
MySQL has some aggregation performance over postgres. Having done a recent migration of an application two things that come to mind are: - its case insensitive by default, which can make filtering simpler, without having to deal with a duplicate column where all values are lower/upper cased. - MySQL implements loose index scan and index skip scan, which improves performance of a number of join aggregation operations (https://wiki.postgresql.org/wiki/Loose_indexscan https://wiki.postgresql.org/wiki/Loose_indexscan)
- dpratt 3y ago
  | its case insensitive by default This is obviously up for debate, but subjectively I find this to be an absolutely terrible design decision.
  - hu3 3y ago
    I agree it's debatable. And not intuitive at first. With that said, in all my years and thousands of tables across multiple jobs, I have yet to see a single case where I had to change a table to be case sensitive. So I guess for me it is a sensible default.
dissident_coder 3y ago
First, MySQL is the "devil you know". If you've spent a decade working exclusively with MySQL quirks, you're just gonna be more comfortable with it regardless of quality. MySQL also tends to be faster for read-heavy workloads and simple queries. Also replication is easier to setup with MySQL in my (outdated) experience, even though it's gotten better with Postgres recently and I haven't really been able to compare them myself since I'm just using Amazon RDS Postgres these days and haven't had the need to setup master-master replication (which is the pain point in postgres, and was pretty straightfoward with mysql the last time I worked with it). Setting up read-replicas with postgres is still ezpz. Postgres specific features tend to be much better than MySQL ones, Postgresql JSON(b) support blows MySQL out of the water. And as far as I can remember MySQL still doesn't support partial/expression indexes, which is a deal breaker for me. Especially in my json heavy workloads where being able to index specific json paths is critical for performance. If you don't need that kind of stuff, you might be fine - but I would hate to hit a wall in my application where I want to reach for it and it's not there. MySQL used to be the only game in town, so it was the "default" choice - but IMO postgres has surpassed it.
- darrenf 3y ago
  > And as far as I can remember MySQL still doesn't support partial/expression indexes, which is a deal breaker for me. Especially in my json heavy workloads where being able to index specific json paths is critical for performance. Do generated column indexes meet this need? CREATE TABLE json_with_id_index ( json_data JSON, id INT GENERATED ALWAYS AS (json_data->"$.id"), INDEX id (id) ) https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html https://dev.mysql.com/doc/refman/8.0/en/create-table-seconda...
  - simcop2387 3y ago
    Looks like that would work as an expression index, though i can't tell at a glance if this requires the column to also be stored which would increase storage size (but probably isn't a huge problem if it is). But that likely won't work for dealing with the partial index case where you're only wanting to keep the ones that aren't null in the index to reduce the size (and speed up null/not null checks).
Thaxll 3y ago
Your memory is failing you maybe you don't remember not too long ago when PG did not have any replications built-in.
- mrkeen 3y ago
  Not too long ago MySQL didn't have transactions. Edit: I would just love a comment from the person who thinks 'missing feature in the past' is wrong, unfair or irrelevant as a reply to a 'missing feature in the past' comment.
  - evanelias 3y ago
    There's a non-trivial nine-year difference between the things you're describing: the InnoDB storage engine was released in 2001. Postgres gained built-in replication in 2010. That said, personally I wouldn't describe either of these as "not too long ago". Technology rapidly changes and many things from either 2001 or 2010 are considered rather old.
Freeaqingme 3y ago
Also from an operations point of view it's quite easy to manage. I'm not that experienced with Postgresql, but my understanding is that until recently you had to vacuum it every once in a while. Besides, it's also using some kind of threading model that most people handle by putting a proxy in front of Postgres to keep connections open. Also, Mysql has had native replication for a very long time, including Galera which does two-step commit in a multimaster cluster. Although Postgres is making some headway in this regard, it is my impression that this is only quite recent and not yet fully up to par with Mysql yet.
- stonemetal12 3y ago
  >my understanding is that until recently you had to vacuum it every once in a while. You still do. The auto Vacuum daemon was added in 2008ish, so it isn't too bad. Just more complexity to manage. > it's also using some kind of threading model It does a process per connection just like web servers did back in the day when C10k was a thing. A lot of the buffers are configured per connection so you can get bigger buffers if you keep the number of connections small.
- dgellow 3y ago
  I think what you reference is known as Transaction ID Wraparound, Postgres still needs to be vacuumed to avoid that problem: https://www.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql https://www.crunchydata.com/blog/managing-transaction-id-wra...
adontz 3y ago
Just want to add, that comparing to postgresql is a very modern view. There were other databases, not popular today, but quite popular back in the day. To name a few: DB2, InterBase, Firebird, Paradox, Access, SQL Server Compact. MySQL was a really shitty database in early 2000s, still THE most popular.
viraptor 3y ago
Others mentioned a few reasons already, but compared to postgres (because typically that's the other option) I'll add index selection. Even with the available plugins and stats and everything, I don't want to in an emergency situation spend time trying to indirectly convince postgres that it should use a different index. "A query takes 20x the time and you can't force it back immediately" is a really bad failure mode.
deleted 3y ago
[deleted]
turtles3 3y ago
This is a great question, and if the choice is between mysql and postgres, I would like to make the case that despite the current popular momentum behind postgres, mysql is a better default. Please note I'm not saying mysql is better, but in the absence of any other criteria, I would suggest stating with mysql. I have a few reasons for this view, but they mostly revolve around operational complexity. From a developer's point of view postgres is fantastic. Far saner SQL dialect, tons of great features. When it comes to operations though, that's where mysql has the edge, and ops is half of using a database - it's an important facet for a business to consider. As other commenters have mentioned, postgres requires careful tuning of the autovacuum process, otherwise it can't keep up as the workload grows. Postgres has a far more advanced query planner, but it comes at the cost of potentially blowing up your app at 3am, and it gives you no tools to patch in a quick fix while you address the root cause. This frankly ignores the reality of operating a business. Sometimes you need a quick fix, even if that might lead to users developing bad habits. Yes there is the pg_hint_plan extension, but that still only helps you later after the problem had happened. You can't pin a query plan. To me the ideal situation would be for postgres to continue to use the old query plan, but emit some structured log to tell you it thinks it's now suboptimal. But I digress. Thirdly, postgres has no way to have an index clustered table. This lets you trade a small cost on write for greater page locality when reading related rows. Postgres let's you do this as a one time operation that takes the table offline for the duration, which isn't sufficient if you need it. Fourthly, mysql is still easier to upgrade. You will need to upgrade your database at some point. Mysql has great support for upgrade in place, as well as using replication to build a new db. Mysql replication has always been logical replication, which has tradeoffs of course, but what it buys you is the ability to replicate across different versions. Pg's logical replication still has a bunch of sharp edges. Ok this rant is long enough already, but I do want to emphasise that this isn't hating on postgres. I know it's controversial to be recommending mysql over postgres, but I do think the ops concerns win out. Ps the orioledb project is fantastic and I hope it one day becomes the default for postgres.
- wbl 3y ago
  Isn't working in the first place an ops concern?
- 3y ago
deleted 3y ago
[deleted]
lmm 3y ago
> Why would anyone start a new project with MySQL? Is it really superior in anything? It's the most developer-friendly thing out there. Particularly for a datastore CLI, which is inherently something you use rarely, MySQL's is just a lot nicer, more discoverable. I think it has the least bad HA story among (free) traditional SQL-RDBMSes too (not that I understand why anyone would start a new project on a traditional SQL-RDBMS at all).
otabdeveloper4 3y ago
> Is it really superior in anything? Yes, its replication support out of the box is decades ahead of Postgres. (Mongodb has an even better replication story than Myqsl, but Mongo isn't a real database.)
bojanz 3y ago
I am going to be controversial for a hot second, and say that in many ways MySQL is a more advanced and better implemented database at its core. Disclaimer: as a developer I love and prefer Postgres. But I've been on many projects where MySQL won for ops-related reasons. Postgres has a MVCC implementation that is recognized as inferior[0] to what MySQL and Oracle do, and requires dealing with vacuuming and all of its related problems. Postgres has a process-based connection model that is recognized as less optimal than the thread-based one that MySQL has. There are ongoing efforts[1] to move Postgres to a thread-based model but it's recognized as a large and uncertain undertaking. Other commenters have also explained the still very noticeable difference in replication support, the lack of query planner hints, the less intuitive local tooling. One thing to keep in mind is that both databases keep evolving, and old prejudices won't take us far. Postgres is improving its performance and replication support with each release. MySQL 8.0 added atomic DDL and a new query planner (MariaDB did their own query planner rework in 11.0, widening their differences). Both are improving their observability. So the race is far from over. But I definitely wouldn't count MySQL out. [0] https://ottertune.com/blog/the-part-of-postgresql-we-hate-the-most https://ottertune.com/blog/the-part-of-postgresql-we-hate-th... [1] https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd9-af5b-ac0d801163f4@iki.fi https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd...
misiek08 3y ago
I don't want to be subjective or start any war, but only to broaden my horizons and perspective. Saying this I want to clear one thing - I use multiple engines, trying to best match one for the problem I'm solving. What do you recommend these days?

by adontz 3y ago

willvarfar 3y ago

Not up to date, but a decade ago MySQL supported pluggable storage engines and so had had some good non-default choices. It was possible to fit really big databases onto small boxes using tokudb, for example. This doesn't explain why it was so popular for starting new small projects, but people were also choosing mongodb at that time too, so ymmv :) Nowadays postgres has grown a lot of features but I believe it is still behind on built-in compression? Added: this old blog post of mine is still getting traffic 10 years later; probably still valid https://williame.github.io/post/25080396258.html https://williame.github.io/post/25080396258.html

Keyframe 3y ago

Once upon a time it was easi-ish to scale out, and was simple to use and fast. There was Percona as well. These days, who knows? Might still be true.

WJW 3y ago

- It works well enough. - It scales up fine for 99+% of companies, and for the ones that need to scale beyond that there are battle tested solutions like Vitess. - It is what people know already so they don't have to learn anything new. Same reason why people still make new websites in PHP I guess. It's not fancy but it works fine and won't bring any unwelcome surprises.

gog 3y ago

We use it, we know it and can troubleshoot it if needed, it satisfies our needs and it works. What more do you need? It also works for others, Github for example. The only thing I am missing at the moment is a native UUID type so I don't have to write functions that convert 16bit binary to textual representation and back when examining the data manually on the server.

dijit 3y ago
I strongly dislike how people look to github as an example, its the highest appeal to authority. I know facebook uses mysql, but I also know that it is a bastardised custom version that has known constraints and has limited use (no foreign keys for example). I spoke to the DBA who first deployed MySQL at Github and the vibe I got from him immediately was that he had doubled down on his prejudice: which is fine, but its not ok to ignore that it can be a lot of effort to work around issues with any given technology. For a great example of what I mean: most people wouldn’t choose PHP for a new project (despite it having improved majorly) - the appeal to authority there is to say “it works for Facebook” without mentioning “Hack” or the myriad of internal processes to avoid the warts of PHP. That a large headcount company can use something does not make it immune from criticism.
- capableweb 3y ago
  > most people wouldn’t choose PHP for a new project Is this really true? I used to be a full-time PHP developer but I personally don't touch that language anymore. But it's still very popular around the world, I've seen multiple projects start this year use PHP, because that's the language the founders/most developers in the company are familiar with. Probably depends a lot on where in the world you're located. Last Stack Overflow survey had ~20% of the people answering the survey saying that they still use PHP in some capacity.
  - deleted 3y ago
    [deleted]
  - sroussey 3y ago
    The beauty of PHP is that it is stateless and the end of the run, everything is freed. It is difficult to have memory leaks. Personally, I like using Typescript/Javascript on both front end and backend, but I don’t look down at PHP backends at all. And it’s come a long way as a language. I’ve been a fan of rolling your own stdlib as the semantics there are old and weird, but vscode tells you so who cares anymore.
- stephenr 3y ago

endorphine 3y ago

As opposed to what? Postgres? Isn't InnoDB most performant for read-heavy apps?

dijit 3y ago
MyISAM is actually considerably faster (than InnoDB) for read heavy apps. InnoDB is comparatively slow, but you get much better transactionality (IE; something that is much closer to ACID compliance). Row level locking is faster for inserts than table level locking, but table level locking is faster for reads than row level locking. Regardless: Both storage engines do not scale with core count as effectively as postgres due to some deadlocking on update that I have witnessed with MySQL. (not that Postgresql is the only alternative btw).
- VintageCool 3y ago
  There is no excuse to be using MyISAM instead of InnoDB in 2023. It was a scarcely forgivable mistake in 2013. The read performance advantages of MyISAM are solved better by using SSDs instead of HDDs. MyISAM will cost you dearly when performing actions like "trying to create a new replica of an existing database".
- menaerus 3y ago
  MyISAM is not a transactional storage engine even to begin with, so saying that you get "much better transactionality with InnoDB" or "MyISAM is actually considerably faster" is either wrong or at best comparing apples to oranges. > Both storage engines do not scale with core count as effectively as postgres due to some deadlocking on update that I have witnessed with MySQL. Strange take since a deadlock is rather an exceptional event you want never to occur so deadlocking, in algorithm design, wouldn't be considered a reason one would say that the implementation does not "scale with the core count". Whether or not the algorithm scales with the core count is for many other different reasons but not deadlocks. Considering the "scale with the core count" design problem, Postgres process-per-connection architecture makes it a much less viable option than, say, MySQL so this is wrong as well.
  - 3y ago

Topgamer7 3y ago

MySQL has some aggregation performance over postgres. Having done a recent migration of an application two things that come to mind are: - its case insensitive by default, which can make filtering simpler, without having to deal with a duplicate column where all values are lower/upper cased. - MySQL implements loose index scan and index skip scan, which improves performance of a number of join aggregation operations (https://wiki.postgresql.org/wiki/Loose_indexscan https://wiki.postgresql.org/wiki/Loose_indexscan)

dpratt 3y ago
| its case insensitive by default This is obviously up for debate, but subjectively I find this to be an absolutely terrible design decision.
- hu3 3y ago
  I agree it's debatable. And not intuitive at first. With that said, in all my years and thousands of tables across multiple jobs, I have yet to see a single case where I had to change a table to be case sensitive. So I guess for me it is a sensible default.

dissident_coder 3y ago

First, MySQL is the "devil you know". If you've spent a decade working exclusively with MySQL quirks, you're just gonna be more comfortable with it regardless of quality. MySQL also tends to be faster for read-heavy workloads and simple queries. Also replication is easier to setup with MySQL in my (outdated) experience, even though it's gotten better with Postgres recently and I haven't really been able to compare them myself since I'm just using Amazon RDS Postgres these days and haven't had the need to setup master-master replication (which is the pain point in postgres, and was pretty straightfoward with mysql the last time I worked with it). Setting up read-replicas with postgres is still ezpz. Postgres specific features tend to be much better than MySQL ones, Postgresql JSON(b) support blows MySQL out of the water. And as far as I can remember MySQL still doesn't support partial/expression indexes, which is a deal breaker for me. Especially in my json heavy workloads where being able to index specific json paths is critical for performance. If you don't need that kind of stuff, you might be fine - but I would hate to hit a wall in my application where I want to reach for it and it's not there. MySQL used to be the only game in town, so it was the "default" choice - but IMO postgres has surpassed it.

darrenf 3y ago
> And as far as I can remember MySQL still doesn't support partial/expression indexes, which is a deal breaker for me. Especially in my json heavy workloads where being able to index specific json paths is critical for performance. Do generated column indexes meet this need? CREATE TABLE json_with_id_index ( json_data JSON, id INT GENERATED ALWAYS AS (json_data->"$.id"), INDEX id (id) ) https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html https://dev.mysql.com/doc/refman/8.0/en/create-table-seconda...
- simcop2387 3y ago
  Looks like that would work as an expression index, though i can't tell at a glance if this requires the column to also be stored which would increase storage size (but probably isn't a huge problem if it is). But that likely won't work for dealing with the partial index case where you're only wanting to keep the ones that aren't null in the index to reduce the size (and speed up null/not null checks).

Thaxll 3y ago

Your memory is failing you maybe you don't remember not too long ago when PG did not have any replications built-in.

mrkeen 3y ago
Not too long ago MySQL didn't have transactions. Edit: I would just love a comment from the person who thinks 'missing feature in the past' is wrong, unfair or irrelevant as a reply to a 'missing feature in the past' comment.
- evanelias 3y ago
  There's a non-trivial nine-year difference between the things you're describing: the InnoDB storage engine was released in 2001. Postgres gained built-in replication in 2010. That said, personally I wouldn't describe either of these as "not too long ago". Technology rapidly changes and many things from either 2001 or 2010 are considered rather old.

Freeaqingme 3y ago

Also from an operations point of view it's quite easy to manage. I'm not that experienced with Postgresql, but my understanding is that until recently you had to vacuum it every once in a while. Besides, it's also using some kind of threading model that most people handle by putting a proxy in front of Postgres to keep connections open. Also, Mysql has had native replication for a very long time, including Galera which does two-step commit in a multimaster cluster. Although Postgres is making some headway in this regard, it is my impression that this is only quite recent and not yet fully up to par with Mysql yet.

stonemetal12 3y ago
>my understanding is that until recently you had to vacuum it every once in a while. You still do. The auto Vacuum daemon was added in 2008ish, so it isn't too bad. Just more complexity to manage. > it's also using some kind of threading model It does a process per connection just like web servers did back in the day when C10k was a thing. A lot of the buffers are configured per connection so you can get bigger buffers if you keep the number of connections small.
dgellow 3y ago
I think what you reference is known as Transaction ID Wraparound, Postgres still needs to be vacuumed to avoid that problem: https://www.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql https://www.crunchydata.com/blog/managing-transaction-id-wra...

adontz 3y ago

Just want to add, that comparing to postgresql is a very modern view. There were other databases, not popular today, but quite popular back in the day. To name a few: DB2, InterBase, Firebird, Paradox, Access, SQL Server Compact. MySQL was a really shitty database in early 2000s, still THE most popular.

viraptor 3y ago

Others mentioned a few reasons already, but compared to postgres (because typically that's the other option) I'll add index selection. Even with the available plugins and stats and everything, I don't want to in an emergency situation spend time trying to indirectly convince postgres that it should use a different index. "A query takes 20x the time and you can't force it back immediately" is a really bad failure mode.

deleted 3y ago

[deleted]

turtles3 3y ago

This is a great question, and if the choice is between mysql and postgres, I would like to make the case that despite the current popular momentum behind postgres, mysql is a better default. Please note I'm not saying mysql is better, but in the absence of any other criteria, I would suggest stating with mysql. I have a few reasons for this view, but they mostly revolve around operational complexity. From a developer's point of view postgres is fantastic. Far saner SQL dialect, tons of great features. When it comes to operations though, that's where mysql has the edge, and ops is half of using a database - it's an important facet for a business to consider. As other commenters have mentioned, postgres requires careful tuning of the autovacuum process, otherwise it can't keep up as the workload grows. Postgres has a far more advanced query planner, but it comes at the cost of potentially blowing up your app at 3am, and it gives you no tools to patch in a quick fix while you address the root cause. This frankly ignores the reality of operating a business. Sometimes you need a quick fix, even if that might lead to users developing bad habits. Yes there is the pg_hint_plan extension, but that still only helps you later after the problem had happened. You can't pin a query plan. To me the ideal situation would be for postgres to continue to use the old query plan, but emit some structured log to tell you it thinks it's now suboptimal. But I digress. Thirdly, postgres has no way to have an index clustered table. This lets you trade a small cost on write for greater page locality when reading related rows. Postgres let's you do this as a one time operation that takes the table offline for the duration, which isn't sufficient if you need it. Fourthly, mysql is still easier to upgrade. You will need to upgrade your database at some point. Mysql has great support for upgrade in place, as well as using replication to build a new db. Mysql replication has always been logical replication, which has tradeoffs of course, but what it buys you is the ability to replicate across different versions. Pg's logical replication still has a bunch of sharp edges. Ok this rant is long enough already, but I do want to emphasise that this isn't hating on postgres. I know it's controversial to be recommending mysql over postgres, but I do think the ops concerns win out. Ps the orioledb project is fantastic and I hope it one day becomes the default for postgres.

wbl 3y ago
Isn't working in the first place an ops concern?
3y ago

deleted 3y ago

[deleted]

lmm 3y ago

> Why would anyone start a new project with MySQL? Is it really superior in anything? It's the most developer-friendly thing out there. Particularly for a datastore CLI, which is inherently something you use rarely, MySQL's is just a lot nicer, more discoverable. I think it has the least bad HA story among (free) traditional SQL-RDBMSes too (not that I understand why anyone would start a new project on a traditional SQL-RDBMS at all).

otabdeveloper4 3y ago

> Is it really superior in anything? Yes, its replication support out of the box is decades ahead of Postgres. (Mongodb has an even better replication story than Myqsl, but Mongo isn't a real database.)

bojanz 3y ago

I am going to be controversial for a hot second, and say that in many ways MySQL is a more advanced and better implemented database at its core. Disclaimer: as a developer I love and prefer Postgres. But I've been on many projects where MySQL won for ops-related reasons. Postgres has a MVCC implementation that is recognized as inferior[0] to what MySQL and Oracle do, and requires dealing with vacuuming and all of its related problems. Postgres has a process-based connection model that is recognized as less optimal than the thread-based one that MySQL has. There are ongoing efforts[1] to move Postgres to a thread-based model but it's recognized as a large and uncertain undertaking. Other commenters have also explained the still very noticeable difference in replication support, the lack of query planner hints, the less intuitive local tooling. One thing to keep in mind is that both databases keep evolving, and old prejudices won't take us far. Postgres is improving its performance and replication support with each release. MySQL 8.0 added atomic DDL and a new query planner (MariaDB did their own query planner rework in 11.0, widening their differences). Both are improving their observability. So the race is far from over. But I definitely wouldn't count MySQL out. [0] https://ottertune.com/blog/the-part-of-postgresql-we-hate-the-most https://ottertune.com/blog/the-part-of-postgresql-we-hate-th... [1] https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd9-af5b-ac0d801163f4@iki.fi https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd...

misiek08 3y ago

I don't want to be subjective or start any war, but only to broaden my horizons and perspective. Saying this I want to clear one thing - I use multiple engines, trying to best match one for the problem I'm solving. What do you recommend these days?