Estimated reading time: 9 minutes
Do you know this? You want to check yesterday's temperature history in your Home Assistant dashboard and the loading cycle seems to take forever. Or even worse: your nightly backups suddenly fail because the files have grown to gigantic proportions and the storage space on your Raspberry Pi or mini PC is bursting.
In 99 % of cases, the cause lies precisely here: The Home Assistant database has become too large.
Every click, every temperature change and every passing cloud that your weather sensor detects is recorded in the database by default (usually the home-assistant_v2.db) is saved. If you don't intervene, the system will clog itself up. But how do you find out which devices are the real culprits?
This is where the fantastic tool comes in DBStats into play. In this article, I'll show you how to use the DBStats add-on from jehy to take a deep look into your database, identify the worst memory hogs and make your system light-footed and lightning-fast again.
For detailed instructions and explanations of all configuration options, I recommend watching the YouTube video linked below. This video goes into detail about each individual setting, providing you with comprehensive instructions on how to set everything up correctly. Any code from the video can be found in this article, so you can use it directly without having to type it out.
What is Home Assistant?
Home Assistant is an open-source platform for home automation that aims to connect all devices in the home and control them centrally. It was developed to provide a unified user interface and simple, flexible control for a wide range of smart home devices from different ecosystems. Here are some points in favor of Home Assistant:
- CompatibilityHome Assistant supports thousands of devices and services from various manufacturers, including lighting, air conditioning, heating, cameras, weather sensors, voice assistants and more.
- Local controlUnlike many commercial smart home platforms that rely on cloud services, Home Assistant runs locally on your network. This means increased privacy and reliability, as your smart home devices work even without an active internet connection.
- Automation and scenesOne of the main features of Home Assistant is the ability to create complex automations and scenarios. For example, you can have lights turn on automatically when it gets dark, or turn on the heating when the temperature drops below a certain level.
- Simple user interfaceHome Assistant offers a user-friendly interface that makes it easy to monitor and control devices. This interface is fully customizable.
- Integration with voice assistantsHome Assistant can be integrated with voice assistants such as Amazon Alexa or Google Assistant to enable voice-controlled control of your smart home devices.
- Open source and community-drivenSince Home Assistant is open source, it is supported by an active community that is constantly developing new integrations and improving existing ones.
- Data protection and securityAs a locally running platform, Home Assistant has the advantage that the data remains within its own network. This protects privacy and increases security compared to cloud-based solutions.
In summary, Home Assistant is a powerful and flexible solution for those who want to design their own smart home and control their devices centrally, regardless of the manufacturer or ecosystem.
What is DBStats and why do you need it?
By default, Home Assistant offers you hardly any options to look deep into the structure of your database. You can see the file size, but not the size of the file, what occupies exactly this memory.
DBStats (hass-dbstats) is a custom add-on for Home Assistant OS (and Supervised). It acts as an analysis tool that scans your database (whether the standard SQLite database or an external MariaDB/MySQL) and shows you which entities generate the most entries in a clear web interface.
Your advantages at a glance:
- Targeted troubleshooting: Instead of fiddling around blindly in the recorder, you know exactly which entities you need to exclude after the scan.
- Visual analysis: You can immediately see which sensors are taking up the most memory.
- Table insights: You can recognize whether the problem is in the table
states(changes of state) or inevents(events).
Install DBStats in Home Assistant
Since DBStats is not an official add-on, you need to add it via a custom repository. This only takes two minutes:
- In your Home Assistant, go to Settings > Apps.
- Click at the bottom right on Add-on store.
- Click on the bottom right on the Install app.
- Click on the top right on the three points and select Repositories.
- Add the following URL from GitHub:
https://github.com/jehy/hass-addonsand click on Add or alternatively use this button:
- Close the window and reload the page.
- Scroll down until you see the new repository, select DBStats and click on Install.
Once the installation is complete, start the add-on and open the web user interface.
(Tip: If you use MariaDB, you may need to enter your database access data in the add-on configuration. If you use the standard SQLite database, the tool usually works out-of-the-box).
The analysis - How to identify problems with DBStats
When you open the DBStats web UI, the system performs a scan. Depending on the size of your database, this can take a few seconds or minutes. DBStats then presents you with various tables and diagrams.
Here you need to pay attention to the following two key areas:
1. the states Table (state changes)
This is usually the number one culprit. Each change of a sensor (e.g. temperature goes from 20.1°C to 20.2°C) is a new entry in this table.
Typical memory hogs that you will find here:
- Electricity metering sockets (smart plugs): Sensors that measure the current wattage are often updated every second. A single refrigerator plug can generate thousands of entries per hour.
- System monitors: Sensors for CPU utilization or free RAM are updated extremely frequently, but are often of little historical value to you.
- Media Player: A playing Chromecast or Sonos speaker changes its status permanently (e.g. current playback time in seconds).
2. the events Table (events)
This is where Home Assistant stores automation triggers, API calls and status events. If you find extremely high values here, this is often due to faulty integrations that are constantly pinging the system in the background or automations that run in endless loops.
A concrete example from practice: You can see in DBStats that the sensor sensor.washing_machine_performance has generated an incredible 450,000 entries in the last 7 days. This one sensor accounts for 30 % of your database! Do you really need every tenth of a watt fluctuation for the history? No. It's enough if the value in the dashboard is up-to-date, but it doesn't necessarily have to end up in the database for eternity.
Eliminate memory hogs (The recorder)
Now that DBStats has served you the culprits on a silver platter, it's time to act. The magic word is: Configuring the recorder.
The recorder integration controls what is written to the database and what is not. Open your configuration.yaml and create filters.
This allows you to specifically exclude the identified memory hogs:
recorder:
# Keep data for 7 days only (default is 10)
purge_keep_days: 7
exclude:
# Exclude entire domains if you never need their history
domains:
- camera
- media_player
- update
# Exclude certain entities that you found in DBStats
entities:
- sensor.washing_machine_performance
- sensor.cpu_utilization
- sensor.memory_free
# Exclude entities according to certain patterns (e.g. all watt sensors)
entity_globs:
- sensor.*_power
- sensor.*_voltage
- sensor.*_currentImportant to understand: If you connect a sensor via the exclude-command, it disappears not from your dashboard. It still shows you the current value in real time! You can also continue to use it as a trigger for automations. The only thing that has been removed is the history graph for the past.
(Advanced tip: If you want to store data long-term without bloating the Home Assistant database, set up an external InfluxDB and move the data there. InfluxDB is specially optimized for such time series data).
Purge the database (Purge)
You have the configuration.yaml adjusted and Home Assistant restarted. Great! Your database will grow much more slowly in future.
But: The old data is still there. To free up the storage space immediately, you must instruct Home Assistant to clean up the database (repack).
- In Home Assistant, go to Developer tools > Actions.
- Search for the service
Recorder: Purge(Clean up). - Set a check mark at Repacking (This is important, otherwise SQLite will not return the memory space to the operating system).
- Click on Execute action.
Depending on the size of the database, this process can take a few minutes. If you check your system afterwards (or make a new backup), you will notice that your home-assistant_v2.db has shrunk massively!
YouTube video implementation
Conclusion
The database is the beating heart of your Home Assistant. If it clogs up, the entire system suffers from a loss of performance, SD cards break more quickly and backups become unwieldy.
Take 15 minutes today, install DBStats and take a look under the hood of your Home Assistant. You'll be amazed at what's there!
The add-on DBStats is an absolute must-have tool for every smart home administrator. It takes the guesswork out of database optimization. Instead of making settings at random, DBStats shows you in black and white where the problem lies. In combination with a clean recorder-configuration, you can keep your system stable, responsive and secure in the long term.
Useful links at a glance
- GitHub repository of DBStats (jehy/hass-dbstats)
- Official Home Assistant documentation on recorder integration
- Home Assistant Forum: Discussions about database performance
0 Comments