Like 95% of websites that aren’t Amazon or google? Ton of sites that run in a single small vm. Postgres scales down quite nicely and will happily run in say, 512MB.

It’s not a stretch to imagine that a scenario where you’re willing to run SQLite locally is also one where it’s acceptable to run Postgres locally. You’ve presumably already got the sharding problem solved, so why not? It’s less esoteric of an architecture than multiwriter SQLite.

> I am pretty sure most of these vendors would offer strict guidance to not do that.

Then you'd be wrong. Running Postgres or MySQL on the same host where Apache is running is an extremely common scenario for sites starting out. They run together on 512 MB instances just fine. And on an SSD, that can often handle a surprising amount of traffic.

As popularity grows, the next step is to separate out the database on its own server, but mostly as a side effect of the fact that you now need multiple web servers, but still a single source of truth for data. Databases are lighter-weight than you seem to think.

In the scenario where you were choosing between it and SQLite...

You do of you access the same database from miltiple processes.