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

Question 1

A company is designing a database for a ride-sharing application that needs to store real-time driver locations and trip history. The application requires low-latency updates to driver locations (every few seconds) and the ability to query nearby drivers within a radius. The company expects millions of drivers and trips. Which AWS database service should the database specialist recommend for storing real-time driver locations and supporting proximity queries?

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

Accepted Answer

Amazon DynamoDB with a Geohash-based partition key and a Global Secondary Index. Amazon DynamoDB with a Geohash-based partition key and a Global Secondary Index is the correct choice because it provides the low-latency writes (single-digit milliseconds) required for updating driver locations every few seconds, while the Geohash-based key enables efficient proximity queries by grouping nearby drivers into the same partition. The Global Secondary Index allows querying by geohash prefix to find drivers within a radius, scaling to millions of drivers and trips with DynamoDB's auto-scaling and fully managed infrastructure.

Answer

Amazon RDS for PostgreSQL with PostGIS extension

Answer

Amazon ElastiCache for Redis with geospatial data types

Answer

Amazon Timestream

Question 2

A company runs a multi-tenant SaaS application on Amazon RDS for PostgreSQL. Each tenant has an isolated database. Recently, the application experienced a sudden increase in connection errors and slow query performance. Amazon RDS instance metrics show high CPU utilization and a high number of database connections. The application uses connection pooling with PgBouncer running on an EC2 instance. The team suspects the issue is due to a few noisy tenants opening too many connections. The current architecture uses one RDS instance per tenant. The company wants to optimize for workload-specific database design to handle noisy tenants without affecting other tenants. Which design should be implemented to isolate noisy tenants and reduce costs?

Accepted Answer

Create separate RDS instances for large tenants and use a single RDS instance for small tenants, with PgBouncer connection pooling per instance.. It directly addresses the need to isolate noisy tenants by creating separate RDS instances for large (noisy) tenants while consolidating small tenants onto a single instance, each fronted by its own PgBouncer connection pool. This design prevents a single tenant's connection surge from affecting others, optimizes costs by avoiding over-provisioning for all tenants, and aligns with workload-specific database design principles for multi-tenant SaaS on RDS for PostgreSQL.

Answer

Use RDS for PostgreSQL with pg_partman to partition data by tenant and implement connection limits per tenant using PostgreSQL advisory locks.

Answer

Replace RDS with Amazon Aurora PostgreSQL and use Aurora Auto Scaling to handle connection spikes.

Answer

Move all tenants to a single RDS instance with separate schemas and use RDS Proxy to manage connections.

Question 3

A financial services company uses Amazon DynamoDB to store transaction records. Each transaction has a partition key of customer_id and a sort key of transaction_timestamp. The application queries transactions for a specific customer within a date range. Recently, the query latency increased significantly for customers with a large number of transactions. The company needs to improve query performance without changing the application code. The table is provisioned with 5000 RCUs and 2000 WCUs. Which design change should be made to optimize for this workload?

Accepted Answer

Create a global secondary index with customer_id as partition key and transaction_timestamp as sort key.. Creating a global secondary index (GSI) with customer_id as the partition key and transaction_timestamp as the sort key allows efficient querying of transactions for a specific customer within a date range. The existing table's sort key is transaction_timestamp, but the GSI provides a separate index optimized for this access pattern, avoiding full table scans on large customer partitions. This improves query performance without requiring application code changes, as the application can query the GSI directly.

Answer

Enable DynamoDB Accelerator (DAX) on the table.

Answer

Increase the provisioned RCUs to 10000.

Answer

Change the sort key to a composite key including a tenant identifier.

Question 4

A company is migrating an on-premises Oracle database to Amazon Aurora PostgreSQL. The database contains a large table that is partitioned by date. The company wants to minimize downtime during migration and ensure compatibility. Which TWO actions should the company take?

Accepted Answer

Use AWS Database Migration Service (DMS) with ongoing replication to migrate the data.. AWS DMS with ongoing replication (change data capture) allows continuous synchronization from the source Oracle database to the target Aurora PostgreSQL, minimizing downtime by keeping the target nearly up-to-date until the final cutover. This approach supports large partitioned tables and reduces the migration window compared to a full load only.

Answer

Migrate the database using a full load only and accept downtime.

Answer

Use AWS Schema Conversion Tool (SCT) to convert the schema automatically without any manual adjustments.

Answer

Replace the Oracle database with Amazon RDS for MySQL.

Question 5

A gaming company uses Amazon ElastiCache for Redis as a leaderboard for real-time game scores. The leaderboard is updated frequently by millions of users. The application uses sorted sets with player scores. Recently, the leaderboard update latency increased and the cache evictions spiked. The company needs to ensure low-latency updates and high availability. The current setup is a single Redis node. Which design should be implemented?

Accepted Answer

