184
submitted 2 weeks ago* (last edited 2 weeks ago) by InEnduringGrowStrong@sh.itjust.works to c/main@sh.itjust.works

Hi,
Many of you have run into this issue lately where the second page of your feed loads so slow that it can even timeout and not load at all.

If anyone can test again to confirm my last fix actually works for everyone and not just me.

I'll explain my findings in this post once fixed.

Update

The first red herring

I was initially chasing an issue in the UI, the symptoms looked like something we ran into in the past related to dead community icons timing out sequentially and making the page take forever to load. This one was probably fixed in lemmy-ui a while ago.

The second red herring

I noticed this morning that my "mod/new" view was taking 30s to load page 2.
I spent some time messing around, but that's another issue (that is still unfixed), possibly related with how that specific query is implemented.

Anyway

The actual problem

It turns out this specific issue was completely different, this time it was a database issue.

In the default postgres settings, autovacuum's analyze trigger is 50 + 0.1 × reltuples, so a table needs ~10% of its rows inserted, updated, or deleted before it re-analyzes. On a multi-million-row table that's an enormous absolute number, and because the threshold scales with table size, the wait gets longer as the table grows. Append-heavy tables like post_read and comment_like basically sit at 2–4% of threshold forever. That degrades slowly and maybe eventually self-corrects, maybe.

What made it an actual issue... I'm not 100% sure... probably it was a stats reset (pg_upgrade doesn't carry activity counters).
The long-ass countdown restarted from scratch, while pg_stats still held pre-upgrade values.

Basically, the psql query planner was thrown off by estimates from older table versions.

Why some people's feed and not everyone's?
Good damned question.
I think the planner was suboptimal for everyone and everything, but that some specific sorts and filters multiplied it to a catastrophic level for some users' feeds.
Active/Hot are likely more expensive than New, people with specific language filters, hide read posts, etc.
I'm not exactly sure.

The temporary fix...

In the process of pulling my hair out, I eventually ran these psql queries:

ANALYZE post;
ANALYZE person;
ANALYZE local_user_language;
ANALYZE community_block;
ANALYZE instance_block;
ANALYZE person_block;
ANALYZE post_hide;
ANALYZE post_read;
ANALYZE community_follower;

1-2 seconds each, bingo, symptoms went away immediately.

The longer term fixes

Adjust autovacuum thresholds and/or just periodically analyze such tables.
Go live in the woods.

all 41 comments
sorted by: hot top new old
[-] Marafon@sh.itjust.works 34 points 2 weeks ago

HEYO! I can finally browse more than one page of All for the first time in a long while. Much appreciated!

Glad to hear, as it was essentially unusable before.
I've updated the post with more info.

[-] SeventySeven@sh.itjust.works 22 points 2 weeks ago

Lets goooooooo thank you so much!

[-] TacoButtPlug@sh.itjust.works 18 points 2 weeks ago

Why some people’s feed and not everyone’s? Good damned question.

It's because some of us are prettier. 💅

[-] TacoButtPlug@sh.itjust.works 12 points 2 weeks ago

No, but honestly it ran fine on my laptop browser but not my phone app. The phone app dynamically loads so I think at about page two was where it was running into the load error you described. My desktop lemmy is paged instead of dynamic so it was no issue there.

[-] AwesomeLowlander@sh.itjust.works 6 points 2 weeks ago

Interesting, I've been having the opposite issue where it runs on my app but not my browser.

[-] TacoButtPlug@sh.itjust.works 4 points 2 weeks ago
[-] AwesomeLowlander@sh.itjust.works 5 points 2 weeks ago

I guess I'm just prettier when I'm on my phone? ✨ ✨ ✨

[-] jumping_redditor@sh.itjust.works 5 points 2 weeks ago

I was having similar issues, but only when on the all feed

[-] westingham@sh.itjust.works 12 points 2 weeks ago

Confirmed, page 2+ now loading. Thanks!

[-] CaptDust@sh.itjust.works 11 points 2 weeks ago

Scrolling all feed is restored for me using Voyager, and it feels like the paging is loading much quicker too. Tremendous work, thanks bud!

[-] bluesheep@sh.itjust.works 11 points 2 weeks ago

Go live in the woods

Take me with you please

(Thanks for the fix tho!)

[-] HerbalGamer@sh.itjust.works 11 points 2 weeks ago

Was wondering what that was.. Works on my end!

Just dropping in to say that I don't understand most of that but it is working now so thank you <3

[-] pelespirit@sh.itjust.works 7 points 2 weeks ago

