PERF: Add `index_for_rebake_old` to `posts`.

PERF: Add index_for_rebake_old to posts.

The index becomes smaller over time and is much faster.

Follow up to 4791d992dc484c8b81ec4250c65c097873e2f708.

diff --git a/app/models/post.rb b/app/models/post.rb
index 019d5d4..b26510c 100644
--- a/app/models/post.rb
+++ b/app/models/post.rb
@@ -20,6 +20,7 @@ class Post < ActiveRecord::Base
   self.plugin_permitted_create_params = {}
 
   # increase this number to force a system wide post rebake
+  # Recreate `index_for_rebake_old` when the number is increased
   # Version 1, was the initial version
   # Version 2 15-12-2017, introduces CommonMark and a huge number of onebox fixes
   BAKED_VERSION = 2
diff --git a/db/migrate/20190409054736_add_index_for_rebake_old_on_posts.rb b/db/migrate/20190409054736_add_index_for_rebake_old_on_posts.rb
new file mode 100644
index 0000000..74c7980
--- /dev/null
+++ b/db/migrate/20190409054736_add_index_for_rebake_old_on_posts.rb
@@ -0,0 +1,22 @@
+class AddIndexForRebakeOldOnPosts < ActiveRecord::Migration[5.2]
+  disable_ddl_transaction!
+
+  def up
+    remove_index :posts, name: :index_posts_on_id_and_baked_version
+
+    add_index :posts, :id,
+      order: { id: :desc },
+      where: "(baked_version IS NULL OR baked_version < 2) AND deleted_at IS NULL",
+      name: :index_for_rebake_old,
+      algorithm: :concurrently
+  end
+
+  def down
+    remove_index :posts, name: :index_for_rebake_old
+
+    add_index :posts, [:id, :baked_version],
+      order: { id: :desc },
+      where: "(deleted_at IS NULL)",
+      algorithm: :concurrently
+  end
+end

GitHub sha: c82a9290

1 Like

I think we need one more followup here, we should only remove the index if it is there :slight_smile: cause the transactionless migration can blow up after the remove.

For rollback or migrate?

Rollback can simply raise imo. But the up migration needs extra protection.

I had a similar one last week. see:

2 Likes

DEV: Safeguard for migration that disables DDL transactions.