3 ms·

Postgres has supported both binary and logical replication for many years now. In fact, in v16 (now in beta) it supports bidirectional replication (aka multi-ma

by ttfkam 3y ago

Postgres has supported both binary and logical replication for many years now. In fact, in v16 (now in beta) it supports bidirectional replication (aka multi-master) out of the box [0]. Beyond that, the feature disparity between MySQL and Postgres is still massive even with the recent improvements in MySQL 8.x [1]. (Uncheck all the engines except PG and MySQL for a stark picture.) MySQL is to lighttpd as Postgres is to Nginx or Apache: far fewer features and not necessarily faster depending on the use cases. [0]: https://postgrespro.com/blog/pgsql/5969859#commit_36628396 https://postgrespro.com/blog/pgsql/5969859#commit_36628396 [1]: https://www.sql-workbench.eu/dbms_comparison.html https://www.sql-workbench.eu/dbms_comparison.html

sgarland 3y ago
The comparative link [1] is outdated and/or has mistakes. For example, in MySQL 8.0.31 (current 8.0 version is 8.0.34; latest 8.x version is 8.1), the `INTERSECT` and `EXCEPT` operators were added. Not that you couldn't get the functionality before, but now it's easier. Similarly, while MySQL doesn't have `NULLS {FIRST, LAST}` for `ORDER BY`, you can get the same functionality with `ORDER BY -<COL>`, which is shorthand for `ORDER BY (0 - <COL>)` - this places NULL values last. Some other things I noted: They claim that MySQL can't index on JSON documents, but as I pointed out in my parent comment, that is only true in the technical sense of it, since creating an index on a generated scalar column produces the same desired result. MySQL has booleans, in that they're aliases for `TINYINT`, which consumes 1 byte. AFAIK, Postgres stores booleans as a single `CHAR` internally, in order to display `t` or `f`, which would also consume 1 byte. MySQL has the `INTERVAL` keyword for date time comparisons. MySQL doesn't have a [working] `DROP TABLE CASCADE` (it exists, but is a no-op), but you can just as easily use `SET FOREIGN_KEY_CHECKS=0; DROP TABLE` for a session, if you want to leave the foreign key index dangling. I'm not trying to say here that MySQL is as feature-rich as Postgres, only that it has more features than people seem to give it credit for.
- ttfkam 3y ago
  Good to know about INTERSECT and EXCEPT. MySQL can use computed columns to index individual JSON fields or serialize the whole thing to a varchar for a broad search, but that's not at all the same as indexing with hierarchical structure preserved, which is what was being described. You cannot index for arbitrary path searches like you can in Postgres. The storage of booleans is a separate issue from syntactic support. In MySQL, true and 1 evaluate as equal. In Postgres there is a type mismatch without an explicit cast. Therefore MySQL does not truly support booleans. INTERVAL is not the same as a range type. Postgres supports intervals as their own type as well, eg., '18 months'::interval. Range types allow for useful exclusion constraints for scheduling and assigning finite resources. Since you mistook ranges for intervals, I HIGHLY recommend you take a closer look at ranges. They are VERY useful to have in your data modeling toolbox. https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTRAINT https://www.postgresql.org/docs/current/rangetypes.html#RANG... You can "work around" the lack of array types in MySQL with comma-delimited strings… kinda. The list of things you can "work around" in with MySQL is long, but not everything can be "worked around" without reinventing the wheel. Eventually it's worth examining whether or not it's worth "working around" instead of using a solution that "just works". MySQL has improved by leaps and bounds in the 8.x-era, and 5.5-5.7 were both huge improvements on the dumpster fire that came in the decades before. Thankfully the famous MySQL gotchas page isn't directly relevant anymore, but that's a very low bar. (Yes, I admit I hold grudges with regard to data integrity.)
  - sgarland 3y ago
    This is tangential to the discussion, but I've always felt that putting JSON into RDBMS is at best a kludge anyways - it's almost never in any normal form, and also if you wanted schema-less objects, then you should use a DB system meant for it. I can appreciate that this is a strict and academic approach that will likely collide with a business' needs, though, and your point about indexing on arbitrary path searches is valid. > In MySQL, true and 1 evaluate as equal... Therefore MySQL does not truly support booleans. Also a fair point. I mostly am in the Python world, and often forget that truthiness is not the same as strict equality. > Since you mistook ranges for intervals, I HIGHLY recommend you take a closer look at ranges. I'll spend some time looking into this, thank you for the recommendation. > The list of things you can "work around" in with MySQL is long, but not everything can be "worked around" without reinventing the wheel. Also true. Much like my first point on JSON in RDBMS, there are often times where business needs make it such that you're going to be forced to work around problems, so it's easy to become accustomed to finding such hacks. Inertia is a hell of a thing. > Yes, I admit I hold grudges with regard to data integrity. A very reasonable thing to hold a grudge about. I still regard Xeons with deep suspicion due to AVX-512 forcing heavy throttling on all cores back in the day. I run them, mind you, but forever in the back of my mind I wonder, "is someone else on this multi-tenant node slowing my workloads down?"
    - ttfkam 3y ago
      While Ted Codd is no longer around, Chris Date is, and even he has admitted the original "academic" notion of what constitutes a proper relational domain in SQL was incomplete/flawed. I can't recommend this presentation enough with regard to modern relational design: https://youtu.be/swR33jIhW8Q https://youtu.be/swR33jIhW8Q

by ttfkam 3y ago

sgarland 3y ago

The comparative link [1] is outdated and/or has mistakes. For example, in MySQL 8.0.31 (current 8.0 version is 8.0.34; latest 8.x version is 8.1), the `INTERSECT` and `EXCEPT` operators were added. Not that you couldn't get the functionality before, but now it's easier. Similarly, while MySQL doesn't have `NULLS {FIRST, LAST}` for `ORDER BY`, you can get the same functionality with `ORDER BY -<COL>`, which is shorthand for `ORDER BY (0 - <COL>)` - this places NULL values last. Some other things I noted: They claim that MySQL can't index on JSON documents, but as I pointed out in my parent comment, that is only true in the technical sense of it, since creating an index on a generated scalar column produces the same desired result. MySQL has booleans, in that they're aliases for `TINYINT`, which consumes 1 byte. AFAIK, Postgres stores booleans as a single `CHAR` internally, in order to display `t` or `f`, which would also consume 1 byte. MySQL has the `INTERVAL` keyword for date time comparisons. MySQL doesn't have a [working] `DROP TABLE CASCADE` (it exists, but is a no-op), but you can just as easily use `SET FOREIGN_KEY_CHECKS=0; DROP TABLE` for a session, if you want to leave the foreign key index dangling. I'm not trying to say here that MySQL is as feature-rich as Postgres, only that it has more features than people seem to give it credit for.

ttfkam 3y ago
Good to know about INTERSECT and EXCEPT. MySQL can use computed columns to index individual JSON fields or serialize the whole thing to a varchar for a broad search, but that's not at all the same as indexing with hierarchical structure preserved, which is what was being described. You cannot index for arbitrary path searches like you can in Postgres. The storage of booleans is a separate issue from syntactic support. In MySQL, true and 1 evaluate as equal. In Postgres there is a type mismatch without an explicit cast. Therefore MySQL does not truly support booleans. INTERVAL is not the same as a range type. Postgres supports intervals as their own type as well, eg., '18 months'::interval. Range types allow for useful exclusion constraints for scheduling and assigning finite resources. Since you mistook ranges for intervals, I HIGHLY recommend you take a closer look at ranges. They are VERY useful to have in your data modeling toolbox. https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTRAINT https://www.postgresql.org/docs/current/rangetypes.html#RANG... You can "work around" the lack of array types in MySQL with comma-delimited strings… kinda. The list of things you can "work around" in with MySQL is long, but not everything can be "worked around" without reinventing the wheel. Eventually it's worth examining whether or not it's worth "working around" instead of using a solution that "just works". MySQL has improved by leaps and bounds in the 8.x-era, and 5.5-5.7 were both huge improvements on the dumpster fire that came in the decades before. Thankfully the famous MySQL gotchas page isn't directly relevant anymore, but that's a very low bar. (Yes, I admit I hold grudges with regard to data integrity.)
- sgarland 3y ago
  This is tangential to the discussion, but I've always felt that putting JSON into RDBMS is at best a kludge anyways - it's almost never in any normal form, and also if you wanted schema-less objects, then you should use a DB system meant for it. I can appreciate that this is a strict and academic approach that will likely collide with a business' needs, though, and your point about indexing on arbitrary path searches is valid. > In MySQL, true and 1 evaluate as equal... Therefore MySQL does not truly support booleans. Also a fair point. I mostly am in the Python world, and often forget that truthiness is not the same as strict equality. > Since you mistook ranges for intervals, I HIGHLY recommend you take a closer look at ranges. I'll spend some time looking into this, thank you for the recommendation. > The list of things you can "work around" in with MySQL is long, but not everything can be "worked around" without reinventing the wheel. Also true. Much like my first point on JSON in RDBMS, there are often times where business needs make it such that you're going to be forced to work around problems, so it's easy to become accustomed to finding such hacks. Inertia is a hell of a thing. > Yes, I admit I hold grudges with regard to data integrity. A very reasonable thing to hold a grudge about. I still regard Xeons with deep suspicion due to AVX-512 forcing heavy throttling on all cores back in the day. I run them, mind you, but forever in the back of my mind I wonder, "is someone else on this multi-tenant node slowing my workloads down?"
  - ttfkam 3y ago
    While Ted Codd is no longer around, Chris Date is, and even he has admitted the original "academic" notion of what constitutes a proper relational domain in SQL was incomplete/flawed. I can't recommend this presentation enough with regard to modern relational design: https://youtu.be/swR33jIhW8Q https://youtu.be/swR33jIhW8Q