Login
You're viewing the techdon.dev public feed.

Replies

  • 💬 1🔄 0⭐ 0
  • 💬 0🔄 0⭐ 0
  • 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
    💬 1🔄 0⭐ 0
  • 💬 0🔄 0⭐ 0
  • Aug 20, 2026, 9:42 AM
    @pernia @phnt decayable was about 130 and I knocked it down to 20 between manual removal and pleroma's built in tools
    💬 0🔄 0⭐ 0
  • 💬 0🔄 0⭐ 0
  • Aug 20, 2026, 9:46 AM
    @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.
    💬 0🔄 0⭐ 0
  • Aug 20, 2026, 9:48 AM
    @pernia @phnt I can post the shell script.
    It's a lot broader than just announce's, it's blasts reactions, likes, etc.
    💬 0🔄 0⭐ 0
  • Aug 20, 2026, 9:49 AM
    @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.
    💬 0🔄 0⭐ 0
  • Aug 20, 2026, 9:49 AM
    @phnt @pernia I did the manual thing first because the mix task takes three days on my hardware with a 130 gigabyte database
    💬 0🔄 0⭐ 0
  • Aug 20, 2026, 10:02 AM

    @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).

    💬 0🔄 0⭐ 0
  • 💬 0🔄 0⭐ 0
  • Aug 22, 2026, 6:41 AM
    @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.
    💬 0🔄 0⭐ 0
  • Aug 22, 2026, 6:48 AM
    @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.
    💬 0🔄 0⭐ 0
  • Aug 22, 2026, 6:54 AM
    @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.
    💬 0🔄 0⭐ 0
  • 💬 0🔄 0⭐ 0
  • 💬 0🔄 0⭐ 0
  • Aug 25, 2026, 5:14 AM
    @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
    💬 0🔄 0⭐ 0
  • Aug 25, 2026, 5:17 AM
    @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.
    💬 0🔄 0⭐ 0
  • Aug 25, 2026, 5:22 AM
    @pwm @phnt its insane that likes and boosts would take more than 100gb though. unless ur script removes more than just that.
    💬 0🔄 0⭐ 0
  • Aug 25, 2026, 5:27 AM
    @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
    💬 0🔄 0⭐ 0
  • 💬 0🔄 0⭐ 0
  • 💬 1🔄 0⭐ 0
  • 💬 1🔄 0⭐ 0
  • 💬 0🔄 0⭐ 0