0

I have searched this forum for an answer but could not find anything relevant. We are designing database schemas for a social media app with posts, groups, users and chats etc and it looks like we have a mix of structured and unstructured data so initially decided to go with PostgreSQL and Mongo. We also have to restrict some data to some regions because of compliance and regulations but allow the rest of the data globally because of users moving around globally. Since we don't have anything critical like a bank, we are wondering if we just have to go with Mongo all the way as it makes our business logic, deployment and maintenance simple and fast. One issue is the many-many relations like user belongs to many groups and a group has many users. In relational, I could have a table with rows (group_id and user_id) and I can easily get all users in a group or all groups a user is in. In unstructured, I can store groups list in user's document and users list in group's document but now if a user joins a group, it becomes two updates (user's document and group's document). Same logic for user liking a post. Here we want all users who liked a post and all posts that the user liked. Even if one does not need one piece of information in real-time such as the app never shows the groups a user is in to the user, we might need it for data analysis which means we have to extract this from reading all groups' documents.

We also feel PostgreSQL is cheaper when looking at AWS RDS vs MongoDB Atlas, so tempted to at least use it partially. Could anyone please let us know if there is an elegant solution for this or any blogs, posts that gives a solution?

Regards, KSRD

1 Answer 1

1

We are designing database schemas for a social media app with posts, groups, users and chats etc and it looks like we have a mix of structured and unstructured data...

Having some data examples would probably be helpful.

Since we don't have anything critical like a bank, we are wondering if we just have to go with Mongo all the way...

Nope. There are solutions for using only a SQL database, only a NoSQL database, or both.

One issue is the many-many relations like user belongs to many groups and a group has many users...In unstructured, I can store groups list in user's document and users list in group's document but now if a user joins a group, it becomes two updates (user's document and group's document).

Yep, that is one of the couple of drawbacks of a NoSQL database which actually makes it less efficient by comparison in this specific case.

Could anyone please let us know if there is an elegant solution for this...

Again, seeing some examples of the data you're dealing with would be helpful. But one solution is to only use a SQL database such as PostgreSQL and either:

  • Store the unstructured data in a field that doesn't care about structure, such as the JSON data type

  • Store the unstructured data as structured columns that are nullable, perhaps in a separate auxiliary table, if you want to reduce the schema changes deployed to the main table

  • Actually manage the unstructured data as structured data in the main table

YMMV on which one's the least maintenance for you, depending on your use cases. But keep in mind, regardless of which database system you use, whenever the unstructured data's shape changes, so does the consuming application need to be updated to support that (usually). So I find it not really much more work to maintain those same changes in the database layer if I'm making them in the application anyway, especially at the benefit of improved efficiency, performance, and queryability. Most times, the juice is worth the squeeze.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.