I'm considering a few solutions for a problem involving some sort of a time series data. Imagine: you have a system that keeps track of financial transactions in multiple currencies. To keep it simple, let's say it just keeps track of money flowing through the system; it doesn't care about which way it goes (no debit, credit, etc.). What we want to do is find the total amount of money that flowed through the system in some recent time period (e.g. last hour, 30 mins, 20 seconds, etc.). The system processes a sizeable amount of transactions ranging from tens of thousands into millions per minute, and the solution needs to scale. The use case for this is really more around sampling, so the value isn't exactly time sensitive; for example, the sum can be for a 5-minute window starting from 6 mins ago (i.e. it's stale by about a minute), although we'd love for it to be as real time as possible. Transactions are stored in a relational database.
I don't have a lot of experience when it comes to systems at this scale, but I'm considering the options below:
(Distributed) Caching
My first thought is to use a cache (e.g. Redis) and set the TTL on the data based on the desired window. The cache can be asynchronously updated by a regularly running job that sends a query to the database, just so that the clients don't have to wait for when the cached value goes stale (like I said, we can tolerate a bit of staleness).
Views? Triggers?
I don't have a lot of experience when it comes to database views and triggers, so I'm not actually sure if this is a feasible option. My understanding is that select queries can be converted into views, and views can be indexed. However, I don't know if it's possible to set it up such that it maintains a sliding window based on the time period. Is this even feasible?
Time Series Database
Another approach I'm thinking of is to use a time series database where transactions are asynchronously copied. Transactions are written into this TSDB mainly for the purpose of fulfilling this use case. I think one of the main downsides to this is that the transactions written to this db are only eventually consistent.
As mentioned, I don't have a lot of hands-on experience with running systems at this scale, so I would appreciate any insights into the pros and cons of these options, or any alternative suggestions. If you know of any product-specific features that would work well for use cases like this, I'd also love to hear about them. Thanks!
