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

Question 1

A company is designing a multi-tenant SaaS application on Amazon RDS for PostgreSQL. Each tenant's data must be isolated for security and performance. The application has millions of tenants, with most tenants having small datasets (under 100 MB). Which database design pattern is MOST cost-effective and operationally efficient?

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

Accepted Answer

Use a single RDS instance with a shared schema and implement Row-Level Security (RLS) policies based on tenant_id.. Using a single RDS for PostgreSQL instance with Row-Level Security (RLS) allows you to isolate tenant data at the row level based on a tenant_id column, without the overhead of managing millions of separate schemas or tables. This design is both cost-effective (single instance, no per-tenant provisioning) and operationally efficient (simple schema management, no connection pooling issues), while still meeting security and performance isolation requirements for small datasets under 100 MB.

Answer

Use Amazon DynamoDB with a separate table per tenant.

Answer

Use a single RDS instance with a separate schema per tenant.

Answer

Use a separate Amazon RDS for PostgreSQL instance per tenant.

Question 2

A gaming company uses Amazon DynamoDB for player session data. Each session has a partition key of `game_id` and a sort key of `session_id`. The table has a global secondary index (GSI) on `player_id` for leaderboard queries. Recently, the company noticed that write traffic to the GSI is causing throttling on the base table, even though the base table's write capacity is not fully utilized. What is the MOST likely cause?

Accepted Answer

The GSI is not designed with a high-cardinality partition key, causing write hot spots on the GSI.. A global secondary index (GSI) has its own provisioned read and write capacity, separate from the base table. If the GSI's partition key (player_id) has low cardinality (e.g., only a few distinct player_id values), writes to the base table will concentrate on a small number of GSI partitions, causing throttling on the GSI. This throttling on the GSI then back-pressures the base table, resulting in write throttling on the base table even if its own write capacity is underutilized.

Answer

The application is using strongly consistent reads on the GSI, which consumes double the read capacity.

Answer

Point-in-time recovery (PITR) is enabled, consuming extra write capacity.

Answer

The table's auto-scaling settings are not configured correctly for the GSI.

Question 3

A company is migrating a MySQL database to Amazon Aurora MySQL. The current database uses multi-statement transactions with read committed isolation level. The application frequently encounters deadlocks on the source database. Which Aurora MySQL feature can help reduce deadlocks without application changes?

Accepted Answer

Use Amazon RDS Proxy to pool and share database connections.. RDS Proxy helps reduce deadlocks by pooling and reusing database connections, which minimizes the overhead of establishing new connections and reduces contention on database resources. In MySQL, deadlocks often occur when multiple transactions compete for the same resources under high connection churn; by maintaining a stable pool of connections, RDS Proxy lowers the probability of concurrent conflicting locks. Since the proxy is transparent to the application, no code changes are required to benefit from this behavior.

Answer

Use Amazon Aurora Auto Scaling to automatically adjust the number of replicas.

Answer

Use Amazon Aurora Global Database to replicate data to multiple regions.

Answer

Use Amazon Aurora Backtrack to quickly revert transactions.

Question 4

A social media startup is using Amazon ElastiCache for Redis to cache user profiles. The cache currently has a 24-hour TTL. The application experiences a sudden spike in traffic after a celebrity mentions the service, causing the cache to be flooded with requests for uncached profiles. This results in high latency and database load. Which design pattern should the company implement to prevent this in the future?

Accepted Answer

Implement a local cache in each application instance to reduce load on the centralized Redis cluster.. Implementing a local cache (e.g., using a library like Caffeine or Guava) in each application instance reduces the number of requests hitting the centralized Redis cluster during a traffic spike. This pattern, often called a multi-tier or near-cache, absorbs repeated reads for the same uncached profiles locally, preventing cache flooding and database overload without relying solely on Redis TTL adjustments.

Answer

Use a read-through cache with a longer TTL (e.g., 48 hours).

Answer

Use a write-through cache with a longer TTL (e.g., 48 hours).

