I had the same question, and wanted a definitive answer (I don't provide that here 😅), especially since some people on hacker news were preferring using generic services which don't lock you into a Cloud provider.

Benefits of PostgresQL

• More people are familiar with PostgresQL than Firestore. It is the standard. Data scientists, infrastructure and normal developers will know how it works. Firestore is a niche offering by Google, and only people in the Firebase ecosystem will know what that is. It may be designed to be simple, but don't underestimate it: a few years ago, apps leaked all their sensitive data or allowed anyone to empty out their entire database (That was firebase 😁). Also, people were inefficiently making queries in Firebase and costing their company thousands of dollars with no real benefit. If using Firestore, you need to make sure you deeply understand its paradigm: e.g. how the queries work, and also the security rules, and most people won't.
• If one day a new serverless database product is created or open sourced, it might be hard to migrate from Firestore to it, than it would be to migrate from Postgres to the same. You're locked into the Firebase ecosystem.
• You don't have to install the firebase SDK into your app, who knows what that library does without watching the network traffic...

Benefits of Firestore

• You only pay for reads, writes, deletes and the actual data size stored. Postgres will need to be hosted somewhere, so that will cost you money immediately, even if someone doesn't use your app. Sure, AWS some serverless database offering called Aurora Serverless, but you pay per hour, so even if nobody uses your app, you're paying!
  • Pricing summary: If nobody uses your app in one month, Firestore is free. AWS Aurora is $60 for a basic machine db.t3.medium. Firestore also has a free tier.
  • In both cases, you can say you pay what you "use", but in Firestore usage is actual benefit to the user, where as usage in AWS would be the machines youre using.
• Integrated with the rest of Firebase
• Firebase makes great videos and integrates with other Google stuff well (Android, Flutter), for example, this fun one and this serious one.

You can compare SQL vs NoSQL somewhere else, but I think the differences (data duplication vs normalised data, performance) are not a big deal compared to the other issues here I mentioned (productivity and cost), especially when you're making a basic apps. More complex applications will want a thorough evaluation of different databases, not just differentiating between NoSQL/ SQL.