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!

  • "Transactions are stored in a relational database." - Which database and version? Your goal is accomplishable with each of the options you mentioned from an equally performant standpoint. The differences of when to choose one are in more granular details. E.g. a caching database isn't meant to hold a large amount of data at once time, I believe, and the eventual consistency is something to consider in the two options you mentioned.
    – J.D.
    Commented Jul 27, 2022 at 22:06
  • What is your minimum quantum? 1s? 5s? What is your absolute minimum staleness time? As mentioned by @J.D. - your RDBMS (with version) would be good to know!
    – Vérace
    Commented Jul 28, 2022 at 1:15


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.