ah, okay you were looking from the relay perspective. Ofc you are right.
In my own SQLite implementation I just use the SELECT COUNT, and it's quite efficient.
Discussion
yeah, i build my DB from scratch using a key/value store so such a search index has to be added. it has part of it already but it doesn't cover the full filter query space, only pubkey/id/timestamp. it also needs tags and kinds.
the extra index eliminates the need to decode events by allowing queries to effectively search by those criteria and not sift through such inordinately large volumes of data.
i've chalked in a mental note to implement count properly for the near future, it's not a big task really.
ah got it. Yeah ofc in a key-value store you have to implement that from scratch.
Btw if you ever need a SQLITE package for nostr events, I wrote this
https://github.com/pippellia-btc/nastro
It provides bare bone tables, but in the constructor you are free to add other schemas, or triggers, and you can even change or modify the default Query builder to take advantage of new tables, indices and so on.
well, i'm so used to building badger based databases i have a bunch of libraries for it, to manage building keys and and generating indexes. doesn't take me that much longer to make an embedded, dedicated database with some particular set of tables this way compared to learning some yet another new thing to work with antiquated sql query engines.
i mean, it's just my thing, building things like this so yeah. i write the encoders and the database engines and the network schedulers. offloading stuff to inherently slower, less efficient, less scalable fixed-purpose flexible database engines just doesn't make sense for me, and when you see how fast it returns results the reason is clear.
IMO, nostr is a unique enough type of network connected database protocol, so open ended, that using generic general purpose parts to build it is a big sacrifice in performance.
This is a fair take
If you are interested, you can open a PR to nastro adding your own badger based DB. I would actually really appreciate it.
The prerequisite is just to satisfy the store interface, expose the internals (so people can make queries directly if needed), and allow for customisation.
I think it would be quite beneficial to the ecosystem to move away from the event store fiatjaf packages, which is quite crappy
yeah, the event store interface is kinda wrong too. it should be sorting the results before sending them, just not depending on the insertion time of the record to be a match with it (this breaks when someone uploads a bunch of old events to a relay). i mean, at least, where sqlite or postgresql or others are used, the results are sorted in the engine, but the badger engine that fiatjaf made does not, and it runs WAY too many threads.
create an issue on the repo and tag me as assigned to the issue of adding a badger based DB engine implementation. i'll get to it in the next month. it's not a difficult task, just looking at the interface, creating all the stub implementations of the methods and then putting the right bits into them based on the existing engine i have built. probably a day or so work maybe.
> i mean, at least, where sqlite or postgresql or others are used, the results are sorted in the engine, but the badger engine that fiatjaf made does not
oooof, not good.
> and it runs WAY too many threads
typical...
> create an issue on the repo and tag me as assigned to the issue of adding a badger based DB engine implementation
That's great, thank you sir, I'll do ASAP!
just following up, the implementation is done and a PR is awaiting you
i added a few helpers and whatsits into #orly tag code mainly that will also help with transitioning.
orly event and filter codecs use binary data where the field represents binary data. event IDs, pubkeys, signatures all are in that form already after they have been unmarshaled. i think it probably helps a little with memory and speed since comparing a byte is faster than comparing two bytes.
anyway, i hope it helps you make your analytics even more efficient, i have it in my mind to look into it, after a few people have put it into their apps. now i'm gonna be in the credits i want to know what the show looks like :)
Thanks mate, I'll look into it tomorrow.