Skip to content

Update container repositories migration index

Steve Abrams requested to merge update_container_repos_migration_index into master

What does this MR do and why?

We are near the end of migrating all container repositories on GitLab.com to the new container registry. This process has been driven by rails. Rails is responsible for finding the next qualified container repository to migrate and starting the migration process. On 2022-01-23, all new container repositories were created on the new registry, so we only needed to query repositories prior to that date. We have a special index to help with that:

"tmp_idx_container_repos_on_non_migrated" btree (project_id, id) WHERE migration_state <> 'import_done'::text AND created_at < '2022-01-23 00:00:00'::timestamp without time zone

Recently, we found there are some special cases where container repositories created after that date needed to be queued and migrated or re-migrated. So we updated the date, which is controlled in an application setting. This caused the index to stop being used and poor performance in the query.

We could update the index to use the new date: 2022-08-17, but that is not necessary since the number of container repositories WHERE migration_state <> 'import_done' is getting very small. We will also soon be changing so all new repositories are created in that migration_state, so that index will only become smaller and smaller.

This MR replaces the index with one that does not check created_at < '2022-01-23 00:00:00'.

Query performance with the old index 13s: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11633/commands/41316

Query performance with the new index 16ms: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11633/commands/41322

This migration system will not be used on self-managed and we will be constructing alternative ways for self-managed users to migrate to the new container repository, so we do not need to keep the old index.

Screenshots or screen recordings

Migration Up 
main: == 20220820214505 UpdateTmpNonMigratedIndexOnContainerRepositories: migrating =
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:container_repositories, [:project_id, :id], {:name=>"tmp_index_container_repos_on_non_migrated", :where=>"migration_state != 'import_done'", :algorithm=>:concurrently})
main:    -> 0.0085s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- add_index(:container_repositories, [:project_id, :id], {:name=>"tmp_index_container_repos_on_non_migrated", :where=>"migration_state != 'import_done'", :algorithm=>:concurrently})
main:    -> 0.0038s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:container_repositories)
main:    -> 0.0036s
main: -- remove_index(:container_repositories, {:algorithm=>:concurrently, :name=>"tmp_idx_container_repos_on_non_migrated"})
main:    -> 0.0027s
main: == 20220820214505 UpdateTmpNonMigratedIndexOnContainerRepositories: migrated (0.0263s)

ci: == 20220820214505 UpdateTmpNonMigratedIndexOnContainerRepositories: migrating =
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:container_repositories, [:project_id, :id], {:name=>"tmp_index_container_repos_on_non_migrated", :where=>"migration_state != 'import_done'", :algorithm=>:concurrently})
ci:    -> 0.0057s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0001s
ci: -- add_index(:container_repositories, [:project_id, :id], {:name=>"tmp_index_container_repos_on_non_migrated", :where=>"migration_state != 'import_done'", :algorithm=>:concurrently})
ci:    -> 0.0044s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0001s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:container_repositories)
ci:    -> 0.0036s
ci: -- remove_index(:container_repositories, {:algorithm=>:concurrently, :name=>"tmp_idx_container_repos_on_non_migrated"})
ci:    -> 0.0024s
ci: == 20220820214505 UpdateTmpNonMigratedIndexOnContainerRepositories: migrated (0.0222s)
Migration Down 
ci: == 20220820214505 UpdateTmpNonMigratedIndexOnContainerRepositories: reverting =
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:container_repositories, [:project_id, :id], {:name=>"tmp_idx_container_repos_on_non_migrated", :where=>"migration_state != 'import_done' AND created_at < '2022-01-23'", :algorithm=>:concurrently})
ci:    -> 0.0070s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:container_repositories, [:project_id, :id], {:name=>"tmp_idx_container_repos_on_non_migrated", :where=>"migration_state != 'import_done' AND created_at < '2022-01-23'", :algorithm=>:concurrently})
ci:    -> 0.0025s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:container_repositories)
ci:    -> 0.0049s
ci: -- remove_index(:container_repositories, {:algorithm=>:concurrently, :name=>"tmp_index_container_repos_on_non_migrated"})
ci:    -> 0.0021s
ci: == 20220820214505 UpdateTmpNonMigratedIndexOnContainerRepositories: reverted (0.0237s)

main: == 20220820214505 UpdateTmpNonMigratedIndexOnContainerRepositories: reverting =
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:container_repositories, [:project_id, :id], {:name=>"tmp_idx_container_repos_on_non_migrated", :where=>"migration_state != 'import_done' AND created_at < '2022-01-23'", :algorithm=>:concurrently})
main:    -> 0.0061s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- add_index(:container_repositories, [:project_id, :id], {:name=>"tmp_idx_container_repos_on_non_migrated", :where=>"migration_state != 'import_done' AND created_at < '2022-01-23'", :algorithm=>:concurrently})
main:    -> 0.0026s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:container_repositories)
main:    -> 0.0032s
main: -- remove_index(:container_repositories, {:algorithm=>:concurrently, :name=>"tmp_index_container_repos_on_non_migrated"})
main:    -> 0.0019s
main: == 20220820214505 UpdateTmpNonMigratedIndexOnContainerRepositories: reverted (0.0201s)

How to set up and validate locally

  1. Pull the branch
  2. bundle exec rails db:migrate

MR acceptance checklist

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

Merge request reports