Workload-Specific Database Design practice questions

Amazon ElastiCache for Redis

Why wrong: Amazon ElastiCache for Redis is an in-memory cache, not a persistent primary database. It does not provide strong consistency across multiple regions and is unsuitable as the primary datastore for a global e-commerce application.

Amazon Neptune

Why wrong: Amazon Neptune is a graph database optimized for connected data like social networks or fraud detection, not for general e-commerce workloads. It does not provide single-digit millisecond latency at scale for typical e-commerce operations.

Amazon DynamoDB with global tables

Why wrong: Amazon DynamoDB global tables provide single-digit millisecond read latency and multi-region replication, but cross-region reads are eventually consistent, not strongly consistent. Therefore, it does not satisfy the strong consistency requirement globally.

Scan the entire table and sort to get top scores

Why wrong: Scanning the entire table and sorting is inefficient and consumes excessive read capacity; it does not scale for frequent updates.

Use a single partition key for all players to easily query top scores

Why wrong: A single partition key creates a hot partition and prevents efficient sorted queries; it is a common anti-pattern.

Enable Multi-AZ deployment to improve read performance.

Why wrong: Multi-AZ improves availability, not query performance related to indexes.

Shard the collection across multiple DocumentDB clusters.

Why wrong: DocumentDB supports sharding, but that does not address the index limitation.

Use the MongoDB aggregation pipeline to bypass indexing.

Why wrong: Aggregation pipeline may still require indexes for performance; not a direct workaround.

Enable DynamoDB Accelerator (DAX) for the table

Why wrong: DAX provides an in-memory cache that reduces latency for repeated queries, but it does not reduce the number of read capacity units consumed on the table itself; it adds cost and complexity without improving the underlying query pattern.

Increase the read capacity units on the table

Why wrong: Increasing read capacity units raises costs and does not improve query efficiency; the existing sort key already allows efficient retrieval.

Use a global secondary index on post_timestamp

Why wrong: A GSI on post_timestamp lacks user_id as a partition key, so it cannot efficiently query posts for a single user; it would require a scan across all users.

Create a local secondary index on device_id and timestamp

Why wrong: Incorrect. The base table already has device_id as partition key and timestamp as sort key, enabling efficient range queries. Adding an LSI on the same attributes is redundant and does not improve performance or reduce cost.

Enable DynamoDB Accelerator (DAX)

Why wrong: Incorrect. DAX is an in-memory cache that improves read performance but adds cost for the DAX cluster. It does not reduce overall cost.

Create a global secondary index on timestamp

Why wrong: Incorrect. A GSI on timestamp allows queries by timestamp only, not by device_id. A query for a specific device_id would still require a full scan of the index, which is inefficient.

Use scan operations for frequent queries to reduce provisioned capacity

Why wrong: Scan is expensive and inefficient.

Use DynamoDB transactions to reduce read capacity consumption

Why wrong: Transactions consume more capacity.

Always use strongly consistent reads for all queries

Why wrong: Strongly consistent reads use more RCU and may not be necessary.

Create manual snapshots every day.

Why wrong: Manual snapshots do not support PITR; they are point-in-time snapshots but not incremental.

Enable deletion protection on the RDS instance.

Why wrong: Deletion protection prevents accidental deletion but does not affect PITR.

Enable Multi-AZ deployment for high availability.

Why wrong: Multi-AZ improves availability but is not required for PITR.

Use Amazon S3 to store and transfer database dump files.

Why wrong: Not necessary for migration.

Manually rewrite all PL/SQL packages to MySQL syntax.

Why wrong: SCT automates conversion.

Amazon Redshift

Why wrong: Amazon Redshift is a data warehouse, not designed for transactional Oracle workloads with complex stored procedures and triggers. It requires significant application changes and is not suitable for this migration.

Amazon DynamoDB

Why wrong: Amazon DynamoDB is a NoSQL database and does not support relational features like stored procedures and triggers in the same way. It would require a complete rewrite of the application.

Amazon Aurora PostgreSQL with Babelfish

Why wrong: Amazon Aurora PostgreSQL with Babelfish provides compatibility for SQL Server's T-SQL, not Oracle's PL/SQL. Migrating Oracle stored procedures and triggers would require extensive rewriting, contradicting the goal of minimizing application changes.

Create a global secondary index (GSI) with the same key structure but projecting only the required attributes.

Why wrong: A GSI with projection only includes a subset of attributes. Since the application retrieves all attributes, queries would need to fetch missing attributes from the base table, adding latency and defeating the purpose.

Enable DynamoDB Streams to replicate data to Amazon ElastiCache.

Why wrong: Replicating data via DynamoDB Streams to Amazon ElastiCache introduces asynchronous latency and does not address the high read throughput required by queries retrieving all attributes from a single partition. While this pattern is effective for implementing real-time event-driven architectures or offloading complex lookups in microservices, it fails to resolve the specific issue of large item scans within DynamoDB partitions that causes the observed query latency.

