I'm a long-time (~1995) Postgres fan here, and my opinion is that MySQL today doesn't have all the weird restrictions and limitations that it used to have in the past. I don't get super deep into advanced features, but when I've used it lately it has had all the features I wanted. Though I was looking for a JSON data type earlier today and that didn't seem to be in there.

I did pick MySQL over Postgres for a old project that I'm currently working on again, the primary reason was easy multi-master replication via galera. I know that Postgres has long had many cluster stories, but I always struggled to even figure out which one was the right choice to make. Most of the clusters I've deployed ended up using DRBD and heartbeat rather than one of the Postgres native options.

As far as advance features, quite a few years ago I was doing this exercise to generate reports on some sample payroll dataset. The expectation was clearly that you would run a query, massage that data in some code, then run a bunch of other queries in that code to further correlate the data. I forget the exact problem that was posed.

I'm not super strong in database advanced features, but in the end I was able to come up with some fairly simple SQL, using materialized views or CTEs or similar, that produced the report entirely using SQL, in a fairly short runtime.

This was my first real experience with using MySQL beyond the basic features it had in decades past, and I was quite impressed with it.

> Though I was looking for a JSON data type earlier today and that didn't seem to be in there.

Which JSON type are you missing? I store and query JSON extensively (probably too extensively) in MySQL and most things work great.

Thanks, I misunderstood the docs page for JSON, it says it is an alias for LONGTEXT, so I just used the latter, I didn't understand that it was doing more than just treating it as text. In my case I'm not expecting to do more with the JSON than treat it as a BLOB, so I didn't dig too far. I just changed my schema to JSON and I'm re-ingesting test data now.

Are you using MySQL or are you using MariaDB? They are distinct databases with different features.

MySQL has had a full-featured native JSON column type since MySQL 5.7 (Oct 2015). Here's the current manual page for MySQL 8: https://dev.mysql.com/doc/refman/8.0/en/json.html

MariaDB does not have a JSON column type, instead aliasing it to LONGTEXT. MariaDB takes an alternative approach of providing JSON manipulation functions which work with stringy types. https://mariadb.com/kb/en/json-data-type/

Note that in some Linux distributions, when you try to install "mysql" packages you instead get "mariadb", so you may need to query the server to see which one it really is!

This was in Maria, thanks for clearing that up. I thought I had mentioned MariaDB but I guess that was another response, sorry for that confusion.

>I did pick MySQL over Postgres for a old project that I'm currently working on again, the primary reason was easy multi-master replication via galera.

I plan to use CockroachDB as a distributed data store since it's protocol compatible with Postgres. Still in testing phase, but the deployment is very easy.

Agreed, the deployment was very easy, that was one of the reasons I wanted to switch to Cockroach from MySQL+Galera. It took me less than a day to get ansible roles to set up a Cockroach cluster, but around a week for the MySQL Galera cluster, to get it completely right. And that was starting with somebody else's Maria+Galera Ansible role.

My blocker was that I'm using it in part for Postfix maps, and the Postfix pgsql module can't be configured to us anything but LATIN-1 encoding, and Cockroach only supports UTF-8.

CitusDB shards PostgreSQL nicely for both data ingestion bandwidth and load balancing + HA

I haven’t used PG in production so I’m probably biased, naive or both, so I know this isn’t a direct response to your question, but…

MySQL has been great for every project I’ve used it in and haven’t had any issues. Largest app we used it in was Twitpic which handled high query traffic and along with the built-in replication, it was easy to use. We had replication lag on spinning disks (this was 2009) but switching to SSDs fixed it. Backups with xtrabackup from Percona is a key piece though for taking incremental or full DB backups.

I think back-in-the-day, PG didn’t have replication built-in and that always kept me using MySQL, but I’ve honestly never understood the bad rap MySQL got in years previous as I’ve never run into any of the described issues and it has been rock solid for me in apps of all sizes.

It's mostly fine and certainly has some advantages, but it also continues to have weird behavior that can randomly creep up. My recent favorite is[1]:

> MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification.

Because of course it's very reasonable to accept a foreign key specification but completely ignore it.

[1]: https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-ke...

