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.
You can also use the whole package if you need a firebase alternative.
Or an easy HA setup...
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.
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!
"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.
Fundamentally, I'm trying to optimize for something like "developer happiness as we build out lots of new features quickly". My dream workflow would look something like: take the Typescript types we've defined on the client, and shove that somewhere in the cloud; then later query to pull out exactly the data we need to render any particular view of our site (ala GraphQL)
AND I'd really like to not have to spend a lot of up-front time knowing exactly which indices to set up, or to figure out complicated migrations later. And I'd like to not think about hosting/managing replications, etc. Maybe that's too many asks, and I'm being too greedy! I'm just hoping that someone's solved this pain point already, and I just haven't heard about it.
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 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.
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!
> 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.
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.
1) There are three insiders on this decision which would be the three cofounders of this site (including me and the OP). We wouldn't insider trade because we genuinely want to know the answer to this question. The market is also tied to what we actually do (and we're not going to lie!).
2) This is both for you to predict what we will do, and to convince us. If you propose a DB and make a case for it, you can gain in expectation because maybe there really is like a 7% chance we'd pick it. So buying up shares from 0-7% is a win for you.
Alternatively, if you think that one DB choice is much better than the others, and we would be somewhat likely to figure that out, then you might gain by buying shares in that answer.
Basically, I think the incentives are good! We're using a more rigorous mechanism — prediction markets! — to do Q&A better than Stack Overflow.
2) Right, so, the way this can go wrong in theory (not saying it will happen in practice) is if a large group of users, or a small group with a lot of points, decide to all get together and focus on one alternative regardless of how good it is. They predict that alternative really strongly and vote against / predict against all the others.
Then, again in theory, you (the site operators) look at the votes and say 'wow, this database must be much better than all the others, we'd better use it.' Then the colluders all get their predictions proven true, so it's a self-fulfilling prophecy.
Here's one research study that looked at this kind of issue, although they end up not finding a lot of manipulation: https://www.researchgate.net/publication/315529106_Manipulat...
Prediction markets are supposed to have insiders buying in. Nothing wrong with that, but they are not supposed to be self fulfilling prophecies. Right? And certainly the prediction market itself should not be the insiders.
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.
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.
Depending on usage patterns the former can be cheaper for apps that don't need reserved capacity and need sporadic/occasional resource access or have unpredictable spikes with otherwise low usage.
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.
