I'm designing a data model for a transactional use case which is write intensive. The write load would be 900 – 1,600 Transactions Per Second (TPS) which would account for 30 million Inserts/Updates per day and read load would be 600 – 800 TPS
Since we have opted for an embedded data model, we are using upsert queries in most places. We have unique indexes built on the right fields to make the search faster. Out of the 30 million TPS day, we will be performing at least 10 million upsert per day
I would like to know:
- Is it wise to keep the documents embedded and perform upserts every time (considering the load) rather than keeping the documents in 2 separate collections and perform joins to report the data to client applications?
- Considering the load, I suspect that too much of upsert would created high IO and crash the system.
Best Answer
To answer you question, and this is my opinion not hardwired:
1. Is it wise to keep the documents embedded and perform upserts every time (considering the load) rather than keeping the documents in 2 separate collections and perform joins to report the data to client applications?
Ans: MongoDB is designed to handle embedded document structure, so don't worry if you are using upserts to update/ insert documents. Also two separate collection will increase your index overhead.
2. Considering the load, I suspect that too much of upsert would created high IO and crash the system.
Ans: If you add an correct index, then the upsert can become faster: after all the index is used to 'find' the document. The caveat is in the field(s) the index operates on and the fields that you're updating. If the updated portion is part of the index, you will have a performance impact on updating the document. If the updated portion is not part of the index, you will not incur a penalty for writing in the existing document. If the document is added though, you will have a minor performance impact, since the index collection is update.
Please feel free to revert back your opinion on this.