More posts = more slow, so we're temporarily hiding older posts to make the bear run faster.
Our devs continue to work on getting in a long-term fix for the performance issues we've seen as the site as grown. The performance issues are due to a couple slow database views.
The bigger these views are, the slower things get. We're trying to replace the views altogether, but in the meantime, we get a significant performance boost by filtering down to only posts and comments from the past couple months.
We're leaving this in place as a workaround until the long-term fix is done for a few reasons:
consistency; you don't have to wonder whether or not the site is in "albanian bunker mode" at any given point
the alternative: the site performance problems have been getting worse as the tables get bigger. having hexbear be frequently unavailable altogether seems worse than limiting functionality
the trade-offs. our experience has been that hexbear has been significantly faster the past couple weeks. of course, individual needs will vary, but on the whole, we expect that most users are ok with the tradeoff of hiding older posts for a smooth site experience--particularly given that this is not intended as a long term state of affairs.
If you have any feedback or concerns, you can comment on this post, or email Joseph Biden at joe@whitehouse.gov . If you know Rust or are familiar with the Diesel library, and want to help us get the long-term fix in, please reach out to:
Layla via Matrix or me via Matrix
DMs to our hexbear accounts are fine as well, @CARCOSA @layla
and so what we're going to do instead is something like:
spoiler
select comment.id, comment.creator_id, post_id, parent_id, content, comment.removed, read, comment.published, comment.updated, comment.deleted, comment.ap_id, comment.local, post.name, post.community_id, community.actor_id AS community_actor_id, community.local AS community_local, community.name AS community_name, community.icon AS community_icon, community_settings.hide_from_all as community_hide_from_all, u.banned, COALESCE(community_user_ban.id, 0)::boolean AS banned_from_community, u.actor_id AS creator_actor_id, u.local AS creator_local, u.name AS creator_name, u.preferred_username AS creator_preferred_username, u.published AS creator_published, u.avatar AS creator_avatar, ut.tags AS creator_tags, cut.tags AS creator_community_tags, COALESCE(ccs.score, 0)::bigint AS score, COALESCE(ccs.upvotes, 0)::bigint AS upvotes, COALESCE(ccs.downvotes, 0)::bigint AS downvotes, COALESCE(ccs.hot_rank, 0) AS hot_rank, COALESCE(ccs.hot_rank_active, 0) AS hot_rank_active, c.* FROM comment left join post on comment.post_id = post.id left join community on post.community_id = community.id left join community_settings on community_settings.id = community.id left join user_ u on u.id=comment.creator_id left join community_user_ban on u.id = community_user_ban.user_id AND community_user_ban.community_id = community.id LEFT JOIN user_tag ut ON u.id = ut.user_id LEFT JOIN community_user_tag cut ON u.id = cut.user_id AND community.id = cut.community_id LEFT JOIN community_user_ban cb ON u.id = cb.user_id AND community.id = cb.community_id LEFT JOIN hexbear.comment_stat ccs ON ccs.comment_id = comment.id left join (select user_id,comment_id,score as my_vote from comment_like where user_id = $1) c on c.comment_id = comment.id;
to achieve basically the exact same result.
the reason this wasn't done back in like january is because lemmy is written in rust, which none of us are super familiar with, and uses diesel for queries, which is kind of limited. so figuring out how to translate the query to rust and diesel is what we're working on. I think we're actually getting close. but we've all been really busy, so progress has been small bursts here and there.
Oof that's rough. I have to manage a 100gig oracle database at work and this is giving me flashbacks, I wish I had time to help.
Also I don't get why lemmy devs decided to use a query builder. They just made queries much harder to optimise for absolutely no reason. What's wrong with SQL?
no clue, haha. I guess it's just one of those things where different people have different schools of thought. I'm also very anti Spring for similar reasons.
But this comment will get very long if I get on my soapbox about the trend towards factories and builders and libraries and generics and everything else that stands between you and what your code is actually doing, haha.
Oh dang I've written a lot of Rust and used Diesel in deployed projects before, I'll needa have a look when I got a working dev environment again.