Home-assistant.db size

Configuration
1

My home-assistant.db is up to 400MB and constantly growing. Should I worry about its size? What’s stored in there anyway? (My backups don’t like how the file keeps changing as it is read so I may have to tweak them)

Is there a way to purge it? Note: I do NOT have the history component enabled, but I DO have the logbook component enabled.

2

There is a Pull Request about a purge-db component, that will allow to do it. For now, if you don’t care much about your data history…you can stop HA, delete homeassistant.db and restart HA. It will recreate the db from scratch.

2 Likes
3

Hadn’t even looked at mine until this post; mine is at 252mb.

4

The next release will enable one to delete old stuff from the database.

https://github.com/home-assistant/home-assistant/pull/2059

4 Likes
5

Nice! Not worried in my case, but still nice! Thanks for the update.

6

Has this been finalized? Not sure how to read the above, mine is 341mb now as well.

7

@Dracoy: It is in the latest release. Add this to your configuration:

# Enables support for tracking state changes over time.
recorder:
  # Delete events and states older than 2 weeks
  purge_days: 14
3 Likes
8

Thanks for this, didn’t spot it in the pull requests!

Does this apply to anything else, like history: or logger: or is it just the recorder: entry?

9

when is it supposed to purge then? I have added it to my config but the db size keeps growing …

10

If you set it for 14 days, then it runs after 14 days of UNINTERRUPTED uptime. If you restart your HA server, then the clock restarts from zero again.

This isn’t really clear in the docs and I may do a PR to update them, if I can find a place for it. I found out while reading a pull release and it was discussed in the comments. I can’t seem to find the doc for the db at the moment.

To test this, you can set it for 2 days and wait. Somewhere around hour 49, it will run the purge.

4 Likes
11

Ok thats a huge caveat then … I saw over 30MB within an hour and while still expanding the system it will get restarted … but I also found out you can just delete it and it will recreate the db …

12

You’re right on that. But just remember you need to stop it to delete it, then restart it so you’re setting the clock back to zero again.

And that’s if you’re just using the built in SQLite db. In my case, I’m using MySQL so I’d have to delete the db through one of my other web interfaces with HA shutdown.

I set mine for 7 normally, but I did go to 2 days just to make sure the purge would run and to lean things up in the db. It does work, but I did notice that it’s not exactly on the 48th hour of uptime. It happened within the first half of hour 49.

13

Here is a PR about this:

2 Likes
14

Wouldn’t it also be cool to be able to kick off that purging manually somehow?

15

I actually would prefer if it creates a daily .db and would be able to read all - then you could purge/delete the daily ones you no longer need … or even better use a file system based database with daily folders …

1 Like
16

Read the PR. It’s been suggested to expose this as a service; you may want to indicate your support for this by liking or giving a thumbs up to those posts like I did. Ultimately, I think this is going to end up as a service myself. It just makes sense.

17

Hello everyone,

I am puzzling with a probably simple problem regarding this topic.
Didn’t want to start a new topic, as it might be helpful for others browsing this topic.
My setup is a RPi3 with Hass.io image (0.67.0) and the home-assistant.db is 944mb.
The history and logbook take way too much time to access, I assume from the database size.
I tried to make it smaller with the following settings in the config yaml:

# Enables support for tracking state changes over time
history:

# View all events in a logbook
logbook:

# recorder
recorder:
  purge_keep_days: 1
  exclude:
    domains:
      - automation
      - weblink
      - updater
    entities:
      - sun.sun # Don't record sun data

How can I make my .db file smaller?
I know I can delete the file when HA service is stopped.
What is the easiest way to solve this? Schutdown and put SD card in laptop to remove db?
Can my windows laptop even access the file system?
Does the addon Samba share still works when I stop the HA service? Then I can delete it…
And how to boot it up again?

Thanks in advance!

FYI, I have a sensor for the database file size (adjusted from {bummer, cannot find source}), that probably works on every HASS.io image:

 - platform: command_line
   name: db_file_size
   command: "du -m /config/home-assistant_v2.db | cut -f1"
   unit_of_measurement: 'MB'
   value_template: '{{ value | int - 1 }}'
18

you can delete the file at any time even while HA is running. I have done it plenty of times without issue so just samba in and hit delete :slight_smile: Try excluding more domains such as media_player and whatever else you dont really need a log of

19

You can try to call the recorder.purge service with the repack parameter.
Don’t know if this works, because i’m using mysql db, but before deleting the db and loosing all history and states, give it a try.

20

Thanks guys, did not know I could just delete the .db when it is on.
Will try that when I am home. thanks @sparkydave.

I am not interested in the old data, I currently have a setup with some components and trying to make it run stable…