Egregoros

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

@phnt @pwm

actually you're right. i think i found out what the actual difference is.

it seems mitra has a post table, where it stores fully normalized activities

CREATE TABLE post (
    id UUID PRIMARY KEY,
    author_id UUID NOT NULL REFERENCES actor_profile (id) ON DELETE CASCADE,
    title TEXT,
    content TEXT NOT NULL,
    content_source TEXT,
    language CHAR(3),
    conversation_id UUID, -- FK is added later
    in_reply_to_id UUID REFERENCES post (id) ON DELETE CASCADE,
    repost_of_id UUID REFERENCES post (id) ON DELETE CASCADE,
    repost_has_deprecated_ap_id BOOLEAN NOT NULL DEFAULT FALSE,
    group_id UUID REFERENCES actor_profile (id) ON DELETE CASCADE,
    visibility SMALLINT NOT NULL,
    is_sensitive BOOLEAN NOT NULL,
    is_pinned BOOLEAN NOT NULL DEFAULT FALSE,
    reply_count INTEGER NOT NULL CHECK (reply_count >= 0) DEFAULT 0,
    reaction_count INTEGER NOT NULL CHECK (reaction_count >= 0) DEFAULT 0,
    repost_count INTEGER NOT NULL CHECK (repost_count >= 0) DEFAULT 0,
    url VARCHAR(2000),
    object_id VARCHAR(2000) UNIQUE,
    ipfs_cid VARCHAR(200),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE,
    UNIQUE (author_id, repost_of_id),
    CHECK ((conversation_id IS NULL) != (repost_of_id IS NULL))
);

see how there's no fuckass blob of jsonb in there? how post content is text and ID's are UUID's and urls are urls?

it ALSO however does keep the jsonb blobs in a separate table called activitypub_object

CREATE TABLE activitypub_object (
    object_id VARCHAR(2000) PRIMARY KEY,
    object_data JSONB NOT NULL,
    profile_id UUID UNIQUE REFERENCES actor_profile (id) ON DELETE CASCADE,
    post_id UUID UNIQUE REFERENCES post (id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

so like wtf? why is pleroma so goddam fat?

look at how mitra stores "Likes":

reaction_count INTEGER NOT NULL CHECK (reaction_count >= 0) DEFAULT 0,

its just an integer. straight up. and its not even a "like", its a "reaction", so likes are really just emoji reactions, and you can see that in the mitra web interface, because when you click like, it shows a thumbs up emoji on the post.

there's another table for emoji_reactions in mitra that stores this. so you save even more data like that.

now, how does pleroma stores "likes"? run this query:

SELECT
    id,
    data->>'type' AS activity_type,
    jsonb_pretty(data) AS full_json
FROM
    activities
WHERE
    data->>'type' = 'Like'
LIMIT 5;

ITS A FULL FUCKASS PIECE OF JSON. A WHOLE FUCKING LOG OF SHIT. FOR A LIKE.

like, sure. i get to see who the like came from. it says who it was for. i get a link to the object as well. pleroma has far greater richness of data.

BUT IT DOES IT FOR EVERY LIKE. THATS LIKE HALF A KILOBYTE OF JSON PER LIKE.

MITRA DOESN'T WASTE EVEN A BYTE (or however much space an integer takes) on EVERY like in a post.

now count in reposts. fuck. no wonder mitra takes 100 times less space for the same data.

that also explains why nuking all those likes and boosts freed up like 80% of my db space. pleroma just loves storing worthless crap, and mitra is far more pragmatic about it.

i'm not well versed in AP, perhaps this is how the spec authors intended to store likes. in half a kilobyte of json. but well, i think this is a proper explanation as to why pleroma is so damn fat all the time and mitra is so hot and skinny.

paging @silverpill @lain @mint for validation and or to call me a dumbass nigger. maybe this has been obvious for a long time and phnt just didn't have the heart to tell me.

@pernia @mischievoustomato @pwm @phnt @kirby @p @lain @meso @graf You're right, mitra stores most data in a normalized form. Reactions are not just a number though, there is a separate table for them: https://codeberg.org/silverpill/mitra/src/commit/c2d3697c3fa0bf1ae41575504094ee340ce16c12/mitra_models/migrations/schema.sql#L256-L268

We also store some raw activities and objects, but they are pruned aggressively and don't take much space.

This may explain the difference in database sizes, I don't know enough about pleroma to say for sure.