[REQUEST] REQ tag search separated from JSONB @> to event_tags table to speed up the process.

[imksoo]

I have been running the Nostr relay for the last year or nearly a year now. My relay is publicly available and used worldwide at wss://relay.nostr.wirednet.jp and currently holds over 15 million Nostr events. It has been working very well since we installed Nostream last June.

However, recently, when processing REQ requests containing a very large number of #e or #p, nostream has been having performance issues where it does not return results after 30 seconds or more.

I have looked at the SQL statements that have been running for a long time in PostgreSQL and most of them are having problems when using the @> operator in JSONB to look up the tags field in the events table.

It seemed that I could speed up the process by separating the tags field from the events table and creating a separate events_tags table of sorts, so I actually made some improvements to Nostream in my relay and it has been running stable for about half a month, so I will PR later.

I have documented my addressing this issue on my blog. You can take a look if you like.
https://zenn.dev/imksoo/articles/19287395ff403e

It was fun to make the modifications because of Nostream's very good code base. Thank you.

[cameri]

Hi @imksoo. I loved your write-up on how you improved Nostream performance by the use of a separate table for tags. I am impressed with your development skills.

Would you like to become an official maintainer of Nostream?

[imksoo]

Hi @cameri, Thanks for the kind words and the offer! I'm really happy to hear that you liked my ideas for improving Nostream. I'd be glad to become a maintainer of this project.