3 ms·

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 th

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.

mmerlin 5y ago
CitusDB shards PostgreSQL nicely for both data ingestion bandwidth and load balancing + HA
DeathArrow 5y ago
>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.
- linsomniac 5y ago
  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.
porker 5y ago
> 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.
- linsomniac 5y ago
  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.
  - evanelias 5y ago
    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 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/ 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!
    - linsomniac 5y ago
      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.

mmerlin 5y ago

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

DeathArrow 5y ago

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

linsomniac 5y ago
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.

porker 5y ago

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

linsomniac 5y ago
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.
- evanelias 5y ago
  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 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/ 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!
  - linsomniac 5y ago
    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.