Answer

Use a write-through cache with a shorter TTL (e.g., 1 hour).

Question 5

A financial services company is designing a ledger system using Amazon QLDB. The application records transactions that must never be modified or deleted. The company expects high write throughput and needs to ensure that the ledger can handle the load without throttling. Which design consideration is MOST important to achieve this?

Accepted Answer

Batch multiple document inserts into a single transaction to reduce the number of transactions.. Amazon QLDB charges per transaction (document insert, update, or delete) and has a maximum throughput limit of 1,000 transactions per second per ledger. By batching multiple document inserts into a single transaction, you reduce the number of transactions, thereby staying within the throughput limit while still achieving high write throughput. This approach directly addresses the need to avoid throttling without sacrificing the immutability requirements of the ledger system.

Answer

Partition the ledger table by transaction date to distribute write load.

Answer

Create multiple ledgers and distribute writes across them.

Answer

Enable auto-scaling on the ledger to handle bursts of traffic.

Question 6

A company is using Amazon DynamoDB for a shopping cart application. The table has a partition key of `user_id` and a sort key of `item_id`. The application performs frequent updates to the `quantity` attribute. The company notices that write requests are being throttled during peak hours. Which TWO actions would help reduce throttling? (Choose two.)

Accepted Answer

Increase the provisioned write capacity for the table.. Increasing the provisioned write capacity directly raises the number of write capacity units (WCUs) available per second, allowing more write requests to succeed without being throttled. Since the application performs frequent updates to the `quantity` attribute, which consumes write capacity, adding more capacity alleviates throttling during peak hours.

Answer

Use conditional writes to prevent overwrites.

Answer

Enable DynamoDB Streams to process writes asynchronously.

Answer

Enable DynamoDB Accelerator (DAX) for the table.

Question 7

A company is migrating an on-premises MongoDB database to Amazon DocumentDB. The database stores IoT sensor data with time-series characteristics. The application performs range queries on timestamp fields and updates recent documents frequently. Which THREE aspects of DocumentDB should the company consider to optimize performance for this workload? (Choose three.)

Accepted Answer

Implement sharding to distribute write load across multiple instances.. DocumentDB does not support native sharding like MongoDB; however, for workloads with high write throughput, you can distribute writes by using multiple DocumentDB instances and routing writes based on a shard key in the application layer. This helps avoid write bottlenecks on a single instance and scales write capacity horizontally.

Answer

Use global secondary indexes to speed up queries on the timestamp field.

Answer

Use a single large instance class to avoid sharding complexity.

Question 8

A gaming company uses Amazon DynamoDB as the primary database for their player sessions. The player sessions table has a partition key of 'player_id' and a sort key of 'session_start_time'. The application frequently queries for recent sessions of a specific player, using the query API with 'player_id' and a filter on 'session_start_time' for the last 24 hours. The average item size is 5 KB. The company notices high latency on these queries during peak hours. The table has 10 Read Capacity Units (RCUs) provisioned. There are no indexes. Which design change would MOST improve query performance?

Accepted Answer

Create a Local Secondary Index (LSI) with the same partition key and a sort key of 'session_start_time', and query the index instead of the table.. Creating a Local Secondary Index (LSI) with the same partition key (player_id) and sort key (session_start_time) allows DynamoDB to efficiently retrieve items for a specific player sorted by session_start_time without scanning and filtering. The current query uses a filter on session_start_time after retrieving all sessions for the player, which wastes read capacity and increases latency. Querying the LSI directly uses the sort key to limit the data read to only the last 24 hours, reducing the read footprint and improving performance.

Answer

Increase the RCUs to 100.

Answer

Add a random suffix to the partition key values to distribute writes across partitions.

Answer

Create a Global Secondary Index (GSI) with partition key 'event_type' and sort key 'session_start_time' and query the GSI.

Question 9

