Azure Cosmos DB API for PostgreSQL

Azure Cosmos DB API for PostgreSQL: Complete Guide for DP-900

Azure Cosmos DB API for PostgreSQL

Why Is This Important?
Azure Cosmos DB API for PostgreSQL (formerly known as Azure Cosmos DB for PostgreSQL or Hyperscale (Citus)) is a critical topic in the DP-900 exam because it bridges two important concepts: the familiarity and power of PostgreSQL with the distributed, globally scalable architecture of Azure Cosmos DB. Understanding this API helps you grasp how Microsoft enables developers to scale relational PostgreSQL workloads horizontally, which is a unique capability in the Azure data ecosystem. For the DP-900 certification, you need to understand where this service fits in the broader landscape of Azure data services, especially within the non-relational data category.

What Is Azure Cosmos DB API for PostgreSQL?
Azure Cosmos DB API for PostgreSQL is a managed service that allows you to run PostgreSQL at any scale. It is built on the Citus open-source extension to PostgreSQL, which transforms PostgreSQL into a distributed database. This means you can use standard PostgreSQL syntax, tools, and drivers while benefiting from horizontal scalability across multiple nodes.

Key characteristics include:
- Distributed PostgreSQL: It distributes your data and queries across multiple nodes (servers), enabling massive scalability.
- Familiar PostgreSQL Interface: You interact with it using standard PostgreSQL queries, making it easy for developers already familiar with PostgreSQL.
- Horizontal Scaling (Sharding): Data is automatically sharded (partitioned) across worker nodes based on a distribution column you specify.
- Managed Service: Microsoft handles infrastructure management, patching, backups, high availability, and monitoring.
- Part of the Cosmos DB Family: Although it is categorized under Cosmos DB, it uses PostgreSQL as its query language and data model, unlike other Cosmos DB APIs (such as NoSQL, MongoDB, Cassandra, Gremlin, or Table).

How Does It Work?

Architecture:
The service uses a coordinator node and one or more worker nodes:
- The coordinator node receives queries from the application, plans the distributed execution, and coordinates the results from worker nodes.
- The worker nodes store shards (partitions) of the data and execute portions of the query in parallel.

Data Distribution:
When you create a table, you choose a distribution column (also called a shard key). The data is then hash-distributed across worker nodes based on the values in that column. This allows the system to process queries in parallel across multiple nodes, dramatically improving performance for large datasets.

Types of Tables:
- Distributed Tables: Data is sharded across worker nodes. Best for large tables that need horizontal scaling.
- Reference Tables: Small tables that are replicated to every worker node. Ideal for lookup tables that are frequently joined with distributed tables.
- Local Tables: Standard PostgreSQL tables that reside only on the coordinator node.

Use Cases:
- Multi-tenant SaaS applications: Each tenant's data can be co-located on the same worker node using the tenant ID as the distribution column.
- Real-time analytics: Large volumes of data can be ingested and queried in parallel for dashboards and reporting.
- High-throughput transactional workloads: Workloads that exceed the capacity of a single PostgreSQL server can be distributed across multiple nodes.
- Time-series data: Large datasets with time-based partitioning benefit from distributed query execution.

Connectivity:
Since it is PostgreSQL-compatible, you can connect using any PostgreSQL client library, including psql, pgAdmin, and programming language drivers for Python, Java, Node.js, .NET, and more.

Key Differences from Other Cosmos DB APIs
- Unlike the Cosmos DB API for NoSQL, MongoDB, Cassandra, Gremlin, or Table, the PostgreSQL API uses a relational data model with SQL queries.
- It is specifically designed for horizontal scaling of PostgreSQL workloads, not for document, graph, or key-value data models.
- It does not use the same underlying Cosmos DB storage engine as other APIs; instead, it is built on the Citus extension for PostgreSQL.

Key Differences from Azure Database for PostgreSQL
- Azure Database for PostgreSQL (Flexible Server) is a single-node managed PostgreSQL service, best for workloads that fit on a single server.
- Cosmos DB API for PostgreSQL is a multi-node distributed PostgreSQL service, best for workloads that need to scale beyond a single server.

---

Exam Tips: Answering Questions on Azure Cosmos DB API for PostgreSQL

1. Know the Service Name and History: Microsoft has rebranded this service multiple times. It was previously known as Azure Database for PostgreSQL - Hyperscale (Citus) and is now Azure Cosmos DB API for PostgreSQL. Exam questions may reference either name. Recognize that they refer to the same underlying distributed PostgreSQL technology.

2. Understand the Architecture: Be prepared for questions about the coordinator node and worker nodes. Remember that the coordinator plans queries and the workers store sharded data and execute queries in parallel.

3. Differentiate from Other Cosmos DB APIs: If a question asks which Cosmos DB API supports relational data or PostgreSQL queries, the answer is the API for PostgreSQL. Do not confuse it with the NoSQL API or MongoDB API.

4. Differentiate from Azure Database for PostgreSQL: If a question involves scaling PostgreSQL beyond a single server or horizontal scaling, think Cosmos DB API for PostgreSQL. If the question involves a single-server PostgreSQL deployment, think Azure Database for PostgreSQL Flexible Server.

5. Recognize Use Cases: Multi-tenant applications, real-time analytics on large datasets, and high-throughput transactional workloads are classic use cases. If the scenario describes a PostgreSQL application that needs to handle terabytes of data or millions of transactions per second, Cosmos DB API for PostgreSQL is likely the answer.

6. Remember It Uses Standard PostgreSQL: Questions may test whether you know that existing PostgreSQL applications can connect to this service without major code changes. The answer is yes, because it is PostgreSQL-compatible.

7. Distribution Column (Shard Key): Understand that choosing the right distribution column is essential for performance. The distribution column determines how data is spread across worker nodes.

8. It Is Still Categorized Under Non-Relational Data on Azure: Despite being PostgreSQL (which is relational), Microsoft categorizes the Cosmos DB family under non-relational services in the DP-900 curriculum. Do not be confused by this classification—understand that Cosmos DB is primarily a non-relational platform, but the PostgreSQL API brings relational capabilities into the Cosmos DB ecosystem.

9. Managed Service Benefits: Like all Azure managed database services, it provides built-in high availability, automated backups, patching, and monitoring. If a question asks about reducing administrative overhead for a distributed PostgreSQL deployment, this is the right answer.

10. Elimination Strategy: In multiple-choice questions, eliminate options that involve non-PostgreSQL APIs (such as MongoDB or Cassandra) if the scenario clearly describes a PostgreSQL workload. Similarly, eliminate single-node solutions if the scenario emphasizes massive scale or distributed data processing.

Summary for Exam Preparation:
Azure Cosmos DB API for PostgreSQL = Distributed PostgreSQL built on Citus, using a coordinator + worker node architecture, designed for horizontal scaling of PostgreSQL workloads, accessible via standard PostgreSQL tools and drivers, and managed by Azure as part of the Cosmos DB family. Focus on understanding when to use it versus other Azure database services, and you will be well-prepared for DP-900 exam questions on this topic.