Ty, it's been driving me crazy. I had to open a new profile without signing in to see the second page. I appreciate the hard work.

[-] TerraRoot@sh.itjust.works 7 points 2 weeks ago

Thank you so much!

[-] queeradjacent@sh.itjust.works 6 points 2 weeks ago

Damn I just figured it was my janky extensions, thanks that worked.

[-] cantstopthesignal@sh.itjust.works 6 points 2 weeks ago

Why use a percentage basis instead of a fixed length or percentage + fixed length cut off?

Good question. I don't design these things.
I'm not even an expert on databases.
The way I understand it is that the 50 is a floor.
Makes sense, you probably don't want to vacuum too often, which might compete with other locked slots and have overall adverse effects.
The work done while vacuuming is proportional to the table size, so vacuuming a small table is small work, while vacuuming a bigger table is more work.
I guess that's why it's using a %.

Thing is there's no upper cap.
Or there wasn't until postgres 18+, and even then it's likely way above what we'd run into.
I imagine that it can make sense for many tables and the default might be conservative as to avoid the drawbacks of over vacuuming.

Even an upper cap would only shorten the amount of time before it fixes itself.
In our case, the issue was mostly due to the database upgrade which basically resets the counters towards the next run.

I'll probably just add a daily job that takes care of this more gracefully without being too agressive.
Regardless, this should probably be added to the upgrade process.

[-] JessyKenning@lemmy.world 4 points 2 weeks ago* (last edited 2 weeks ago)

This is awesome.

Now can someone smarter than me explain or forward this to the people who run lemmy.wolrld.

Is this an issue you're having with your lemmy.world account? And if so, on which feed and which sort type?

[-] JessyKenning@lemmy.world 4 points 2 weeks ago

Yes. I thought it was the same problem that was happening here. All for feed type, and it doesn't matter what I pick as the sort type.

I wish I could explain better but I'm not entirely sure how all this works. Like for example, shit just works is a different instance I think. But it is using the same software? so the fix should work? on this page https://sh.itjust.works/c/main I can hit next at the bottom and it loads a second page, but it does not work on lemmyworld.

The problem might very well be the same.
Probably, if you browse lemmy.world without being logged in, it'll work fine, at least that how it was with us.
I'll reach out to them and point them here.

[-] ayyy@sh.itjust.works 4 points 2 weeks ago

I never experienced an issue, but thank you for your hard work!

[-] dartonias@sh.itjust.works 4 points 2 weeks ago

Great to hear. I noticed that looking at a specific community didn't have the issue and that was my workaround. Glad it's resolved.

[-] szprot@sh.itjust.works 3 points 2 weeks ago

looks good, thank you!

[-] fake@sh.itjust.works 3 points 2 weeks ago
[-] _haha_oh_wow_@sh.itjust.works 3 points 2 weeks ago
[-] Underwaterbob@sh.itjust.works 2 points 2 weeks ago

Working here. Thanks so much!

[-] toggy_groove@sh.itjust.works 2 points 2 weeks ago

I'm on jerboa 0.0.88_fdroid version and it's working. Thank you so much :)

[-] Gaspard@sh.itjust.works 2 points 2 weeks ago

Thanks for fixing the bug. However, I found it genuinely useful for the page to not load in full... to stop doom scrolling!

Do you think pagination can be introduced? As an option or setting? Or is this something to address to jerboa that I'm using?

The web UI has pagination by default.
Not sure about Jerboa, I haven't used it in a while

[-] clay_pidgin@sh.itjust.works 2 points 2 weeks ago

Thank you very much! Is this a fix that should be upstreamed?

[-] WhyJiffie@sh.itjust.works 2 points 2 weeks ago

the fix is for the database software, lemmy is not supposed (or even privileged?) to do this itself. but, this could go to some kind of an operator recommendations manual upstream, if they have one

[-] clay_pidgin@sh.itjust.works 1 points 2 weeks ago

Makes sense, thank you!

[-] choss@sh.itjust.works 2 points 2 weeks ago

I just tested and it works great! 😃

I had tried it while logged out and didn't have the problem, so I thought it was a problem specific to my account and accepted having a limit. Thank you so much for everything you do!

I'm honestly not sure, but I just did the thing again just in case it helps you out.
Let me know.

this post was submitted on 31 Oct 2026
184 points (100.0% liked)

sh.itjust.works Main Community

8545 readers
1 users here now

Home of the sh.itjust.works instance.

Matrix

founded 3 years ago
MODERATORS