pernfugeepernia@ryona.agency
Aug 20, 2026, 9:31 AM@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
#!/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).