Re:Pleroma 2.0: a free, federated social networking server b...
by hau on Monday March 09, 2020 @06:26AM
(#22524273)
Your strong statement about postgres doesn't sound right to me. You can't use an array values as foreign keys. Array column wouldn't be my first choice even it could do this: you're limited and constricted in usage overall for a little gain if any. Which imageboard/booru engines use array column for tags?
Re:Pleroma 2.0: a free, federated social networking server b...
by claudiawerner on Monday March 09, 2020 @08:00AM
(#22524681)
Danbooru (and forks like Moebooru) and Philomena use string arrays in the database for tags.
Edit: nevermind, Philomena has a table for the many-to-many relationship and uses Elasticsearch for the heavy lifting.
Re:Pleroma 2.0: a free, federated social networking server b...
by hau on Monday March 09, 2020 @09:01AM
(#22524995)
Why arrays are preferable over m2m tables? What so special about pg arrays that makes anything but them inferior in "tagging" case? I genuinely don't get it.
Re:Pleroma 2.0: a free, federated social networking server b...
by claudiawerner on Monday March 09, 2020 @09:45AM
(#22525255)
Accodring to these rough benchmarks, it's faster to use arrays rather than joins:
https://shon.github.io/2015/12/21/postgres_array_performance...
https://gist.github.com/joevandyk/031cf5812bd656887623
To search for a post with multiple tags, you need to do a join for each tag. You don't have that problem with arrays.
Chances are, if you have a server capable of running the Elixir + web framework stack, storage space for cached images, and a proxying web server, Postgres probably isn't much of an overhead on top of that.
Bare in mind that each fedi instance dosen't only host the content generated by users, but also whatever content they repeat/reply to. The fact it needs to be stored (or cached) is one reason why there's so much blocklisting/defederation - because people may not want to host certain content from instances known to commonly host it. It may even store more from the public timeline (I'm not sure about the specifics of Pleroma). Nevertheless, even with five users, you can fill up quite a lot of space in a database and on the filesystem.