Add purge services for LTS

Feature Requests
1

Home assistant provides two actions to purge the database to a number of days:

  • recorder.purge
  • recorder.purge_entities

For many of us, the LTS that isn’t purged, starts to become significantly large (ca 75% of my DB).

My proposal is to add two similar actions to purge the LTS to a number of months:

  • recorder.lts_purge
  • recorder.lts_purge_entities
2

Absolutely. I have a database now of 16GB large. 75% is LTS and big portion I never use. So would love to get rid of the entities I,do not need. If purging is not possible, at least give us the opportunity to set the State Class different from Measurement - Total - … But there is today no way of changing that.
Next to that purging-repacking a database large like this always ends up in a corrupt database. So making things a bit easier to work in the standard database would be very helpful.

3

Do you mean that LTS occupies 12GB? How did you estimate this?

4

This piqued my curiosity so I ran sqlite3_analyzer on my HA database to see how much of its total ~900MB was being used by statistics. Including indices, mine is slightly more than half; not including indices, a third. For reference, I have over 2600 entities of which about half are in the “sensor” domain. My HA installation is over five years old. 12GB of LTS seems like quite an accomplishment.

*** Table STATISTICS and all its indices **************************************

Percentage of total database......................  54.3%    
Number of entries................................. 19854534  
Bytes of storage consumed......................... 482836480 
...

*** Table STATISTICS w/o any indices ******************************************

Percentage of total database......................  32.7%    
Number of entries................................. 6618178   
Bytes of storage consumed......................... 290721792

Given this number can never go down, having an option to, for example, “Keep n years of historical statistics data” might be a useful option to people that don’t need the data, or that need to keep the database from growing indefinitely, as the growth rate depends on how many sensors you have.

I think you can use the customize.yaml file to change an entity’s state_class.

5

If there are LTS for entities you know you won’t use, you can always erase them from developer-tools/statistics.

6

Yes correct. I used DBStats and installed in HA. It gave me these rough estimates.

7

I tried but STATE CLASS seems to be off-limits.

8

Deleting will remove all data. I would like to purge instead and preserve ie the last two years

9

For those sharing the need of purging LTS, this custom integration GitHub - davefx/dfx-ha-history-editor: History Editor for Home Assistant · GitHub recently added LTS purge :tada:

I tested it on two systems. It worked as expected and I ran repacks and integrity checks on the DB afterwards without issues