Skip to content

Disable fastupdate on GIN issue and merge requests indexes

We have found that disabling fastupdate in gitlab-com/gl-infra/production#7840 (closed) appears to make database updates to issues and merge_requests much more reliable.

I think we should do this by default:

ALTER INDEX index_merge_requests_on_description_trigram SET ( fastupdate = false ) ;
ALTER INDEX index_merge_requests_on_title_trigram SET ( fastupdate = false ) ;
ALTER INDEX index_issues_on_title_trigram SET ( fastupdate = false ) ;
ALTER INDEX index_issues_on_description_trigram SET ( fastupdate = false ) ;

I believe db/structure.sql should reflect this:

CREATE INDEX index_issues_on_title_trigram ON public.issues USING gin (title public.gin_trgm_ops) WITH (fastupdate='false');
CREATE INDEX index_issues_on_description_trigram ON public.issues USING gin (description public.gin_trgm_ops) WITH (fastupdate='false');
CREATE INDEX index_merge_requests_on_description_trigram ON public.merge_requests USING gin (description public.gin_trgm_ops) WITH (fastupdate='false');
CREATE INDEX index_merge_requests_on_title_trigram ON public.merge_requests USING gin (title public.gin_trgm_ops) WITH (fastupdate='false');

Note that we have dozens of other GIN indexes, but it's not as critical to disable them because the number of trigrams tend to be small. We might want to recommend that PostgreSQL disable fastupdate by default.

Edited by Mario Celi