Is there any ways to manually purge some entities from my SQLite database — not entirely but based on some rules (e.g., records older than one month)?
A while ago I used some SQL extension to take a look at the database entries and using some magic SQL spell found somewhere here I learned that it is just some dozen entities that take up over 90% of the space.
I cannot control the update interval of these sensor entities (Shelly) so they just spam the records with new values almost every second. And this is just fine, but storing all that from some 6 months (my Recorder purge setting) is getting a little too difficult to handle.
Ideal thing for me would be to have different purge intervals for different entities (e.g., keeping some for a year, some for a month) but I understand there is no such setting in Recorder. Or even ”controlled data rot” so that after a while, all the exact raw data is turned into simple min/max/avg with lower resolution.
I did try to simply filter these entities out with a Recorder exclusion rule but then I lost also the current & near history, which I very much like to keep. Or I could maybe just make a bunch of template sensors with lower update interval but I’m not too happy about that either.
So, could I use some scheduled (automation) SQL spells to manually purge some of the oldest data of these known massive entities? And would this be safe or is there a risk of nuking the whole db eventually?
My database has grown so large that full backups (compressed) are almost a gigabyte now, so backups before updates has become pretty tedious as well.