It's an application design choice. It's perfectly reasonable to consider those two options when designing a system. The constraints of the system determine which is better for the application.
I'd pick a centralized network-reachable database with a strong relational schema for write-heavy applications, and a lighter in-process system for something that is mostly reads and where latency matters. It's not a false dichotomy — but more like a continuum that certainly includes both extremes on it.
Re:LiteFS
by yencabulator on Thursday September 22, 2022 @11:33AM
(#32940360)
There's still plenty of overhead in serializing data over a unix domain socket to a different process, waiting for that process to be scheduled, waiting for it to serialize a response, waiting for the client process to be scheduled. SQLite avoids all of that.
However, "local SQLite vs. remote Postgres/MySQL" remains a false dichotomy when talking about network latency.