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

Note Detail

Reply to @phnt@fluffytail.org
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
---Reply---
pernfugee@pernia@ryona.agency (2026-09-01 16:54:49)
@phnt @lain @mint @pwm @silverpill cc @meso @mischievoustomato @p @kirby @graf fuck it hellthread :hellthread:

---Replies---

silverpill@silverpill@mitra.social (2026-09-02 04:59:51)
@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.