The correct answer is to create a covering index on the filter column. A covering index includes all columns required by the reporting query, allowing PostgreSQL to satisfy the query entirely from the index pages without needing to access the heap (table) rows, which eliminates costly random I/O. This is especially effective for high-cardinality, low-selectivity columns, where many rows match the filter but the index alone can return the data, drastically reducing read IOPS on Amazon RDS for PostgreSQL. On the AWS Certified Database Specialty DBS-C01 exam, this scenario tests your understanding of index-only scans versus traditional index scans; a common trap is to recommend a standard B-tree index on the filter column alone, which still requires heap lookups. Remember the memory tip: “Cover the query, skip the heap” — if your index covers every column in the SELECT and WHERE clauses, PostgreSQL never touches the table.
DBS-C01 Workload-Specific Database Design Practice Question
This DBS-C01 practice question tests your understanding of workload-specific database design. Read the scenario carefully and evaluate each option against the stated constraints before committing to an answer. After answering, compare your reasoning against the explanation and wrong-answer breakdown below. Once you have made your selection, read the full explanation to reinforce the concept and understand why each distractor is designed to mislead on exam day.
A company is migrating an on-premises PostgreSQL database to Amazon RDS for PostgreSQL. The database has a large table that is frequently accessed by reporting queries. The reporting queries filter on a column that has a high cardinality but low selectivity. To optimize query performance on this table, which design choice should the database specialist recommend?
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Create a covering index on the filter column
Option D is correct because a covering index includes all columns needed by the reporting queries, allowing PostgreSQL to satisfy the query entirely from the index without accessing the heap (table) pages. This eliminates the overhead of random I/O for row lookups, which is especially beneficial when filtering on a high-cardinality, low-selectivity column where many rows match but the index scan alone can return the required data. In Amazon RDS for PostgreSQL, this reduces read IOPS consumption and improves query latency.
Key principle: Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option.
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 the table by the filter column
Why it's wrong here
Partitioning helps with large table management but doesn't optimize queries filtering on low-selectivity columns.
✗
Use a read replica to offload reporting queries
Why it's wrong here
Read replicas scale read traffic but do not optimize the performance of an individual query.
✗
Increase the provisioned read IOPS for the RDS instance
Why it's wrong here
Provisioned IOPS improves I/O performance but does not directly optimize query execution plans.
✓
Create a covering index on the filter column
Why this is correct
A covering index includes all columns needed, allowing query results to be returned from the index alone.
Related concept
Read the scenario before looking for a memorised answer.
Common exam traps
Common exam trap: answer the scenario, not the keyword
The trap here is that candidates often choose partitioning (Option A) for any large table with filtering, but fail to recognize that low selectivity means partitioning offers no pruning benefit, while a covering index directly reduces I/O by avoiding heap access.
Detailed technical explanation
How to think about this question
A covering index in PostgreSQL leverages index-only scans, which rely on the visibility map to determine if all rows in a page are visible without checking the heap. When the visibility map is not fully set (e.g., after recent updates or vacuum operations), PostgreSQL may still need to visit the heap, so regular VACUUM is critical to maintain index-only scan efficiency. In RDS, enabling enhanced monitoring and tuning autovacuum settings can help keep the visibility map up to date for optimal covering index performance.
KKey Concepts to Remember
Read the scenario before looking for a memorised answer.
Find the constraint that changes the correct option.
Eliminate answers that are true in general but not in this case.
TExam Day Tips
→Watch for words such as best, first, most likely and least administrative effort.
→Review why wrong options are wrong, not only why the correct option is correct.
Key takeaway
Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option.
Real-world example
How this comes up in practice
A company's IT admin needs to give a contractor read-only access to production logs without sharing account credentials. Using role-based access control (RBAC) and temporary scoped permissions — not a permanent shared password — is the correct pattern. Questions like this test whether you can apply least-privilege access across cloud identity services.
What to study next
Got this wrong? Here's your next step.
Identify which exam domain this question belongs to, review the core concept, then practise similar questions from the same domain.
Workload-Specific Database Design — This question tests Workload-Specific Database Design — Read the scenario before looking for a memorised answer..
What is the correct answer to this question?
The correct answer is: Create a covering index on the filter column — Option D is correct because a covering index includes all columns needed by the reporting queries, allowing PostgreSQL to satisfy the query entirely from the index without accessing the heap (table) pages. This eliminates the overhead of random I/O for row lookups, which is especially beneficial when filtering on a high-cardinality, low-selectivity column where many rows match but the index scan alone can return the required data. In Amazon RDS for PostgreSQL, this reduces read IOPS consumption and improves query latency.
What should I do if I get this DBS-C01 question wrong?
Identify which exam domain this question belongs to, review the core concept, then practise similar questions from the same domain.
What is the key concept behind this question?
Read the scenario before looking for a memorised answer.
About these practice questions
Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
This DBS-C01 practice question is part of Courseiva's free Amazon Web Services 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 DBS-C01 exam.
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
Sign in to join the discussion.