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.
Ah, nop. If it goes down, we'll figure it out.. 24hrs outage wouldn't be the end of the world. Would have been nice to have a second (read replica) machine in East Asia, to reduce latency for users there, but didn't find a provider like Hetzner.. maybe could take a server in a suitcase and install it in a collocation center there. Bit of a hassle though.
I have a similar setup as OP, and I use ZFS snapshots to be able to quickly rebuild the whole machine on another host. Of course, it still requires manual intervention in an outage (it could be automated, theoretically), and I may lose up to 5 minutes of data due to my snapshot schedule.
Yes, ZFS snapshots are atomic, so as long as the database use sane semantics to do disk I/O, you're fine (AFAIK, PostgreSQL and MySQL/InnoDB are ok, MySQL/MyISAM is not though).
A snapshot is effectively identical to a killed database server (due to OOM, or power outage), and database servers should be crash-resistant.
With PostgreSQL, checkpointing the database before taking the snapshot may make the crash recovery quicker when loading the snapshotted database.
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.