Background
We have several stories open related to improving the experience of proposals search:
These stories boil down to: make search faster, give users more fields to search within and advanced options, support pagination (and presumably, though not yet mentioned, sorting by any field).
There are a few aspects of our data model and code architecture that make improving search a non-trivial problem:
- data is heavily normalized in the db, across
proposals
, client_data
, comments
, versions
, and attachments
.
- authorization is similarly normalized across
users
and roles
.
- each new client use cases means new custom column names.
The current implementation uses a big block of hard-coded SQL plus some ActiveRecord associations in order to create a very large, multiple-JOIN query, which includes manual calls to Postgres full-text search functions for every column against which the user wants to search. The current implementation does zero pagination or user-controlled sorting, and does not distinguish between client slugs (all proposals are searched, regardless of the user's client_slug
value).
Current search usage indicates that 80-90% of searches are simply for the public_id
of a known proposal. That use case could be solved far more simply and with better performance than the current implementation supports. However, it may be that the high percentage of public_id
searches is because the existing user base has figured out that the current search feature doesn't really support much more than that. Only a small minority of client_data
fields are searched, comments are ignored, multiple search query terms do not behave as expected, and performance is slow. I.e., our data might indicate a narrow need because users have self-selected a cow path that fits the existing feature constraints, not their actual business needs.
Options
I see three distinct ways forward.
Continue with the current, hand-rolled SQL
- Refactoring code to limit to the current user
client_slug
and interrogating the relevant client_data
model class to autogenerate the Postgres full-text search syntax. This allows us to add new client models and write very little custom search SQL.
- Column weighting for ranking could be defined as a class-based hash lookup.
- Mangle the incoming search query terms in order to correctly implement AND/OR/NOT boolean logic.
- Postgres term highlighting is very slow, so use it sparingly if ever.
Pros
- Gives us total control over the features
- No added dependencies
Cons
- Re-inventing wheels already solved by other solutions (see other 2 potential paths)
- Performance will degrade (even further) as we add columns to the search
- Code maintenance
- Hard to debug errors since data remains normalized
pg_search
Rewrite the search features to use the industry standard pg_search gem. The multisearch feature should support our normalized data.
NOTE I looked at textacular as an alternative gem but it does not have a multisearch-like feature and so our normalized data structure would prove a challenge to its simpler feature set.
Pros
- Uses a standard gem
- Search stays within Postgres (no external system dependencies)
- Compared to option (1) above, our code should be configuration and declaration, instead of generating custom SQL
Cons
- Hard to debug errors since data remains normalized
- Performance will degrade (even further) as we add columns to the search
Elasticsearch
Offload search entirely to an external service. Query parsing, ranking, sorting, pagination, highlighting, would all be performed by ES.
Pros
- Industry standard
- Best performance available
- Standard service offering from cloud.gov
- Minimal code to write, mostly around serializing/de-normalizing at index time, and query munging at search time
- Data is de-normalized, which makes debugging easier
Cons
- External service increases app complexity
- Small latency in search availability while data is indexed (usually measured in seconds)
Recommendation
Elasticsearch. Best performance, least amount of code.