Maybe this is a cynical take - but we used https://pgloader.io/ a few years ago to migrate to Postgres, and have never been happier. MySQL has a lot of stupid decisions like this.
Postgres defaults to the system's collation for UTF-8, which caught me by surprise. Now I'm stuck with a production DB where text prefix queries, which should be a perfect use of a btree, are a full table scan. Probably gonna have to take some maintenance downtime to fix the situation :-\
And more generally about operating Postgres in production: https://blog.nelhage.com/post/some-opinionated-sql-takes/
As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it slightly wrong can utterly tank your performance or availability. In addition, because MySQL is, in my experience, more widely deployed, it’s easier to find and hire engineers with experience deploying and operating it. Postgres is a fine choice, especially if you already have expertise using it on your team, but I’ve personally been burned too many times.
The only supported way to set the database's default collation is at creation time, so I'd need to create a new database, copy the data over, and switch my application over. (There appears to be some Postgres voodoo to change the default collation for an existing database, but that's too risky for me.)
Until I get that done, I need to explicitly specify the collation for all new text fields, e.g.
CREATE TABLE t (
f1 text NOT NULL COLLATE "C"
)
The answer is Postgres. I have some previous experience with MySQL and there are advantages and disadvantages. The biggest advantage so far is partial indexes.
But since I don't have as much experience with Postgres, there's still the occasional surprise, e.g. index names not being scoped to the table, this collation thing. And from what I've read online, I should probably set up some monitoring for vacuuming issues.
The article about Postgres operational footguns recommends outsourcing those issues by using a managed Postgres service (e.g. AWS RDS or Google Cloud SQL), and that's what I do.
But those preferences may be specific to my situation -- a software developer running an HA web application.
* If you're not a software developer, you might be surprised by the "C" collation, which puts "Z" before "a".
* If you're not running an HA web application, then it's easy to change the collation later -- just dump/restore the DB.
I don't think it's always smart to just change out your data layer.
But, if you're starting a new project, I do think PG is one of the better options and tends to follow the principle of least surprise. (hence: big fan)
Index hints.
If you aren't hitting an index in Postgres you have to dig in to table stats and figure out what is wrong but MySQL gives you more control.
However, I would still rather work with Postgres AND have to juggle a connection pooler than deal with MySQL. Transactions on DDL are _great_ and the ability to use foreign keys across partitioned tables is how it should be.
It’s a shame that almost every job I worked at uses MySQL and not Postgres. But that could be because those companies all got their start like a decade or more ago when Postgres was not as well known.
> Index hints.
Eh, it's an extension. One you shouldn't use, but it's there.
Is this comment relating to the overhead of idle connections, which has historically necessitated the use of a pooler in front of PG? If so, I believe this is resolved in postgres 14
https://pganalyze.com/blog/postgres-14-performance-monitorin...
Perhaps, but I'd argue that the "weird" behavior of postgres just tends to be clearly thought out design decisions that they made for a valid reason that may cause you some pain with how you use it (e.g. their process-per-connection model).
MySQL's "weird" behavior, on the other hand, just tends to be completely invalid footguns like this. Despite what some people are arguing in this thread, a 3-byte version of UTF-8 was never in any spec anywhere and was an invalid shortcut from day 1.
I hated how postgres forced you to create a system user to connect I wonder if it still requires this.
We only caught this, because thankfully, we had written a manual checksum script, which looped through every table, read out all values from each row into the application, and hashed the results, then compared between when the app was connected to the source MySQL database vs the destination Postgres database. We ended up having to massage and fix those silently-corrupted characters.