I am having trouble pruning my database

I’ve read some of the other topics requesting assistance on this matter, and I realize that this operation will take a lot of time. My instance is about 10 months old at this point, and I was subscribed at one point to 2-3 relays. Those have since been turned off once I built up my social graph a bit.

Prior to running “pleroma_ctl database prune_objects --prune-orphaned-activities”, I modified the remote post retention time to 270 days (~9 months). However, things just seem to choke up on my instance when this command is run - I realize it is database intensive work, so I scheduled it to run overnight, thinking it would only take a few hours. Unfortunately, I come back the next day and my database is still hovering around 39 GB, CPU and RAM are utilized up near 90% of max, and I have no way of knowing if this operation is close to completing.

Any tips here? I can raise the retention time even higher (I was subscribed to relays right off the bat - maybe the bulk of my database is stacked from earlier in it’s existence?). I can also scale up my VM with extra RAM and CPU Cores if it would help the operation complete quicker.

I think I saw elsewhere on here that you can set retention out longer, do a prune (which can still take a looong time) then bring the time back in and run again. edit: oh 9 of 10 months my bad.

But yeah it can take a while I believe.

subscribing to relays massively inflates your db

my instance has been up for 5 years and is around 10gb, so 40gb in 10 months is absolutely mental

it will take a long time, you are basically asking for a total scan of 40gb of data; you can do as said above, which is to set the retention to, say, your instance age minus one month, run the prune which should take a shorter amount of time, decrease retention a little and repeat

but there is no getting around having to fully scan your data, it’s why i never recommend subscribing to relays to anyone that isn’t completely ok with massive databases, most of the time it is not worth it.

Haha, yeah, not my proudest moment.

Is there any way to run this operation while the Akkoma service isn’t running? ie. if I take my instance offline for X amount of time for maintenance. The intention being to reduce the load so the prune operation can run at full force.

I think you can run the prune operation while akkoma is offline so long as postgres itself is still up. I know I had to do that when I ran the prune task on my instance since I also had performance issues with akkoma still running.

Tried this last night but unfortunately did not seem to like akkoma being down.

I’m also on an old version, via Yunohost, which cannot be updated due to some bug with Yunohost. I’m now wondering if I can export critical data (users, configuration, local statuses) and nuke the whole site and do a fresh install.

1 Like