PERF: reduce exclusive locking when changing tables

PERF: reduce exclusive locking when changing tables

posts and topics tables can be huge, this migration change ensures the migration performs less work in one giant transaction reducing risk of deadlocks

Migration is re-runnable thanks to NOT EXISTS checks

diff --git a/db/migrate/20200429095034_add_topic_thumbnail_information.rb b/db/migrate/20200429095034_add_topic_thumbnail_information.rb
index be34f93..ee48265 100644
--- a/db/migrate/20200429095034_add_topic_thumbnail_information.rb
+++ b/db/migrate/20200429095034_add_topic_thumbnail_information.rb
@@ -1,19 +1,36 @@
 # frozen_string_literal: true
 
 class AddTopicThumbnailInformation < ActiveRecord::Migration[6.0]
-  def change
-    add_reference :posts, :image_upload
-    add_reference :topics, :image_upload
+  disable_ddl_transaction!
 
-    add_column :theme_modifier_sets, :topic_thumbnail_sizes, :string, array: true
+  def up
 
-    create_table :topic_thumbnails do |t|
-      t.references :upload, null: false
-      t.references :optimized_image, null: true
-      t.integer :max_width, null: false
-      t.integer :max_height, null: false
+    # tables are huge ... avoid holding on to large number of locks by doing one at a time
+    execute <<~SQL
+      ALTER TABLE posts
+      ADD COLUMN IF NOT EXISTS image_upload_id bigint
+    SQL
+
+    execute <<~SQL
+      ALTER TABLE topics
+      ADD COLUMN IF NOT EXISTS image_upload_id bigint
+    SQL
+
+    ActiveRecord::Base.transaction do
+      add_column :theme_modifier_sets, :topic_thumbnail_sizes, :string, array: true
+
+      create_table :topic_thumbnails do |t|
+        t.references :upload, null: false
+        t.references :optimized_image, null: true
+        t.integer :max_width, null: false
+        t.integer :max_height, null: false
+      end
+
+      add_index :topic_thumbnails, [:upload_id, :max_width, :max_height], name: :unique_topic_thumbnails, unique: true
     end
+  end
 
-    add_index :topic_thumbnails, [:upload_id, :max_width, :max_height], name: :unique_topic_thumbnails, unique: true
+  def down
+    raise IrriversibleMigration
   end
 end

GitHub sha: aa2a9da4