Introduction to Cosmos DB Indexes

   

INDEXING IN COSMOS DB

      In SQL Server, you need to create indexes manually. SQL Server likes to suggest indexes, but it does not create them for you. After seeing some of those suggestions from SQL Server, I am glad it does not create indexes itself.  By Default, CosmosDB indexes everything. You don’t need to worry about missing indexes in your containers. Does that mean we don't need to worry about indexes anymore in CosmosDB? Short answer is No! you still need to adjust settings of CosmosDB indexing policies mainly for saving money.
   
       In SQL Server you need to pick which columns you like to index, In CosmosDB you need to pick which columns not to index. It's kind of same thing at the end. You might ask "If everything is indexed and working fine, why do you want me to poke the well running system?" When we compare SQL Server indexes to CosmosDB Indexes, one thing works exactly same. That is the index file size. CosmosDB holds the indexes in a separate file like SQL Server and if we want to index everything, index file size is going to get large. Since we need to pay for the file space in CosmosDB, you might need to pay extra for indexes that you might never use. Also, your updates, inserts and deletes might cost you more Request Units since CosmosDB needs to maintain all the indexes in the background.

    You can save good amount of money by customizing your database’s Indexing Policy. You can check or update your current Indexing Policy from the Azure portal. by clicking Scale & Settings link under any of your containers.

Here is the default settings of Indexing Policy of my container.

     

Indexing Mode

      This property tells CosmosDB when to change indexing for your data. You need to pick one of the following options for this property.

Consistent: This is the default setting. CosmosDB synchronously updates the indexes when your application changes any data.

Lazy: CosmosDB maintains your indexes eventually not as soon as you change data in your container. By just changing indexingMode to Lazy, you can save 20 to 30 percent Request Units!

None: By selecting this option, you are telling CosmosDB to not Index anything. This makes sense if you have a solution which uses CosmosDB as key-value storage.

---

Automatic: Default option is true, CosmosDB handles what needs to index by selecting true. If you select false, you need to tell CosmosDB what needs to index by using SDK in each transaction. I recommend keeping this true.

---

includedPaths & exludedPaths

     You need to use these properties to tell CosmosDB which nodes should or should not be indexed. You don’t need to list every column thanks to wildcard support. Custom paths should start with “/” and typically end with “?” wildcard. For example, let’s say you have a node named "Comments",  You store long text in this column and you are never going to use this column in any of your Where clauses. It makes sense to not index this column. To exclude this column from indexing, all you need to do is adding the following path to “exludedPaths”.

"excludedPaths": [
"path":  "/Posts/Comments/?"
]


Indexes Property

Kind: CosmosDB supports 3 types of indexes for data. Depending how you are going to filter data, you might want to change the kind of index on columns.

• Hash Index: This index is used for equality indexes. It supports String or Number type of data. 
• Range Index: This index is used if you want to use range operators (> < !=) and ORDER By operator.
• Spatial Index: This index is for GeoJSON documents in your tables. It supports Point, Polygon and LineString spatial objects.

---

Data Type:  Probably you are going to pick String, or Number for this property. If you store spatial data in CosmosDB you can pick Point, Polygon, or LineString for this property.

---

Precision: 
      For number type of data, I would leave this alone and keep the maximum value which is -1. In Json documents, Number data type takes 8 bytes and by keeping precision -1, we keep it 8

      Precision property might make more sense for string data types mainly because strings can be any length. For example, if you store State codes, and you are sure that data length will not be larger than 2 characters you can specify 2 for precision. Just be careful how specific you want to be with precision. You don’t want to go back and change your index policy when length of data change in your documents.