There should be a blog post to point to people who have a 1990s view of MySQL. It has changed a lot and has most of PostgreSQL's feature set while being faster and simpler to use for the average user.
> and has most of PostgreSQL's feature set while being faster and simpler to use for the average user.
I would read that blog post, because I am firmly in the "mysql/mariadb is for people who like mongo" camp but I like learning new things
While I was delighted to see that 11 no longer just straight-up throws input data in the trash, I get a lot of mileage out of transactional DDL which seems to still be a PG feature
docker run -d --name my -e MARIADB_ROOT_PASSWORD=sekrit docker.io/library/mariadb:11.4.7
docker exec -i my mariadb -psekrit mysql <<SQL
BEGIN;
CREATE TABLE just_kidding (pk int);
CREATE TABLE onoz (migrations are hard, yo);
ROLLBACK;
SQL
docker exec -i my mariadb -psekrit mysql <<SQL
SELECT count(1) FROM just_kidding;
SQL
count(1)
0
While digging into its stored-proc story, I found these two gems
> I was delighted to see that 11 no longer just straight-up throws input data in the trash
You're essentially proving the upthread commenter's point here... the relevant setting is strict sql_mode, which has been available as an option for literally 20 years, and has generally been used by any serious MySQL/MariaDB shop for that whole time. Long ago it wasn't enabled by default out of the box, but it has been since MySQL 5.7 (released 10 years ago) and MariaDB 10.2 (released over 8 years ago).
> I get a lot of mileage out of transactional DDL which seems to still be a PG feature
Correct, MySQL and MariaDB do not support transactional DDL, and maybe never will. That's not a unique shortcoming though, as Oracle and SQLite don't support it either. MS SQL Server does support it, but if I recall correctly there are caveats depending on the isolation level in use.
Postgres clearly wins out on that feature, but as with everything in computing, it comes with serious trade-offs: a rather sub-par MVCC implementation [1], and lack of DDL support in logical replication [2].
I'm biased because I work in this space, but IMO it's easy to live without transactional DDL in MySQL/MariaDB if you pair a good schema management system (which allows you to test and lint DDL) with an online schema change tool (which allows you to throw away the shadow table if something goes wrong). And generally you shouldn't be running DDL by hand directly in prod anyway...
Well, I did say that I'd enjoy learning more, because I'll join in any good rant about autovacuum being some "oh gawd"
But, let's be real talk here: what kind of RDBMS ships with a flag named "strict sql mode" that's available to be set to something? Its reputation wasn't born from uncharitable twitter take downs, it was the kind of thing I had to try in order to know if it still did crazypants things like `CREATE TABLE foo (d DATE); INSERT INTO foo VALUES ('lololo')`. So, sure, I hear you about "it hasn't been stupid for 10 years" but don't lose track of the first part of that qualifier
I hear rumors that Mongo isn't insaney pants anymore, either, but I for damn sure ain't running that shit and will quit places that do
> what kind of RDBMS ships with a flag named "strict sql mode" that's available to be set to something?
How about some of the most widely deployed database software on earth? For example SQLite doesn't even enforce column types by default today, and has only had the option to do so for less than four years!
In MySQL/MariaDB's case, yes they should have changed that default much earlier, but they historically over-indexed on backwards compatibility concerns around that time period.
Mostly on the administrative side. MySQL doesn’t have VACUUM to deal with, and that inevitably catches people by surprise in Postgres if they aren’t already experienced with it.
Postgres is far more flexible and capable, but it requires a lot of care and feeding, as well as deeply reading its docs to get the most out of it. MySQL is good enough for 99.99% of tech companies, and requires little to no maintenance in comparison.
Both will require you to know how to design a performant schema and query, however.
I would read that blog post, because I am firmly in the "mysql/mariadb is for people who like mongo" camp but I like learning new things
While I was delighted to see that 11 no longer just straight-up throws input data in the trash, I get a lot of mileage out of transactional DDL which seems to still be a PG feature
While digging into its stored-proc story, I found these two gemshttps://mariadb.com/kb/en/sql_modemssql/
https://mariadb.com/kb/en/sql_modeoracle/
which I would enjoy exploring more