Unless you need realtime PostgreSQL is always the most obvious solution.

If you do need realtime you can build it on PostgreSQL yourself depending on your requirements either using LISTEN/NOTIFY or logical replication. There are tradeoffs to both if tbh if you are asking this question you probably don't want to go that path.

Non-realtime it's very easy to handle nested JSON in PostgreSQL but I would still avoid it like the plague unless it's user-supplied data without any real schema.

You might feel like schema-less lets you "move faster" but it's a load of horseshit that really starts to stink much sooner than you might think.

Schemas and by extension database integrity make it easier to move faster because migrations allow you to ensure there are no edge conditions related to stored data when upgrading your code to use an extended or otherwise modified data model.

The other main benefit of PostgreSQL is just the sheer body of resources available, with the exception of the other main RDBMS (MySQL/MSSQL) it just completely dwarfs what is available for other data stores. You will rarely if ever encounter a problem someone hasn't already solved.

> - Related: Firebase cloud functions have extremely slow cold-start times (>5 seconds is common), and deploying new versions can take minutes.

This somehow has gotten a lot worse. When I first started with Firebase Cloud Functions, which to be clear are amazing and simple to get up and running with compared to anything competitors offer, it was a few seconds to deploy. Sadly it has gotten worse and worse as time goes on.

Still though, the paradigm around cloud functions is so simple compared to the nightmare that is AWS Lambdas. When trying to explain why cloud functions are better, AWS users just stare at me like I am talking in a foreign language about some sort of mythical land of make believe.

More context: The main thing I'm unhappy with is the extra developer burden imposed by needing to denormalize information. E.g: I have a user document in Firestore, with userId, name, and avatarUrl. If I want to be able to fetch a list of comments and have the name & avatarUrl of the creators, in Firestore I have to write those alongside userId. Then, if I later add isVerified to the user document, I need to either backfill my entire db and denormalize again; or client-side handle the missing case.

Then the other pain point is the "joins" use case; right now we do the equivalent of fetching all comments & users, then doing an in-memory join. Ideally, we could craft a single request that just says "get the 10 latest comments on this market, plus the associated avatars" without data duplication and without doing a bunch of up-front thinking about exactly how to structure indexes.

My hesitation with relational DBs comes from the mismatch between client data model (loosely, JSON objects of pointers) and how it's represented in the DB (in a row); plus the requirements of needing to specify your e.g. indices up front, and annoyance of doing migrations. I'm hopeful someone's found a graph-type solution to work really well for them!

1) Any thoughts on the conflict of interest? Any insider can easily take advantage of a market like this.

2) Is there any element of a decision market here? Are you asking users to just predict, or help you decide? The incentives change a lot in the latter case, since one can get cyclic dependency -- self-fulfilling prophecies.

Firestore has very specific usecases that don't match a lot of things that Postgres is really meant to handle. Namely, as you are learning the hard way, it isn't a relational database. It doesn't make it bad, it just isn't what you thought it was. $1400 a month is nuts, it sounds like something isn't optimized well.

GCP Cloud SQL (Postgres) has been fantastic for me. Easy to connect to, easy to scale and not that expensive.

I have GCP Cloud Functions (golang) in front of that and they spin up, connect to the database and serve the whole request in under 1s. Hot requests are 80ms for submitting some simple data.

If you do it right, you can minimize persistent connections to the SQL backend (offload as much as possible to PubSub messages which a backend function can handle). This will keep your bills down too because you won't need as large of an instance to serve requests.

I wish GCP had 2 things. A dumber key-value-store/mongodb-clone thing. Firebase has too much going on with it's confusing await getCollection().getDocument().getSnapshot().data() thing, the half documented transition to composition API, and I didn't need realtime.

Also a serverless postgres offering. Not even the fancy kind. There are some great free tiers outside of GCP. I have some projects where my usage would, if given true pay-as-you-go pricing, would fairly be between $0.10/month and $25/year. But GCP starts at $9/month for a postgres and a lot of competitors just leap straight to $25/month when leaving the free tier.

The site sounds neat, and using the site to answer this question about itself is amusingly meta. I'm not sure if M$ are 1:1 dollars or even real money, so I wanted a FAQ or something, but there's only a sign up form (on mobile), which I'm certainly not going to do for something that might just be a Hollywood Stock Exchange type of game. This is all probably intentional, but letting you know just in case!

You could use PostgreSQL with your own back-end. Related to this, I'm working on an SDK for Flutter that works via REST and is quite easy to use: https://nexusdev.tools/

Tip: You can avoid saving the avatar url if you use a predictable url like /avatars/:userId instead of something like /avatars/:randomToken

Do you mean Firebase or Firestore?

If you mean Firestore, what about minio? It's incredibly scriptable and awesome.

If you mean Firebase, use rxdb and connect over graphql (hasura is fantastic) to your postgres database. It can be a little work to understand how the models all map into the database, but once you get it, it's magical.

Both are easy to self host. I run my entire stack of all components on dokku, so I get easy logging, backup, and can migrate to a new host in a few standard commands.

I mean, if you don’t want to denormalize your data, you’re going to need to think about indexes in some capacity regardless (this is true for non-relational DBs like Dynamo and Mongo too).

"get the 10 latest comments on this market, plus the associated avatars" couldn’t be better suited to a relational DB. That’s a textbook use case that Postgres would be amazingly well suited to.

Also: remember with Firestone that you’re paying for redundancy and availability that’s entirely Google managed. Most DB offerings you work with on your own are significantly more hands on as far as recovery / backups / replication go.

Engineering time is usually more expensive than server costs when you’re a startup, so think about how much time it’d take to do it yourself before you decide to optimize your server costs over R&D costs.

Why not do something like:

1) Fetch the list of comments

2) Add a listener on the public user info for each comment poster

3) Render the comments immediately. When the user info is available, re-render with the avatar information.

The nice thing about this is that the avatar information will immediately update in real-time as soon as someone updates their avatar. Yes, with a KV-store, you need to do more reads because you can't join data (which implicitly will do a read btw), but it doesn't seem like that big of a deal to me. Immediately reflecting changes to the public user state seems nicer than the convenience of a join.

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.

> developer burden imposed by needing to denormalize information.

> Then the other pain point is the "joins" use case;

We usually do that client side, with the aid of a web-component holding a ref to the (realtime db, not firestore) database path, and rendering its value. The payload is small as you only fetch data you use.

That works pretty well, even with long lists or grids; quotas/price on the realtime db are pretty generous.

What initially motivated you to use Firestore?