I’ve got a very large database and would like some advise on what steps to take to reduce the size and prevent it getting so large. The database size is preventing me from upgrading beyond 2024.4.3 as the upgrade process will stop data from being recorded as the database structure is upgraded/changed.
I had changed purge_keep_days to 3 years (1095) days - which I realise is a big part of the problem.
I would genuinely like to keep some data for a long time, especially temperature and energy data.
How can I find what is taking the most data?
How do I purge certain data that I don’t want such long-term data for?
I don’t want to start again.
Even with such a large database my system is responsive enough.
Would moving the database to a MariaDB database help with future upgrades?
(system is running on a Proxmox VM, SSD, Core i7-6700T, 8GB RAM dedicated to the HA VM which could be increased)
You should ensure the data you want to keep is in long-term statistics, and then set the purge duration to 10 days. Long-term statistics are kept forever (currently - could change in the future I guess). Most temperature and enegry sensors will already be configured for long-term statistics.
For an immediate fix, go to Developer Tools / Actions and type in Recorder: Purge. That will allow you to do an immediate purge down to whatever number of days you prefer. Be sure to select “Repack” and turn on the slider to the right of that field.
That should get you through the update. Then I’d recommend following the advice in the thread I linked above to exclude entities you don’t need to keep. You can’t directly purge or exclude things from the long-term statistics tables, but you may not have to if you have plenty of room and performance isn’t an issue. If you need more information check out this thread: