What's MySQL like as a database these days? I get the impression that it used to be a bit of a mess but it's improved a lot and has some advantages over Postgres.
Did anyone choose it over pg, and if so, why?
Did anyone choose it over pg, and if so, why?
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.
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.
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.
Which JSON type are you missing? I store and query JSON extensively (probably too extensively) in MySQL and most things work great.
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!
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.
> 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...
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.
> 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.
These days you have shiny companies like PlanetScale and SingleStore implementing the MySQL wire protocol.
Tryin to find Postgres tooling like that hasn’t been very productive. Thanks for the tip just for this one.
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.