We are considering using AWS Timestream to store financial transactions from various sources for users. Any solution needs to be able to support ~4k transactions per user per year for 1 million users. Some of the important requirements are:
- Be able to update transaction amount for three months in the past
- Calculate daily, weekly and monthly balances by tallying up amounts in real time
- Balances need to reflect edited transaction amounts, so we'd need to recalculate these in real time often.
Is a time series database a good fit for something like this in terms of real time AND historical record availability?
A few things that worry me about timestream:
- Can't delete records at all. If bad data enters your system, its going to be extremely difficult to clean it.
- No backups offered by AWS, we'd have to write something custom for this.
Are there perhaps better time series databases I should consider?