From 8b2adc4fb405cfafd6cca5daa8baf8af24230a7a Mon Sep 17 00:00:00 2001 From: ilja Date: Fri, 27 Jan 2023 16:06:27 +0100 Subject: [PATCH] Rename users_ap_id_COALESCE_follower_address_index for faster db restoration By default Postgresql first restores the data and then the indexes when dumping and restoring the database. Restoring index activities_visibility_index took a very long time. users_ap_id_COALESCE_follower_address_index was later added because having this could speed up the restoration tremendously. The problem now is that restoration apparently happens in alphabetical order, so this new index wasn't created yet by the time activities_visibility_index needed it. There were several work-arounds which included more complex steps during backup/restore. By renaming this index, it should be restored first and thus activities_visibility_index can make use of it. This speeds up restoration significantly without requiring more complex or unexpected steps from people. --- ..._ap_id_coalesce_follower_address_index.exs | 23 +++++++++++++++++++ 1 file changed, 23 insertions(+) create mode 100644 priv/repo/migrations/20230127143303_rename_index_users_ap_id_coalesce_follower_address_index.exs diff --git a/priv/repo/migrations/20230127143303_rename_index_users_ap_id_coalesce_follower_address_index.exs b/priv/repo/migrations/20230127143303_rename_index_users_ap_id_coalesce_follower_address_index.exs new file mode 100644 index 000000000..f9b9b12c7 --- /dev/null +++ b/priv/repo/migrations/20230127143303_rename_index_users_ap_id_coalesce_follower_address_index.exs @@ -0,0 +1,23 @@ +defmodule Pleroma.Repo.Migrations.RenameIndexUsersApId_COALESCEFollowerAddressIndex do + alias Pleroma.Repo + + use Ecto.Migration + + def up do + # By default Postgresql first restores the data and then the indexes when dumping and restoring the database. + # Restoring index activities_visibility_index took a very long time. + # users_ap_id_COALESCE_follower_address_index was later added because having this could speed up the restoration tremendously. + # The problem now is that restoration apparently happens in alphabetical order, so this new index wasn't created yet + # by the time activities_visibility_index needed it. + # There were several work-arounds which included more complex steps during backup/restore. + # By renaming this index, it should be restored first and thus activities_visibility_index can make use of it. + # This speeds up restoration significantly without requiring more complex or unexpected steps from people. + Repo.query!("ALTER INDEX public.\"users_ap_id_COALESCE_follower_address_index\" + RENAME TO \"aa_users_ap_id_COALESCE_follower_address_index\";") + end + + def down do + Repo.query!("ALTER INDEX public.\"aa_users_ap_id_COALESCE_follower_address_index\" + RENAME TO \"users_ap_id_COALESCE_follower_address_index\";") + end +end -- 2.44.2