feat(datastore): add index to speed up lookups of manifests by tag

This adds a new index to the tags table on (top_level_namespace_id, repository_id, manifest_id, name). This is required to speed up the underperforming query identified in https://gitlab.com/gitlab-com/gl-infra/production/-/issues/8407.

• Before: https://console.postgres.ai/gitlab/gitlab-production-registry/sessions/15745/commands/54534
• After: https://console.postgres.ai/gitlab/gitlab-production-registry/sessions/15947/commands/55010

Because tags is a partitioned table, we split the indexes for each of the 64 partitions across 4 post-deployment migrations and then have another post-deployment migration just for the (metadata) index on the parent table.

Logs from applying all up migrations in a database lab clone:

Click to expand

CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_0_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_0 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 8837.599 ms (00:08.838)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_1_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_1 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 8305.122 ms (00:08.305)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_2_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_2 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 9935.489 ms (00:09.935)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_3_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_3 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 5584.144 ms (00:05.584)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_4_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_4 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 7146.388 ms (00:07.146)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_5_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_5 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 10413.189 ms (00:10.413)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_6_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_6 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 23053.039 ms (00:23.053)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_7_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_7 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 2900.719 ms (00:02.901)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_8_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_8 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 5905.553 ms (00:05.906)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_9_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_9 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 7092.381 ms (00:07.092)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_10_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_10 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 9855.736 ms (00:09.856)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_11_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_11 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 28800.154 ms (00:28.800)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_12_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_12 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 13103.130 ms (00:13.103)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_13_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_13 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 6326.729 ms (00:06.327)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_14_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_14 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 7949.919 ms (00:07.950)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_15_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_15 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 5618.862 ms (00:05.619)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_16_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_16 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 7157.892 ms (00:07.158)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_17_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_17 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 6090.207 ms (00:06.090)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_18_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_18 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 5615.378 ms (00:05.615)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_19_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_19 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 8267.459 ms (00:08.267)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_20_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_20 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 4426.895 ms (00:04.427)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_21_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_21 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 7100.810 ms (00:07.101)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_22_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_22 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 8324.842 ms (00:08.325)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_23_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_23 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 8348.630 ms (00:08.349)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_24_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_24 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 10813.728 ms (00:10.814)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_25_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_25 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 10721.749 ms (00:10.722)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_26_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_26 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 7153.667 ms (00:07.154)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_27_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_27 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 6509.995 ms (00:06.510)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_28_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_28 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 6413.265 ms (00:06.413)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_29_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_29 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 8657.292 ms (00:08.657)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_30_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_30 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 4210.303 ms (00:04.210)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_31_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_31 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 3430.346 ms (00:03.430)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_32_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_32 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 6185.921 ms (00:06.186)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_33_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_33 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 15522.533 ms (00:15.523)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_34_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_34 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 6175.511 ms (00:06.176)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_35_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_35 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 34606.612 ms (00:34.607)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_36_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_36 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 6361.567 ms (00:06.362)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_37_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_37 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 6092.063 ms (00:06.092)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_38_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_38 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 4191.494 ms (00:04.191)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_39_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_39 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 18800.463 ms (00:18.800)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_40_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_40 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 11173.052 ms (00:11.173)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_41_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_41 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 9115.534 ms (00:09.116)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_42_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_42 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 7463.616 ms (00:07.464)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_43_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_43 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 8497.973 ms (00:08.498)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_44_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_44 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 8832.685 ms (00:08.833)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_45_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_45 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 4468.579 ms (00:04.469)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_46_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_46 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 3531.772 ms (00:03.532)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_47_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_47 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 6486.131 ms (00:06.486)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_48_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_48 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 8098.215 ms (00:08.098)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_49_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_49 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 10467.427 ms (00:10.467)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_50_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_50 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 5416.399 ms (00:05.416)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_51_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_51 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 5622.416 ms (00:05.622)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_52_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_52 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 6067.765 ms (00:06.068)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_53_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_53 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 8656.895 ms (00:08.657)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_54_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_54 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 8256.642 ms (00:08.257)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_55_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_55 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 6641.529 ms (00:06.642)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_56_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_56 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 5868.406 ms (00:05.868)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_57_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_57 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 7382.807 ms (00:07.383)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_58_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_58 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 18308.903 ms (00:18.309)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_59_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_59 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 6778.037 ms (00:06.778)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_60_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_60 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 9052.996 ms (00:09.053)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_61_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_61 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 13275.844 ms (00:13.276)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_62_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_62 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 9786.523 ms (00:09.787)
CREATE INDEX CONCURRENTLY IF NOT EXISTS index_tags_p_63_on_ns_id_and_repo_id_and_manifest_id_and_name ON partitions.tags_p_63 USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 9156.593 ms (00:09.157)
CREATE INDEX index_tags_on_ns_id_and_repo_id_and_manifest_id_and_name ON public.tags USING btree (top_level_namespace_id, repository_id, manifest_id, name);
CREATE INDEX
Time: 850.470 ms

Related to https://gitlab.com/gitlab-com/gl-infra/production/-/issues/8407.