A financial services company uses Amazon RDS for MySQL to store transaction data. The database has a single table 'transactions' with 500 million rows. The table has an auto-increment primary key and an index on 'transaction_date'. The company runs a monthly report that aggregates transactions by account_id and transaction_date. The report query uses a GROUP BY on account_id and transaction_date, and scans the entire table. The query takes over 2 hours to complete and often times out. The DBA suggests creating a materialized view. However, the company wants to minimize operational overhead. Which solution meets the requirements with the LEAST operational overhead?

Accepted Answer

Migrate the reporting workload to Amazon Redshift by loading the transactions table into Redshift and running the report query there.. Amazon Redshift is purpose-built for large-scale analytical queries. By migrating the reporting workload to Redshift, the company offloads the heavy aggregation from the transactional RDS instance to a columnar storage engine that can scan and aggregate 500 million rows efficiently using massively parallel processing (MPP). This approach requires no changes to the existing RDS database and minimizes operational overhead compared to managing a materialized view or manual indexing.

Answer

Increase the RDS instance size to the largest available to improve performance.

Answer

Create a materialized view in MySQL that pre-aggregates the data and refreshes it nightly.

Answer

Add a composite index on (account_id, transaction_date) to speed up the GROUP BY.

Question 10

A company is designing a database for an e-commerce application that needs to store product catalog data. The data is highly structured with relationships between products, categories, and suppliers. The application requires ACID transactions to maintain data integrity when updating inventory. The company expects moderate read and write traffic. Which AWS database service is MOST suitable for this workload?

Accepted Answer

Amazon RDS for MySQL. Amazon RDS for MySQL is the most suitable choice because it provides a fully managed relational database that supports ACID transactions, which are essential for maintaining data integrity during inventory updates. The highly structured nature of product catalog data with relationships between products, categories, and suppliers maps naturally to MySQL's relational model with foreign keys and joins. RDS for MySQL handles moderate read/write traffic efficiently and offers features like Multi-AZ for high availability and read replicas for scaling reads.

Answer

Amazon ElastiCache for Redis

Answer

Amazon Neptune

Answer

Amazon DynamoDB

Question 11

A social media company uses Amazon DynamoDB to store user posts. The table has a partition key of 'user_id' and a sort key of 'post_timestamp'. Each item is about 10 KB. The application needs to retrieve all posts for a given user within a date range. The company recently added a new feature that allows users to 'like' posts, and they store the like count as an attribute in the post item. The like count is updated frequently. The application experiences high write throttling on the table. The table has 1000 WCUs provisioned. The write pattern is bursty. Which design change would MOST effectively reduce write throttling?

Accepted Answer

Add a random suffix to the user_id partition key to distribute writes across multiple partitions.. The write throttling is caused by a 'hot partition' — all writes for a given user_id go to the same partition, and the bursty write pattern (e.g., many likes on a single post) exceeds the partition's 1,000 WCU limit (1,000 write capacity units per partition). Adding a random suffix to the user_id partition key distributes writes across multiple partitions, effectively increasing the write throughput for that logical user's data. This is a common design pattern for DynamoDB to handle high-traffic items without increasing provisioned capacity.

Answer

Increase the WCUs to 5000.

Answer

Enable DynamoDB Accelerator (DAX) to cache writes.

Answer

Create a Global Secondary Index (GSI) on the like count attribute.

Question 12

A company is designing a database for an IoT application that ingests sensor data from thousands of devices. Each device sends a reading every minute. The data includes device_id, timestamp, temperature, humidity, and pressure. The application needs to store this data and support queries that retrieve all readings for a specific device within a time range. The company expects high write throughput and moderate read frequency. The data must be stored with high durability. Which TWO database designs are appropriate for this workload? (Choose TWO.)

Accepted Answer

Use Amazon DynamoDB with device_id as partition key and timestamp as sort key.. DynamoDB's partition key (device_id) and sort key (timestamp) design allows efficient retrieval of all readings for a specific device within a time range using a Query operation with a KeyConditionExpression on the sort key. This schema supports high write throughput by distributing writes across partitions based on device_id, and DynamoDB's multi-AZ replication provides high durability.

