I am building a website that is a bit similar to indie hackers(users' posts, feed, etc). considering the fact that firebase has many limitations, what could be the best option for the Database?
I'd go with a relational DB like PostgreSQL.
IH is/was built with Firebase and has (often) had performance issues.
Not to say you wouldn't have them in a SQL DB, I do think it's a lot easier to scale (from my experience).
I don't know your tech stack, but MariaDB or even MySQL would be fine, if you're more comfortable with that.
Firestore gets a bit expensive as it scales (relatively). Unless you're super comfortable with it already or don't mind migrating later, I would just go straight to a relational database.
I'd say you want a relational database for a web app like IH. That most likely means Postgres or MySQL. use whatever you're most comfortable with but that only goes so far. If, let's say you're most comfortable with a NoSQL variant, it would make sense to use that but since a community like IH is so relational, it gets harder to justify NoSQL merely on the grounds of it being the tool you're most comfortable with. Sometimes it does make sense to learn a new tool to get the job done instead of using what you're already comfortable with. That's a judgement call you have to make yourself.
I typically use Postgres because I'm a rails developer and rails and Postgres are like 2 peas in a pod (rails also works well with a bunch of other databases out of the box like mysql).
I believe Indie Hackers uses Firebase Auth, Firestore DB, and Algolia for search. Since the data seems fairly relational then something like Postgres would be a good choice as well. I'd personally go with what I know the best, which is Firestore.
I've built web apps like this with both firebase and postgres and I'd very much recommend using postgres over firebase. To be honest, any time I've used firestore or the older realtime dB for the backend I lived to regret it. Even if you know firebase better I'd take the time to learn postgres (or another relational dB). Also, it might be worth looking into something like Hasura for creating a graphQL API for postgres. Now that stuff like this is getting traction you can get some powerful features, like subscriptions (which is one of the big reasons I reached for firebase in the past), out of the box. I've been pretty impressed with this so far. Frontend frameworks are easier to change if your product gets real traction, but the backend is not easy to change! I do really like firebase for authentication, but that's all I ever use it for now.
Firebase takes care of a lot more for you than just the database, and speeds up development massively.
Honestly I would use firebase, a similar PAAS or a even a headless CMS if I wanted to build something like indiehackers myself.
Get it off the ground ASAP and worry about scaling limitation if/when you need to. 👍
Users, posts, feeds - it's structured data, you don't want end up having the same record with different fields (which can be the case with a document database like Firestore unless you write lots of custom schema enforcement logic and/or avoid any bugs in CRUD related code).
If you host your app in GCP, using Google Cloud SQL for PostgreSQL would be a good choice. It will enforce data integrity for you, yet you don't need to manage the database yourself.
What limitations did you run into with firebase? I use Firebase for https://getsplashpad.com. Have been very happy with it.
Use whatever you have experience with. I personally would pick something like Postgres because it's just the one I know the most and good fit for your use case.
The https://www.dynamodbbook.com/ has an entire chapter on a backend for a github-like backend. I highly recommend the book for a mixture of good technical advice on using DynamoDB, detailed recipes for how different relational-like patterns map to DynamoDB and then the chapters with realistic examples.
I am using SQLite with sequelize (so I can easily transfer to mysql or something similar if needed). SQLite should definitely be more than enough for the beginning at least, from their website - "any site that gets fewer than 100K hits/day should work fine with SQLite". Also, I don't really like noSQL databases like mongo or firebase.
All things code. Ask questions, share tech tips, etc.