You're right - I should have clarified that I'm talking about new projects - as in "Why would someone choose Postgres over one of these distributed SQLite providers for a new project?"
(I'm sure those use-cases exist, but I'm not sure if they exist inside the use-cases Supabase supports)
Postgres is simply a much more feature-ful option. Going with database-per-tenant with e.g. Cloudflare D1 (besides the fact that D1 is still in beta, technically, but that's important when we're talking about databases) is a specific architectural decision that has its own tradeoffs (like making analytics much more difficult).
(I'm sure those use-cases exist, but I'm not sure if they exist inside the use-cases Supabase supports)