[SPIKE] Investigate best strategy for Postgres Full Text Search of vulnerability information
As a follow up to #352665 (closed), we need to perform further investigation to determine the best strategy to enable well performing and scaling full text search using Postgres Full Text Search capabilities, as this should provide us the minimum viable solution from which we can iterate with higher complexity solutions at a later stage.
Prior analysis has already determined a moderate viability by creating a new vulnerability search terms table with partitioning and GIST indexing, however this does not find the engineering complexity of converting user search queries into PostgreSQL lexeme search queries, and bears caveats relating to effective pagination. Possible solutions may include partitioning the existing Vulnerability::Reads table and extending it with computed lexeme generation to enable full text search to be combined with standard column filter patterns.
Expected Outcomes
-
Determine the engineering cost of translating user search requests into lexeme search queries efficiently (and securely, we don't want this to become a Denial-Of-Service vector). (There is a pg_search gem that may fulfil this) -
Determine a reasonably optimal data architecture to full-text-search that can be effectively combined with standard filtering mechanics and paginated logically.