Skip to content

Cleanup work_item_type_id issues table backfill

Mario Celi requested to merge 345830-cleanup-issues-backfill into master

What does this MR do and why?

In !71869 (merged) we introduced some temp solutions to aid with the backfill of the large issues table. Here we are undoing those changes since the backfill has been completed and the NOT NULL constraint validated in production. This MR does:

  1. DROP tmp_index_issues_on_issue_type_and_id (39.822 ms in database-lab)
  2. CREATE index_issues_on_work_item_type_id asynchronously. It was removed to allow HOT updates during the backfill

Migration output

UP

bin/rails db:migrate
main: == 20221128220043 DropTempWorkItemTypeIdBackfillIndex: migrating ==============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0148s
main: -- indexes(:issues)
main:    -> 0.0285s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"tmp_index_issues_on_issue_type_and_id"})
main:    -> 0.0032s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20221128220043 DropTempWorkItemTypeIdBackfillIndex: migrated (0.0638s) =====

main: == 20221128222417 AddBackIssuesWorkItemTypeIdIndex: migrating =================
main: -- index_exists?(:issues, :work_item_type_id, {:name=>"index_issues_on_work_item_type_id", :algorithm=>:concurrently})
main:    -> 0.0232s
main: -- add_index_options(:issues, :work_item_type_id, {:name=>"index_issues_on_work_item_type_id", :algorithm=>:concurrently})
main:    -> 0.0005s
main: == 20221128222417 AddBackIssuesWorkItemTypeIdIndex: migrated (0.0506s) ========

ci: == 20221128220043 DropTempWorkItemTypeIdBackfillIndex: migrating ==============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0009s
ci: -- indexes(:issues)
ci:    -> 0.0259s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- remove_index(:issues, {:algorithm=>:concurrently, :name=>"tmp_index_issues_on_issue_type_and_id"})
ci:    -> 0.0031s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20221128220043 DropTempWorkItemTypeIdBackfillIndex: migrated (0.0382s) =====

ci: == 20221128222417 AddBackIssuesWorkItemTypeIdIndex: migrating =================
ci: -- index_exists?(:issues, :work_item_type_id, {:name=>"index_issues_on_work_item_type_id", :algorithm=>:concurrently})
ci:    -> 0.0465s
ci: -- add_index_options(:issues, :work_item_type_id, {:name=>"index_issues_on_work_item_type_id", :algorithm=>:concurrently})
ci:    -> 0.0006s
ci: == 20221128222417 AddBackIssuesWorkItemTypeIdIndex: migrated (0.0593s) ========

DOWN

bin/rails db:rollback:main db:rollback:ci STEP=2
main: == 20221128222417 AddBackIssuesWorkItemTypeIdIndex: reverting =================
main: == 20221128222417 AddBackIssuesWorkItemTypeIdIndex: reverted (0.0251s) ========

main: == 20221128220043 DropTempWorkItemTypeIdBackfillIndex: reverting ==============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0007s
main: -- index_exists?(:issues, [:issue_type, :id], {:name=>"tmp_index_issues_on_issue_type_and_id", :algorithm=>:concurrently})
main:    -> 0.0208s
main: -- add_index(:issues, [:issue_type, :id], {:name=>"tmp_index_issues_on_issue_type_and_id", :algorithm=>:concurrently})
main:    -> 0.0076s
main: == 20221128220043 DropTempWorkItemTypeIdBackfillIndex: reverted (0.0357s) =====

ci: == 20221128222417 AddBackIssuesWorkItemTypeIdIndex: reverting =================
ci: == 20221128222417 AddBackIssuesWorkItemTypeIdIndex: reverted (0.0106s) ========

ci: == 20221128220043 DropTempWorkItemTypeIdBackfillIndex: reverting ==============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0007s
ci: -- index_exists?(:issues, [:issue_type, :id], {:name=>"tmp_index_issues_on_issue_type_and_id", :algorithm=>:concurrently})
ci:    -> 0.0457s
ci: -- add_index(:issues, [:issue_type, :id], {:name=>"tmp_index_issues_on_issue_type_and_id", :algorithm=>:concurrently})
ci:    -> 0.0084s
ci: == 20221128220043 DropTempWorkItemTypeIdBackfillIndex: reverted (0.0617s) =====

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

Edited by Mario Celi

Merge request reports

Loading