Lemmy’s PostgreSQL was developed with this philosophy, intentional or otherwise:

  1. Real-time client connection and notification via websocket that gets every single action that passes through PostgreSQL. One upvote, instantly sent to client. One new comment, instantly appeared on Lemmy-ui with version 0.17.4

  2. INSERT overhead for PostgreSQL. As soon as a Lemmy post or comment is done, a parallel post_aggregate and comment_aggregate row is created.

  3. INSERT counting overhead. Extreme effort is made by Lemmy to count things, all the time. Every new INSERT of a comment or post does a real-time update of the total server count. This is done via a SQL UPDATE and not by just issuing a COUNT(*) on the rows when the data is requested.

  4. No archiving or secondary storage concept. PostgreSQL has it in the main tables or nothing.

  5. Raw numbers, local and unique for each instance, for comment and post. But community name and username tend to be more known than raw numbers.

  6. Sorting choices presented on many things: communities, posts, comments. And new methods of sorting and slicing the data keep being added in 2023.

  7. No caching of data. The developers of lemmy have gone to extremes to avoid caching on either lemmy-ui or within the Rust code of lemmy_server. Lemmy philosophy favors putting constant connection to a single PostgreSQL.

  8. User preferences and customization are offloaded to PostgreSQL do do the heavy lifting. PostgreSQL has to look at the activity of each user to know if they have saved a post, previously read a post, upvoted that post, or even blocked the person who created the post.

  9. Language choice is built into the system early, but I see no evidence it has proven to be useful. But it carries a high overhead in how many PostgreSQL database rows each site carries - and is used in filtering More often than not, I’ve found end-users confused why they can’t find content when they accidentally turned off choices in lemmy-ui

  10. All fields on SELECT. Throughout the Rust Diesel ORM code, it’s every field in every table being touched.

  11. SELECT statements are almost always ORM machine generated. TRIGGER FUNCTION logic is hand-written.

  • RoundSparrow @ BT@bulletintree.comOPM
    1·
    1 year ago

    The general problem of scalability that lemmy_server is facing:

    comment and post are endless tables, and over 500,000 posts in a single community and join logic can go into nonsense states where it does not age out old content.

    Almost all the sorting orders can basically be considered filtering choices too. But the SQL doesn’t treat it that way, and tries to use LIMIT and paging as a filter without really doing so.

    Aren’t almost ALL of the ‘useful’ sorts that a person using Lemmy would want… “recent”. how often do you really need to sort by “Old”?

    Problem post sorts, Aged

    1. Old
    2. Most Comments
    3. Top All Time
    4. Top Year

    Aren’t all others based on recency in one form or another?