DB changes to support environments search within folder
What does this MR do and why?
Create index_environments_for_name_search_within_folder
to support environments search within folders for the backend changes covered in !102227 (diffs)
This index will be used by the queries mentioned here
Other alternate solution options looked at from note #373850 (comment 1149868832)
- How GitLab issues search feature is addressing this problem
- They have a separate table
issue_search_data
where the search data is stored in tsvector format- For this usecase this looks like a complicated solution.
- Do we need a fuzzy search like the file finder
- This also can be iterated upon and is a complex solution
- Using a substring search on index (as proposed) will not work for environments sub-folders feature.
- This can be an iteration issue for that feature as
environment_type
column itself will require a revamp for that feature.- Using GIN index
- If the above index is not scaling, we can iterate to Gin index as it requires lot of storage space and also can cause performance issues as noted in gitlab-com/gl-infra/production#4725 (comment 596146675) and also additional backend changes with refactor of existing abstractions.
How to set up and validate locally
rails db:migrate:up:main VERSION=20221102150737
main: == 20221102150737 IndexEnvironmentsForNameSearchWithinFolder: migrating =======
main: -- transaction_open?()
main: -> 0.0000s
main: -- index_exists?(:environments, "project_id, lower(ltrim(name, environment_type || '/')) varchar_pattern_ops, state", {:name=>"index_environments_for_name_search_within_folder", :algorithm=>:concurrently})
main: -> 0.0148s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0005s
main: -- add_index(:environments, "project_id, lower(ltrim(name, environment_type || '/')) varchar_pattern_ops, state", {:name=>"index_environments_for_name_search_within_folder", :algorithm=>:concurrently})
main: -> 0.0130s
main: -- execute("RESET statement_timeout")
main: -> 0.0008s
main: == 20221102150737 IndexEnvironmentsForNameSearchWithinFolder: migrated (0.0411s)
rails db:migrate:down:main VERSION=20221102150737
main: == 20221102150737 IndexEnvironmentsForNameSearchWithinFolder: reverting =======
main: -- transaction_open?()
main: -> 0.0000s
main: -- indexes(:environments)
main: -> 0.0228s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0005s
main: -- remove_index(:environments, {:algorithm=>:concurrently, :name=>"index_environments_for_name_search_within_folder"})
main: -> 0.0186s
main: -- execute("RESET statement_timeout")
main: -> 0.0024s
main: == 20221102150737 IndexEnvironmentsForNameSearchWithinFolder: reverted (0.0602s)
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #373850 (closed)
Edited by Bala Kumar