DBS-C01 Workload-Specific Database Design Practice Question
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?
⚠ Common exam trap
The trap here is that candidates often focus on index overhead or IOPS as the cause of slow inserts, overlooking the fundamental impact of UUID fragmentation on clustered index write performance.
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
✓
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 analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Add a read replica and route insert queries to the replica.
Why it's wrong here
Read replicas are for read scaling, not for improving write performance.
- ✗
Drop the index on tracking_number to reduce write overhead.
Why it's wrong here
Dropping the index would slow down queries by tracking_number, which is a frequent access pattern.
- ✓
Change the primary key from UUID to an auto-increment integer, and keep the tracking_number index.
Why this is correct
An auto-increment primary key allows sequential inserts, reducing page splits and improving insert speed.
- ✗
Increase the provisioned IOPS to 20,000.
Why it's wrong here
IOPS is not the bottleneck; the issue is index fragmentation due to random primary key.
Go deeper
Related to this question
About these practice questions
This DBS-C01 question is part of Courseiva's 1,663-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
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.