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

    With all the INSERT overhead, I feel like the other-end of SELECT isn’t leveraging it.

    comment to comment_aggregates has a pure one to one relationship… it may as well be just more columns on the original comment table.

    same with post and post_aggregates and community + community_aggregates

    It leads to concerns if a SQL SELECT is joining community_id of a post to the aggregate or the real, given how desired attributes may be mixed and duplicated on both.