Increase the read capacity units (RCUs) on the table.

Why wrong: The table uses on-demand capacity, which automatically scales. Increasing RCUs is unnecessary and does not address the root cause of large data reads per query.

The read replica is under-provisioned causing replication lag.

Why wrong: Read replicas do not impact write latency on the primary.

The Write IOPS has exceeded the gp2 volume's maximum burst IOPS of 3,000.

Why wrong: 2,500 IOPS is below the burst maximum; the issue is credit exhaustion.

Multi-AZ synchronous replication is causing high latency.

Why wrong: Multi-AZ adds minimal latency.

Amazon DynamoDB

Why wrong: Amazon DynamoDB is a NoSQL database and does not support stored procedures or triggers in PL/SQL, requiring significant application rewrite.

Amazon S3

Why wrong: Amazon S3 is an object storage service, not a relational database, and cannot run stored procedures or triggers.

Amazon Redshift

Why wrong: Amazon Redshift is a data warehouse optimized for analytical queries, not for transactional workloads with complex stored procedures.

Amazon Aurora PostgreSQL with Babelfish

Why wrong: Amazon Aurora PostgreSQL with Babelfish provides compatibility with SQL Server, not Oracle. It would require converting PL/SQL to PostgreSQL-compatible code, which is not minimal.

Implement synchronous replication between writer and replicas.

Why wrong: Incorrect. Synchronous replication is not supported in Amazon Aurora; Aurora uses asynchronous replication, so this strategy is not available.

Use Aurora Auto Scaling to add read replicas during peak traffic.

Why wrong: Incorrect. Adding read replicas does not reduce replication lag; it can increase the replication load and potentially worsen lag because the writer must send changes to more replicas.

The buffer pool cache hit ratio is actually low

Why wrong: A low cache hit ratio would be an issue, but it's high.

The database instance type is too small for the write workload

Why wrong: Insufficient write capacity is not a problem with Aurora.

The read replicas are lagging behind the primary

Why wrong: Replica lag affects reads from replicas, not primary.

Amazon Neptune

Why wrong: Graph database, not for time-series.

Amazon RDS for PostgreSQL

Why wrong: Not optimized for high-ingestion time-series.

Amazon ElastiCache for Redis

Why wrong: Cache, not persistent store.

Enable Multi-AZ to distribute the load.

Why wrong: Multi-AZ does not distribute load.

Create read replicas to offload read traffic.

Why wrong: Does not address CPU due to connections.

Increase the DB instance size to a larger instance type.

Why wrong: May help but is not the most cost-effective.

The IAM policy does not include a condition for the table

Why wrong: Incorrect. The IAM policy does include a condition (LeadingKeys). The problem is not missing condition but likely the condition key value (sub) being wrong due to Cognito misconfiguration.

The table ARN is incorrect

Why wrong: Incorrect. An incorrect table ARN would result in access denied errors, not seeing other users' items.

The ForAllValues:StringEquals should be ForAnyValue:StringEquals

Why wrong: Incorrect. ForAllValues:StringEquals is the AWS-recommended operator for LeadingKeys. Switching to ForAnyValue would not fix the issue and may introduce security gaps.

Use DynamoDB Accelerator (DAX) to cache write operations

Why wrong: Incorrect. DAX is a read cache; it does not improve write performance and may add write latency.

Enable DynamoDB global tables to distribute writes across regions

Why wrong: Incorrect. Global tables replicate data across regions but do not directly improve write performance; they serve disaster recovery and low-latency reads.

Implement TTL to automatically delete old items

Why wrong: Incorrect. TTL automates deletion of old items but does not improve write performance.

Enable ElastiCache for Redis Cluster Mode to distribute data across shards.

Why wrong: Enabling cluster mode distributes data across shards, which can improve write throughput and scalability, but it does not increase total memory unless additional shards are added. It may even reduce hit ratio due to uneven key distribution. Not the most effective improvement.

Enable Redis persistence (AOF) and configure a replication group with a read replica.

Why wrong: Enabling persistence (AOF) and adding a read replica improves durability and read throughput, but does not increase the cache memory. The primary node still has the same memory limit, so evictions and low hit ratio persist. This does not effectively improve the hit ratio.

Decrease the TTL of session keys to force more frequent refreshes.

Why wrong: Decreasing the TTL of session keys causes keys to expire sooner, increasing the frequency of cache misses. This would worsen, not improve, the cache hit ratio.

Amazon ElastiCache for Redis with Time Series module.

Why wrong: ElastiCache is a cache, not a primary database for long-term storage.

Amazon Redshift for real-time queries and historical analysis.

Why wrong: Redshift is not designed for sub-second real-time queries on high-velocity writes.

Amazon Quantum Ledger Database (QLDB) for immutable time-series records.

Why wrong: QLDB is for ledger databases, not optimized for high-velocity time-series.