Architecture and Database re-design for migration and support of multiple clients

Question

I'm evaluating the migration of the following application's architecture: - Nginx + PHP + MySQL - Currently the infrastructure is scalable and redundant in the AWS cloud and It was designed to support one client.

Our next plan is to expand it to support multiple clients, these are my concerns: 1- The nature of the application is focused on collecting granular data by a set of companies with a task force of users based on a list of tasks per item in a given place. In summary: A user can potentially collect around 14.4k records per day (1 account x 2 campaigns x 12 actions x 12 tasks x 50 items) So a big client with 50 employees and 10 places to visit per day would record around 7.2M rows per day, anything can be measured ( a text, file as in a photo or video, etc) Our current way of storing the most granular values in a table is based on the EAV model. This allows the app to be very flexible, but as you can see generates a row vertically. making redundant much of the fk values and exponentially growing that table, giving us some issues when trying to run reports on this database, unless we offload the report somewhere else.

I was wondering if moving to: - Play + Mongodb And making the visit our main collection would reduce the redundant information. In the end a visit would be our most important document and would store 14.4k values, but everything would be contained inside. That means, in terms of documents, I would generate with 50 employes x 10 visit only 500 documents per day, which seems much more mangeable from the operations perspective.

However, the issue here is granular or cross-vertical reporting, like a report of your task force for the month. I guess this could be offloaded.

Would Play + Postgres be a better option and using the table with bjson data with slick?

Answer 1

A a document database lie MongoDB has a very different storage model than a relational database such as Postgres.

If you go for MongoDB, you will have to chose between the normalized data model or the embedded model:

• The normalized model uses references to manage one to many relations between independent entities. You won't need foreign keys, but the database uses a 12 byte reference instead. Navigating between the records using a reference is ultra-fast (faster than going via an index). However, to read all the related data, requires you to have several reads, which makes reporting a little bit more difficult.
• The embedded model stores the related data together using collections when needed. It's faster for reading all the related data at once, but it could be slower at writing, if the collections are growing dynamically. Access could also be slower if search/navigation is not done one the document but on the collection (e.g. search all accounts related to a specific item).

If you go for Postgres, you'll continue to use a fk based relational model. Maybe it's not as performant as MongoDB relations, but it's quite flexible in the selection criteria.

It will be difficult to predict performances. Benchmarks do generally not take into account the different data models that you could use in MongoDB. The performance will also heavily depend on the kind of activity (read, write, or what mix) and the number of clients. Nevertheless, according to the raw data, it seems that Postgres is still an excellent choice