For the events and states tables in the database, we have a purge_keep_days setting in the config file. We also have the Recorder: Purge and Recorder: Purge Entities services. Yet the statistics table grows forever, with no way in the UI or config to clean it up or restrict its growth.
Today I ran the following SQL against the database:
DELETE FROM statistics WHERE start < DATE('now','-4 day');
After a commit and save, the database was one-quarter the size it had been before running this command. In other words, 75% of the database held data which I never use, and never will use.
One of the great things about HA is it can run on a Raspberry Pi with an SD card. This is exactly what lead me to HA in the first place; I wanted to try out the RPi and HA seemed like a good project. I could get it running with minimal investment and minimal experience. It performs a useful purpose.
I think we’re setting new users up for failure. If I hadn’t been familiar with database management and SQL, this environment would have crashed long ago as the database grew out of control.
There should be similar configuration settings and purge services for the statistics table as there are for events and states.