I'm not talking about extra code, I'm talking about _layers_ of code. With PostgreSQL you're still sending data over TCP/IP or a UNIX socket, and are copying things around in memory. Compare that to SQLite that runs in the memory space of the program, thus no need for copying and socket traffic. There's just less middlemen (middlepersons?) with SQLite that are unavoidable with PostgreSQL. So less layers = less interpreting/serialization/deserialization/copying/... = higher performance. I will even argue that even if the SQLite query engine is slightly less efficient than PostgreSQL, you're still winning because of less memory copying going around.

> less interpreting/serialization/deserialization/copying/... = higher performance

Unfortunately for many database workloads you are overestimating the relative cost of this factor.

> even if the SQLite query engine is slightly less efficient than PostgreSQL

And this is absurd - the postgresql query engine isn't just "slightly" more efficient. It is tremendously more sophisticated. People using a SQL datastore as a glorified key-value store are not going to notice - which seems to be a large percentage of the sqlite install base. It's not really a fair comparison.

With SQLite, though, you could reasonably just skip doing fancy joins and do everything in tiny queries in tight loops because SQLite is literally embedded in your app’s code. You can be careless with SQLite in ways you cannot with a monolithic database server because of that reason. I still agree there are use cases where a centralized database is better, but SQLite is a strange beast that needs a special diet to perform best.

Sometimes. This is only universally true if your datasets are tiny and your access patterns are simple. Moving a shitty suboptimal O(n^2) or worse algorithm locally when something linear or better is possible is going to hurt no matter where the DB is.

> but SQLite is a strange beast that needs a special diet to perform best.

I don’t see what is strange about it - for large datasets it’s the same complexity issues as anywhere else.

Not sure specifically what your comment is trying to add, since I acknowledged the type of use case SQLite excels in - those where roundtripping are a dominating cost and “k-v” stores, ie simple queries. My entire point is that those are a common but still niche use case.