When sensor entities are disabled, they will just vanish from the developer tools > statistics overview. There is no fix provided to remove old stored data from the LONG TERM STATISTICS. Therefore, this data remains present in the statistics database forever.
(PS: In my case it’s really significant, about 50% for 1,9GiB table size is from disabled entities, this can not be ignored forever)
In contrast:
When an entity is removed:
a FIX button (There is no state available for this entity) is provided giving the opportunity to remove old data.
User has the choice, good!
When an entity is excluded from the recorder:
Again a FIX button (( This entity is no longer being recorded) is provided and old data can be removed. After the data is removed and since the 2024.10 an INFO reminder remains (This entity is excluded from being recorded that this value is no longer stored in LTS.
User has the choice, good!
When the state class is forced to ‘none’:
By forcing the state_class to none there is also a INFO message (entity no longer has a state class) and the ability to remove the history.
User has the choice, good!
However:
When a sensor is disabled,
Nothing. No button appears, the records from statistics is gone in the frontend, but data remains inside the database!
User has no choice!
REQUEST:
I would expect the same choice with a fix button to either keep or remote the data.
A message like: This entity is disabled, it no longer be recorded,
I know, the third use-case is slightly different, it possible to re-enable and still have the old statistics. Could be useful. But the same could be said from recorder exclusion or state_class adjustments, right? You can also revert those options.
Something has to be done as LTS statistics table is growing forever, it consuming 60% of my total DB size. Along the way many more values found their way into LTS, I have ~1500 tracked values! (okay, large installation ;))
With a lot of DB hacking I managed to trim down this table, but that not really sustainable, right?
After removing all disabled entities the table size was reduces to 1GiB
There is one thing to take into account. While I can rrelate to wanting to be able to kill long term statistics for irrelevant sensors that no longer exist, I do not want old entities to keep up popping repair issues, or delete them unasked for, because of energy dashboard history. Now you can keep your old energy sensors in there for past use. If that is gone, you’ll lose valuable information.
True, never delete any LTS unasked for.
Disabled entities could be treated the same as “state_class” removal, so only an INFO message (which still give you the option to keep or remove), it’s not a FIX so it will not appear in the “repairs”.
Maybe it’s an option now to use the new INFO and not a FIX, prevent from many repair issues?
Moreover, add a filter in the new table view to be able to filter out all INFO messages, could even be filtered out by default.
At least provide the option. If disabled entities where visible now, the new delete function already solves the issue.
For the one’s that want to clean-up manually:
Use this query to find disabled entities that are still in LTS.
(As freshly disabled entities are still in ‘states_meta’ for the purge-period, do this manual clean-up later)
Step1- Search for disabled id’s
SELECT * FROM statistics_meta
WHERE statistics_meta.statistic_id NOT IN (SELECT entity_id FROM states_meta);
Step 2 - Remove (BE CAREFUL, MAKE BACKUP) DELETE FROM statisticsWHEREmetadata_id IN (<< fill in some of the ID's here, comma seperated >>)
there is probably an easier way to remove all with one query using the result from query 1, but this gives me at least a more careful approach.
