Egregoros

@phnt @f0x yes, for the vacuum thing it was warranted (lol).

perhaps picrel, when you completely blew me off, in the cute and smug little way that is common in microblogging platforms, i took it as a jab.

and then when i had to make a big ass post detailing that yes, mitra's post table *is* normalized, and that yes, i *did* look at it (you fucking dick), it was a little frustrating.

and it didn't help that *you* were then wrong and pete had to correct you (lmao)

it fucking sucks cuz i don't pretend i'm not retarded. i'm pretty transparent. you shit on me for making a guess and you don't even have the answer.

and that's why i unfollowed you.
image.png

@phnt @f0x then i misread, i thought the index "activities_actor__data______type___text_id_DESC_NULLS_LAST_inde" btree (actor, (data ->> 'type'::text), id DESC NULLS LAST)

indexed the entire data field, not just data->>'type'

which again, instead of strawmanning and calling me stupid, you could've given me the response you just did now. where was this before?

and it begs the question: how come mitra doesn't have indexes half the size of the rest of the DB, and it runs just as fast?

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