Remove notification settings in batchesimprove-notification-settings-migrations

This improves performance of the duplicate notification settings
migration by removing duplicates in batches instead of using one big
"DELETE FROM" query.

The previous query would locally run over 45 minutes without even
finishing. This new setup finished in a matter of seconds.

Fixes #18289

2 files changed, 31 insertions, 4 deletions

diff --git a/db/migrate/20160603180330_remove_duplicated_notification_settings.rb b/db/migrate/20160603180330_remove_duplicated_notification_settings.rb
index c2fcac4c53d..fe1c863b5b9 100644
--- a/db/migrate/20160603180330_remove_duplicated_notification_settings.rb
+++ b/db/migrate/20160603180330_remove_duplicated_notification_settings.rb
@@ -1,7 +1,32 @@
 class RemoveDuplicatedNotificationSettings < ActiveRecord::Migration
   def up
-    execute <<-SQL
-      DELETE FROM notification_settings WHERE id NOT IN ( SELECT min_id from (SELECT MIN(id) as min_id FROM notification_settings GROUP BY user_id, source_type, source_id) as dups )
-    SQL
+    duplicates = exec_query(%Q{
+      SELECT user_id, source_type, source_id
+      FROM notification_settings
+      GROUP BY user_id, source_type, source_id
+      HAVING COUNT(*) > 1
+    })
+
+    duplicates.each do |row|
+      uid = row['user_id']
+      stype = connection.quote(row['source_type'])
+      sid = row['source_id']
+
+      execute(%Q{
+        DELETE FROM notification_settings
+        WHERE user_id = #{uid}
+        AND source_type = #{stype}
+        AND source_id = #{sid}
+        AND id != (
+          SELECT id FROM (
+            SELECT min(id) AS id
+            FROM notification_settings
+            WHERE user_id = #{uid}
+            AND source_type = #{stype}
+            AND source_id = #{sid}
+          ) min_ids
+        )
+      })
+    end
   end
 end
diff --git a/db/schema.rb b/db/schema.rb
index 69e37470de0..00829d63b66 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -11,7 +11,8 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema.define(version: 20160530150109) do
+ActiveRecord::Schema.define(version: 20160603182247) do
+
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
   enable_extension "pg_trgm"
@@ -676,6 +677,7 @@ ActiveRecord::Schema.define(version: 20160530150109) do
   end
 
   add_index "notification_settings", ["source_id", "source_type"], name: "index_notification_settings_on_source_id_and_source_type", using: :btree
+  add_index "notification_settings", ["user_id", "source_id", "source_type"], name: "index_notifications_on_user_id_and_source_id_and_source_type", unique: true, using: :btree
   add_index "notification_settings", ["user_id"], name: "index_notification_settings_on_user_id", using: :btree
 
   create_table "oauth_access_grants", force: :cascade do |t|