LLI Wiki — My Digital Knowledge Base

Azure Cosmos DB for a SQL DBA: vocabulary, APIs and when to use it

Learning notes: account, database, container, item, partition key, RU, the six APIs and choosing between Cosmos DB and Azure SQL.

I'm taking a training on Azure Cosmos DB and, coming from SQL Server and Azure SQL, my first hurdle was vocabulary: familiar words (database, index, transaction) that don't quite mean the same thing here. These are my learning notes, based on Microsoft documentation, with a "SQL Server side" mapping table.

What I take away: the resource hierarchy

flowchart TD
    A["Cosmos DB account (cosmos-contoso-dev)"] --> B["Database (AppDb)"]
    B --> C["Container (orders)"]
    C --> D["Item (JSON document)"]
    C --> E["Partition key (/customerId)"]
    C --> F["Throughput: RU/s"]
    C --> G["Indexing policy"]

Mapping to SQL Server

Cosmos DB Roughly the SQL equivalent Watch out
Account Logical server / instance Also carries multi-region replication
Database Schema / namespace No files or log to manage
Container Table Schema-agnostic: several item shapes allowed
Item Row JSON document, no typed columns
Partition key Sharding key (not an index) Immutable, drives scale-out
RU/s vCores / DTU A normalized unit of CPU + IO + memory
Indexing policy Indexes Everything is indexed by default

The mental trap: a container is not a normalized table. The documentation instead suggests colocating entities that are read together (for example a customer and their orders) in one container, as long as they share the same partition key.

The APIs

A Cosmos DB account is created for one API, and the docs recommend using a single API per account to access the data.

API Who it is for Container vocabulary
NoSQL New applications, SELECT ... FROM c queries Container / Item
MongoDB Migrating a MongoDB app (wire protocol compatible) Collection / Document
Apache Cassandra CQL applications Table / Row (database = keyspace)
Apache Gremlin Graphs Graph / node or edge
Table Modernizing Azure Table Storage Table / Item
PostgreSQL Distributed PostgreSQL see below

Heads-up: the docs state that Azure Cosmos DB for PostgreSQL is on a retirement path and is no longer recommended for new projects. For distributed PostgreSQL they point to the Elastic Clusters feature of Azure Database for PostgreSQL.

What I retain: not every feature exists in every API. For example, hierarchical partition keys are only supported in the NoSQL API, so I always check the docs for the API I target.

Cosmos DB or Azure SQL?

My beginner's summary (these are my notes, not an official rule):

The question to ask: "are my accesses almost always through the same key?". If yes, Cosmos DB is a candidate. If not, I stay relational.

Lessons learned

Further reading

Mastodon LLIMastodon SocialFosstodon