Support TimescaleDB

[nhoening]

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.

[nhoening]

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):

1. Faster queries
2. 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.

2. 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).

[nhoening]

I just want to add that we would use event_start as time column and sensor_id as the first chunking dimension. Maybe horizon as a 2nd one. Our timed_beliefs table is a better candidate than I thought for trying TimeScaleDB, as we have only one clear time column.

Also note that AntiSol forked the SQLAlchemy integration package due to inactivity: https://gitlab.com/AntiSol/sqlalchemy-timescaledb

And here is some info about migration: https://docs.tigerdata.com/self-hosted/latest/migration/entire-database/

We'd have to research how to use this for our purposes. For instance, we often search beliefs of a sensor, based on event_start (a date range), but look for the ones with most recent event_start, or the most recent beliefs for all beliefs with the same event_start (e.g. latest forecast / schedule that was made).
Probably, the last() function is suitable for that, and dimensions as discussed above would work.

[nhoening]

Chatting with AI about the best way to do this might also be a good step forward.

• How does our timed_belief table fit with TimescaleDB, just given its format and the way we query it? (also, we use materialized views soon: Speed up queries via materialized view #1671)
• How to integrate sqlalchemy with timescaledb?