Managing DocumentDB(CosmosDB) stored procedure – Partitioning, Scaling and Limits

What is stored procedure for NoSQL DocumentDB(CosmosDB)?

Similar to traditional relational database, DocumentDB(CosmosDB) provides a way to perform multiple operations atomically – at document level. If you ask me what is the biggest difference between MongoDB and DocumentDB, my answer would be the stored procedure provided in DocumentDB. However, there are a couple of things you should know about before enjoying the caviar.

DocumentDB stored procedure runs within partition – Bounded execution

Stored procedure in DocumentDB can only be executed within a target partition – you can only execute your store procedure over the data on a single partition. With this in mind, choose your partition key wisely.

DocumentDB stored procedure has TIMEOUT limits

Store procedure has a hard limit of the execution time – 5 seconds per run. Complex logic might not be a good fit for your store procedure. A solution to get around this is to use a “Continuation Token” to call the store procedure with multiple times. See here for more details: How to avoid store procedure timeout?

Watch your RU limits of DocumentDB

Just as the native query, store procedure is also limited by your pricing tier RU limits. Plan ahead on how many RUs you need by checking the official documents: Request units.

Basic RU usage table

Item size	Reads/second	Writes/second	Request units
1 KB	500	100	(500 * 1) + (100 * 5) = 1,000 RU/s
1 KB	500	500	(500 * 1) + (500 * 5) = 3,000 RU/s
4 KB	500	100	(500 * 1.3) + (100 * 7) = 1,350 RU/s
4 KB	500	500	(500 * 1.3) + (500 * 7) = 4,150 RU/s
64 KB	500	100	(500 * 10) + (100 * 48) = 9,800 RU/s
64 KB	500	500	(500 * 10) + (500 * 48) = 29,000 RU/s

Creating first DocumentDB store procedure

Here is an example of DocumentDB store procedure:

function yourfuncname(arg) {
    var collection = getContext().getCollection();

    // Use parameterize query
    var query = {
        query: "select * from collection o where o.name = @doc_name", 
        parameters: [{name: "@doc_name", value: "doc1"}]};
    
    // fecth all documents for customer.
    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        query,
        function (err, documents, options) {
            if (err) throw err;
            
            if (!documents && documents.length == 0) {
                throw new Error("No document found");
            }
            
            //Do your stuff here
        }
        
        if (!isAccepted) throw new Error(‘The query was not accepted by the server.’);
}

See more examples here: Azure Cosmos DB server-side programming: Stored procedures, database triggers, and UDFs

DocumentDB UDF (User define function)

User define function is your good friend. Use that for any code in common for each of your store procedures: such as time conversion, formatting, and utilities.

Tips for partitioning DocumentDB

Choosing a good partition key is crucial: the last thing you want is having to query multiple partitions (if not all of them). Try to use something that:

1. Has a wide range of values (100+ would be great)
2. Can be use to find all documents you need within a single query (common identifier like jobid or contextid)

Tips for performance and scaling DocumentDB

Because of the nature of the service, there are not much of rooms for wild hacks. Some lesson I learned are:

1. Make your stored procedure as simple as possible (very helpful to avoid traffic spike and timeout).
2. Don’t do full table scan(calling stored procedure on all partitions)
3. Distribute your data as balance as possible.
4. Use UDF to optimize your code.

References

Official website: Azure Cosmos DB server-side programming: Stored procedures, database triggers, and UDFs

Request units:  Request units.

Avoiding stored procedure timeout: How to avoid store procedure timeout?

Further reading:

Protect your server from ssh password brute forcing