PERF: Ensure transaction is of minimal size

PERF: Ensure transaction is of minimal size

A giant transaction in a post migration can be very risky.

This splits the large amount of work this migration needs to do into 2 parts:

  1. A re-runnable cleanup job prior to transaction
  2. A minimally sized transaction to add the database constraint

This avoids large amounts of churn on the table

diff --git a/db/post_migrate/20200818084329_update_private_message_on_post_search_data.rb b/db/post_migrate/20200818084329_update_private_message_on_post_search_data.rb
index c15e9bc..5fc8445 100644
--- a/db/post_migrate/20200818084329_update_private_message_on_post_search_data.rb
+++ b/db/post_migrate/20200818084329_update_private_message_on_post_search_data.rb
@@ -1,6 +1,29 @@
 # frozen_string_literal: true
 
 class UpdatePrivateMessageOnPostSearchData < ActiveRecord::Migration[6.0]
+  # this is a very big change ... avoid an enormous transaction here
+  disable_ddl_transaction!
+
+  def update_private_message_flag
+    execute <<~SQL
+    UPDATE post_search_data
+    SET private_message = true
+    FROM posts
+    INNER JOIN topics ON topics.id = posts.topic_id AND topics.archetype = 'private_message'
+    WHERE posts.id = post_search_data.post_id AND
+      (private_message IS NULL or private_message = false)
+    SQL
+
+    execute <<~SQL
+    UPDATE post_search_data
+    SET private_message = false
+    FROM posts
+    INNER JOIN topics ON topics.id = posts.topic_id AND topics.archetype <> 'private_message'
+    WHERE posts.id = post_search_data.post_id AND
+      (private_message IS NULL or private_message = true)
+    SQL
+  end
+
   def up
     # Delete post_search_data of orphaned posts
     execute <<~SQL
@@ -24,23 +47,12 @@ class UpdatePrivateMessageOnPostSearchData < ActiveRecord::Migration[6.0]
     )
     SQL
 
-    execute <<~SQL
-    UPDATE post_search_data
-    SET private_message = true
-    FROM posts
-    INNER JOIN topics ON topics.id = posts.topic_id AND topics.archetype = 'private_message'
-    WHERE posts.id = post_search_data.post_id
-    SQL
-
-    execute <<~SQL
-    UPDATE post_search_data
-    SET private_message = false
-    FROM posts
-    INNER JOIN topics ON topics.id = posts.topic_id AND topics.archetype <> 'private_message'
-    WHERE posts.id = post_search_data.post_id
-    SQL
+    update_private_message_flag
 
-    change_column_null(:post_search_data, :private_message, false)
+    ActiveRecord::Base.transaction do
+      update_private_message_flag
+      change_column_null(:post_search_data, :private_message, false)
+    end
   end
 
   def down

GitHub sha: fcfaa8b0