Yes PostgreSQL (and others) are a better choice in these scenarios. I think the point is that a lot of applications/systems might not even need this separation since a single server would be able to handle the load. In this case a local SQLite database could be a serious performance enhancement.
A lot of factors play into this and it certainly does not work in every case. But I recently got to re-write an application at work in that way and was baffled how simple the application could be if I did not outright overengineer it from the start.
That is just anecdata. But my guess is that this applies to a lot of applications out there. The choice is not between PostgreSQL/MySQL and SQLite, but between choosing a single node to host your application or splitting them between multiple servers for load balancing or other reasons. So the choice is architectural in nature.
A lot of factors play into this and it certainly does not work in every case. But I recently got to re-write an application at work in that way and was baffled how simple the application could be if I did not outright overengineer it from the start.
That is just anecdata. But my guess is that this applies to a lot of applications out there. The choice is not between PostgreSQL/MySQL and SQLite, but between choosing a single node to host your application or splitting them between multiple servers for load balancing or other reasons. So the choice is architectural in nature.