You need a persistent and durable storage to trace call activity of an IVR (Interactive Voice Response) system. Call duration is mostly in the 2-3 minutes timeframe.
Each traced call can be either active or terminated. An external application needs to know each minute the list of currently active calls, which are usually a few calls/ second. Put once per month there is a periodic peak up to 1000 calls/ second for a few hours. The system is open 24/7 and any downtime should be avoided.
Historical data is periodically archived to files. Cost saving is a priority for this project.
What database implementation would better fit this scenario, keeping costs as low as possible?
A. 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.
B. 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.
C. 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.
D. 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.
RDS setup that is able to handle 1000 calls/second is quite expensive! And you’ll have to pay for that all the time even though you need it only few hours once a month. For such peak usage DynamoDB provides better cost.
The idea behind sparse indexes is that only items with isActive = “y” will be in the index, so require less storage and processing than your main table
B is the right option.