Egregoros · Phoenix Framework

Post

Remote status

Context

Phantasm

09:25:50.777 [info] Pruning objects older than 90 days, keeping non public posts, keeping threads intact, pruning orphaned activities

Running the forbidden task ;)
pleroma-pruned-db-winning.png

pernfugee

@pernia@ryona.agency remote

@phnt let me know how big ur db is after. i ran this on my old pleroma db (after getting the sql query out of the debug logs cuz the mix task kept timing out) and it went from 127gb to 127gb

Pilgrim

@pwm@darkdork.dev remote

@pernia @phnt manual activities table pruning has always yielded the most gains for me

Pilgrim

@pwm@darkdork.dev remote

@pernia @phnt decayable was about 130 and I knocked it down to 20 between manual removal and pleroma's built in tools

pernfugee

@pernia@ryona.agency remote

@pwm @phnt damn. what queries did you run manually?

Pilgrim

@pwm@darkdork.dev remote

@pernia @phnt I deleted remote activities that referenced objects that did not come from decayable (so that we didn't lose any history of interactions). I did not run anything else manually except a vacuum full maybe? I let pleroma decide on which objects to keep.
I also ran the deletions in batches of ~10k with a shell script to keep things moving and give me an indication of progress.

Pilgrim

@pwm@darkdork.dev remote

@pernia @phnt I can post the shell script.
It's a lot broader than just announce's, it's blasts reactions, likes, etc.

Phantasm

@phnt@fluffytail.org remote

@pwm @pernia The Mix task should delete all of those if it also deleted the referenced remote object. That's literally what the --prune-orphaned-activities flag is for.

Pilgrim

@pwm@darkdork.dev remote

@phnt @pernia I did the manual thing first because the mix task takes three days on my hardware with a 130 gigabyte database

Pilgrim

@pwm@darkdork.dev remote

@phnt @pernia

#!/bin/bash
to_delete=$1
batch_size=$2
do_delete () {
	n=$1
	out=$(psql -U pleroma -d pleroma -p 5435 -c "delete from activities where id in (select id from activities a where a.inserted_at < '2026-01-01'::date and not local and split_part('data->>object', '/', 3) != 'decayable.ink' limit $n)")
	echo $out
}

i=$to_delete
while [[ $i -gt 0 ]]; do
	a=$(do_delete $batch_size);
	i=$((i-$batch_size))
	deleted=$(($to_delete - $i))
	pct=$(($deleted * 100 / $to_delete))
	echo $(date --iso-8601="seconds") $pct $a
done

you probably need to adjust how psql is called, and obviously the date and the domain. The query is a little ugly in there but here it is formatted a little better:

delete from activities where id in (
  select id from activities a 
  where 
  a.inserted_at < '2026-01-01'::date 
  and not local 
  and split_part(data->>'object', '/', 3) != 'decayable.ink'
  limit 10000
)

The compound select is necessary to do this in batches, which keeps postgres flushing the deletes constantly instead of aggregating everything up and do one biiiiiig delete. As a bonus the script eats the output and gives you progress readouts. It's okay to go a little over the total number of rows you want to delete (you can count(*) the inner select in order to get the exact amount).

pernfugee

@pernia@ryona.agency remote

@pwm @phnt does this query keep hellthreads?

Pilgrim

@pwm@darkdork.dev remote

@pernia @phnt the one I posted doesn't mess with objects (posts e.g.). Just things like repeats and reactions etc.
I used pleroma's built in tooling to determine which posts to delete for itself.

pernfugee

@pernia@ryona.agency remote

@pwm @phnt cuz it seems that its just deleting all activities that are basically remote (and not from decayable.ink). it would leave the objects but orphan them.

at least according to the robot.

Pilgrim

@pwm@darkdork.dev remote

@pernia @phnt what this query does is delete remote activities which reference remote objects, that's all.
So if you left an eggplant react on a post from poast, we delete our record of that interaction, because you're not a local user, and that post doesn't belong to one of our users.

pernfugee

@pernia@ryona.agency remote

@pwm @phnt what are the arguments for? is to_delete the max u want to delete?

Pilgrim

@pwm@darkdork.dev remote

@pernia @phnt yeah, and the second argument is the batch size.

pernfugee

@pernia@ryona.agency remote

@pwm @phnt holy shit

after i ran ur script, as well as phnt's relay query, and vacuumed, my 127gb db went down to 19gb

Pilgrim

@pwm@darkdork.dev remote

@pernia @phnt basically how it went for decayable.
You've been needlessly archiving the whole gd network. Run the builtin oleroma stuff regularly and the size will stay manageable. My script is only useful for the batched deletion.

pernfugee

@pernia@ryona.agency remote

@pwm @phnt its insane that likes and boosts would take more than 100gb though. unless ur script removes more than just that.

Pilgrim

@pwm@darkdork.dev remote

@pernia @phnt it doesn't discriminate what type of activity, it just yeets everything that didn't come from a local user or reference a local object, I should say

pernfugee

@pernia@ryona.agency remote

@pwm @phnt so its not gonna preserve posts liked by local users

Pilgrim

@pwm@darkdork.dev remote

@pernia @phnt it should because those activities are local.
Read the fucking sql jackass

Pilgrim

@pwm@darkdork.dev remote

@pernia @phnt
It starts with activities from other instances (`where remote`)
THEN from only those activities yeets the ones that refer to remote objects as determined by the domain portion of the uri

Replying to @pwm@darkdork.dev

Merc Jr. Jr.

@Merc@fanimations.club remote

@pwm @phnt @pernia >it starts with
One thing, I don't know why
It doesn't even matter how hard you try
Keep that in mind, I designed this rhyme to explain in due time
All I know, time is a valuable thing, watch it fly by as the pendulum swings
Watch it count down to the end of the day, the clock ticks life away
It's so unreal, didn't look out below, watch the time go right out the window
Tryna hold on, d-didn't even know, I wasted it all just to watch you go
I kept everything inside, and even though I tried, it all fell apart
What it meant to me will eventually be a memory of a time when
I tried so hard, and got so far
But in the end, it doesn't even matter
I had to fall to lose it all
But in the end, it doesn't even matter
One thing, I don't know why
It doesn't even matter how hard you try
Keep that in mind, I designed this rhyme to remind myself how I tried so hard
In spite of the way you were mockin' me, actin' like I was part of your property
Rememberin' all the times you fought with me
I'm surprised it got so far
Things aren't the way they were before
You wouldn't even recognize me anymore
Not that you knew me back then, but it all comes back to me in the end
You kept everything inside, and even though I tried, it all fell apart
What it meant to me will eventually be a memory of a time when
I tried so hard, and got so far
But in the end, it doesn't even matter
I had to fall to lose it all
But in the end, it doesn't even matter
I've put my trust in you
Pushed as far as I can go
For all this, there's only one thing you should know
I've put my trust in you
Pushed as far as I can go
For all this, there's only one thing you should know
I tried so hard and got so far
But in the end, it doesn't even matter
I had to fall to lose it all
But in the end, it doesn't even matter

Replies

No replies yet.