Azure Cosmos DB Partition Key and Indexing for Sorted Queries
A social media application stores user posts in Azure Cosmos DB. Each document contains fields: PostID (unique), UserID, Timestamp, Content, LikesCount. The most common query retrieves all posts by a specific UserID ordered by Timestamp descending. Which partition key and indexing strategy minimizes Request Unit (RU) consumption?
Quick Answer
The correct answer is UserID as the partition key with a range index on Timestamp. This strategy minimizes Request Unit consumption because the query filters on UserID, so using UserID as the partition key ensures all posts for a given user reside in the same physical partition, eliminating costly cross-partition queries. The range index on Timestamp then allows Cosmos DB to sort results in descending order directly from the index without scanning or sorting documents, which would otherwise add significant RU overhead. On the DP-900 exam, this scenario tests your understanding of how partition key selection directly impacts query efficiency and how indexing supports sorted queries—a common trap is choosing a unique key like PostID, which scatters data across partitions and forces fan-out queries. Remember the memory tip: “Filter first, sort second—partition on the filter, index on the sort.”
⚠ Common exam trap
Candidates often choose a composite index (Option D) thinking it optimizes both filter and sort, but Cosmos DB's indexing engine can satisfy the ORDER BY with a simple range index on the sort column alone, and a composite index would only add unnecessary write RU cost.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Partition key: UserID; Index: range on Timestamp
The query filters on UserID, so setting UserID as the partition key ensures all posts for a user are in the same physical partition, avoiding cross-partition queries. Adding a range index on Timestamp allows efficient sorting without additional RU overhead, as Cosmos DB can use the index to return results in descending order directly.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Partition key: PostID; Index: range on Timestamp
Why it's wrong here
PostID is unique, so each post lands in its own logical partition; the UserID filter then scans every partition, and a Timestamp index cannot satisfy the UserID predicate. PostID partitioning suits point reads by PostID, not per-user ordered retrieval.
- ✓
Partition key: UserID; Index: range on Timestamp
Why this is correct
Partitioning on UserID colocates all posts for one user in a single logical partition, so the common query hits one partition. A range index on Timestamp lets Cosmos DB return results already ordered descending, avoiding a cross-partition fan-out and costly sorting.
- ✗
Partition key: Timestamp; Index: range on UserID
Why it's wrong here
Partitioning by Timestamp scatters a user's posts across every partition, forcing a cross-partition fan-out for the UserID filter, and indexing UserID alone cannot serve the Timestamp descending sort. Timestamp suits time-bucketed workloads querying recent items across all users, not per-user feeds.
- ✗
Partition key: UserID; Index: composite on (UserID, PostID)
Why it's wrong here
Partitioning by UserID co-locates a user's posts, but the composite index on (UserID, PostID) cannot serve the Timestamp descending sort, so the query still sorts in memory. Composite indexes suit equality filters combined with a different ordered field, not Timestamp ordering.
Go deeper
Related to this question
Learn chapter
Document Databases and JSON Storage
Key term
Partition key
A partition key is a value used by database systems to distribute data across multiple storage partitions, enabling faster queries and efficient scaling.
Key term
Request unit
A request unit (RU) is a normalized measure of the computational capacity and resources needed to perform a database operation in Azure Cosmos DB.
About these practice questions
Courseiva writes every DP-900 question from scratch — 851 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on DP-900
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. A social media application stores user posts in Azure Cosmos DB. Each post has fields: PostID (unique), UserID, Timestamp, Content, LikesCount. The application frequently queries for all posts by a specific UserID ordered by Timestamp descending. To minimize Request Unit (RU) consumption, which partition key and indexing strategy should be used?
medium- ✓ A.Partition key: UserID, and create a composite index on (UserID, Timestamp DESC)
- B.Partition key: Timestamp, and sort by UserID in the query
- C.Partition key: PostID, and use ORDER BY Timestamp
- D.Partition key: UserID, and use ORDER BY PostID
Why A: UserID is the most frequently filtered attribute, making it an ideal partition key to distribute data evenly and avoid cross-partition queries. Adding a composite index on (UserID, Timestamp DESC) allows the query to be served from a single physical partition with an index seek, minimizing RU consumption by avoiding a full scan or sort operation.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-900 exam.