Add data migration to flip draft status column false->true for all open wip/draft MRs

What does this MR do?

Adds a data migration to backfill draft for all open draft MRs.

Database

Up

== 20210526222715 BackfillDraftStatusOnMergeRequests: migrating ===============
-- indexes(:merge_requests)
   -> 0.0287s
-- current_schema()
   -> 0.0003s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:merge_requests, [:state_id, :draft, :title], {:where=>"state_id = 1 AND draft = false AND((title)::text ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'::text)", :name=>"tmp_index_merge_requests_draft_and_status", :algorithm=>:concurrently})
   -> 0.0164s
-- execute("SET statement_timeout TO 0")
   -> 0.0009s
-- add_index(:merge_requests, [:state_id, :draft, :title], {:where=>"state_id = 1 AND draft = false AND((title)::text ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'::text)", :name=>"tmp_index_merge_requests_draft_and_status", :algorithm=>:concurrently})
   -> 0.0109s
-- execute("RESET ALL")
   -> 0.0013s
-- transaction_open?()
   -> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"merge_requests\" WHERE \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"draft\" = FALSE AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'")
   -> 0.0144s
-- exec_query("SELECT \"merge_requests\".\"id\" FROM \"merge_requests\" WHERE \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP' ORDER BY \"merge_requests\".\"id\" ASC LIMIT 1")
   -> 0.0028s
-- exec_query("SELECT \"merge_requests\".\"id\" FROM \"merge_requests\" WHERE \"merge_requests\".\"id\" >= 99 AND \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP' ORDER BY \"merge_requests\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0029s
-- execute("UPDATE \"merge_requests\" SET \"draft\" = TRUE WHERE \"merge_requests\".\"id\" >= 99 AND \"merge_requests\".\"id\" < 212 AND \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'")
   -> 0.0049s
-- exec_query("SELECT \"merge_requests\".\"id\" FROM \"merge_requests\" WHERE \"merge_requests\".\"id\" >= 212 AND \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP' ORDER BY \"merge_requests\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0019s
-- execute("UPDATE \"merge_requests\" SET \"draft\" = TRUE WHERE \"merge_requests\".\"id\" >= 212 AND \"merge_requests\".\"id\" < 214 AND \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'")
   -> 0.0016s
-- exec_query("SELECT \"merge_requests\".\"id\" FROM \"merge_requests\" WHERE \"merge_requests\".\"id\" >= 214 AND \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP' ORDER BY \"merge_requests\".\"id\" ASC LIMIT 1 OFFSET 1")
   -> 0.0015s
-- execute("UPDATE \"merge_requests\" SET \"draft\" = TRUE WHERE \"merge_requests\".\"id\" >= 214 AND \"merge_requests\".\"state_id\" = 1 AND \"merge_requests\".\"title\" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'")
   -> 0.0021s
-- indexes(:merge_requests)
   -> 0.0149s
-- transaction_open?()
   -> 0.0000s
-- indexes(:merge_requests)
   -> 0.0158s
-- remove_index(:merge_requests, {:algorithm=>:concurrently, :name=>"tmp_index_merge_requests_draft_and_status"})
   -> 0.0047s
== 20210526222715 BackfillDraftStatusOnMergeRequests: migrated (0.1165s) ======

Down

== 20210526222715 BackfillDraftStatusOnMergeRequests: reverting ===============
-- indexes(:merge_requests)
   -> 0.0197s
-- current_schema()
   -> 0.0002s
== 20210526222715 BackfillDraftStatusOnMergeRequests: reverted (0.0233s) ======

Temp Index

exec CREATE INDEX merge_request_draft_temp_index ON merge_requests USING btree (id) WHERE state_id = 1 AND draft = FALSE AND ((title)::text ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'::text);

The query has been executed. Duration: 52.945 min

\di+ merge_request_draft_temp_index
Session: 4317
Command output:
List of relations
 Schema |              Name              | Type  |    Owner     |     Table      |  Size   | Description 
--------+--------------------------------+-------+--------------+----------------+---------+-------------
 public | merge_request_draft_temp_index | index | joe_acroitor | merge_requests | 5784 kB | 
(1 row)

• With the index:

explain SELECT id FROM "merge_requests" WHERE "merge_requests"."state_id" = 1 AND "merge_requests"."draft" = FALSE AND "merge_requests"."title" ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'

Index Only Scan using merge_request_draft_temp_index on public.merge_requests  (cost=0.42..1315.74 rows=58377 width=4) (actual time=0.084..146.856 rows=262468 loops=1)
   Heap Fetches: 6684
   Buffers: shared hit=226833
   I/O Timings: read=0.000 write=0.000

Time: 184.845 ms
  - planning: 18.312 ms
  - execution: 166.533 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 226833 (~1.70 GiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Related to #330276 (closed)

db/post_migrate/20210526222715_backfill_draft_status_on_merge_requests.rb

+# frozen_string_literal: true
+
+class BackfillDraftStatusOnMergeRequests < ActiveRecord::Migration[6.0]
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  INDEX_NAME = "tmp_index_merge_requests_draft_and_status"
+
+  disable_ddl_transaction!
+
+  def up
+    add_concurrent_index :merge_requests, :id,
+      where: "draft = false AND state_id = 1 AND ((title)::text ~* '^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP'::text)",
+      name: INDEX_NAME
+
+    update_column_in_batches(:merge_requests, :draft, true, batch_size: 100) do |table, query|
+      query
+        .where(table[:state_id].eq(1))
+        .where(table[:draft].eq(false))
+        .where(table[:title].matches_regexp('^\\[draft\\]|\\(draft\\)|draft:|draft|\\[WIP\\]|WIP:|WIP', false))
+    end
+
+    remove_concurrent_index_by_name :merge_requests, INDEX_NAME
+  end
+
+  def down
+    remove_concurrent_index_by_name :merge_requests, INDEX_NAME
+  end
+end