Calculating time an object stays in a state

Question

I use Python sqlalchemy to store my model in a MySql database. One of my objects has a 'state' field (for simplicity let's assume there are two states: AVAILABLE and UNAVAILABLE).

There are different micro services which can modify the states of the objects - such as the API service, periodic tasks, background tasks, etc. States are updated with UPDATE queries, which means that currently I have no way to get historical states for each object. States can be updated as frequently as multiple times per second, or as infrequently as once a year.

My goal is to track the time an object spends in each state. For example:

• Within the last 24 hours, instance A spent 6.5 hours in AVAILABLE state and 17.5 hours in UNAVAILABLE state
• Within the last 6 months, instance B spent a total of 95.9 days in AVAILABLE state and 84.1 days in UNAVAILABLE state

How would you design an architecture for that? Do I create a separate table in MySql to store historical states? Or perhaps a NoSql database is better for this task? What tables/object structure would you use here? I prefer to rely on open-source or even commercial solutions rather than develop myself.

TIA

Answer 1

It sounds like you are working with TimeSeries data. Knowing that may be helpful to your research. There are product solutions and patterns of problem solving specific to that kind of data.

It might be a good fit for a SQL database of TimeSeries data. Typically each row has a timestamp associated with it. And each column stores a value associated with the timestamp.

If you anticipate a LOT of records, I highly recommend TimeScaleDB. It runs on top of PostgreSQL. We use TimeScaleDB to store data that results in 300+ new records inserted every 5 minutes. It has worked for us without a hitch for over 2 months and we're going to be moving to 2 minute or 1 minute inserts resulting in 300+ new records per minute 24 hours a day. We anticipate no problems.

If you build a TimeSeries database, you'd have a lot of flexibility because you can have independent systems report their update times without any central mechanism other than the database. Then you can use a separate processing application that can process the database however often you like to get the report results you're looking for.

Edit: I figured this might be enough to get you going in the right direction, but if you need more specifics on table structures or database interaction, just comment and I'll help.