This is yet another guide on:
- How to view the size of your database.
- How to figure out which entities are using too much space.
- And how to filter them out.
I’m running Home Assistant on a Raspberry Pi with the default SQLite database. If your setup is different, this guide can still be useful, but you have to adapt to your environment.
Why should you care about the database size? Well, there are several reasons…
But first, there is one reason why you should not care: if you are new to Home Assistant, don’t worry about the database size at the beginning. Wait for a couple of weeks, get yourself more familiar with the project, enjoy your new home automation system, then come back to this guide. Bookmark this page, go have fun, and come back later!
After using Home Assistant for several weeks, and after adding many integrations and devices, the recorder database will grow. A lot. The larger the database, the slower Home Assistant will run, because SQLite will have to read/write more data (and more metadata, such as indexes). This will mostly cause excessive I/O, making some parts of the system stall while waiting for reads and writes. Excessive I/O also causes more stress (i.e. more wear) on flash-based storage (such as SD cards), meaning they will degrade faster and they will fail sooner than you might expect.
Additionally, the database is included by default in Home Assistant snapshots (i.e. the backup file). Smaller backups are faster to create, download and restore; and they won’t fill up your backup storage location. (You’re doing automatic backups, right? If not, that should be your next priority, just after checking the database size.)
There are other similar guides:
- How to reduce your database size and extend the life of your SD card
- Taming the Recorder
- Steps to reduce Write Cycles and extend SD/SSD life expectancy
And there are many topics asking about the ever-growing
In the default configuration, Home Assistant keeps a history of all the events and states for all the entities, for about 10 days. This data powers the History and the Logbook features. The data itself is stored by the Recorder integration, which by default writes it to a SQLite database file
After some investigation, you can figure out that some entities update too often, and a few others have very long attributes; and those entities account for most of the space in the database. Thus, you can filter such entities and that will help trimming the fat out of your database. You can go from hundreds of megabytes, or even from a few gigabytes, to a database under a hundred megabytes. And a smaller database means a faster and more responsive Home Assistant, smaller snapshots, and also less wear on the SD card where the database is stored.
Also note that you can safely delete that database file, and Home Assistant will recreate an empty one upon starting. You can also safely disable the Recorder integration altogether, as it is only used for historical data, and it is not needed for normal usage.
The first step is to measure the size of your database. This way you can see if you have a problem with inflated database, and how large is the problem.
However, a much simpler and easier way is to configure a sensor that shows the size of the database file. Just make sure you understand the implications of
allowlist_external_dirs (and feel free to add a comment below about this). Edit your
homeassistant: allowlist_external_dirs: - /config sensor: - platform: filesize file_paths: - /config/home-assistant_v2.db
This sensor will be updated every half minute. Since each state change will add a couple of rows to the recorder database, you may prefer to reduce the update frequency. If you so desire, just add
scan_interval: 1800 (for 30-minute interval) to the sensor configuration. (This option can be configured for any integration that uses polling.)
Then, you can just add
sensor.home_assistant_v2_db to your Lovelace dashboard.
To find which entities are using too much space, you will have to dig into the database itself and run a SQL query. Fortunately, there is already an add-on to make it easier for you.
- Go to the Add-on store:
- Install the SQLite Web add-on:
- If you want, you can Show in sidebar and also Start on boot. The choice is yours.
- Make sure this add-on is started before you continue.
When you open the SQLite Web interface, you can see a few tables, of which only two are relevant:
statescontains a log of all the states for all the entities.
eventscontains a log of all the events. Some events include
event_data(such as parameters in
state_changedevents have empty
event_data, because the state data is stored in the other table.
If you are curious to view the size of each table, try this SQL query:
SELECT SUM(pgsize) bytes, name FROM dbstat GROUP BY name ORDER BY bytes DESC
It will also show
ix_* entries, which are not real tables, but just indexes to keys in the actual tables.
- Click on the
eventstable in SQLite Web.
- Click on Query.
- Type the following SQL query:
SELECT COUNT(*) as cnt, SUM(LENGTH(event_data)) as bytes, event_type FROM events GROUP BY event_type ORDER BY cnt DESC
In my case, I had 10 times
state_changed events than
call_service, which was 3 times the amount of all remaining events combined. Thus, we can easily conclude these are the main contributors to the size of this table.
NOTE: The space usage in this query is only for the
event_data column. It is not a full representation of the row size, because the other columns take up space as well. Don’t pay too much attention to the value.
- Click on the
statestable in SQLite Web.
- Click on Query.
- Type the following SQL query:
SELECT COUNT(*) AS cnt, COUNT(*) * 100 / (SELECT COUNT(*) FROM states) AS cnt_pct, SUM(LENGTH(attributes)) AS bytes, SUM(LENGTH(attributes)) * 100 / (SELECT SUM(LENGTH(attributes)) FROM states) AS bytes_pct, entity_id FROM states GROUP BY entity_id ORDER BY cnt DESC
Analyze the results. Most likely, the top few entities will contribute to a quarter (or maybe half) of the table size (either as number of rows, or as size of the rows).
NOTE: The byte count in this query only considers the
attributes column (which is the only column with arbitrary limitless length). It does not mean the actual size of the rows, but instead we can understand it as a lower bound.
Now that you know which entities are bloating your database, you can filter them out. Feel free to filter as many or as few as you want. When considering if you should filter it or not, ask yourself: Does the history of the state changes for this entity bring me value? Does it bring me as much value as the size it takes?
/config/configuration.yaml file, here’s an example from mine:
recorder: exclude: domains: - device_tracker - media_player - uptime - time_date - worldclock entity_globs: - sensor.clock* - sensor.date* - sensor.glances* - sensor.load_*m - sensor.time* - sensor.uptime* entities: - camera.front_door - sensor.home_assistant_v2_db - sensor.memory_free - sensor.memory_use - sensor.memory_use_percent - sensor.processor_use - weather.openweathermap
Please restart your Home Assistant after editing the configuration file.
It may also take some time before you actually see the database file size decreasing, as the Recorder integration has to purge the database, and the SQLite itself might need to do some internal work to reallocate the data in the file. If you can’t wait, or if the database size doesn’t go down, try calling the
recorder.purge service with the
repack: true parameter. If you want to go to the extreme, feel free to delete the database file, and thus restarting Home Assistant with a clean empty history.
Note: I guess the database file size will never go down, unless you purge it with the
repack parameter. I have not confirmed this, and I don’t have enough experience with SQLite to be sure about it.
You can also change
purge_keep_days from 10 days to 1 or 2. There are no adverse effects, other than forgetting history sooner.
And if you want to reduce the frequency of file writes, increase
commit_interval from the default 1 second to 5, 10, 15, 30 or 60 seconds (or any other interval you may like). This may help prolonging the lifespan of the SD card with minimal side-effect (the most recent history and logbook entries will take longer to show up).
I was having trouble with my Home Assistant instance, and it was related to the database file being over 500MB, getting close to 1GB. With the changes mentioned in this guide, the database is now under 35MB, and Home Assistant is much more stable.
Also, it is ironic that the sensor to monitor the database file size contributes heavily to increasing its file size.