You get SQL and ACID. If you don't need those then you pay a performance price for having them. If you do need them, then you pay a price for not having them.

The best solution depends on the unit economics of the problem you are trying to solve. If you have a small number of high value users, then these approaches are premature optimisation, just use Postgres. If your business model is ad eyeballs then squeezing every last drop begins to seem very attractive because you can multiply your profitability (potentially).

You usually want ACID, but with N+1 queries not being a problem you no longer need SQL. The database can now be a really low-level key-value store with your favorite query language on top.

Litestream author here. I wrote an embedded key/value database called BoltDB a while back and I experimented with using it for application development. It had its pros and cons. The pros were that it was crazy fast and a lot of data access is just basic iterators. Some of the biggest cons were having to implement indexes on my own and that my schema was tied to my application language. Migrations can be more difficult if everything is tied to structs in your app and it's also difficult to inspect your database since there's no CLI REPL.

So the answer to my question might be "because SQLite already does some stuff really well, like indexes and its CLI, and it just happens to be relational and use SQL as its query language, but that's not the important part". I buy that.

So what you're saying is we need FoundationDB-like layers on top of the fundamental data store? :D

Yeah, but what if my favorite query language is SQL? And what if I want to grab things not by the key (all items in area X, between times Y and Z)? Key-value isn't really great at that.

Most data is relational, so why not store it that way?

Or, from another angle, what would your “local cache” be?

Objects / documents / "key-value". If the data is small enough to fit on a single application server node, it seems to me that the relational model is likely overkill. But I don't doubt that there are good counter-examples to this, it's just my intuition.

Key-value pretty trivially fits into the relationship model if need be. But yeah, if you’re very sure of the key-valueness of your data, something like RocksDB could be a more direct fit. It’s just a little harder to work with in my experience.

I guess my experience is the opposite. We're probably both right, just about different things.