if you don't add an extra index as i have that includes id, pubkey, kind and timestamp

oh, my docs are inaccurate. i wrote a variable length integer encoding for this, but anyway:

// FullIndex is an index designed to enable sorting and filtering of results found via

// other indexes, without having to decode the event.

//

// [ prefix ][ 32 bytes full event ID ][ 8 bytes truncated hash of pubkey ][ 2 bytes kind ][ 8 bytes created_at timestamp ][ 8 serial ]

with this index, you can get the id without decoding the event, you can filter out pubkeys and kinds, and sort them in ascending or descending order of timestamp.

it's the bulkiest index in the tables i designed but it's there to avoid decoding events. and anyway, i also made it so decoding events is as fast as possible, using a streaming decoder scheme i wrote by hand, which is the fastest binary codec for nostr events there is, i don't see how anyone can make it faster or more compact. ah yes, and i did add further logic so e and p tags are compactly stored as the binary, the p tags i had to make an exception because some clients put hashtags in them in follow lists. so the p tags are 1 byte, 32 bytes for the pubkey, so 33 bytes. which is literally half as big as hex encoding.

i also added some further indexes, pubkey/kind and pubkey/created_at and ... well i'm not finished defining them exactly, my goal is to enable searching the event without decoding it within 2 or 3 index iterations. this is the optimal.

i also have written tests that show how big the events and indexes are, 203mb of events (my cache from my realy.mleku.dev) with 64mb of indexes, and the actual binary store of events is 130mb. so it works out to store index and event without complex, expensive compression, to about the same as the raw json of the events. i classify that as a total win.