Resolve "Allow to search environments within folders"

What does this MR do and why?

Backend changes to support search environments within folders along with index_environments_for_name_search_within_folder

Other alternate solution options looked at as mentioned in 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.

Documentation update will be covered in a separate MR.

Index performance details

Case 1: (Counters usecase)

• Invoked from app/models/environment.rb:212:in 'count_by_state'
• Query

SELECT COUNT(*) AS count_all, "environments"."state" AS environments_state FROM ((SELECT "environments".* FROM "environments" WHERE "environments"."project_id" = 278964 AND (LOWER(environments.name) LIKE LOWER('review/r') || '%'))
UNION
(SELECT "environments".* FROM "environments" WHERE "environments"."project_id" = 278964 AND (LOWER(ltrim(environments.name, environments.environment_type || '/')) LIKE LOWER('review/r') || '%'))) environments GROUP BY "environments"."state"

Case 2: (Count per folder for pagination)

• Invoked from config/initializers/kaminari_active_record_relation_methods_with_limit.rb:31:in 'total_count_with_limit'
• Query

SELECT COUNT(*) AS count_all, COALESCE(environment_type, id::text) AS coalesce_environment_type_id_text, COALESCE(environment_type, name) AS coalesce_environment_type_name FROM ((SELECT "environments".* FROM "environments" WHERE "environments"."project_id" = 278964 AND (LOWER(environments.name) LIKE LOWER('review/r') || '%'))
  UNION
  (SELECT "environments".* FROM "environments" WHERE "environments"."project_id" = 278964 AND (LOWER(ltrim(environments.name, environments.environment_type || '/')) LIKE LOWER('review/r') || '%'))) environments WHERE ("environments"."state" IN ('available')) GROUP BY COALESCE(environment_type, id::text), COALESCE(environment_type, name) LIMIT 10001

Case 3: (First 20 envs actual listing)

• Invoked from app/serializers/environment_serializer.rb:46:in 'map'
• Query

SELECT COALESCE(environment_type, id::text), COALESCE(environment_type, name) AS folder, COUNT(*) AS size, MAX(id) AS last_id FROM ((SELECT "environments".* FROM "environments" WHERE "environments"."project_id" = 278964 AND (LOWER(environments.name) LIKE LOWER('review/r') || '%'))
UNION
(SELECT "environments".* FROM "environments" WHERE "environments"."project_id" = 278964 AND (LOWER(ltrim(environments.name, environments.environment_type || '/')) LIKE LOWER('review/r') || '%'))) environments WHERE ("environments"."state" IN ('available')) GROUP BY COALESCE(environment_type, id::text), COALESCE(environment_type, name) ORDER BY folder ASC LIMIT 20 OFFSET 0

Summary

Note: Users can still query with folder name prefixed, so tested the index with search string review/r as well.

Query	Search string	Without Index (cold cache)	With index (cold cache)	Details with DB lab links
Case 1
	revi	Buffer: 83.3 MiB Time: 2.547 s	Buffer: 41.8 MiB Time: 175.603 ms
	review/r	Buffer: 43.4 MiB Time: 310.420 ms	Buffer: 1.73 MiB Time: 15.318 ms
				!102227 (comment 1164713104)
Case 2
	revi	Buffer: 61.3 MiB Time: 3.805 s	Buffer: 31.6 MiB Time: 187.463 ms
	review/r	Buffer: 31.2 MiB Time: 2.924 s	Buffer: 1.47 MiB Time: 10.551 ms
				!102227 (comment 1164735850)
Case 3
	revi	Buffer: 61.3 MiB Time: 4.306 s	Buffer: 31.6 MiB Time: 97.576 ms
	review/r	Buffer: 31.2 MiB Time: 644.155 ms	Buffer: 1.49 MiB Time: 10.848 ms
				!102227 (comment 1164762406)

Summary of without subfolder search vs with subfolder search (Details in !102227 (comment 1167388017))

Type	Old query	New query with new index
Case - 1	Buffer: 41.8 MiB Time: 4 s DB lab link	Buffer: 41.8 MiB Time: 2s DB lab link
Case - 2	Buffer: 31 MiB Time: 4.4 s DB lab link	Buffer: 32 MiB Time: 500 ms DB lab link
Case - 3	Buffer: 32 MiB Time: 691 ms DB lab link	Buffer: 32 MiB Time: 172 ms DB lab link

Screenshots or screen recordings

How to set up and validate locally

Migration logs in development

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)

• Use the below simple ci config to create a foldered environment name

image: alpine:latest

stages:
  - deploy

prod:
  stage: deploy
  script:
    - echo "Deploy beta-app"
  environment:
    name: test/test-app-1

• In Rails console enable FF Feature.enable(:enable_environments_search_within_folder).
• Visit the environments page and search for test-app in the search bar and the fetched results should include test/test-app-1.

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)