Workload-Specific Database Design practice questions

Partition the table by the filter column

Why wrong: Partitioning helps with large table management but doesn't optimize queries filtering on low-selectivity columns.

Use a read replica to offload reporting queries

Why wrong: Read replicas scale read traffic but do not optimize the performance of an individual query.

Increase the provisioned read IOPS for the RDS instance

Why wrong: Provisioned IOPS improves I/O performance but does not directly optimize query execution plans.

Create an AWS Lambda function that runs every hour and deletes expired session data

Why wrong: Lambda-based deletion is possible but add complexity and potential cost, and is not the simplest design.

Store session data in Amazon S3 with a lifecycle policy to delete objects after 24 hours

Why wrong: S3 can handle session data but does not provide the required single-digit millisecond latency for session token lookups.

Use DynamoDB Accelerator (DAX) to cache session data and set a 24-hour TTL on the cache

Why wrong: DAX is an in-memory cache that can improve latency but does not automatically delete items from the DynamoDB table.

AWS Data Pipeline to export data from Oracle and import into Aurora PostgreSQL

Why wrong: Data Pipeline is not designed for database migrations and does not support ongoing replication.

AWS Schema Conversion Tool (AWS SCT) to convert the schema and then use native PostgreSQL tools to migrate data

Why wrong: AWS SCT converts schema but does not handle ongoing replication; native tools may require significant downtime.

AWS Glue to extract data from Oracle and load into Aurora PostgreSQL

Why wrong: AWS Glue is an ETL service, not optimized for database migrations with minimal downtime.

Use a partition key of post ID and a local secondary index on the followee ID

Why wrong: Partition key as post ID does not support querying by follower or followee efficiently.

Use a single table with a scan operation and filter on the followee attribute

Why wrong: Scan operations are expensive and not recommended for low-latency access patterns.

Design the table with a partition key of user ID and a sort key of timestamp, and create a global secondary index (GSI) on followee ID

Why wrong: A GSI with followee ID as partition key might still lead to hot partitions if a popular user has many followers.

Migrate the JSON data to Amazon DynamoDB and use DynamoDB's document model

Why wrong: Migrating to DynamoDB would require application changes and may not be necessary if the data can be handled within RDS.

Store JSON documents in a VARCHAR(MAX) column and use LIKE operations for queries

Why wrong: LIKE operations on VARCHAR columns are inefficient and do not support JSON path queries.

Store JSON documents as BLOBs and parse them in application code

Why wrong: This approach puts parsing burden on the application and prevents database-level optimization.

Use a single Aurora cluster with read replicas for each tenant

Why wrong: Read replicas only offload read traffic; write traffic still hits the primary instance, causing contention.

Migrate all tenants to Amazon DynamoDB and use DynamoDB Accelerator (DAX) for caching

Why wrong: DynamoDB is a different database model and may not fit relational workloads.

Use Amazon RDS Proxy to pool connections and limit throughput per tenant

Why wrong: RDS Proxy manages connections but does not provide workload isolation.

Change the partition key to 'timestamp' to improve read distribution

Why wrong: Changing the partition key would require re-architecting the application and may not solve the issue.

Decrease the provisioned write capacity units (WCU) to free up resources for reads

Why wrong: Read and write capacities are separate; decreasing WCU does not affect read throttling.

Combine the orders and inventory tables into a single table to avoid multiple table locks

Why wrong: Combining tables denormalizes the schema and is not a standard solution for deadlocks.

Use SELECT ... FOR UPDATE on both tables before updating

Why wrong: This still requires a consistent lock order; if two transactions lock in different order, deadlock can still occur.

Change the transaction isolation level to READ UNCOMMITTED

Why wrong: READ UNCOMMITTED may reduce locking but can lead to dirty reads and other concurrency issues.

Allows the application to perform all operations on the UserSessions table without restrictions

Why wrong: The condition restricts access based on the partition key; it does not grant unrestricted access.

Allows the application to read but not write items in the UserSessions table

Why wrong: The policy allows PutItem, UpdateItem, and DeleteItem, so write operations are permitted.

Allows the application to access only the UserSessions table but not other tables

Why wrong: The policy grants access to the UserSessions table only, but with the condition, it further restricts to specific items.

Use Amazon DynamoDB with a separate table per tenant.

Why wrong: DynamoDB is NoSQL; the application may require relational features, and separate tables for millions of tenants is not scalable.

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

Why wrong: Managing millions of schemas in one database is inefficient and can cause performance issues.

Use a separate Amazon RDS for PostgreSQL instance per tenant.

Why wrong: This is not cost-effective for millions of small tenants; it would create massive operational overhead and cost.

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

Why wrong: Consistent reads affect read capacity, not write capacity.

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

Why wrong: PITR uses separate storage and does not consume write capacity from the table.

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

Why wrong: Auto-scaling adjusts table capacity, but if a GSI partition is hot, throttling can still occur.

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

Why wrong: Auto Scaling handles read traffic, not deadlocks.

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

Why wrong: Global Database is for disaster recovery, not deadlock reduction.

Use Amazon Aurora Backtrack to quickly revert transactions.

Why wrong: Backtrack recovers from unintended changes, but does not prevent deadlocks.

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

Why wrong: Read-through still causes a thundering herd on the first miss for each item.

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

Why wrong: Write-through helps keep cache updated but does not prevent initial misses for uncached items.

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

Why wrong: Shorter TTL increases cache misses, worsening the problem.

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

Why wrong: QLDB does not support partitioning; it is a single ledger.

Create multiple ledgers and distribute writes across them.

Why wrong: Multiple ledgers add complexity and do not increase the throughput of a single ledger.

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

Why wrong: QLDB automatically scales within its limits, but cannot exceed the maximum throughput per ledger.

Use conditional writes to prevent overwrites.

Why wrong: Conditional writes reduce unnecessary updates but do not address throttling from high write volume.

Enable DynamoDB Streams to process writes asynchronously.

Why wrong: Streams capture changes but do not reduce write throttling.

Enable DynamoDB Accelerator (DAX) for the table.

Why wrong: DAX is a read cache and does not improve write throughput.

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

Why wrong: DocumentDB does not support global secondary indexes; it uses standard secondary indexes.

Use a single large instance class to avoid sharding complexity.

Why wrong: For high write throughput, sharding is more scalable than a single large instance.

Increase the RCUs to 100.

Why wrong: Increasing RCUs increases throughput but does not reduce latency caused by filter expression on sort key.

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

Why wrong: Sharding would scatter data and break the ability to query by player_id efficiently.

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

Why wrong: The query is by player_id, not event_type, so this GSI would not help.

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

Why wrong: Scaling up may help but is not the most efficient; the query still scans 500M rows.

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

Why wrong: MySQL does not natively support materialized views; this requires manual implementation and adds operational overhead.

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

Why wrong: A composite index does not help a full table scan with aggregation; MySQL may still scan the entire table.

Amazon ElastiCache for Redis

Why wrong: ElastiCache is a caching service, not a durable database for ACID transactions.

Amazon Neptune

Why wrong: Neptune is a graph database, which is overkill for simple relational data.

Amazon DynamoDB

Why wrong: DynamoDB is NoSQL and does not natively support complex joins and ACID transactions across multiple items.

Increase the WCUs to 5000.

Why wrong: Increasing capacity helps but is not the most efficient; sharding is more cost-effective.

Enable DynamoDB Accelerator (DAX) to cache writes.

Why wrong: DAX is a read cache, it does not help write throttling.

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

Why wrong: A GSI does not reduce write throttling on the base table.