Backend: Denormalize visibility_level and improve query plan for `All resources` in Ci::Catalog::Listing

Problem

Ci::Catalog::Listing is used to find all catalog resources to display for a given user. Currently we are joining catalog_resources and projects filtering on project authorizations in order to display catalog resources that a give user can view. Query plan for such queries don't look great and need to be improved.

Solution

One idea is: we may need to denormalize, index and sync visibility_level since we use it for authorization. Then we should be able to implement a method like Project.public_or_visible_to_user where in User#authorizations_for_projects we would use related_project_column: 'catalog_resources.project_id'. I believe this would allow us to just use catalog_resources table with all its indices and not relying on the JOIN with projects. Ultimately we need to be guided by EXPLAIN results of the query plan analyzer.

Additionally, for Beta we decided to limit the scope of the All resources query plan to only:

• Public projects for Unauthenticated users.
• Public+Internal projects for Authenticated users.

--> Decision reversed in 2023-11-29 CI Catalog weekly meeting.

So in order to improve the search query performance, we will need to complete #430889 (closed) and add the following indices (see #429056 (comment 1632642530) for details):

• Index on <PG full text search vector> WHERE visibility_level IN (10, 20) (Public+Internal)
• Index on <PG full text search vector> WHERE visibility_level = 20 (Public)

--> Since private projects will remain included and combined with Public/Internal data in the Ci::Catalog::Listing queries per 2023-11-29 CI Catalog weekly meeting, it's uncertain how much of a benefit these indices would offer or if they need to be changed. Created a separate issue to address the search vector indices: #433153 (closed).

Steps

1. Create a migration to add visibilty_level to catalog_resources. The data type and constraints should match projects.visbility_level exactly.
2. Complete or update #429376 (closed) to ensure the column stays in sync with the projects table.
3. Create a migration to backfill the data.
4. Implement a query like Project.public_or_visible_to_user in Ci::Catalog::Resource::Listing so that the queries use the denormalized visibility_level column.

MR Implementation

Description	MR / Issue
Step 1: Denormalize projects.visibility_level in catalo... (!136111 - merged)	!136111 (merged)
Step 2: Backend: Implement background syncing to ensure... (#429376 - closed)	#429376 (closed)
Step 3: Backfill catalog_resources.visibility_level wit... (!139165 - merged)	!139165 (merged)
Step 4: Update catalog listing queries to use new visib... (!139295 - merged)	!139295 (merged)

Performance Implementation for 'All resources'

Group	Issue	Link
backend	Backend: Denormalize name and description in Ci... (#427928 - closed)	#427928 (closed)
backend	Backend: Denormalize visibility_level and impro... (#429056 - closed)	👈 You are here
backend	Backend: Implement background syncing to ensure... (#429376 - closed)	#429376 (closed)