4 ms·
Postgres vs. MySQL: A Complete Comparison in 2023
- mikece 3y ago"MySQL is probably still the world's most popular open source database by install base." I wonder how much of that statement is a result of (1) WordPress and (2) large web hosting providers who only offer MySQL as a database (because WordPress is the dominant hosting need)?
- giraffe_lady 3y agoYou'd also need to qualify it to exclude sqlite, which is on everything. No way there's more wordpress sites than phones.
- mikece 3y agoI don't know... I run six wordpress sites and only have three phones.
- petre 3y agoActually SQLite would have been a much better and easier choice for most WordPress blogs.
- paulryanrogers 3y agoAutomattic seems to be coming around, they plan to offer official SQLite support in the future[0] [0] https://wordpress.org/plugins/sqlite-database-integration/ https://wordpress.org/plugins/sqlite-database-integration/
- ttfkam 3y agoAnd web browsers. Every major web browser uses SQLite under the hood for bookmark management, IndexedDB, localstorage, sessionstorage, and various metadata. Windows and MacOS also use it extensively in various apps installed by default. The Notes app in MacOS comes immediately to mind.
- spacedcowboy 3y agoSo I'm no database guru or anything, but I do spin up a database instance for projects from time to time. These days it's more likely to be SQLite rather than a multi-client one like MySQL or Postgres but it does still happen. I choose Mysql every time, despite knowing that Postgres has been "better" historically for a few things. The reason is simple, I can get my shit done a lot faster in MySQL. I can type "SHOW TABLES" and ... see the tables, instead of looking up in the manual for whatever obscure escape sequence is required for Postgres. When I do a SELECT * in the shell, the default is a nicely formatted result that's easy for me to read. That's not my experience with Postgres. I know that Postgres can do all these things, but I don't care - MySQL makes it easy, and that's one less thing to keep track of. Less barriers to entry == more times I'll use it. Just my $0.02.
- justinclift 3y agoWell... kind of the point of using things like "\d" for displaying things from the cli is that it's a lot faster to type rather than "SHOW TABLES". So: \dt <-- shows tables \df <-- shows functions \dv <-- shows views (there's a large number of things like this, super useful if you use the PG cli a lot) \? <-- shows the help, including all of the various other \<stuff> commands Then again, tab completion is a thing too I guess for the MySQL cli as well. :)
- spacedcowboy 3y agoI don't disagree with any of that. My point is that I know sufficient SQL to do "stuff" in a database, and I don't have to go looking up documentation for \d when I'm using MySQL. SHOW DATABASES, SHOW TABLES is intuitive. \d (and being a unix guy, using \ rather than / is ... weird) is definitely not intuitive. Don't get me wrong, I've used Postgres when someone else has installed it for a project - it's a fine and capable database, and if I used it daily I'd probably get used to its syntax. It doesn't, however, seem to go out of its way to make things easier, IMHO. If you're going to have \dx as shortcuts for the typing-shy, then I don't see why you can't provide the more long-winded ones that don't require a manual lookup as well... I'm going to choose something that makes my life easier, when the line between one or the other choice is so fine - and MySQL is just ... easier.
- g42gregory 3y agoI am surprised the article did not focus on Query Optimization/Query Manager. Last time I did a direct comparison between MySQL, Postgres and Oracle was a few years back. At that time, for very complicated queries, there was no comparison between Posgres and MySQL. Such queries will not come up in a startup environment, but could be a factor in Enterprise IT environments.
- deleted 3y ago[deleted]
- rat9988 3y agoCan you tell us more about your experience? What was the superior solution?
- sgarland 3y agoThis 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 agoThe complete feature comparison is here: https://www.sql-workbench.eu/dbms_comparison.html https://www.sql-workbench.eu/dbms_comparison.html Range types, array types, foreign table wrappers, partial indexes, statement-level triggers, MERGE, non-blocking index creation, row-level security, etc. And that all isn't counting the huge ecosystem of extensions and custom functions & types for Postgres. It still boggles my mind that in 2023, MySQL STILL cannot use a temporary table in a query more than once [0]. [0]: https://stackoverflow.com/questions/9364602/how-can-i-refer-to-a-temporary-table-more-than-once-in-the-same-query https://stackoverflow.com/questions/9364602/how-can-i-refer-...