Home | Notifications | New Note | Local | Federated | Search | Logout

Note Detail

Reply to @pwm@darkdork.dev
pernfugee@pernia@ryona.agency (2026-09-01 10:46:25)
@pwm @phnt fuuuuuuuuuuuuckkkkk youuuuuuuuuuuuuuuuuuu.

anyways thanks. it doesn't solve the mystery as to why mitra with the same data is so much smaller (i have a feeling that normalizing AP objects instead of just shoving jsonb in the db helps a lot.)
---Reply---
Phantasm@phnt@fluffytail.org (2026-09-01 14:34:03)
@pernia @pwm guess what Mitra doesn't do, normalize objects except a few keys. You haven't even looked at the schema, have you. Only Mastodon normalizes AP and that's also why it is so inflexible.

---Replies---

pernfugee@pernia@ryona.agency (2026-09-01 16:52:12)
@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