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)
Alex Gleason 🐍🚬 /
npub1q3…cd26p
2024-10-22 15:09:13
in reply to nevent1q…2elc
Author Public Key
npub1q3sle0kvfsehgsuexttt3ugjd8xdklxfwwkh559wxckmzddywnws6cd26pPublished at
2024-10-22 15:09:13Event JSON
{
"id": "852cc2d98ea4741c17cf31daa4ecc15716995ae936c391c3f2b39ec9d609d602",
"pubkey": "0461fcbecc4c3374439932d6b8f11269ccdb7cc973ad7a50ae362db135a474dd",
"created_at": 1729609753,
"kind": 1,
"tags": [
[
"e",
"e6772a8398d27811aa527cb4d5f6cad5c4cac1fd2ae5645854e152ecd460a0ad",
"wss://gleasonator.dev/relay",
"root",
"460c25e682fda7832b52d1f22d3d22b3176d972f60dcdc3212ed8c92ef85065c"
],
[
"e",
"e6772a8398d27811aa527cb4d5f6cad5c4cac1fd2ae5645854e152ecd460a0ad",
"wss://gleasonator.dev/relay",
"reply",
"460c25e682fda7832b52d1f22d3d22b3176d972f60dcdc3212ed8c92ef85065c"
],
[
"p",
"460c25e682fda7832b52d1f22d3d22b3176d972f60dcdc3212ed8c92ef85065c",
"wss://gleasonator.dev/relay"
],
[
"r",
"https://gitlab.com/soapbox-pub/nostrify/-/blob/main/packages/db/NPostgres.ts?ref_type=heads#L176"
]
],
"content": "Pro-tip: add unique index on (pubkey, kind) where kind \u003e= 10000 and kind \u003c 20000 or (kind in (0, 3))\n\nUse ON CONFLICT clause on insert: https://gitlab.com/soapbox-pub/nostrify/-/blob/main/packages/db/NPostgres.ts?ref_type=heads#L176 \n\n10x performance of replaceable event lookups AND insertions while guaranteeing correctness.\n\n(Addressable events get the same treatment if you copy the d tag to a column and index it for the kind ranges too)",
"sig": "c4fa3ed743834a9872a5c42096bcd2b5c0ab970fed6f6fdbeea6b93bc197e5e45d08c3b3a1bc2aa8a0f97ab6da397385ccc7994f800afbe82f9937b3445cd1af"
}