What database implementation would better fit this scenario, keeping costs as low as possible?
Use DynamoDB with a “Calls” table and a Global Secondary Index on a “State” attribute that can equal to “active” or “terminated”. In this way the Global Secondary Index can be used for all items in the table.
Use RDS Multi-AZ with a “CALLS” table and an indexed “STATE” field that can be equal to “ACTIVE” or ‘TERMINATED”. In this way the SQL query is optimized by the use of the Index.
Use RDS Multi-AZ with two tables, one for “ACTIVE_CALLS” and one for “TERMINATED_CALLS”. In this way the “ACTIVE_CALLS” table is always small and effective to access.
Use DynamoDB with a “Calls” table and a Global Secondary Index on a “IsActive” attribute that is present for active calls only. In this way the Global Secondary Index is sparse and more effective.
Explanations:
DynamoDB’s Global Secondary Index (GSI) on a “State” attribute that has both “active” and “terminated” values would be inefficient because it would still need to index both states in a highly dynamic and large dataset, which could lead to high costs during peak traffic. The monthly peak of 1000 calls/second would also require complex handling to maintain cost-effective performance.
RDS Multi-AZ with a single “CALLS” table and an indexed “STATE” field is not optimal for high throughput scenarios, especially during the periodic peak of 1000 calls/second. SQL queries on a large table with this index would struggle to meet the required real-time performance. Also, RDS might face scaling and cost issues due to the high volume of queries and data.
Using two tables (ACTIVE_CALLS and TERMINATED_CALLS) in RDS Multi-AZ increases complexity and leads to potential consistency issues, especially with high-throughput calls and real-time updates. Managing two tables would also incur extra cost and operational overhead while not solving the issue of handling 1000 calls/second peaks.
DynamoDB with a GSI on an “IsActive” attribute is the most cost-effective and scalable solution. A sparse index for active calls only would reduce storage and indexing costs because only the active calls would be indexed, and the system can handle the required throughput efficiently during high traffic peaks, as DynamoDB scales automatically with demand.
From my point of view, the answer is:
Use DynamoDB with a “Calls” table and a Global Secondary Index on a “IsActive” attribute that is present for active calls only. In this way the Global Secondary Index is sparse and more effective.