CombineActivitiesAndObjects: cleanup, add missing indexes

2 files changed, 34 insertions, 9 deletions

diff --git a/priv/repo/migrations/20211218181647_combine_activities_and_objects.exs b/priv/repo/migrations/20211218181647_combine_activities_and_objects.exs
index 749704cf1..bc692d99d 100644
--- a/priv/repo/migrations/20211218181647_combine_activities_and_objects.exs
+++ b/priv/repo/migrations/20211218181647_combine_activities_and_objects.exs
@@ -5,11 +5,15 @@ defmodule Pleroma.Repo.Migrations.CombineActivitiesAndObjects do
   @trigger_name "status_visibility_counter_cache_trigger"
 
   def up do
+    # Lock both tables to avoid a running server meddling with our transaction
+    execute("LOCK TABLE activities")
+    execute("LOCK TABLE users")
+
     # Add missing fields to objects table
     alter table(:objects) do
       add(:local, :boolean, null: false, default: true)
       add(:actor, :string)
-      add(:recipients, {:array, :string}, default: []))
+      add(:recipients, {:array, :string}, default: [])
     end
 
     # Add missing indexes to objects
@@ -17,13 +21,15 @@ defmodule Pleroma.Repo.Migrations.CombineActivitiesAndObjects do
     create_if_not_exists(index(:objects, [:actor, "id DESC NULLS LAST"]))
     create_if_not_exists(index(:objects, [:recipients], using: :gin))
 
-    create_if_not_exists(
-      index(:objects, ["(data->'to')"], name: :objects_to_index, using: :gin)
-    )
-
-    create_if_not_exists(
-      index(:objects, ["(data->'cc')"], name: :objects_cc_index, using: :gin)
-    )
+    # Intentionally omit these. According to LiveDashboard they're not used:
+    #
+    # create_if_not_exists(
+    #   index(:objects, ["(data->'to')"], name: :objects_to_index, using: :gin)
+    # )
+    #
+    # create_if_not_exists(
+    #   index(:objects, ["(data->'cc')"], name: :objects_cc_index, using: :gin)
+    # )
 
     create_if_not_exists(
       index(:objects, ["(data->>'actor')", "inserted_at desc"], name: :objects_actor_index)
diff --git a/priv/repo/migrations/20211231013155_add_object_concurrent_indexes.exs b/priv/repo/migrations/20211231013155_add_object_concurrent_indexes.exs
index 370d86733..d6809f692 100644
--- a/priv/repo/migrations/20211231013155_add_object_concurrent_indexes.exs
+++ b/priv/repo/migrations/20211231013155_add_object_concurrent_indexes.exs
@@ -1,9 +1,28 @@
 defmodule Pleroma.Repo.Migrations.AddObjectConcurrentIndexes do
   use Ecto.Migration
+  @disable_migration_lock true
   @disable_ddl_transaction true
 
   def change do
-    create(
+    create_if_not_exists(index(:objects, [:actor, "id DESC NULLS LAST"], concurrently: true))
+
+    create_if_not_exists(
+      index(:objects, ["(data->>'type')", "(data->>'context')"],
+        name: :objects_context_index,
+        concurrently: true
+      )
+    )
+
+    create_if_not_exists(
+      index(:objects, ["(split_part(actor, '/', 3))"],
+        concurrently: true,
+        name: :objects_hosts
+      )
+    )
+
+    create_if_not_exists(index(:objects, ["id desc nulls last", "local"], concurrently: true))
+
+    create_if_not_exists(
       index(:objects, ["activity_visibility(actor, recipients, data)", "id DESC NULLS LAST"],
         name: :objects_visibility_index,
         concurrently: true,