We have a single big bare-metal machine. We run Postgres with a ~1TB DB, moderate load, on a Hetzner AX101 (16C/128GB ram). It has 2* 3.84TB nvme drives (zfs mirror with hourly snapshots) used for postgres storage only, and a seperate pair of mirrored sata drives for system/boot (had to request the extra drive, ask support to change boot option in BIOS, and reinstall OS using rescue system). It's about 100 EUR/mo with unlimited data. We bounce all incoming requests from clients (the machine also runs a node backend) through a digital ocean machine (NGINX proxy), as their peering agreements are better, without this some users in Brazil, Turkey etc. have very slow access. OVH I think would be even better for this use (better? peering and IIRC cheaper data). ZFS snapshots are backed up with sanoid to a machine under my desk with spinning disks. AX101 can be fitted with up six 3.84TB drives, that's almost 12TB of mirrored storage, we should be good for a while. You can (should) use at least lz4 compression on zfs.. can consider zstd-1, bit slower, that could double the effective space. The compression also applies to the in-ram ZFS cache, that can be 100GB+.

We used firestore before.. got a bit tired of some of the limitations (latency, indexing). Cost-wise I don't think it's that different actually, but we aren't using much bandwidth, then self-hosted can be dramatically cheaper. Have to manage some details of course (zfs filesystem parameters, set up backups, config postgres etc.), but I found that stuff quite interesting and it's knowledge that will always be useful.