Hi everybody, I'm developing an application to be used in a gym setting where athletes fill out a health survey, and coaches can analyze the results. However, due to the dynamic nature of some aspects of the app and more static aspects of the other, I am wondering if/how I would integrate MongoDB with my existing PostgreSQL database. I would like to store things like registrations, license information, and club information in
Postgres, while I am thinking about moving things like user surveys, logging, and user settings information over to
MongoDB. Some fields on the survey are integers, some large blocks of text, and some are arrays. My thought is, if I moved that data to
MongoDB, it would give us greater flexibility in terms of adding and removing fields and data to them, and it would scale a lot easier than
Postgres. Not to mention it will be easier to organize that kind of data. Is that overkill or am I approaching this issue the right way? Thank you!
With PostgreSQL you could easily integrate JSON or array type columns and develope a simple interface to add columns on your application. Anyway handling all the data this way will require some intermediate skill with PostgreSQL dialect and a mix and match of syntaxes for your analitical queryes. Also you will need to have a good design for you backend to handle all this. MongoDB will handle all this in a more natural way and I believe will be more easily integrated with a Node.js backend.
Thanks for the response. Analytics is definitely a big part of this project and I would agree that Mongo would probably handle it more naturally than Postgres. My backend is a 3-tier Java Spring REST API, but I'm toying with the idea of using GraphQL for managing the more dynamic data coming from Mongo.
You can have your cake and eat it too. If you really need the flexibility of a document store, Postgresql's JSONB support allows you to mix and match relational data and document data within the same database/table. You can just as easily run analytical queries against JSONB data in Postgresql as you can against "normal" relational data. MongoDB comes with a significant operational overhead and cost (hello replica sets), so unless you really need MongoDB's sharding capabilities (which you shouldn't until you get to extreme scaling numbers), then just stick with Postgresql and use JSONB where you need it.