Use a Redis Cluster with multiple shards. Enable AOF persistence and use a read replica for the leaderboard queries.. Redis Cluster with multiple shards distributes the write load across shards, reducing per-node pressure and evictions. Enabling AOF persistence ensures durability, while using a read replica for leaderboard queries offloads read traffic from the primary shard, maintaining low-latency updates. This design provides both horizontal scaling and high availability, addressing the increased update latency and eviction spikes.

Answer

Upgrade to a larger single Redis node instance type to handle the load.

Answer

Replace Redis with DynamoDB for the leaderboard, using a global secondary index on score.

Answer

Use ElastiCache for Redis with cluster mode disabled and enable Multi-AZ.

Question 6

A company is designing a highly available e-commerce application using Amazon DynamoDB. The application requires strongly consistent reads for inventory data and eventual consistency for user session data. Which TWO design decisions should the company make?

Accepted Answer

Use strongly consistent reads for the inventory table by setting ConsistentRead=true in the query.. DynamoDB supports strongly consistent reads by setting the `ConsistentRead=true` parameter in the GetItem, Query, or Scan API calls. This ensures that the application always reads the most recent write, which is critical for inventory data where accuracy is paramount. Strongly consistent reads come at the cost of higher latency and lower throughput compared to eventually consistent reads, but they meet the requirement for inventory consistency.

Answer

Enable DynamoDB Streams on the inventory table to replicate data for disaster recovery.

Answer

Use DynamoDB Accelerator (DAX) for the inventory table to provide strongly consistent reads.

Answer

Use a single DynamoDB table for both inventory and session data with different partition keys.

Question 7

A media company stores video metadata in Amazon DynamoDB. Each record has a partition key of video_id and a sort key of uploaded_timestamp. The application frequently queries videos by genre and upload date. The access pattern is read-heavy with occasional writes. The table is provisioned with 3000 RCUs and 1000 WCUs. The company notices that queries by genre are slow and consume many RCUs. Which design change should be made to optimize for this workload?

Accepted Answer

Create a global secondary index (GSI) with genre as partition key and uploaded_timestamp as sort key.. Creating a Global Secondary Index (GSI) with genre as the partition key and uploaded_timestamp as the sort key allows efficient querying by genre and date without scanning the entire table. This directly supports the access pattern, reducing RCU consumption by using index key lookups instead of full table scans. The GSI is ideal for read-heavy workloads with occasional writes, as it offloads query traffic from the main table.

Answer

Use DynamoDB Accelerator (DAX) to cache query results.

Answer

Create a local secondary index (LSI) with genre as sort key and uploaded_timestamp as partition key.

Answer

Increase the provisioned RCUs to 6000.

Question 8

A healthcare company stores patient records in Amazon DynamoDB. Each record includes patient_id (partition key), visit_date (sort key), and a large JSON attribute for medical history. The application frequently queries recent visits for a patient and scans historical data for analytics. The scans on the medical history attribute cause high RCU consumption. The company wants to reduce costs and improve query performance. Which design should be implemented?

Accepted Answer

Move the medical history attribute to a separate table with patient_id as partition key and visit_date as sort key. Use DynamoDB Streams to keep both tables in sync.. It separates the large, infrequently accessed medical history attribute from the frequently queried core record, reducing the item size for common queries and thus lowering RCU consumption. By using DynamoDB Streams to synchronize the two tables, you maintain data consistency without adding complexity to the application, and queries against the main table become faster and cheaper since they no longer read the large JSON payload.

Answer

Compress the medical history attribute using gzip before storing in DynamoDB.

Answer

Enable DynamoDB Accelerator (DAX) for the table to cache frequent queries.

Answer

Use Amazon S3 to store the medical history as a separate object and reference it from DynamoDB.

Question 9

A company is designing a new application that requires a relational database with read replicas for reporting. The application has unpredictable traffic patterns. The company wants to minimize operational overhead and automatically scale compute capacity. Which TWO services should the company consider?

Accepted Answer

Amazon Aurora Serverless v2. The company requires a relational database with read replicas for reporting, minimal operational overhead, and automatic scaling of compute capacity. Amazon Aurora Serverless v2 (D) meets all these requirements: it is a relational database compatible with PostgreSQL and MySQL, automatically scales compute capacity based on demand, and supports up to 15 read replicas for reporting workloads. Amazon RDS Proxy (E) provides connection pooling to handle unpredictable traffic patterns efficiently, reducing overhead and improving scalability. Together, these two services fulfill the requirements without the need for an additional database instance, making option C unnecessary.

Answer

Amazon DynamoDB Accelerator (DAX)

Answer

Amazon RDS for MySQL with Multi-AZ

Answer

Amazon RDS for PostgreSQL with read replicas

Question 10

A startup is building a social media application that stores user posts in Amazon DynamoDB. The access pattern is to retrieve posts by user_id (partition key) sorted by post_timestamp (sort key) in descending order. The table has a global secondary index (GSI) with the same key structure but with different projection. The application reads from the GSI. Recently, the team noticed that writes to the base table are throttled during peak hours. The write capacity is balanced across partitions. Which design change should be made to reduce write throttling?

