[stickfigure]

I'm a longtime user of Google App Engine / Cloud Datastore / Firestore and wrote a Java ORM for that environment that has achieved some popularity. I really like the datastore for some applications, but there's a pretty good chance that it's a bad fit for you. While I'm only casually familiar with your problem domain, it seems at first glance like the kind of thing that would scale reasonably well in a traditional RDBMS. You could would get a lot of value from joins and aggregations, and you don't really have zillions of elements changing all at once.

I could probably give you some advice on how to use the datastore better (most of which would be along the lines of "don't denormalize, store foreign keys and use batch key fetches instead") but it might just be the wrong tool for the job. If you want to talk about it, contact info is on my profile.

[akrolsmir]

Yeah - we do store foreign keys, but Firestore only supports fetching a batch of 10 keys at a time afaict. It might just be the wrong tool, like you said.

We do very much want real-time updating, but there are okay integrations for that with RBDMS's now (eg Supabase). Primarily, I'm curious about some of the newer/more modern DBs, and whether anyone has had good or bad experiences with them!

[burggraf]

Supabase developer here. I came from Firestore to Supabase due to running into a lot of limitations you're seeing. Just my biased opinion, but looking at "newer/more modern DBs" is not necessarily the route you want to take. That's why I looked at Firestore and ended up at Supabase. PostgreSQL is not "newer/more modern" but it's time-tested, battle-tested, and I know thousands of companies have used it in production for decades. I prefer to go with something I know works, will work at scale, and has tons of community and commercial support. FWIW