How to Shrink the Database

Configuration
21

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
22

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
23

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.

24

Perhaps ?

And

25

I would have quessed that HA vacuums the database automatically at some interval. Am I wrong?

26

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.

27

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.

28

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.