I'm going to plus one what others are saying here. As someone who wears dev and sysadmin hats, Postgres is just a way better database. For new projects, Postgres should be the go to unless you've got a niche use case.

Even with Diesel (or some other query builder) in use, it's not uncommon for apps to use special features specific to a database. It can also be a major pain just to test that your app actually works on these other databases, and then support weird slow downs, migrations, etc

I'll give the example that MySQL and MariaDB don't support transactions for SQL DDL. Basically what that means if that if you want to write a database migration to update the layout of one of your tables, there's no way in MySQL to have the database automatically revert your changes if something fails during the database changes... You're just screwed unless you have a backup. Meanwhile with Postgres, you just say rollback in your transaction, and things are back to normal.

There are also other less serious quirks like if you insert a NULL into and Integer field that's specifically marked not null, Postgres will error, MySQL/MariaDB will just silently convert that to a 0.

Like in a way, MySQL is the "JavaScript" of databases, and that's not what you want at all.

Possible, but I don't see it happening. Postgres provides functionality that MySQL/MariaDB don't, so if a lot of that functionality is used, the primary devs probably aren't going to want to take on the extra overhead of maintaining and testing it. In those cases, it would require additional rewriting on the Lemmy side to make it work seemlessly. A fork could likely do it, though.