Remove statistics of disabled (not deleted!) entities from recorder

Feature Requests
1

Would be great to have this. Scenario:

  • I disabled a lot of entities which produced many thousand rows in the database (including statistics and statistics_short_term tables) which I want to remove now.
  • Note that I disabled them, they are not deleted. For reasons (e. g. to know the integration offers/offered this entity or maybe I decide to just re-enable the entity later).
  • Additionally I ran the recorder service “purge_entities” for the affected entities, but that still does not allow to delete the statistics using “fix it” button in dev-tools statistics section.
  • So all the corresponding statistics rows stay in the database, which are roughly 4.743 in statistics and 4.166 in statistics_short_term - per entity.

Overview of current situation (scenarios and options as known to me after digging into this for six hours) when it comes to database hygiene and size reduction, where this feature request aims at scenario #4:

  1. :white_check_mark: Delete the statistics of single deleted entities:
    simply use the “fix” button in the dev-tools statistics section

  2. :x: Delete the statistics of multiple deleted entities:
    not easily possible currently, see this feature request Implement a "Fix All" button in "Developer Tools > Statistics" to remove orphaned IDs · Discussion #12644 · home-assistant/frontend · GitHub plus this feature request Removing statistics of deleted devices in one click
    (it is possible by doing all of this in the backend which is quite risky: Statistics - Fix Issue - large amount of data - #3 by vinzcenzo)

  3. :white_check_mark: Delete the state and events of single entities:
    use purge_entities service of Recorder - Home Assistant

  4. :x: Delete the statistics of single or multiple disabled (not deleted!) entities:
    not easily possible currently, see proposed solution for this below
    (it is possible by doing all of this in the backend which is quite risky: Statistics - Fix Issue - large amount of data - #3 by vinzcenzo)

Proposed solution (depending on how easily this should be accessable to normal users):

  1. Either add option in dev-tools statistics section similar to the existing one but enable it for ALL statistic entities, not only the deleted ones.

  2. Or add a recorder service like the existing one Recorder - Home Assistant but for statistics tables instead of states and events, e. g. service recorder.purge_statistics.

I would be very happy with the service option in 2 already, even I would need to run that service for every single entity.

2

Addendum:

Running this (from @vinzcenzo / Statistics - Fix Issue - large amount of data) in your database gives you an overview of affected entities.

    SELECT
    	CASE WHEN entity_id IS NULL
    		THEN 'Orphaned'
    		ELSE 'OK'
    	END Issue,
    	entity_id,
    	statistic_id
    FROM	(
    		SELECT
    			b.entity_id,
    			a.statistic_id				
    		FROM
    			statistics_meta a
    		LEFT JOIN 'states' b
    			ON a.statistic_id = b.entity_id
    		GROUP BY
    			a.statistic_id -- we want to avoid duplicates
    		)
    ORDER BY Issue DESC;

grafik
grafik2097×1182 132 KB

and this gives you the number of affected entities:

SELECT COUNT (*) AS "Orphaned statistic entities"
FROM	(
		SELECT 
			b.entity_id, 
			a.statistic_id				
		FROM 
			statistics_meta a
		LEFT JOIN 'states' b 
			ON a.statistic_id = b.entity_id
		GROUP BY 
			a.statistic_id -- we want to avoid duplicates
		)
WHERE "entity_id" IS NULL;

grafik

3

Also kind of related to Disable long term statistics?! - #13 by erik3

TL;DR someone thinks this is not a problem, as there is still space on the disk.

4

:joy:

I read that topic and get what you mean. Funny to read as the team put quite some effort into reducing database sizes this year e. g. by creating states attributes tables etc… See release notes of I think beginning with 2022.3.

5

This is some pretty low hanging fruit as far as I can see - something that with a small amount of work reaps great rewards. As a developer this is the kind of thing I implement to throw the client a bone when they are waiting on larger more complex features

1 Like
6

…nevertheless unfortunately nobody seems to be willing or able to do that small amout of work.

Meanwhile databases fill up with trash data. Too bad.

1 Like