Optimal approach for scheduling and processing 5M time-based records in AWS

For processing 5 million time-based records that need evaluation at specific times, you're right to be exploring asynchronous processing patterns. Let me suggest some optimized approaches:

Your first approach using DynamoDB with a GSI on the date field is viable, but could be enhanced. Instead of scheduling a single daily compute job, consider implementing a queue-driven architecture where you:

1. Use a scheduled Lambda function to query the GSI for records due for processing
2. Push these records to an SQS queue
3. Have auto-scaling workers (Lambda or EC2 with Auto Scaling) process the queue

This pattern allows independent scaling of the processing component based on the actual workload volume.

Another efficient approach would be to combine Amazon EventBridge Scheduler with SQS:

• Store your records in DynamoDB
• When a record is created/updated, create a scheduled event in EventBridge Scheduler for the exact evaluation time
• When triggered, the scheduler sends the record ID to an SQS queue
• Processing workers consume from the queue and retrieve the full record from DynamoDB

This approach provides precise timing control and scales well for millions of records.

For records with evaluation times that may change, you could implement a "rolling window" approach:

• Group records by time windows (hourly/daily)
• Schedule periodic jobs to process each window
• Use SQS to distribute the processing load

These event-driven and queue-driven architectures would be more efficient than your original approaches, providing better resource utilization and scalability for your 5M record processing requirement.
Sources
Scaling and event processing with SNS and SQS | AWS re:Post
SUS03-BP01 Optimize software and architecture for asynchronous and scheduled jobs - AWS Well-Architected Framework