It’s not the tech here. Postgres can scale both vertically and horizontally (yes there are others that can scale easier or in different factors of CAP).

The problem is how the data is being stored and accessed. Lemmy is doing some really inefficient data access and it’s causing bottlenecks under load.

Lemmy (unfortunately) just wasn’t ready for this level of primetime yet… It has a number of issues that are going to be quite tricky to fix now that it’s seen such wide adoption (database migrations are tricky on their own, doing so on a production site even harder, doing so on 8k+ independent production sites… Sounds like a nightmare)

What’s really the difference between a federated Lemmy instance for hosting vs a 3rd party anyways?

If another Lemmy instance goes down, all the content on it is gone anyways. Federated != Mirrored. Just because you can browse the content doesn’t mean it’s safe from going away at the whims of one person