3 ms·

This article has some flaws/misunderstandings. ACID Transaction: They state that MySQL has transactional DDL in a table, with the caveat that it "can't be per

This article has some flaws/misunderstandings. ACID Transaction: They state that MySQL has transactional DDL in a table, with the caveat that it "can't be performed within another transaction," which is linked to the MySQL docs that clearly state that is it NOT transactional DDL. Replication: They state that Postgres is physical via WAL, MySQL is logical vs binlog. While the first part is definitely true, arguably MySQL using row-based replication is close to physical replication, in that it describes the end state for a given tuple, rather than re-executing a statement. This also elides the fact that MySQL doesn't need a unified WAL because it has undo logs and redo logs, which combined with binlogs, perform all necessary functions of the WAL. JSON: They state that Postgres has more operators to access JSON features, and that Postgres allows the creation of indices on JSON fields. I would argue that MySQL is if anything less confusing, since it only has a single type (JSON) which internally stores the data in a binary format; Postgres has both JSON and JSONB, forcing you to decide - if you know the difference and their pros/cons at all. Furthermore, MySQL has plenty of operators to access and modify JSON values, including in-place partial updates. Perhaps if the article listed what specific operators were lacking in MySQL, I could have a stronger opinion. As to indices, MySQL allows you to create indices on generated columns from extracted scalars of a JSON value. Postgres can create GIN indices, but only if you're using JSONB. The tradeoff in complexity between the two, I think, is in the DDL vs. DML (or DQL if you're being pedantic). With MySQL, you have to add a generated column [0] (which can be made invisible, if you want to hide it from SELECT * queries) from whatever scalar you'd like to index, and then use _that_ column in WHERE predicates. Conversely, Postgres lets you create the index using nearly the same syntax as a normal index, but then forces you to use abstruse operators [1] in queries. Window Functions: They say that "Postgres supports more advanced window functions, such as LAG(), LEAD(), FIRST_VALUE(), and LAST_VALUE()." MySQL supports all of those [2]. Extensibility: Admittedly, Postgres has a larger ecosystem. The specific example they list - PostGIS - has some support natively in MySQL, though, via its spatial data types [3]. I've not used it myself, and know it lacks K-nearest-neighbor searches for now, but to imply that there is zero support for it in MySQL is untrue. Usability: "MySQL allows to include non-aggregated columns in a SELECT that uses the GROUP BY clause. Postgres doesn't." By default, MySQL does not allow this (ONLY_FULL_GROUP_BY). If you desire that behavior, you can modify it for a session, or globally. "MySQL is case-insensitive by default. Postgres is case-sensitive by default." This isn't exactly true. Postgres has a default collation that is case-sensitive, yes, but it also silently casts unquoted identifiers to lowercase, thus `SELECT fOO FROM baR` is identical to `SELECT foo FROM bar`. If you use double-quotes, it maintains case as specified. MySQL does use a case-insensitive (and accent-insensitive) collation by default, true, and you can switch that out at a database, table, or column level as desired. [0]: https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html#json-column-indirect-index https://dev.mysql.com/doc/refman/8.0/en/create-table-seconda... [1]: https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSONB-OP-TABLE https://www.postgresql.org/docs/current/functions-json.html#... [2]: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html https://dev.mysql.com/doc/refman/8.0/en/window-function-desc... [3]: https://dev.mysql.com/doc/refman/8.0/en/spatial-type-overview.html https://dev.mysql.com/doc/refman/8.0/en/spatial-type-overvie... [4]: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

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

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