Comment by dimgl

MySQL is definitely easier to use if you don’t want to ever have to think about DB maintenance; on the other hand, you’re giving up a TON of features that could make your queries enormously performant if your schema is designed around them - like BRIN indices, partial indices, way better partition management, etc.

OTOH, if and only if you design your schema to exploit MySQL’s clustering index (like for 1:M, make the PK of the child table something like (FK, some_id)), your range scans will become incredibly fast. But practically no one does that.

Major change for us to replace postgresql was replication and HA across geographies - i think on the Postgres side greenplum and cockroachdb are/were an option.

With MySQl variants like percona xtradb setup can go from 1 instance to cluster to geo replicating cluster with minimal effort.

While vanilla postges for an equivalent setup is basically pulling teeth.

I have a lot of respect for Postgres' massive feature set, and how easy it is to immediately put to use, but I don't care for the care and feeding of it, especially dealing with upgrades, vacuuming, and maintaining replication chains.

Once upon a time, logical replication wasn't a thing, and upgrading major versions was a nightmare, as all databases in the chain had to be on the same major version. Upgrading big databases took days because you had to dump and restore. The MVCC bloat and VACCUM problem was such a pain in the ass, whereas with MySQL I rarely had any problems with InnoDB purge threads not able to keep up with garbage collecting historical row versions.

Lots of these problems are mitigated now, but the scars still sometimes itch.

I’ve thought of doing this myself. Upgrades also seem easier in MySQL. That said, it does seem to be stagnating relative to Postgres.

I agree that SQLite requires less maintenance, but you still need to vacuum to prevent the database file from accumulating space (for apps, I run VACUUM at startup).

SQLite is awesome, no services to run, ports to open or anything like that, just a nice little database to use as you need.

It’s kind of remarkable how little operational maintenance mysql requires. I’ve been a Postgres fan for a long time, but after working with a giant mysql cluster recently I am impressed. Postgres requires constant babysitting, vacuums, reindexing, various sorcery. MySQL just… works.

There's a fascinating gap between PostgreSQL theory and practice here. Elsewhere in this thread, I complained that PostgreSQL extensions can't do everything yet. One thing they can do, however, or ought to be able to do, is provide alternative storage engines. That's the central thing they're supposed to be especially good at providing.

So what did the VACUUM-free, undo-based MVCC storage engine project stall? https://wiki.postgresql.org/wiki/Zheap

Why is there no InnoDB for PostgreSQL?

(Maybe OrioleDB will avoid a similar fate.)

MySQL is Oracle, you want MariaDB

I'm in the same boat, the idiosyncrasies of postgres are real; mysql / sqlite are far more predictable.