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.
As someone who learned to think in MySQL, this is really true, at the time Postgres was a viable alternative too, only the tooling to get started reached me a little easier and quicker.
The major thing I advocate for is don't pick a NOSQL database to avoid relational dbs, only to try and do a bunch of relational work in NOSQL that would have been trivial in an RBDMS. Postgres can even power graph query results which is great.
> The major thing I advocate for is don't pick a NOSQL database to avoid relational dbs, only to try and do a bunch of relational work in NOSQL that would have been trivial in an RBDMS.
It has always felt to me like devs will gravitate towards doing the opposite of what makes sense for their DB. If they have a Document DB, they'll try to use it relationally. If they have a relational database, they'll shove everything into a JSON column. Then in both cases, they complain that it's slow.
> 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.
You can achieve that with Postgres as well if you accept duplicating the data by adding an index with an include clause containing all the non-key columns you want to return in the SELECT clause. This way, you'll get fast index-only scans.
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.