[ ] Search for users by Mii Name (including symbols) and not just NNID
[ ] Search for text in posts across all communities (including symbols, special characters, and more)
[ ] Search for text in replies in all posts
[ ] Search for text in posts in users profiles
Originally I had searching for posts made available on the site which used a trig ram GIN index on the PostgreSQL table. However, doing this made the db go brr, reaching cpu utilization near 100%. This throttled the performance on database queries across the site. To reimplement these search functionalites, a couple approaches could be done...
Use an entirely new elastic search / open search database which mirrors the Postgres database, and this will be used for solely intensive plaintext searching so we can offload the computation costs. Doing this will cost me $70 / mo, hence why I'm waiting for Google adsense to get approved on the site so this cost can be offloaded.
Rethink the original GIN index approach on the PostgreSQL database. There might be a better way to index these columns while minimizing CPU utilization. At the moment, I don't feel that this approach is feasible, as I've done a ton of research on this approach already and came up short. Point 1 seems to be the play here
This contains a bunch of tasks:
Originally I had searching for posts made available on the site which used a trig ram GIN index on the PostgreSQL table. However, doing this made the db go brr, reaching cpu utilization near 100%. This throttled the performance on database queries across the site. To reimplement these search functionalites, a couple approaches could be done...
Use an entirely new elastic search / open search database which mirrors the Postgres database, and this will be used for solely intensive plaintext searching so we can offload the computation costs. Doing this will cost me $70 / mo, hence why I'm waiting for Google adsense to get approved on the site so this cost can be offloaded.
Rethink the original GIN index approach on the PostgreSQL database. There might be a better way to index these columns while minimizing CPU utilization. At the moment, I don't feel that this approach is feasible, as I've done a ton of research on this approach already and came up short. Point 1 seems to be the play here