but Lemmy really fucked up their database performance somehow.

Can confirm. I spent like 4 hours one day configuring auto vacuum and other shit on my Lemmy DB because it was bloating to hell and eating up resources. Runs much smoother now but it was a massive PITA to get there.