cosmosdb sql api vs mongodb api which one to use for my scenario.

Question

I have a document called "chat"

 "Chat": [
        {
          "User": {},
          "Message": "i have a question",
          "Time": "06:55 PM"
        },
        {
          "User": {},
          "Message": "will you be able to ",
          "Time": "06:25 PM"
        },
        {
          "User": {},
          "Message": "ok i will do that",
          "Time": "07:01 PM"
        }

every time a new chat message arrives i should be able to simple append to this array.

mongodb API aggregation pipeline (preview) allows me to use things like $push $addToSet for that

if i use sql api i will have to pull the entire document every time modify it and create a new document every time.

Other Considerations : This array can grow rapidly. This "chat" document might also be nested into other document as well.

My Question Does this means that mongodb API is better suited for this and sql api will have a performance hit for this scenario ?

Which API you use... that's a fairly broad question, and really has no specific answer. However, whichever route you go, you're going to run into trouble as soon as that array reaches the maximum document size (2MB currently). That's an unbounded array, and once you can't add to the array, your model (and code) will be broken. — David Makogon, Oct 18 '18 at 21:22
Also - you'll need to do some benchmarking between both APIs and implementations, to determine which method of updates works best for you. Remember that the MongoDB API still uses the native Cosmos DB engine underneath, so it's possible the two have similar performance characteristics. — David Makogon, Oct 18 '18 at 21:26

score 0 · Accepted Answer · edited Aug 22 '19 at 21:11

Does this means that mongodb API is better suited for this and sql api will have a performance hit for this scenario ?

It's hard to say which database is the best choice.

Yes,as found in the doc, Cosmos Mongo API supports $push and $addToSet which is more efficient. However,in fact, Cosmos Mongo API just supports a subset of the MongoDB features and translates requests into the Cosmos sql equivalent. So, maybe Cosmos Mongo API has some different behaviours and results. But the onus is on Cosmos Mongo API to improve their emulation of MongoDB.

When it comes to Cosmos Sql Api, partial update is not supported so far but it is hitting the road. You could commit feedback here. Currently, you need to update the entire document. Surely, you could use stored procedure to do this job to release pressure of your client side.

The next thing I want to say, which is the most important, is the limitation mentioned by @David. The document size has 2MB limitation in sql api and 4MB in mongo api:What is the size limit of a cosmosdb item?. Since your chat data is growing, you need to consider to split them. Then give the documents a partition key such as "type": "chatdata" to classify them.

cosmosdb sql api vs mongodb api which one to use for my scenario.

1 Answers1

Linked