To answer what I think you are getting at lemmy scales based on two things:

1. Database size (and write volume) scales mostly on what communities are being federated to you. Unless you are .world the volume of remote content is going to massively outweigh local content. On my (mostly) single-user instance I have found this to be the same with Pictrs as well, as it is mostly eating storage to store federated thumbnails.
2. Database read load scales mostly on the number of users you have. For a single-user instance this is pretty minimal. For an instance like .world (with thousands of users) I imagine it is significant and scaling postgres to have read-only replicas to scale this load.

~18 hours ago I wrote

My instance has been running for 23 days, and I am pretty much the only active local user:

7.3G    pictrs
5.3G    postgres

I may have a slight Reddit Lemmy problem

As of right now

7.5G    pictrs
5.7G    postgres

So my storage is currently growing at around 1G per day, though pictrs is mostly cached thumbnails so that should mostly level out at some point as the cache expires.

To answer your stated question: I run an instance on a mini PC with 32G of RAM (using <2G including all lemmy things such as pg, pictrs, etc and any OS overhead) and a quad core i5-6500T (CPU load usually around 0.3). You could probably easily run Lemmy on a Pi so long as you use an external drive for storage.