nhoening
commented
1 year ago There is a library someone began recently (one month ago): https://pypi.org/project/sqlalchemy-timescaledb/ It seems like a good beginning.
I'm thinking that the actual implementation of queries that are Timescale-DB capable might have to happen in timely-beliefs package, but we'd need more research. Maybe it needs to become a configuration switch (USE_TIMESCALE_DB) and then some queries are built diffrerently.
I see two major reasons to use TimescaleDB in FlexMeaures (there might be more):
- Faster queries
- Aggregation support
For reason 1, we have to discuss the MultiIndex we use in the beliefs table. We often query for two time columns, the event_start, as well as belief_time (to find out what was said before or after some horizon, to know what was known at some time in simulations, and to separate measurements from forecasts). And then we also query by sensor_id, of course. I wonder if/how TimescaleDB would help us. This page might be a good indicator.
- Aggregation support is maybe most interesting if you (also) have very fine-grained data (e.g. resolutions under a ,inute). FlexMeasures by itself does not really need those, as its meant to plan 12 hours ahead. For organizations who have such fine-grained data, it could be advisable to let FlexMeasures run on its own Postgres DB with only the aggregated data (e.g. 5 minutes). There are ways, surely, to run all on the same TimescaleDB server, just a question of effort (and still allowing plain postgres).
For time series data in Postgres, TimescaleDB offers better throughput support.
FlexMeasures should support using it (in addition to vanilla Postgres). I'm not sure what would be needed to do that, so users familiar with it might weigh in here, e.g. @anirudh-ramesh.