Remove temporary index tmp_idx_package_files_on_non_zero_size
🌳 Context
In Add background migration to fix packages_sizes ... (!109341 - merged), we created a background migration for %15.9 that aimed to fix the packages_size
field in the project statistics objects.
Given that large amount of statistics objects that we needed to go through, we introduced a temporary index: tmp_idx_package_files_on_non_zero_size
.
With this MR, we remove that index.
We can see in Thanos that it's not in use for gitlab.com
See Remove temporary index on package files (#386695 - closed).
🔍 What does this MR do and why?
Following the two last steps of https://docs.gitlab.com/ee/development/database/batched_background_migrations.html#example:
- Ensure that the background migration was executed.
- Remove the temporary index.
Each step is in its own post deployment migration.
🖼 Screenshots or screen recordings
n / a
⚙ How to set up and validate locally
Execute the migrations:
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.
-
I have evaluated the MR acceptance checklist for this MR.
💾 Database
⤴ Migration up
$ rail db:migrate
main: == [advisory_lock_connection] object_id: 272820, pg_backend_pid: 63289
main: == 20230420065656 FinalizeFixIncoherentPackagesSizeOnProjectStatistics: migrating
main: -- transaction_open?()
main: -> 0.0000s
main: == 20230420065656 FinalizeFixIncoherentPackagesSizeOnProjectStatistics: migrated (0.0178s)
main: == 20230420070009 DropTmpIdxPackageFilesOnNonZeroSize: migrating ==============
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0003s
main: -- index_exists?(:packages_package_files, [:package_id, :size], {:name=>"tmp_idx_package_files_on_non_zero_size", :algorithm=>:concurrently})
main: -> 0.0043s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- remove_index(:packages_package_files, {:name=>"tmp_idx_package_files_on_non_zero_size", :algorithm=>:concurrently, :column=>[:package_id, :size]})
main: -> 0.0054s
main: -- execute("RESET statement_timeout")
main: -> 0.0002s
main: == 20230420070009 DropTmpIdxPackageFilesOnNonZeroSize: migrated (0.0188s) =====
main: == [advisory_lock_connection] object_id: 272820, pg_backend_pid: 63289
⤵ Migration down
$ rails db:rollback
main: == [advisory_lock_connection] object_id: 272640, pg_backend_pid: 62763
main: == 20230420070009 DropTmpIdxPackageFilesOnNonZeroSize: reverting ==============
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0650s
main: -- index_exists?(:packages_package_files, [:package_id, :size], {:where=>"size IS NOT NULL", :name=>"tmp_idx_package_files_on_non_zero_size", :algorithm=>:concurrently})
main: -> 0.0043s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0001s
main: -- add_index(:packages_package_files, [:package_id, :size], {:where=>"size IS NOT NULL", :name=>"tmp_idx_package_files_on_non_zero_size", :algorithm=>:concurrently})
main: -> 0.0034s
main: -- execute("RESET statement_timeout")
main: -> 0.0002s
main: == 20230420070009 DropTmpIdxPackageFilesOnNonZeroSize: reverted (0.0841s) =====
main: == [advisory_lock_connection] object_id: 272640, pg_backend_pid: 62763
main: == [advisory_lock_connection] object_id: 272580, pg_backend_pid: 63023
main: == 20230420065656 FinalizeFixIncoherentPackagesSizeOnProjectStatistics: reverting
main: == 20230420065656 FinalizeFixIncoherentPackagesSizeOnProjectStatistics: reverted (0.0031s)
Related to #386695 (closed)