Doug Hoyte on Nostr: Got it, makes sense. I think if you make sure the event is written before updating ...
Got it, makes sense. I think if you make sure the event is written before updating the DB then that would be safe, as you describe. It still might be worthwhile to check if there is any performance difference between the parallel file and storing the validated event JSON in sled DB along with the indices. If it works like LMDB then retrieving it will be functionally identical (an offset into an mmap), and an event write will require only one fsync, not two. It also might save you a lot of trouble later on, when it comes to backups and periodic rewrites you mention.
About the indices: No, I don't think it's a dumb algorithm. It's actually a really difficult problem, to figure out which index or indices to use in a scan. strfry has all the indices you mention, also composite with created_at. Additionally, there is a (pubkey,kind,created_at) index, which I think is useful because I believe queries like "get contact list of user X" are quite common.
strfry currently never joins two indices together or does a set intersection as you describe -- its algorithm is even dumber. It only ever uses 1 index. If the query can be fully satisfied by the index then it doesn't need to load the flatbuffers (most queries are like this). But if it does need to load the flatbuffers this is when it's important that accessing them and applying filters are fast. Optimisations for this code path are also beneficial for the live events filtering (ie post EOSE).
Your approach sounds like it might be quite effective. The only thing I'd watch out for is if your IO patterns are excessively random. The nice thing about working on one index at once is that the access is largely contiguous data. Even several contiguous streams is probably OK, but some queries have like hundreds of pubkeys in them (for instance).
About the indices: No, I don't think it's a dumb algorithm. It's actually a really difficult problem, to figure out which index or indices to use in a scan. strfry has all the indices you mention, also composite with created_at. Additionally, there is a (pubkey,kind,created_at) index, which I think is useful because I believe queries like "get contact list of user X" are quite common.
strfry currently never joins two indices together or does a set intersection as you describe -- its algorithm is even dumber. It only ever uses 1 index. If the query can be fully satisfied by the index then it doesn't need to load the flatbuffers (most queries are like this). But if it does need to load the flatbuffers this is when it's important that accessing them and applying filters are fast. Optimisations for this code path are also beneficial for the live events filtering (ie post EOSE).
Your approach sounds like it might be quite effective. The only thing I'd watch out for is if your IO patterns are excessively random. The nice thing about working on one index at once is that the access is largely contiguous data. Even several contiguous streams is probably OK, but some queries have like hundreds of pubkeys in them (for instance).