DBS-C01 Workload-Specific Database Design Practice Test 16 — 15 Questions

Question 1

A company is building a real-time chat application that requires storing messages with a maximum of 10,000 characters per message. The application needs sub-millisecond latency for reads and writes. The data must be durable and replicated across three Availability Zones. The development team wants to minimize operational overhead. Which AWS database service is most appropriate?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Amazon DynamoDB with DAX. Amazon DynamoDB with DAX is the most appropriate choice because it provides single-digit millisecond latency for reads and writes, supports up to 400 KB per item (easily accommodating 10,000 characters), and offers built-in replication across three Availability Zones for durability. DAX (DynamoDB Accelerator) further reduces read latency to sub-millisecond by serving as an in-memory cache, while DynamoDB itself handles write durability and replication automatically, minimizing operational overhead.

Answer

Amazon ElastiCache for Redis with replication

Answer

Amazon RDS for PostgreSQL with Multi-AZ

Answer

Amazon Aurora MySQL with Multi-AZ

Question 2

A company uses Amazon RDS for SQL Server with a 4 TB database for a financial reporting application. The database performs nightly batch updates that take 6 hours. The company needs to reduce the batch update time to under 2 hours. The current instance is db.r5.8xlarge with 64 vCPUs and 512 GB memory. The batch process is I/O-bound with high write throughput. Which change will MOST effectively reduce the batch update time?

Accepted Answer

Switch to Provisioned IOPS (io2) with 80,000 IOPS.. The batch process is I/O-bound with high write throughput, so the bottleneck is disk I/O, not compute or memory. Switching to Provisioned IOPS (io2) with 80,000 IOPS provides a predictable, high-performance storage tier that can sustain the required write throughput, directly reducing the batch update time from 6 hours to under 2 hours. RDS for SQL Server on io2 volumes delivers consistent low-latency I/O, which is critical for write-heavy workloads.

Answer

Upgrade to db.r5.16xlarge with 128 vCPUs.

Answer

Increase the instance memory to 1024 GB.

Answer

Enable Multi-AZ deployment.

Question 3

A company is running a MySQL database on Amazon RDS for a web application. The application experiences read-heavy traffic, and the company wants to improve read performance without changing the application code. Which design should the database specialist recommend?

Accepted Answer

Create one or more read replicas of the RDS DB instance.. Amazon RDS read replicas allow you to offload read traffic from the primary DB instance without any application code changes. The application simply connects to the read replica endpoint(s) for SELECT queries, while writes continue to the primary instance. This directly addresses the read-heavy workload by distributing read requests across multiple copies of the database.

Answer

Implement an Amazon ElastiCache Redis cluster in front of the database.

Answer

Increase the instance size of the RDS DB instance.

Answer

Enable DynamoDB Accelerator (DAX) for the RDS instance.

Question 4

A company is designing a database for a global e-commerce platform that requires low-latency reads and writes across multiple AWS Regions. The database must support strongly consistent reads and provide automatic failover. Which AWS service should the company use?

Accepted Answer

Amazon DynamoDB global tables. Amazon DynamoDB global tables provide a fully managed, multi-Region, multi-active database solution that delivers low-latency reads and writes across AWS Regions. It supports strongly consistent reads when using the same-Region endpoint and offers automatic failover by allowing any Region to handle writes independently, ensuring high availability without manual intervention.

Answer

Amazon ElastiCache for Redis global datastore

Answer

Amazon S3 with cross-region replication

Answer

Amazon Aurora Global Database

Question 5

A company is migrating a PostgreSQL database to Amazon Aurora PostgreSQL. The current database has complex queries that join multiple tables and performs well. After migration, the same queries are slower on Aurora. What is the most likely cause?

Accepted Answer

The DB instance class does not have enough memory for the buffer cache.. After migrating to Aurora PostgreSQL, the same complex queries are slower, which often indicates that the buffer cache is too small to hold the working set of data. Aurora uses a distributed storage system where the buffer cache is managed by the DB instance's memory; if the instance class lacks sufficient memory, frequently accessed data pages must be read from storage more often, increasing I/O latency. This is a common performance bottleneck when migrating from on-premises PostgreSQL, where the buffer cache might have been larger or the working set fit entirely in memory.

Answer

Aurora PostgreSQL does not support complex joins; the queries must be rewritten.

Answer

Aurora PostgreSQL does not support indexes on joined columns.

