Since I didn't specify the default collation when creating my database, Postgres inferred it from my system, so I ended up with "en_US.UTF-8". The "C" collation is probably what most developers expect.

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

https://simply.name/pg-lc-collate.html

Oh whoops, my previous response was about which collation I (want to) use. Were you asking which DB I use?

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.

MySQL or MariaDB/PerconaDB most likely. For all the footguns people are bringing up in this thread it's an extremely well trodden path where anything you could ever imagine doing with a DB someone has already done a hundred times with MySQL and documented it.