@graf @splitshockvirus @dcc

> on a slow instance with little/no users (<100 active) HDD speed (90-100MB/s r/w) is fine.

It depends more on how many remote accounts have local followers and how many local accounts have remote followers than on the number of local accounts, but since small instances can run fine on shit-tier VMs with less than a gig of RAM, I'd say this is correct.

> the problem is the design of the pleroma database is just so bad. @p will agree with me and can explain it in maybe a more elegant way

Well, I wouldn't agree that it's bad, but there are some tradeoffs that were made for the sake of correctness/flexibility and efficiency was on the other end. For example, the indexes are bloated because of the URLs being used as keys, the URLs are used as keys because the raw AP JSON blobs are treated as canonical.

This gives a lot of advantages: the FE can just deal with AP objects, the backend just has to ship AP objects around between servers and occasionally send them to the FE. There are no transformation problems to debug because there are no transformations, and debugging is easier in general because what you keep in the DB matches what you put on the wire, learning how the protocol works and learning how the DB is structured are nearly the same thing.

The downside is that, you know, instead of looking up a 64-bit int in the DB, you're looking up 'https://poa.st/objects/78a0e635-cc57-4d91-a550-715790ce7acc', and there's non-zero overhead for that, especially in terms of on-disk representations for indexes, and that eventually piles up, you do more seeks because the data is bigger, etc. Indexes are taken off the JSON blobs directly (well, JSONB, but it's still got non-zero overhead) so there's a lot of packing/unpacking of fields, and because the data is kept as-is, there are a lot of queries that use COALESCE() and CASE/WHEN that you wouldn't need otherwise. But that is one of the reasons Pleroma has such great compatibility, even forward-compatibility in a lot of cases: when EmojiReact happened, instances that didn't support it only needed frontend changes to support the new Activity type and since the backend is relatively agnostic¹, adding frontend support magically made EmojiReact activities from the past appear. Mastodon can't do that sort of thing (though it doesn't really have to, because it's the majority of the network and decides what everyone *else* has to support).


¹ Ironically, given the software was named for a Gnostic concept.