Which solution will meet these requirements?
Migrate the database to Amazon DynamoDB. Store the leaderboard data in two different tables. Use Apache HiveQL JOIN statements to build the leaderboard.
Keep the leaderboard data in the RDS DB instance. Provision a Multi-AZ deployment of an Amazon ElastiCache for Redis cluster.
Stream the leaderboard data by using Amazon Kinesis Data Firehose with an Amazon S3 bucket as the destination. Query the S3 bucket by using Amazon Athena for the leaderboard.
Add a read-only replica to the RDS DB instance. Add an RDS Proxy database proxy.
Explanations:
Migrating to DynamoDB and using HiveQL JOINs is not suitable for real-time leaderboard queries, as DynamoDB is not designed for complex joins and might lead to increased latency. Additionally, separating data into different tables complicates retrieval.
Using ElastiCache for Redis will significantly enhance performance by caching leaderboard data, reducing load on the RDS instance and allowing faster retrieval of player rankings, which is crucial for scalability during user surges.
Streaming data to S3 and querying with Athena introduces latency and is not optimal for real-time leaderboard updates. This approach may not handle the immediate ranking needs efficiently, especially during user surges.
Adding a read-only replica can help distribute read traffic, but it may not be sufficient alone to address the performance issues. An RDS Proxy would assist in managing connections but does not resolve the underlying slow query performance as effectively as caching with ElastiCache.
I value that the answer is:
Keep the leaderboard data in the RDS DB instance. Provision a Multi-AZ deployment of an Amazon ElastiCache for Redis cluster.