Answer

The default DB parameter group is optimized for write-heavy workloads, not read-heavy.

Question 6

A company uses Amazon DynamoDB for a gaming application that stores player data. The application frequently accesses items by the player's user ID. However, the company also needs to query players by their subscription tier (Gold, Silver, Bronze) and registration date. Which design should the database specialist recommend for this access pattern?

Accepted Answer

Create a Global Secondary Index (GSI) on subscription tier and registration date.. A Global Secondary Index (GSI) on subscription tier and registration date is the correct choice because it allows efficient querying on non-primary key attributes without affecting the base table's primary key structure. DynamoDB GSIs support eventually consistent reads and can be created on any table, enabling the required access pattern of querying players by subscription tier and registration date while maintaining the primary access pattern by user ID.

Answer

Export the data to Amazon Elasticsearch Service for querying.

Answer

Create a Local Secondary Index (LSI) on subscription tier and registration date.

Answer

Enable DynamoDB Streams and process the stream to populate a separate table.

Question 7

A company is running a MongoDB-compatible database on Amazon DocumentDB. The application performs frequent updates to a small subset of fields in documents. The company notices that write latency is high. What should the database specialist recommend to reduce write latency?

Accepted Answer

Review and remove unnecessary indexes on the collection.. Unnecessary indexes impose a write penalty on every insert, update, and delete operation. In Amazon DocumentDB, each write must update all indexes on the collection, so removing unused indexes reduces the per-document write overhead and directly lowers write latency for frequent updates to a small subset of fields.

Answer

Increase the instance size of the DocumentDB cluster.

Answer

Enable multi-master writes to distribute write load.

Answer

Change the storage type from standard to provisioned IOPS.

Question 8

A company has a critical application that uses Amazon RDS for MySQL with Multi-AZ deployment. During a recent failure, the automatic failover took 2 minutes, causing application timeout. The company needs to reduce failover time to under 30 seconds. Which solution should the database specialist recommend?

Accepted Answer

Migrate the database to Amazon Aurora MySQL.. Amazon Aurora MySQL is designed to reduce failover time significantly compared to standard RDS MySQL Multi-AZ. Aurora typically completes failover in under 30 seconds by using a shared distributed storage volume across multiple Availability Zones, allowing the primary instance to fail over to a read replica without the need to replay redo logs or perform crash recovery. This meets the requirement of reducing failover time to under 30 seconds.

Answer

Create a read replica and promote it during failover.

Answer

Increase the DB instance class to a larger size.

Answer

Implement Amazon RDS Proxy to handle connection draining.

Question 9

A company is designing a database for an IoT application that ingests millions of small sensor readings per second. The data is time-series and queries are mostly range scans over time. The company needs a cost-effective solution with high write throughput. Which AWS service should the database specialist recommend?

Accepted Answer

Amazon Timestream. Amazon Timestream is a purpose-built time-series database designed for IoT and operational applications that ingest millions of data points per second. It automatically manages storage tiers (in-memory and magnetic) to optimize cost, and its query engine is optimized for range scans over time, making it the most cost-effective and high-throughput choice for this workload.

Answer

Amazon Redshift with auto-ingest

Answer

Amazon RDS for PostgreSQL with pg_partman extension

Answer

Amazon DynamoDB with time-series data modeling

Question 10

A company is designing a database for an e-commerce platform that needs to store product catalog data. The data is highly relational with many-to-many relationships between products, categories, and suppliers. The platform requires ACID transactions and complex joins. Which TWO AWS database solutions are suitable for this workload? (Choose TWO.)

Accepted Answer

Amazon Aurora MySQL. Amazon Aurora MySQL is a fully ACID-compliant relational database that supports complex joins and many-to-many relationships through foreign keys and junction tables. It is optimized for high-throughput e-commerce workloads with features like auto-scaling storage and up to 15 low-latency read replicas, making it suitable for product catalog data that requires transactional consistency.

Answer

Amazon ElastiCache for Redis

Answer

Amazon Neptune

Answer

Amazon DynamoDB

Question 11

A company is designing a database for a social media application that requires storing user profiles, posts, and follower relationships. The application needs low-latency queries for user timelines and social graph traversals. Which TWO AWS database services should the database specialist consider? (Choose TWO.)

Accepted Answer

