The single-file simplicity of SQLite is a huge win for self-hosted apps. I've been using SQLite in WAL mode for a single-user app and it handles concurrent reads from the API while background workers write without issues. Backup is just cp. For anything that doesn't need multi-user concurrent writes, it's hard to justify the operational overhead of Postgres. ko
Yeah, I get that, and I'm fully on your side. SQLite would have been a nice fit. The only downside is the delta compression problem. Creating an extension for SQLite works, but it's slow. I had two options:
1) Do the delta compression and caching and so on on the pgit side and lose SQL queryability (or I need to do my own), or
2) Use postgres
if you want to use key value store using sqlite then you can try : https://github.com/hash-anu/snkv
in which i am directly accessing b-tree layer and not consuming query layer.
for kv workloads it is much faster compare to sql.
and yeah you will get same benefits of sqlite storage engine.