In the case of one project I've been in, the issue was the ORM creating queries, which Postgres deemed too large to do in-memory, so it fell back to performing them on-disk.

Interestingly it didn't even use JOIN everywhere it could because, according to the documentation, not all databases had the necessary features.

A hard lesson in the caveats of outsourcing work to ORMs.

I've worked both with ORMs and without. As a general rule, if the ORM is telling you there is something wrong with your query / tables it is probably right.

The only time I've seen this is my career was a project that was an absolute pile of waste. The "CTO" was self taught, all the tables were far too wide with a ton of null values. The company did very well financially, but the tech was so damn terrible. It was such a liability.

Scalability is not the keyword here.

The same principle applies to small applications too.

If you apply it correctly, the application never going to be slow due to slow db queries and you won’t have to optimize complex queries at all.

Plus if you want to split out part of an app to its own service, it’ll be easily possible.

One of the last companies I worked at had very fast queries and response times doing all the joins in-memory in the database. And that was only on a database on a small machine with 8GB RAM. That leaves a vast amount of room for vertical scaling before we started hitting limits.

Vertical scaling is criminally underrated, unfortunately. Maybe, it's because horizontal scaling looks so much better on Linkedin.

Sooner or later even small apps reach hardware limits.

My proposed design doesn’t bring many hard disadvantages.

But it allows you to avoid vertical hardware scaling.

Saves money and development time.

Not really disagreeing with you here, but that "later" never comes for most companies.