Alex Gleason on Nostr: I have been forced to finally master Nostr SQLite. I started on SQLite, hit walls, ...
I have been forced to finally master Nostr SQLite. I started on SQLite, hit walls, moved to Postgres, hit further walls, and finally landed on ElasticSearch/OpenSearch with infinite scalability. But now I am forced to finally master SQLite for local database on Android/iOS. I've built 4 different variations of Nostr SQLite and I'm benchmarking them against each other. The current fastest implementation (for reads) stores tags in an FTS table like "t:hello e:abc123..." The naive way is to keep a separate nostr_tags table and JOIN it, but then the query planner behaves wrong. This way has 25x read performance for 100k+ datasets.
Published at
2026-08-01 03:15:05 UTCEvent JSON
{
"id": "f94da248d0255196299fd23639d2fd354c98f8badbd6212dfdd4fdf9e7c29582",
"pubkey": "0461fcbecc4c3374439932d6b8f11269ccdb7cc973ad7a50ae362db135a474dd",
"created_at": 1785554105,
"kind": 1,
"tags": [
[
"client",
"Ditto",
"31990:781a1527055f74c1f70230f10384609b34548f8ab6a0a6caa74025827f9fdae5:ditto"
]
],
"content": "I have been forced to finally master Nostr SQLite. I started on SQLite, hit walls, moved to Postgres, hit further walls, and finally landed on ElasticSearch/OpenSearch with infinite scalability. But now I am forced to finally master SQLite for local database on Android/iOS. I've built 4 different variations of Nostr SQLite and I'm benchmarking them against each other. The current fastest implementation (for reads) stores tags in an FTS table like \"t:hello e:abc123...\" The naive way is to keep a separate nostr_tags table and JOIN it, but then the query planner behaves wrong. This way has 25x read performance for 100k+ datasets.",
"sig": "191e70362e1ea20278129edd051ce99c2b25fa323e97644164dd544ec84692b5ec6a2e358765961ff9b5864bdd7254bcd3aee6e6b26f29e512ec2c4692523819"
}
