Skip to content

Create namespace descendants table

Adam Hegyi requested to merge 428464-create-namespace-descendants-table into master

What does this MR do and why?

This change adds a cache table for storing namespace descendant ids for speeding up group-level database queries. The table is hash-partitioned in case we enable the optimization for all groups later on.

See the related epic: &11469

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

DB

Up:

main: == 20231221171135 CreateNamespaceDescendantsTable: migrating ==================
main: -- execute("CREATE TABLE namespace_descendants (\n  namespace_id bigint NOT NULL,\n  self_and_descendant_group_ids bigint[] NOT NULL DEFAULT ARRAY[]::bigint[],\n  all_project_ids bigint[] NOT NULL DEFAULT ARRAY[]::bigint[],\n  traversal_ids bigint[] NOT NULL DEFAULT ARRAY[]::bigint[],\n  outdated_at timestamp with time zone DEFAULT NOW(),\n  calculated_at timestamp with time zone,\n  PRIMARY KEY(namespace_id)\n)\nPARTITION BY HASH (namespace_id);\n")
main:    -> 0.0134s
main: -- execute("CREATE INDEX\nindex_on_namespace_descendants_outdated\nON namespace_descendants (namespace_id)\nWHERE outdated_at IS NOT NULL\n")
main:    -> 0.0005s
main: -- transaction(nil)
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_00\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 0);\n")
main:    -> 0.0023s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_01\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 1);\n")
main:    -> 0.0012s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_02\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 2);\n")
main:    -> 0.0011s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_03\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 3);\n")
main:    -> 0.0012s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_04\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 4);\n")
main:    -> 0.0012s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_05\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 5);\n")
main:    -> 0.0016s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_06\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 6);\n")
main:    -> 0.0015s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_07\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 7);\n")
main:    -> 0.0011s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_08\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 8);\n")
main:    -> 0.0011s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_09\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 9);\n")
main:    -> 0.0010s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_10\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 10);\n")
main:    -> 0.0014s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_11\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 11);\n")
main:    -> 0.0011s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_12\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 12);\n")
main:    -> 0.0011s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_13\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 13);\n")
main:    -> 0.0011s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_14\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 14);\n")
main:    -> 0.0009s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_15\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 15);\n")
main:    -> 0.0010s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_16\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 16);\n")
main:    -> 0.0009s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_17\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 17);\n")
main:    -> 0.0010s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_18\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 18);\n")
main:    -> 0.0012s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_19\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 19);\n")
main:    -> 0.0010s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_20\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 20);\n")
main:    -> 0.0009s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_21\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 21);\n")
main:    -> 0.0010s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_22\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 22);\n")
main:    -> 0.0013s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_23\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 23);\n")
main:    -> 0.0013s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_24\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 24);\n")
main:    -> 0.0009s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_25\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 25);\n")
main:    -> 0.0009s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_26\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 26);\n")
main:    -> 0.0010s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_27\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 27);\n")
main:    -> 0.0014s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_28\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 28);\n")
main:    -> 0.0011s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_29\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 29);\n")
main:    -> 0.0011s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_30\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 30);\n")
main:    -> 0.0011s
main: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_31\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 31);\n")
main:    -> 0.0014s
main:    -> 0.0384s

ci: == 20231221171135 CreateNamespaceDescendantsTable: migrating ==================
ci: -- execute("CREATE TABLE namespace_descendants (\n  namespace_id bigint NOT NULL,\n  self_and_descendant_group_ids bigint[] NOT NULL DEFAULT ARRAY[]::bigint[],\n  all_project_ids bigint[] NOT NULL DEFAULT ARRAY[]::bigint[],\n  traversal_ids bigint[] NOT NULL DEFAULT ARRAY[]::bigint[],\n  outdated_at timestamp with time zone DEFAULT NOW(),\n  calculated_at timestamp with time zone,\n  PRIMARY KEY(namespace_id)\n)\nPARTITION BY HASH (namespace_id);\n")
ci:    -> 0.0134s
ci: -- execute("CREATE INDEX\nindex_on_namespace_descendants_outdated\nON namespace_descendants (namespace_id)\nWHERE outdated_at IS NOT NULL\n")
ci:    -> 0.0004s
ci: -- transaction(nil)
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_00\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 0);\n")
ci:    -> 0.0026s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_01\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 1);\n")
ci:    -> 0.0025s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_02\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 2);\n")
ci:    -> 0.0020s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_03\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 3);\n")
ci:    -> 0.0017s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_04\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 4);\n")
ci:    -> 0.0020s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_05\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 5);\n")
ci:    -> 0.0018s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_06\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 6);\n")
ci:    -> 0.0015s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_07\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 7);\n")
ci:    -> 0.0015s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_08\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 8);\n")
ci:    -> 0.0010s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_09\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 9);\n")
ci:    -> 0.0011s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_10\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 10);\n")
ci:    -> 0.0016s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_11\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 11);\n")
ci:    -> 0.0014s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_12\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 12);\n")
ci:    -> 0.0009s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_13\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 13);\n")
ci:    -> 0.0013s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_14\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 14);\n")
ci:    -> 0.0009s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_15\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 15);\n")
ci:    -> 0.0009s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_16\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 16);\n")
ci:    -> 0.0010s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_17\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 17);\n")
ci:    -> 0.0009s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_18\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 18);\n")
ci:    -> 0.0010s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_19\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 19);\n")
ci:    -> 0.0009s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_20\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 20);\n")
ci:    -> 0.0013s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_21\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 21);\n")
ci:    -> 0.0009s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_22\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 22);\n")
ci:    -> 0.0013s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_23\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 23);\n")
ci:    -> 0.0015s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_24\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 24);\n")
ci:    -> 0.0011s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_25\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 25);\n")
ci:    -> 0.0012s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_26\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 26);\n")
ci:    -> 0.0011s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_27\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 27);\n")
ci:    -> 0.0014s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_28\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 28);\n")
ci:    -> 0.0012s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_29\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 29);\n")
ci:    -> 0.0014s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_30\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 30);\n")
ci:    -> 0.0011s
ci: -- execute("CREATE TABLE gitlab_partitions_static.namespace_descendants_31\nPARTITION OF namespace_descendants\nFOR VALUES WITH (MODULUS 32, REMAINDER 31);\n")
ci:    -> 0.0016s
ci:    -> 0.0441s
I, [2023-12-29T07:56:50.591483 #211992]  INFO -- : Database: 'ci', Table: 'namespace_descendants': Lock Writes
ci: == 20231221171135 CreateNamespaceDescendantsTable: migrated (0.0858s) =========

Down:

main: == 20231221171135 CreateNamespaceDescendantsTable: reverting ==================
main: -- drop_table(:namespace_descendants)
main:    -> 0.0146s
main: == 20231221171135 CreateNamespaceDescendantsTable: reverted (0.0181s) =========

ci: == 20231221171135 CreateNamespaceDescendantsTable: reverting ==================
ci: -- drop_table(:namespace_descendants)
ci:    -> 0.0216s
ci: == 20231221171135 CreateNamespaceDescendantsTable: reverted (0.0289s) =========

Related to #428464 (closed)

Edited by Adam Hegyi

Merge request reports