We gave the geospatial features a try with a real estate dataset that contains ~50M rows. It's crappy to say the least. Half-baked features, poor performance, random crashes, barely documented gotchas with no good reason.

At least the MariaDB folks try to fix the problems that are passed down to them. MySQL just seems content to tick the box, "Yeah we have that feature too, please don't switch to PostgreSQL!" and leave it at that. MySQL is okay for typical CRUD workloads with old-fashioned scalar types, but don't expect much with respect to the newer, fancier features.

The list of companies using MySQL as their primary database is absolutely massive. It's a sizeable chunk of the world's economy and it's not just used for CRUD workloads. InnoDB is admittedly much better at OLTP than OLAP, but OLTP encompasses a lot more than simple CRUD workloads.

> At least the MariaDB folks try to fix the problems that are passed down to them

This assertion simply does not make sense. MariaDB hard-forked its codebase from MySQL's a full decade ago. They were able to use/port some open source MySQL code through ~5.7 but that seems to have stopped in recent years with MySQL 8, probably due to its major architectural changes.

These days MySQL and MariaDB each have some nice features that the other one lacks.

With respect to what is "passed down", by whom and to whom: the creator of MySQL is literally the chief architect at MariaDB, and many other early MySQL AB engineers work at MariaDB. Meanwhile most of MySQL's poor reputation today is a lingering effect of its early versions, largely before the Oracle acquisition of Sun. In this context, I am continually confused as to why some Postgres users have a higher opinion of MariaDB than MySQL.

Edit to add: I failed to mention that I do absolutely concede that for a new application with heavy geospatial component, Postgres+PostGIS is absolutely the correct choice. InnoDB's geospatial support is comparatively much newer and less widely used.

MySQL’s spatial indexes work just fine and yield extremely low-latency queries when used correctly.

Well, the last time we tried to insert a bunch of complex polygons (hundreds of points, multiple parts, etc.) into an indexed column using several concurrent transactions, MySQL crashed.

I think to think of it as an interface, https://dbdb.io/browse?compatible=mysql

These days you have shiny companies like PlanetScale and SingleStore implementing the MySQL wire protocol.

MySQL been rock solid for us, handling a million+ queries per hour on a fairly large database, running continually for over 10 years (other than occasional updates), but we're not using any advanced features, just run of the mill relational db with some full text searching. The tooling (phpmyadmin, percona xtrabackup) and knowledge available online are also a big plus.

I use MySQL (and increasingly, MariaDB) because I prefer the tooling. I use software like PHPMyAdmin and mycli, and when I need to use Postgres or MSSQL the tools available do not live up to the same high standards.

What does PHPMyAdmin offer over, say, Postico? I avoid MySQL because of its history of being "a bit of a mess" and its lack of tooling.

I’ve heard of it for a starter…

Tryin to find Postgres tooling like that hasn’t been very productive. Thanks for the tip just for this one.

I've used both professionally. I only have a mild preference for PG, MySQL has been perfectly serviceable for me.

I'm using MySQL 5.6 and 5.7 and the error messages for syntax errors in queries are next to useless.

In terms of popularity, it sure looks like that ...

https://db-engines.com/en/ranking_trend

It's terrifyingly bad and its global buffer pool mutex that's designed to look smart on benchmarks falls to pieces if you have a few dozen queries running at the same time. The only places I've had to suffer through it were ones that made the fatal decision early in the life of the company when they didn't have anyone on staff qualified to make that decision, but then were stuck with it forever.

This comment looks like an attempt at implying some kind of deep knowledge, but you're demonstrating a terrible ability to convey information. "Global buffer pool mutex that's designed to look smart" does not fall apart if you have a few dozen queries running at the same time. Source: the place I work at has a multiple of several thousand of what you wrote, and surprisingly - nothing falls apart, and we've been using MySQL for decades. Like any software, it has its negatives and positives.

Our job is to be aware of shortcomings and work around it. This rule applies to any software, any language. I'm sorry that using MySQL has made you so mad, but deliberate spreading false knowledge simply because you're angry helps no one.

I think you need to update your knowledge about MySQL and not bang on about v5.1 and earlier.