For a social media platform that I'm developing, I need to model the database for the user content. The content published by the user will be typically title, description, comments with replies and few hashtags. (Need full text search for description) The old platform has around 200K registered users.
For the database I'm thinking to have a PostgreSQL and a MongoDB instance. I need to use a PostgreSQL because there are some schema that I have to enforce and needs ACID compliant transactions. I was thinking to use MongoDB to store user content like title, description but having second thoughts whether I really need a MongoDB instance. I know PostgreSQL has this JSON and JSONB types and have used it before to store unstructured content. But if I go this way my only concern is that the performance of the search queries for JSONB and the complexity of the search queries that I have to implement. For example I may need a search feature where I have to search a phrase in several fields and sort the results based on a relevance score and return the results (like in elasticsearch and I think MongoDB has this automatically generated relevance score). So what do you guys think? Any tip or suggestion is much appreciated.
PS - In terms of the scalability, I'm not expecting to exceed 400K users in the next 5 years. Also if I use PostgreSQL and MongoDB, I have to have some kind of a two phase commit mechanism for the transactions that involves both the databases which is kind of an unnecessary effort at this point as I see.