Amazon Neptune. Amazon Neptune is a fully managed graph database service optimized for storing and querying highly connected data, such as social graphs. It supports property graph and RDF models, enabling low-latency traversals of follower relationships and user timelines using Gremlin or SPARQL queries.

Answer

Amazon Timestream

Answer

Amazon Redshift

Answer

Amazon RDS for MySQL

Question 12

Refer to the exhibit. A company uses this DynamoDB table to store user session data. The application frequently queries by user_id alone to get all sessions for a user. However, the query is slow. What is the most likely cause?

Accepted Answer

The table's partition key is session_id, not user_id, so querying by user_id requires a scan.. The table's primary key is session_id, not user_id. Querying by user_id without a secondary index forces DynamoDB to perform a full table scan, which reads every item and is significantly slower than a query operation. This is the most likely cause of the slow performance.

Answer

The table has no sort key on user_id.

Answer

The table has too many items, causing slow scans.

Answer

The provisioned read capacity is too low.

Question 13

Refer to the exhibit. A database specialist is troubleshooting an issue where an application cannot connect to an RDS for MySQL instance using IAM database authentication. The application uses the database user 'db_user1'. The IAM policy shown is attached to the IAM role used by the application. What is the most likely reason for the connection failure?

Accepted Answer

The resource ARN in the policy uses an incorrect RDS resource ID.. IAM database authentication for RDS MySQL requires the resource ARN in the IAM policy to include the correct RDS resource ID (the 'db-xxxxx' identifier from the RDS console), not the DB instance name or endpoint. If the ARN uses an incorrect resource ID, the policy will not match the target RDS instance, causing the authentication to fail even if the user name and action are correct.

Answer

The action 'rds-db:connect' is not allowed for RDS MySQL.

Answer

The policy should have 'Deny' effect instead of 'Allow'.

Answer

The database user name in the ARN must be 'admin', not 'db_user1'.

Question 14

A company runs a financial analytics platform on Amazon DynamoDB. The table stores transaction records with a partition key of account_id and a sort key of transaction_timestamp. Each account has thousands of transactions. The application frequently queries the most recent transactions for a given account_id, sorted by timestamp in descending order. Recently, as the number of accounts grew, the query latency increased significantly. The DynamoDB table has provisioned read capacity of 10,000 RCUs, and CloudWatch metrics show that consumed read capacity is at 60%. The database specialist suspects that the issue is due to the query pattern. Which action should the database specialist take to reduce query latency?

Accepted Answer

Create a Global Secondary Index (GSI) with account_id as partition key and transaction_timestamp as sort key, and query the GSI with ScanIndexForward set to false.. Creating a Global Secondary Index (GSI) with account_id as the partition key and transaction_timestamp as the sort key allows the application to query the most recent transactions for a given account_id efficiently. By setting ScanIndexForward to false in the query, DynamoDB returns items in descending order by sort key, which directly matches the query pattern. This avoids the need to scan all transactions for an account and then sort them, significantly reducing latency. The existing table's sort key is transaction_timestamp, but the query pattern requires descending order; the GSI provides an optimized access path without changing the base table structure.

Answer

Migrate the table to Amazon Aurora PostgreSQL with a secondary index on account_id and transaction_timestamp.

Answer

Enable DynamoDB Accelerator (DAX) to cache query results.

Answer

Increase the provisioned read capacity to 20,000 RCUs to handle the load.

Question 15

A company runs a customer relationship management (CRM) application on Amazon RDS for PostgreSQL. The application stores customer data in a table with over 50 million rows. The company recently added a new query that searches for customers by their email domain (e.g., '@example.com'). The query uses a LIKE pattern: 'WHERE email LIKE ''%@example.com'''. The query takes over 30 seconds to complete. The DBA has already created a B-tree index on the email column, but it does not help. Which action should the database specialist recommend to improve query performance?

Accepted Answer

Create a trigram index (using pg_trgm extension) on the email column.. The query uses a leading wildcard LIKE pattern ('%@example.com'), which prevents a standard B-tree index from being used because the search string does not have a fixed prefix. A trigram index, provided by the pg_trgm extension, breaks strings into three-character substrings (trigrams) and allows the database to efficiently match patterns with leading wildcards. This index type is specifically designed for fuzzy text matching and LIKE queries with wildcards, reducing the query time from over 30 seconds to milliseconds.

Answer

Create a hash index on the email column.

Answer

Increase the shared_buffers parameter to improve caching.

Answer

Create a B-tree index on the reversed email string.