Skip to content

Denormalize projects.visibility_level in catalog_resources table

Leaminn Ma requested to merge add-visibility-level-to-catalog-resources into master

What does this MR do and why?

In order to avoid JOIN-ing the catalog_resources and projects tables, this MR denormalizes projects.visibility_level in catalog_resources. This will improve the performance of queries in Ci::Catalog::Listing, which will improve our overall ability to scale the Global Ci Catalog search feature.

Upon creating a new Ci::Catalog::Resource record, a callback is triggered to sync the denormalized columns with the corresponding projects values. Future updates to the projects columns will be synced with the process implemented in #429376 (closed).

NOTE:

Resolves Steps 1 & 2 in #429056 (closed).

Query plan

Up

main: == [advisory_lock_connection] object_id: 180600, pg_backend_pid: 25434
main: == 20231106212340 AddVisibilityLevelToCatalogResources: migrating =============
main: -- add_column(:catalog_resources, :visibility_level, :integer, {:default=>0, :null=>false})
main:    -> 0.0023s
main: == 20231106212340 AddVisibilityLevelToCatalogResources: migrated (0.0068s) ====

main: == [advisory_lock_connection] object_id: 180600, pg_backend_pid: 25434
ci: == [advisory_lock_connection] object_id: 180900, pg_backend_pid: 25436
ci: == 20231106212340 AddVisibilityLevelToCatalogResources: migrating =============
ci: -- add_column(:catalog_resources, :visibility_level, :integer, {:default=>0, :null=>false})
ci:    -> 0.0014s
ci: == 20231106212340 AddVisibilityLevelToCatalogResources: migrated (0.0111s) ====

ci: == [advisory_lock_connection] object_id: 180900, pg_backend_pid: 25436

Down

main: == [advisory_lock_connection] object_id: 180240, pg_backend_pid: 25982
main: == 20231106212340 AddVisibilityLevelToCatalogResources: reverting =============
main: -- remove_column(:catalog_resources, :visibility_level, :integer, {:default=>0, :null=>false})
main:    -> 0.0018s
main: == 20231106212340 AddVisibilityLevelToCatalogResources: reverted (0.0080s) ====

main: == [advisory_lock_connection] object_id: 180240, pg_backend_pid: 25982
ci: == [advisory_lock_connection] object_id: 180240, pg_backend_pid: 26402
ci: == 20231106212340 AddVisibilityLevelToCatalogResources: reverting =============
ci: -- remove_column(:catalog_resources, :visibility_level, :integer, {:default=>0, :null=>false})
ci:    -> 0.0014s
ci: == 20231106212340 AddVisibilityLevelToCatalogResources: reverted (0.0108s) ====

ci: == [advisory_lock_connection] object_id: 180240, pg_backend_pid: 26402

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #429056 (closed)

Edited by Leaminn Ma

Merge request reports

Loading