Selfhosted

Well, "ish".

My experience with databases in general (granted, more the big ones than stuff like Postgres and mySQL) is that a lot if not most of the stuff that's important for performance is held in memory (certainly they'll tend to keep the most frequently fetched stuff in memory, along with the most used indexes) so I suspect the bigger Pi devices (with 4GB and 8GB) might just have enough memory to handle a good number of people doing common usage stuff (say, checking All in Active mode).

With a really big database and usage profile which has a random uniform distribution (i.e. any data piece is just as likely to need to be fetched as any other) then for the DB to be I/O bound in a Pi makes sense, but it's my impression (or maybe its just me ;)) that Lemmy data access is very concentrated in a just a few things (which do change over time but the DB engine wll naturally adjust the memory cache contents for that kind of change)

From the little that I know about the structure of the Lemmy software, I expect it's the image server that'll have problems with slow I/O rather than the database.

Of course, all this is just conjecture, as while I worked in high performance computing, it wasn't exactly done with Raspberry Pi devices ;)