Accepted Answer

Add a write sharding pattern by appending a random suffix to the partition key.. The write throttling is caused by a hot partition, where a single partition key (user_id) receives a disproportionate number of writes. By appending a random suffix to the partition key, the writes are distributed evenly across multiple partitions, eliminating the hot spot. This is a well-known sharding pattern for DynamoDB when access patterns create uneven write traffic, and it does not require changing the read logic because the GSI can be queried with a sort key condition on post_timestamp.

Answer

Use DynamoDB Accelerator (DAX) for writes.

Answer

Increase the write capacity units (WCUs) on the base table.

Answer

Switch to on-demand capacity mode.

Question 11

A company runs an OLTP application on Amazon RDS for PostgreSQL. The database stores customer orders. The application frequently queries orders by customer_id and order_date. The orders table has 100 million rows. The query performance has degraded over time. The database has a single index on customer_id. The company needs to improve query performance without changing the application code. Which design change should be made?

Accepted Answer

Create a composite index on (customer_id, order_date).. The query performance has degraded because the existing single-column index on customer_id can filter by customer but still requires a full sort or scan within that customer's rows to satisfy the order_date condition. Creating a composite index on (customer_id, order_date) allows the database to use a single index seek to locate the exact rows matching both columns, eliminating the need for an additional sort or filter pass. This directly addresses the query pattern without any application code changes.

Answer

Partition the table by order_date using PostgreSQL declarative partitioning.

Answer

Upgrade to a larger RDS instance type.

Answer

Enable RDS Performance Insights to identify bottlenecks.

Question 12

A company is migrating a self-managed MongoDB database to Amazon DocumentDB. The database stores user profiles and activity logs. The activity logs are write-heavy and accessed infrequently. The company wants to optimize cost and performance for the workload. Which THREE design decisions should the company make?

Accepted Answer

Enable encryption at rest for both databases.. Enabling encryption at rest for Amazon DocumentDB is a security best practice that ensures data is encrypted using AWS Key Management Service (KMS) keys. This is a mandatory design consideration for compliance and data protection, and it does not significantly impact cost or performance for the workload described.

Answer

Use Amazon S3 for storing activity logs instead of DocumentDB.

Answer

Use a single DocumentDB cluster for both workloads with appropriate read replicas.

Question 13

A company runs a reporting application on Amazon Redshift. The application queries a large fact table that is distributed by a key. The report queries filter on a date column. The report performance is slow. The database has 10 nodes. The company wants to improve query performance by optimizing the table design. Which design change should be made?

Accepted Answer

Set the sort key to the date column.. Setting the sort key to the date column improves query performance by enabling range-restricted scans. When queries filter on a date column, Redshift uses zone maps to skip blocks that do not contain relevant data, drastically reducing the number of rows scanned. This is the most direct and cost-effective optimization for filter-heavy workloads on large fact tables.

Answer

Increase the number of nodes in the cluster.

Answer

Change the distribution style to ALL to avoid data redistribution.

Answer

Change the distribution style to KEY on the date column.

Question 14

A company uses Amazon RDS for MySQL to store e-commerce order data. The orders table has millions of rows and is frequently queried by order_id. The company also runs periodic reports that aggregate data by order_date. The reports are slow. The database has a primary key on order_id. The company needs to improve report performance without affecting OLTP queries. Which design change should be made?

Accepted Answer

Create a secondary index on order_date.. Creating a secondary index on order_date allows MySQL to quickly locate rows matching the report's date range without scanning the entire table, significantly improving aggregation performance. This index is separate from the primary key on order_id, so OLTP queries that filter by order_id remain unaffected. The index provides a balanced approach: it accelerates read-heavy reporting while adding minimal overhead to write operations.

Answer

Upgrade to a larger instance type.

Answer

Create a read replica and run reports on the replica.

Answer

Partition the table by order_date.

Question 15

A company is designing a disaster recovery strategy for an Amazon RDS for PostgreSQL database. The database is 2 TB in size. The company wants to recover to a different AWS Region with minimal data loss. Which TWO options meet these requirements?

Accepted Answer

Create a read replica in the other Region.. Both A and B are valid options for achieving minimal data loss when recovering to a different AWS Region. Option A uses Amazon RDS native cross-Region read replicas with PostgreSQL streaming replication, providing near real-time synchronization. Option B uses AWS Database Migration Service (DMS) with ongoing replication (change data capture) to replicate changes continuously, achieving a low RPO. Options C, D, and E involve snapshot-based solutions that are not continuous and therefore do not meet the minimal data loss requirement.

Answer

Take a manual snapshot and copy it to the other Region. Restore from the snapshot.

Answer

Enable automatic backups and copy automated snapshots to the other Region.

Answer

Use AWS Backup to schedule cross-Region backups.