Rename index for faster database restore #455

Merged

floatingghost merged 2 commits from ilja/akkoma:rename_index_for_faster_restore into develop

2023-02-26 21:58:57 +00:00

ilja commented

2023-01-27 11:50:25 +00:00

Contributor

fix #440

I manually changed the index name. On a 10G dump it took ~30min to restore with this rename. I don't know exactly how long it took without renaming the index, but I tried and cancelled the operation after it was already stuck on the problematic index for over 1h.

I also saw that the renamed index is indeed the very first index that's being restored (and before the "problematic" activities_visibility_index).

Renaming the index:

sudo -Hu postgres psql -d akkoma -c "ALTER INDEX public.\"users_ap_id_COALESCE_follower_address_index\" RENAME TO \"aa_users_ap_id_COALESCE_follower_address_index\";"

It's a bit dirty because we go away from naming conventions, but if it works, I think it's worth it.

Write a migration for it (and make it clear in a comment why we do it)
See that new installations can still create a DB from scratch (sometimes you need things outside of transactions, I'm unsure if this is also such one)
Adapt docs (backup-restore doesn't need the note for this any more, maybe also add to changelog)
Run in prod 🐶
- Cherry picked the commits, ran the migration and restarted my instance. Things still work
- Stopped instance, did db dump, extracted the list to see the order of indexes, the renamed one is indeed the first.
- Dropped database, recreate database, restore from dump. We see that the renamed index is restored as first index[1]. DB is 3G in size, restoration took less then 10 minutes. Afterwards I restarted the instance and things seem to still run smoothly.

[1]

Excerpt from the output from restoring the DB. We see the renamed index being the first index restored (2nd output line), and the "problematic" visibility index only later (last output line of this excerpt).

...
pg_restore: creating CONSTRAINT "public.users users_pkey"
pg_restore: creating INDEX "public.aa_users_ap_id_COALESCE_follower_address_index"
pg_restore: creating INDEX "public.activities_actor_id_DESC_NULLS_LAST_index"
pg_restore: creating INDEX "public.activities_cc_index"
pg_restore: creating INDEX "public.activities_context_index"
pg_restore: creating INDEX "public.activities_create_objects_index"
pg_restore: creating INDEX "public.activities_hosts"
pg_restore: creating INDEX "public.activities_id_desc_nulls_last_local_index"
pg_restore: creating INDEX "public.activities_in_reply_to"
pg_restore: creating INDEX "public.activities_likes"
pg_restore: creating INDEX "public.activities_local_index"
pg_restore: creating INDEX "public.activities_recipients_index"
pg_restore: creating INDEX "public.activities_to_index"
pg_restore: creating INDEX "public.activities_unique_apid_index"
pg_restore: creating INDEX "public.activities_visibility_index"
...

fix https://akkoma.dev/AkkomaGang/akkoma/issues/440 I manually changed the index name. On a 10G dump it took ~30min to restore with this rename. I don't know exactly how long it took without renaming the index, but I tried and cancelled the operation after it was already stuck on the problematic index for over 1h. I also saw that the renamed index is indeed the very first index that's being restored (and before the "problematic" activities_visibility_index). Renaming the index: ```sh sudo -Hu postgres psql -d akkoma -c "ALTER INDEX public.\"users_ap_id_COALESCE_follower_address_index\" RENAME TO \"aa_users_ap_id_COALESCE_follower_address_index\";" ``` It's a bit dirty because we go away from naming conventions, but if it works, I think it's worth it. * [x] Write a migration for it (and make it clear in a comment why we do it) * [x] See that new installations can still create a DB from scratch (sometimes you need things outside of transactions, I'm unsure if this is also such one) * [x] Adapt docs (backup-restore doesn't need the note for this any more, maybe also add to changelog) * [x] Run in prod 🐶 * Cherry picked the commits, ran the migration and restarted my instance. Things still work * Stopped instance, did db dump, extracted the list to see the order of indexes, the renamed one is indeed the first. * Dropped database, recreate database, restore from dump. We see that the renamed index is restored as first index[1]. DB is 3G in size, restoration took less then 10 minutes. Afterwards I restarted the instance and things seem to still run smoothly. [1] Excerpt from the output from restoring the DB. We see the renamed index being the first index restored (2nd output line), and the "problematic" visibility index only later (last output line of this excerpt). ``` ... pg_restore: creating CONSTRAINT "public.users users_pkey" pg_restore: creating INDEX "public.aa_users_ap_id_COALESCE_follower_address_index" pg_restore: creating INDEX "public.activities_actor_id_DESC_NULLS_LAST_index" pg_restore: creating INDEX "public.activities_cc_index" pg_restore: creating INDEX "public.activities_context_index" pg_restore: creating INDEX "public.activities_create_objects_index" pg_restore: creating INDEX "public.activities_hosts" pg_restore: creating INDEX "public.activities_id_desc_nulls_last_local_index" pg_restore: creating INDEX "public.activities_in_reply_to" pg_restore: creating INDEX "public.activities_likes" pg_restore: creating INDEX "public.activities_local_index" pg_restore: creating INDEX "public.activities_recipients_index" pg_restore: creating INDEX "public.activities_to_index" pg_restore: creating INDEX "public.activities_unique_apid_index" pg_restore: creating INDEX "public.activities_visibility_index" ... ```