What is a home assistant database for?

Good morning, I’m new to this topic of the home assistant platform, what function does a database have, or what is it used for? I have seen in videos that add a database to home assistanttexto en negrita

See here: Database - Home Assistant
It stores events and data for entity history and tracking.

The default database has a tendency of getting corrupted from time to time, especially if running on an SD card, resulting in that you would need to delete it. HA then creates a new database, but any history for the entities are lost so you will not see any graphs etc until new data has been stored in the database. For this reason, it is a good option to use an external database stored on a more reliable medium. You can also store the entity history in a time-series database such as InfluxDB and make custom graphs with Grafana, https://grafana.com/

Thanks for the information, how can I add a database to my home assistant? Well, I am new to the subject and I am learning everything I can from this very interesting platform

My recommendation would be to get the system working how you want it to first — then play with databases. If you’re new and have problems, it makes helping you harder if you have configured things in an advanced and non-standard way.

A database is set up by the installation automatically. Only when you see a need to do someting more advanced as Troon mentions, you can have a look at the link and see if you need to customize your setup.

I thank you very much for your collaboration

This is actually a very good question, and one that I wondered about for a long time as a new user.

As stated above, the database only stores events and state changes that happen on your system. So, you can delete it any time and HA just creates a new one. No functionality lost except for a history of things that “happened” in your system.

The database is managed by the “recorder.” That’s a good name to know, because by default the recorder saves EVERYthing. So your database can get huge. And, if you installed all the defaults as a good newbie should, it will do so very quickly.

It’s a good idea to review some of the documentation on how to exclude states and events from recorder. These exceptions are pretty easy to add to the configuration.yaml file, but it takes some database knowledge, and experience with HA, to figure out what to exclude and what to keep.

And since the database by default is located on the SD card that a typical newbie is most likely using, you are shortening its life (and risking your whole HA setup) if you don’t fix this.

To me this is probably the biggest weakness in HA. It seems to almost intentionally set up new users for failure. While all the experts and developers post about how awful the default database is, and how bad it is to do excessive writes to SD, nothing ever seems to be done to steer beginners away from this trap.

I didn’t really find all this out until I started researching why there was this database file on my system that kept growing, eventually to 3GB. Fortunately I’m not completely unfamiliar with SLQ databases and was able to get that down to a manageable and stable 44.5M, and my little Raspberry Pi 3B+ and SD card have been chugging along for over two years now. So it is possible to run HA and the default database just fine on that hardware, but it takes some effort.

1 Like

The built-in database is definitely a weakness. When I originally made the switch from Domoticz to HA , it really surprised me that by default everything was recorded. The default behavior in Domoticz was to record time series data like temperature, humidity, power use, etc. It would store detailed data for the last 24 hours and then provide time compressed weekly, monthly and yearly views with min, max and average tendency. This default behavior made a lot of sense to me. Because let’s be honest, do you really use the HA history for anything more than maybe a handful of sensors ?

Another big issue is the lack of UI control over the recorder integration. The usual answer here to the very common new user problem with corrupted SD cards is turn off recorder for all entities you don’t need. Yet there is no way to do this from the UI. You have to fall back to YAML for an operation that is super important to make HA stable on a Pi with SD card.

1 Like

You are so right on all counts!

About three months ago I put in this feature request for a relatively small improvement in the Recorder functionality, but nobody seemed to take any interest. It only got 11 votes.

The default behavior you describe is exactly what’s needed in HA.It would probably require a nearly full re-write of the Recorder. Don’t hold your breath.

That, or integrating a proper time-series data-store (like Prometheus or QuestDB). I’d absolutely LOVE to see QuestDB replace the standard Sqlite3 data store.

1 Like