If I'm understanding correctly, Fly Postgres isn't too different from something like Postgres on Heroku? It's a single instance/cluster in a single location.
My understanding of Fly Postgres is they put a lot into the tools to orchestrate, but there is not centralized monitoring and in the event of a failure it is up to you to realize and remediate.
Disclaimer: Was part of the team that built Heroku Postgres, and know the Fly team pretty well but don't personally use Fly Postgres so it's my understanding from the team. We've had a number of customers leverage Crunchy Bridge (build by a lot of the original Heroku Postgres team) use us for the managed Postgres connected to fly.io via Tailscale.
Management is a huge difference of course, but I was mostly asking about the database from the point of view of a user of the database. It doesn't sounds like Fly Postgres is doing anything like running your database globally - you still have single instance of the database.
Apologies if I'm missing some details. I intentionally try to stay out of the technical devops type stuff. I'm the kind of person who just pays Heroku for a Postgres and doesn't think much about it after that.
I'm not sure the full details on supabase as it's more recent.
This is a pretty good breakdown of various database providers and in particular a lot paired with Fly - https://dancroak.com/webstack/