Enable fuzzy string matching for license search
Problem
Searching using the license search box is very slow (15-30 seconds) and this causes outages easily.
Proposal
Add the following indexes:
create index licenses_lower_company_trgm on licenses using gin (lower(company) gin_trgm_ops);
create index licenses_lower_name_trgm on licenses using gin (lower(name) gin_trgm_ops);
create index licenses_lower_email_trgm on licenses using gin (lower(email) gin_trgm_ops);
create index licenses_lower_recurly_subscription_id_trgm on licenses using gin (lower(recurly_subscription_id) gin_trgm_ops)
create index licenses_md5_license_file_trgm on licenses (md5(license_file))
This also needs the pg_trgm extension.
I've been trying to get rails migrations to behave for this, but I'm not skilled enough to make it happen, and I welcome some help. Also creating the extension needs superuser privs and I don't know how rails migrations would normally handle this. We could do it all by hand, but that's not a great option.
Result
If we also remove the complicated md5-based query (https://gitlab.com/gitlab-org/license-gitlab-com/issues/120), then this will mean the search is fully indexed and should only take a second at most (at a guess).
How will we measure success?
Searches are performant.
Edited by Mark Chao