3.4 Azure Cosmos DB for PostgreSQL

What Azure Cosmos DB for PostgreSQL Is

Quick Answer: Azure Cosmos DB for PostgreSQL is a managed, distributed PostgreSQL service built on the open-source Citus extension. It is relational — it speaks ordinary PostgreSQL — but it scales horizontally across many nodes by sharding tables. It is the renamed product that replaced the older Hyperscale (Citus) deployment option of Azure Database for PostgreSQL.

Despite sharing the Cosmos DB brand, this is not the non-relational, multi-model Cosmos DB service that supports NoSQL APIs (covered in Chapter 4). Microsoft groups it under the Cosmos DB family because it is globally distributed, but the engine, query language, and data model are pure PostgreSQL.

How Citus Distributes Data

A Cosmos DB for PostgreSQL cluster has one coordinator node and one or more worker nodes. When you mark a table as distributed, Citus:

1. Hashes a chosen distribution column (often a tenant ID or customer ID).
2. Splits the table into shards by that hash.
3. Places shards across worker nodes.
4. Routes queries through the coordinator, which executes them in parallel on workers and combines results.

Reference tables that need to be available everywhere (small lookup data) can be replicated to every node using create_reference_table. Large fact tables that drive throughput use create_distributed_table.

When to Choose It

When Not to Use It

• Small workloads that fit comfortably on a single PostgreSQL server — use Azure Database for PostgreSQL Flexible Server instead.
• Applications that require document, key-value, or graph APIs — use Azure Cosmos DB (NoSQL, covered in Chapter 4).
• Workloads that depend on the Microsoft SQL Server engine or T-SQL — use the Azure SQL family.

Compatibility

Azure Cosmos DB for PostgreSQL is 100% PostgreSQL — you connect using standard PostgreSQL drivers (libpq, JDBC, psycopg, Npgsql) and tools (psql, pgAdmin, Azure Data Studio). The main code change versus a single-node Postgres is choosing which tables to distribute and on which column. ACID transactions are supported within a single node and across distributed tables when the transaction touches the same distribution key.

Why It Matters for DP-900

The exam expects you to know that:

• Cosmos DB for PostgreSQL is relational, not NoSQL.
• It is built on the Citus open-source extension.
• It is the right answer for very large PostgreSQL workloads and multi-tenant SaaS.
• It replaces the former Hyperscale (Citus) deployment option of Azure Database for PostgreSQL.

Architecture in More Detail

A cluster has a fixed node configuration you choose at provisioning: a coordinator node size, a worker node size, and a worker node count. You scale out by adding workers and up by resizing nodes. Because the coordinator holds the metadata and plans queries, it is sized for connection handling and planning, while workers are sized for storage and parallel execution. The platform also offers a single-node (basic) configuration for small workloads or development, which can later grow into a multi-node cluster without an application rewrite.

Sharding Strategy and Co-location

The power of Citus comes from co-location. If two distributed tables share the same distribution column type and value range (for example, orders and order_items both distributed on tenant_id), Citus places matching shards on the same worker node. Joins between co-located tables then run locally on each worker with no cross-node data shuffle, which is what makes multi-tenant SaaS queries fast. Picking a single, consistent distribution column across the tenant's tables is therefore the central design decision, mirroring how a Cosmos DB partition key or a SQL Server partition scheme drives performance.

Performance and Indexing

Because it is real PostgreSQL, you keep the full PostgreSQL toolbox: B-tree, GIN, GiST, and BRIN indexes, the EXPLAIN planner, materialized views, common table expressions, window functions, and extensions such as postgis and pg_stat_statements. Citus simply distributes these features across the cluster. Columnar storage is available for analytical tables, helping the HTAP (hybrid transactional/analytical) scenario where the same cluster serves both operational writes and dashboard reads.

Exam Contrast Table

The recurring DP-900 trap is to assume anything branded "Cosmos DB" is NoSQL. Cosmos DB for PostgreSQL is the exception: it is relational PostgreSQL that happens to share the globally distributed Cosmos DB brand and management surface.