Skip to content

Add index for namespaces by root namespace lookup

Adam Hegyi requested to merge 402737-create-optimized-namespaces-by-top-level-namespace-index into master

This MR adds an async index to namespaces table (high-traffic table) to support looking up descendant namespaces by the top-level namespace id efficiently and support EachBatch iteration.

The index will be used within &10202 (closed) but there are other use cases where the index could be used for improving performance:

  • groups API (include subgroups) sorted by id
  • projects API (include subgroups) sorted by id
  • Efficiently EachBatch all subgroups in the group hierarchy.
  • Efficiently EachBatch all projects in the group hierarchy.

DB

Up:

main: == 20230414075119 AddNamespacesByTopLevelNamespaceIndex: migrating ============
main: -- index_exists?(:namespaces, "((traversal_ids[1]), type, id)", {:name=>"index_on_namespaces_namespaces_by_top_level_namespace", :algorithm=>:concurrently})
main:    -> 0.0269s
main: -- add_index_options(:namespaces, "((traversal_ids[1]), type, id)", {:name=>"index_on_namespaces_namespaces_by_top_level_namespace", :algorithm=>:concurrently})
main:    -> 0.0001s
main: == 20230414075119 AddNamespacesByTopLevelNamespaceIndex: migrated (0.0503s) ===

ci: == 20230414075119 AddNamespacesByTopLevelNamespaceIndex: migrating ============
ci: -- index_exists?(:namespaces, "((traversal_ids[1]), type, id)", {:name=>"index_on_namespaces_namespaces_by_top_level_namespace", :algorithm=>:concurrently})
ci:    -> 0.0242s
ci: -- add_index_options(:namespaces, "((traversal_ids[1]), type, id)", {:name=>"index_on_namespaces_namespaces_by_top_level_namespace", :algorithm=>:concurrently})
ci:    -> 0.0000s
ci: == 20230414075119 AddNamespacesByTopLevelNamespaceIndex: migrated (0.0474s) ===

Down:

main: == [advisory_lock_connection] object_id: 273780, pg_backend_pid: 16875
main: == 20230414075119 AddNamespacesByTopLevelNamespaceIndex: reverting ============
main: == 20230414075119 AddNamespacesByTopLevelNamespaceIndex: reverted (0.0788s) ===

main: == [advisory_lock_connection] object_id: 273780, pg_backend_pid: 16875

ci: == [advisory_lock_connection] object_id: 273720, pg_backend_pid: 17298
ci: == 20230414075119 AddNamespacesByTopLevelNamespaceIndex: reverting ============
ci: == 20230414075119 AddNamespacesByTopLevelNamespaceIndex: reverted (0.1103s) ===

ci: == [advisory_lock_connection] object_id: 273720, pg_backend_pid: 17298

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 #402737 (closed)

Edited by Adam Hegyi

Merge request reports