Postgres is a very capable database and it sounds like your application is already designed with relationships in mind. Firestore is not relational and so that's probably why you're having a tough time - your application is just better suited by a relational database and that's ok.

I'd need to know more about your application and it's needs. What was the draw to Firebase? Just that it's a "noSQL" database? Don't let anyone tell you one is better than the other.

Interestingly enough, some people are building an open source project that provides an alternative to Firebase. At least some of its features anyway. It's called supabase. It uses Postgres too. It's a collection of a few modules, one of which is called "realtime" that provides realtime updates via websockets. I'd check that project out because it may provide some of the features you were interested in. Again, I'd need to know more about what your application needs and what you were drawn to with Firebase.

Also bear in mind the convenience of working with Postgres over Firebase. It's a lot more straightforward and you can find many GUI clients or use CLI to make queries and browse data. Yes, there are some for Firestore as well, but not as many. Also, don't forget about query insights if you're using GCP Cloud SQL or if you run your own database instance and install observability tools. Firebase may hide all of this, but you still may one day end up with a performance concern. Even though it's advertised as this infinitely scalable thing. That's not quite true, though it is very scalable, one of the trade offs is limited query features.

Then consider vendor lock-in. If you're really interested in a noSQL database or want a more straightforward one that doesn't include vendor lock-in and limited querying, check out something like MongoDB instead.

Social Harvest Stack

Application and Data

DevOps

Business Tools

More stacks from Tom Maiaroto