I think it would help performance and db-size if it’s possible to limit writes to database by writing the average of a sensor , and the state of a binary sensor once per x time. For example:
-
I have the DSMR sensor that reads it’s data every 10 seconds. 2 things that would be nice to have:
- Write the average every 5 minutes to the database. Averages can be hold in memory for a short period of time I think. (this would reduce database size dramatically)
- Use sql to calculate the average/max/difference over 5 minutes even though HA has written with a 10 seconds interval. (this would make it easier for HA to process larger periods of time, for example to show a daily power usage over a month).
-
I have a motion sensor (binary_sensor) that writes every detection.
- Write every 5 minutes the state to database. If movement has been detected in those 5 minutes, write 1, otherwise write 0. (Also reduces size).
- query count the value for this sensor over time (5 minutes for example) where value is 1. When the count returns > 0, the sensor has been triggered and should return 1. (This would also increase performance when calculating over a larger period of time).
The write period could be configurable in the recorder component up to a reasonable maximum.
The read period could be calculated automatically when asking for a certain period of time so that the amount of results is more or less always the same (no more then let say 10000?). This way processing by HA would be at least reasonable in time. Also it could be possible that certain cards ask for a specific interval? for example when you make a graph that represents daily power usage for a month, just build a query that ask for the the first and last datapoint for that per 24 hours and calculate the difference.
I’m just thinking about how the performance for graphing over longer periods of time would be better to handle for HA on slower devices like a raspberry pi. There are probably a lot of things I did not think of but it’s a start 