It looks like stuff can be individually enabled / disabled here:
http://nnn.nnn.nnn.nnn:8123/config/entity_registry
Right, but I think that’ll completely disable them, not just prevent their data from being recorded to the database. I’m thinking maybe someday I’d want to see the current information, even if I don’t want to record historical data.
1 Like
This is sending me to the fair again. My database is limited to 1 week capture. Since Christmas, the DB size hasn’t exceeded 2Gb. The web pages served within HA take a good minute to refresh. There’s got to be a faster way to recover the data to generate history graphs.
I’ve been thinking about moving my HA from the SD to a USB drive (either SSD or HD), but know that’s a one way trip for the RPi. You haven’t taken that jump, have you? Would be curious to hear from anyone who has and how it went. I too fear of the SD card crash.
Yeah, wouldn’t it be nice to exclude by node_id!
How bizarre that I have earned the “Popular Link” Badge due to this link being clicked 50 times. It must have been by Bots. None of YOU would click this link, which evidently is not a link!!
1 Like
quick question here - I am fairly new to HA and had been playing around with changing the recorder’s history period to a year, but I realize that’s not good as the backups are now over 500MB, so I changed it to 90 days but it didn’t seem to change the backup size.
This tells me that there’s historical (>90 days) of data that will need to get purged, and after some research folks say to delete the DB and start over, but I’d rather just purge entries older than 90 days.
Is there some simple SQL command I can run to purge this data? What do folks use as an interface? I’m happy with just dropping in to an sql client/console and purging that way but am unclear how to get there. I don’t even know which DB I’m running now:
$ ps aux | grep sql from the terminal yields nothing running.
@dmcentire
Go to /developer-tools/service , choose… Recorder: Purge … remember to Mark Repack and the “toogle” to the right of repack
Thanks for the info – just to clarify, there’s also a setting for “Days to keep” that I’m tempted to turn on and set to 90 days.
Will that also be needed as I don’t want to lose the last 90 days of data if possible?
yes i assumed youd noticed that 
Cool, thanks for the info and help!
I don’t know exactly how to tell when it’s completed though, in case there’s somewhere I can check.
Currently, I’ve got a terminal into the system (Rpi4) and check uptime every now and then to see how the load averages are.
ok, i don’t know how large your DB was, before you Purged/repack , but if it was 500MB as you mention above, it should be done now, look at the DB-size in /config
PS: In Core-log you see the service called, but for some reason, they don’t find it relevant to “note/log” whether it was successful 
…
EDIT2: It’s an SQLite DB, so it’s a fairly easy “task” to remove everything after i.e 90 days, then “repack” basically just “rewrite size”
EDIT3: And “Recorder” is the service that writes to the DB-File
I had this same issue long ago. I have since migrated from rpi to a nuc, no more issues with writing to sd card on that.
But I did limit the logs, this is in my configuration.yaml
recorder:
purge_keep_days: 7
# purge_interval: 1
I used the following commands in order, in a SSH / Web Terminal session. Shrunk a 650MB DB to 100MB roughly.
Start of terminal session:
cd config
ls -lh
sqlite3 home-assistant_v2.db
VACUUM;
.exit
ls -lh
You get to see the before VACUUM and after sizes.
If there is a downside to doing this, please let me know.
1 Like
UPDATE
After a day or 2 of using the VACUUM command for sqlite3, i can say I have noticed 0 negativities. Only excelent performance increases. HA loads faster, DB searches no longer take forever for a large date search, and everything works excellent.
It would be nice if you could write a script to run terminal commands. If you can, I do not know how to. Ide love to automate this on a weekly or biweekly rotation.
Perhaps ?
And
I would have quessed that HA vacuums the database automatically at some interval. Am I wrong?
On which platform are you running Home Assistant? I am running my instance on a Raspberry Pi 4 and there is no sqlite3 available from the command line in HA. I also tried to copy the db file to my laptop and do the vacuum there, but get an Error saying The database disk image is malformed.
My home-assistant_v2.db is currently 830MB, so I really would like to shrink it quite a bit if possible.
Any help appreciated, thanks.
I have tried to enable this by adding the following to configuration.yaml:
recorder:
auto_purge: true
auto_repack: true
purge_keep_days: 10
but it appears to do nothing. The database just keeps growing.
There may be a number of things going on here. One that I’ve noticed is that the statistics table is never purged, and seems to keep growing forever. Since I don’t use these data, I’ve made shrinking this table part of my monthly maintenance, when I do my upgrades. Specifically, I run these two queries:
SELECT * FROM statistics WHERE start_ts < DATE('now','-4 day');
DELETE FROM statistics WHERE start_ts < DATE('now','-4 day');
I also do a purge and repack from the UI before shutting HA down. And of course I’ve been pretty aggressive in excluding entities and keeping my purge_keep_days to a low number. I’ve been trying different values between 3 and 6 days. (As you can see from the SQL above, this month it’s 4.)
Finally, a brief warning: There has been some ongoing optimization with the database structure. This is a good thing! But if you plan to use any of the SQL statements from earlier in the thread, understand that the field names, and possibly even table names and functionality, may have changed. I don’t know if this has occurred in 2023.11.x yet. (I hope to update next week.) Always browse the database before running any posted SQL.
1 Like