Egregoros

@pernia @phnt conceptually, pleroma keeps "likes" and "posts" in "activities" and "objects" respectively
This query gets rid of likes and things like it such as announces and reactions because something that happens in prod is that those effectively get archived forever unless you prune and I don't care about them on my instance. When the bloat gets to a certain point I find it useful to batch deletes of these.
That's really it, there's nothing crazy like searching for certain criteria, it's just batched deletion of the lowest hanging fruit, of which there's a lot

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

@silverpill @mischievoustomato @pwm @kirby @p @lain @meso @graf @pernia Pleroma basically took the completely opposite extreme to what Mastodon did (normalizing everything into a very specific schema). The Pleroma schema is very simple and mostly stores raw AP Activities/Objects which causes the size difference per-post/reaction. What Mitra stores in a separate table (post content, visibility,...), Pleroma stores in a single jsonb column. reaction count, repeat count, like count, tags are all stored in the same jsonb blob along the original AP Object representation. There are some advantages to that, like not needing a join across different tables. jsonb is also slightly larger because it is a pre-parsed json representation.

The split activities/objects table schema also introduced more DB size as there are more indexes needed for it to work. It's too late to deeply optimize and normalize the schema now, maybe with the eventual Pleroma 3.0 some day in the likely distant future and an hours/days long data migration.

@lain @phnt @graf @kirby @meso @mischievoustomato @pernia @pwm @silverpill When I set up the DB last time, btrfs was worst in the benchmarks and actively fried some disks; ext4 was top slot for pgbench on NVMe, it had better performance than f2fs. I do not know if things have changed but I think a lot of it comes more from using big strings in the indexes rather than just the JSON blobs themselves, so it's a 200-byte URL rather than an 8-byte integer, and you can't get around storing it (no matter how clever about hashing, the data has to go *somewhere*), and this factors into how many pages Postgres has to scan when it reads the index, and that gets compounded by the inevitable gaps that form.

@lain@lain.com @phnt@fluffytail.org @mischievoustomato@0.5dollah.click @pwm@darkdork.dev @kirby@freerobuxextremist.com @p@fsebugoutzone.org @silverpill@mitra.social @meso@new.asbestos.cafe @graf@poa.st @pernia@ryona.agency

# compsize postgresql/18/
Processed 1571 files, 936133 regular extents (1137990 refs), 13 inline.
Type Perc Disk Usage Uncompressed Referenced
TOTAL 81% 36G 45G 39G
none 100% 34G 34G 30G
zstd 20% 2.2G 10G 9.6G
prealloc 100% 2.0M 2.0M 1.1M
# compsize postgresql/14
Processed 1876 files, 1156080 regular extents (1364864 refs), 14 inline.
Type Perc Disk Usage Uncompressed Referenced
TOTAL 89% 56G 62G 52G
none 100% 51G 51G 42G
lzo 74% 2.4G 3.2G 3.2G
zstd 26% 1.9G 7.5G 6.7G

not really that great for double or triple read times.

@pernia @phnt @mischievoustomato @pwm @kirby @lain @silverpill @meso @graf @pernia

> pleroma didn't do any better by just doing the opposite thing.

If you learn how to write a program some day, then Pleroma will make sense to you.

Pleroma was able to ship features on the frontend by just having the frontend and backend pass AP objects back and forth, so activities that arrived before there was support for them just got a frontend patch and suddenly they worked. This is what allowed Pleroma, with a smaller and more talented team, to cope with Mastodon just throwing shit at the wall. Mastodon implements something retarded and Pleroma doesn't break and can often get retroactive support for activities/objects and in way less time than it took Mastodon to either ignore or kowtow to the people that felt unsafe.

I don't think anyone is going to claim that Pleroma did everything right, but the design was very thoughtful and it evidently came from people working under real constraints that thought hard about the network and cared about the network.

@pernia @mischievoustomato @pwm @kirby @p @lain @silverpill @meso @graf @pernia The whole point of the schema is that it is extremely flexible. You don't have to write large migrations to support features, deal with split normalized/non-normalized metadata schema that can change depending on when you support new features (pulling out previously unnormalized metadata into separate tables/columns resulting in expensive data migrations). You can even store data you don't yet understand, implement support for it later and that data will be usable from earlier.

But of course hindsight is 20/20 and the size of current instances and load was probably nowhere near what lain expected when that schema was decided. Honestly I wouldn't really change much of it, except the split Activities/Objects tables.

@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