> I guess we have a different view of the reliability of sqlite
I also consider reliability as a project, rather than just as software.
The latter has been proven-enough, but the former could fail at any time (although I mentioned the mitigation caveat).
I also just don't really like the idea of storing large blobs in SQLite, the way it handles that is pretty bad in my opinion (and multi-DB support is *documented* flakey in a bunch of additional ways too). I spent a while investigating the various databases to see if it was practical (because I wanted to do it) and was rather disappointed even by the ones that handled it best (PostgreSQL has a bunch of dumb limitations that suggest it was implemented before they had partitioned tables and was never touched since).
The closest to a satisfying option I could imagine there (without changing a *lot* of its storage management model) would be to add an extension to SQLite that enables handling external blobs in the same transactions as internal storage (a lot more reasonable to do this with SQLite than PostgreSQL actually, since collocating the DBMS and the user simplifies a bunch of things).
That does constrain implementation languages to ones that can do FFI callbacks (or C) or at the very least require one such language for implementing that part.
> sqlite also provides indexing, which will be unavoidable for pruning old content, deduplicating blobs, finding files that actually changed, etc...
Yeah, all of that would need to be handled.
> You may 'pragma wal_autocheckpoint=0' to disable autocheckpointing in wal mode
Not quite what I had in mind. This still calls fsync at least once per transaction.
The not-completely-applicable PostgreSQL example I linked batches multiple transactions as a single unit to write out & sync, if they happen with a given timespan.
My idea was more like accumulating N chunks of changes (or up to M files if they're smaller) before committing any of that to disk.
LisPi
npub132…qgzqx
2026-10-07 03:32:04 UTC
in reply to nevent1q…shxh
Author Public Key
npub1326y4p8mw4l750yc8e52n0xguvcl5j0dn88ul5r2gjva2sh2ay7qjqgzqxPublished at
2026-10-07 03:32:04 UTCEvent JSON
{
"id": "c7b3cbf6441f0bd5f4ff586d02f3dbdb42a4b755ad2c403c648ab78b33961760",
"pubkey": "8ab44a84fb757fea3c983e68a9bcc8e331fa49ed99cfcfd06a4499d542eae93c",
"created_at": 1791343924,
"kind": 1,
"tags": [
[
"p",
"9096c95c05b71521d6812b521bc02db89f6677a33ce3670d9af2bcfee5042497"
],
[
"e",
"3acaeda9aba45f18c5f17442b5187574dcf07ad2bb0c7810caf2f9aeee471550",
"",
"root",
"8ab44a84fb757fea3c983e68a9bcc8e331fa49ed99cfcfd06a4499d542eae93c"
],
[
"p",
"8ab44a84fb757fea3c983e68a9bcc8e331fa49ed99cfcfd06a4499d542eae93c"
],
[
"e",
"9a1ea9ef1e52a211dafd2ceced247510b81080e2e7fdcb1511f891af43e58941",
"",
"reply",
"256c71390808dd435898b3cff116683ab924d6387efea5206c4d35b1f49551b6"
],
[
"p",
"256c71390808dd435898b3cff116683ab924d6387efea5206c4d35b1f49551b6"
],
[
"proxy",
"https://udongein.xyz/objects/c109d3fc-6a0a-4cbb-a5d6-f15450945e33",
"activitypub"
],
[
"L",
"pink.momostr"
],
[
"l",
"pink.momostr.activitypub:https://udongein.xyz/objects/c109d3fc-6a0a-4cbb-a5d6-f15450945e33",
"pink.momostr"
],
[
"-"
]
],
"content": "\u003e I guess we have a different view of the reliability of sqlite\r\n\r\nI also consider reliability as a project, rather than just as software.\r\n\r\nThe latter has been proven-enough, but the former could fail at any time (although I mentioned the mitigation caveat).\r\n\r\nI also just don't really like the idea of storing large blobs in SQLite, the way it handles that is pretty bad in my opinion (and multi-DB support is *documented* flakey in a bunch of additional ways too). I spent a while investigating the various databases to see if it was practical (because I wanted to do it) and was rather disappointed even by the ones that handled it best (PostgreSQL has a bunch of dumb limitations that suggest it was implemented before they had partitioned tables and was never touched since).\r\n\r\nThe closest to a satisfying option I could imagine there (without changing a *lot* of its storage management model) would be to add an extension to SQLite that enables handling external blobs in the same transactions as internal storage (a lot more reasonable to do this with SQLite than PostgreSQL actually, since collocating the DBMS and the user simplifies a bunch of things).\r\n\r\nThat does constrain implementation languages to ones that can do FFI callbacks (or C) or at the very least require one such language for implementing that part.\r\n\r\n\u003e sqlite also provides indexing, which will be unavoidable for pruning old content, deduplicating blobs, finding files that actually changed, etc...\r\n\r\nYeah, all of that would need to be handled.\r\n\r\n\u003e You may 'pragma wal_autocheckpoint=0' to disable autocheckpointing in wal mode\r\n\r\nNot quite what I had in mind. This still calls fsync at least once per transaction.\r\n\r\nThe not-completely-applicable PostgreSQL example I linked batches multiple transactions as a single unit to write out \u0026 sync, if they happen with a given timespan.\r\n\r\nMy idea was more like accumulating N chunks of changes (or up to M files if they're smaller) before committing any of that to disk.",
"sig": "14048b5fb814b7046f1d1219f210fd44a2bcbcfb41f069dbf3b40c5eaffb751b027cd1579ecb9e64d62367b0f3ba8ba64d86dcb4ea4e572d906b29c7a2a338d3"
}