I am building a chat service and I designed the chat schema, so that it nests all the users that belong to this chat. I only nested the essential data of users such as name and avatarUrl and userId.
I believe compared to relational databases, this nesting feature is the power of MongoDB, where you basically store the "JOIN"ed data. So querying a nested document should generally be faster than querying a chat row and joining to multiple user rows.
Now even though I only store essential data, some of the chat document size became quite large(30kB), because there were chat rooms where it had more than 100 users. There will be a max user limit so the chat document will not grow indefinitely. But nesting about 100 users, leading to a document size of about 30kb looks reasonable to me.
But, then I realized that the chat page loading with large users became significantly slow. I measured the time taken for the query to execute from backend(node.js on local environment. so there is some latency. my laptop is in Korea and the database server is in US).
For small documents the query time was within 230ms, but for the 30kb document a simple Chat.findOne({_id:chatId }), took about 500ms and thats just way too long. The collection only has like 100 documents, so index would not improve performance.
Now two things come in my mind. First, Why is the document so big? Maybe its best practice to remove keys and store everything in array(matrix) format? This would be terrible to work with in the backend... But maybe this is necessary for performance optimization later. Is this common practice?
Second, my real question. Why does it take so long to load 20kb of data? if 20kb already takes up 500ms, I am guessing that larger documents would be pretty much unusable.
I am a huge fan of MongoDB and its nesting style. But if the document needs to stay small for reasonable response time, then this is really upsetting. Because it means I can only use nesting sparingly, then I would have to use $lookup or mongoose populate to make joins, which are terrible in terms of performance.
I am currently using MongoDB 4.4 with MongoDB Atlas M10. The current app has a very small user pool, which is why I thought M10 is sufficient.