Alex on Nostr: Pro-tip: add unique index on (pubkey, kind) where kind >= 10000 and kind < 20000 or ...

Pro-tip: add unique index on (pubkey, kind) where kind >= 10000 and kind < 20000 or (kind in (0, 3))

Use ON CONFLICT clause on insert: https://gitlab.com/soapbox-pub/nostrify/-/blob/main/packages/db/NPostgres.ts?ref_type=heads#L176

10x performance of replaceable event lookups AND insertions while guaranteeing correctness.

(Addressable events get the same treatment if you copy the d tag to a column and index it for the kind ranges too)