Run --prune-orphaned-activities as low priority background task

Hello all!

I recently migrated from Pleroma to Akkoma, which was quite flawless btw. One of the first things I thought might be a good idea was to run --prune-orphaned-activities. However, I had to stop the task after 21 hours as it rendered my instance nearly unusable. Digging around I found:

WARNING THE SECOND: this is massively database-intensive. if you are an older instance and plan to do this, i’d advise making a thread in support, you’ll likely want to slowly delete activities in the background

Well, and here we are now :slight_smile: How can I run --prune-orphaned-activities as low priority background task?

Many thanks!

The way I would do it is to set the config :pleroma, :instance, remote_post_retention_days to a high number. E.g. if your instance is 4 years old, that’s about 4*336=1344 days old. Start with setting it to, idk, 1200 or something, and see how that holds out. You can then lower the number and rerun until you have the desired number of days.

1 Like

Okay, thanks! I will give it a try.

One other thing I found are those errors in PostgreSQL log:

2023-09-17 13:16:25.688 CEST [2918] pleroma@pleroma ERROR:  canceling statement due to user request
2023-09-17 13:16:25.688 CEST [2918] pleroma@pleroma CONTEXT:  while inserting index tuple (80,141) in relation "counter_cache"
        SQL statement "INSERT INTO "counter_cache" ("instance", public) VALUES ($1, 1)
                              ON CONFLICT ("instance") DO
                              UPDATE SET public = "counter_cache".public + 1"
        PL/pgSQL function update_status_visibility_counter_cache() line 18 at EXECUTE
2023-09-17 13:16:25.688 CEST [2918] pleroma@pleroma STATEMENT:  INSERT INTO "activities" ("actor","data","local","recipients","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7)

I get really a lot of them when running --prune-orphaned-activities. Maybe this is related to the long runtime?

Running --prune-orphaned-activities in small bites has worked somewhow. My instance is about 30 months old. So I set config :pleroma, :instance, remote_post_retention_days to 800 which should clean up about 100 to 150 days. This first run took 12 hours and completed successfully. In the second step I reduced to 700 days. This ran through in 4 seconds. In the third step I immediately went to 90 days and this round also took only about 4 seconds.

1 Like