sql for nostr doesn’t work. It’s been tried.
A proper SQLite database shouldn't have issues with this amount of traffic. D1 isn't just SQLite though, it's SQLite + an API.
I would try using SQLite-in-DO instead of using D1. There's a good blog post about it here: https://blog.cloudflare.com/sqlite-in-durable-objects/
Secondly I would look at splitting up the database into multiple databases.
Discussion
I see! Is a document based DB the way to go? In memory?
strfry/nostrdb model is the only thing that has worked. lmdb + indexes that cover common use cases:
- created
- pubkeyKindCreated
- kindCreated
- tagKindCreated
Bonus points for storing json string and flatbuffer version of the note for optimal performance
Intersting! Did not occur to me to use a kv solution for this.