Answer

Use Amazon DynamoDB with device_id as partition key and store all readings for a device as a list attribute in a single item, updating the list every minute.

Answer

Use Amazon S3 to store compressed JSON files per device per hour, and query using Amazon Athena.

Answer

Use Amazon RDS for MySQL with a single table and index on device_id and timestamp.

Question 13

A company runs a customer relationship management (CRM) application on Amazon RDS for PostgreSQL. The application has a table 'customers' with columns: id (primary key), name, email, signup_date, and status. The table has 10 million rows. The application frequently queries by email address. The company also needs to run analytical queries that aggregate customers by signup_date and status. The DBA notices that the index on email is heavily used and causes high write latency. The company wants to improve write performance without sacrificing read performance for email queries. Which THREE actions should the database specialist take? (Choose THREE.)

Accepted Answer

Create a BRIN index on signup_date.. A BRIN (Block Range INdex) index on signup_date is ideal for large tables where data is naturally ordered by insertion time. BRIN indexes are much smaller and have lower write overhead than B-tree indexes, making them suitable for analytical queries on signup_date without significantly impacting write performance.

Answer

Remove the index on email to reduce write latency.

Answer

Create a GIN index on email.

Question 14

A company runs a multi-tenant SaaS application on Amazon DynamoDB. Each tenant's data is stored in a separate table named with a tenant-specific prefix (e.g., tenant1_orders, tenant2_orders). The application uses DynamoDB Streams to replicate data to a central analytics table. Recently, the company added a new large tenant that generates 10x more write traffic than any other tenant. The DynamoDB Streams for the large tenant's table is falling behind by several hours, causing stale data in the analytics table. The company has already increased the write capacity of the large tenant's table to 50,000 WCUs, but the streams lag persists. The analytics table is also in DynamoDB and uses a Global Secondary Index (GSI) for querying. The streams processing Lambda function performs simple transformations and writes to the analytics table. The Lambda function is not throttled. Which action would resolve the streams lag?

Accepted Answer

Enable DynamoDB on-demand mode for the large tenant's table to allow automatic scaling of stream shards.. DynamoDB Streams shards are directly tied to the physical partitions of the table. When a table is in provisioned mode, the number of stream shards is fixed and determined by the table's partitions, which cannot scale independently. Enabling on-demand mode allows DynamoDB to automatically split partitions and thus increase the number of stream shards, enabling higher stream throughput to keep up with the large tenant's write volume. This directly addresses the root cause of the streams lag without requiring manual partition management.

Answer

Remove the GSI from the analytics table to reduce write amplification.

Answer

Increase the Lambda function's reserved concurrency to the maximum.

Answer

Increase the write capacity of the large tenant's table to 100,000 WCUs.

Question 15

A logistics company uses Amazon RDS for MySQL to track package shipments. The 'shipments' table contains 200 million rows and has a primary key on 'shipment_id' (UUID). The application frequently queries for shipments by 'tracking_number', which is a unique string of 20 characters. The DBA created a B-tree index on tracking_number. The queries by tracking_number are fast, but inserts are becoming slower over time. The table has 50 GB of data. The company plans to double the insert rate next month. The database is a db.r5.large instance with 500 GB of Provisioned IOPS SSD storage. The instance's CPU utilization is below 30%, and there is no lock contention. What should the database specialist do to improve insert performance?

Accepted Answer

Change the primary key from UUID to an auto-increment integer, and keep the tracking_number index.. UUID primary keys cause random writes and index fragmentation, degrading insert performance as the table grows. Switching to an auto-increment integer primary key allows sequential writes to the clustered index, reducing page splits and improving insert throughput. The B-tree index on tracking_number remains to support fast queries, while the new primary key eliminates the UUID write overhead.

Answer

Add a read replica and route insert queries to the replica.

Answer

Drop the index on tracking_number to reduce write overhead.

Answer

Increase the provisioned IOPS to 20,000.