Hello everyone,
I would like to request an automatic database handover/switchover from the internal database to the external database (e.g. InfluxDB) whenever data is requested from e.g. a graph card that exceeds the tracking time in the internal DB.
By default, the internal database does not track all entities and it also cuts off the tracking after 14 days. It is not designed for long-term statistics and not for large amounts of data. Hence a lot of people are using databases such as InfluxDB, which are perfectly integrated into HA. Everything is easily stored there but it is difficult to get the information from there. None of the stock dashboard cards nor the custom cards can directly access this data (only possible with python code or similar, which is not suitable for beginners and bloats the code significantly).
In addition, the influxDB which holds data for weeks, months and years, can be easily restored and the data immediately becomes available again.
As the integration of InfluxDB into HA is so good anyway, I would like to request a seemless handover to the InfluxDB data once the tracking for an entity goes blank in the internal DB.
HA recognizes if an external DB is available so this feature will only work if a DB is available. If one is detected, then when data for a time is missing, automatic search in external DB is launched and reported to the requester.
This will be slightly slower than the internal DB, but it will make life a thousand fold simpler for most users and is fully in line with the current approach of making life easier to non-coders.
Thank you for your consideration.
Bast regards
Alex
EDIT: As I think there is a misunderstanding of my goal, please see further explanation in my following post
The goal is not to change or enhance the abilities of the internal database. The goal is to get Home Assistant to automatically re-route a query to the external database if it cannot find the information in the internal one.
There should be no changes to the internal database or its functionality.
If I am not mistaken, they do not solve the underlying issue. The goal is neither to increase the data storage time in the internal DB nor to replace it with a different, poorly supported DB.
My problem is that the external DBs available are not properly accessible from HA dashboard cards.
So unless every single card dev out there rewrites his/her code, there is no way of properly accessing the data in the external database.
There are things like Grafana, but they are separate add-ons that are not present in the dashboards. This makes it less versatile as all things like auto-entities etc. do not work.
The only alternative right now is to use additional code or to create sensors that get the data from the external database and report it to the internal one (but this is then not real-time and/or you need to define dozens or hundreds of fake sensors).
The solution would be to have two databases fully integrated into HA. One for short-term (the current SQLite(?)) and one for long-term data (e.g. InfluxDB as this is maintained by @frenck anyway).
This would allow every user to access all data with the same simple code (e.g. “entitiy: sensor.bla.bla”).
HA would simply re-route the query between the two databases depending on whether the data is available in the SQLite or not. If not, get it from InfluxDB.
P.S.: Just to be clear, I don’t care if it is InfluxDB or whatever else, as long as it is suited for long-term data storage of large amounts of data (which the internal one is not).
Thank you, @AleXSR7001, for moving the discussion off the 2022.4 announcement thread
@tom_l is correct that this keeps coming up. In addition to the two links he posted, without even searching I found:
And of course, my own:
The common theme here, and in lots of other threads, is that the HA database is being asked to do too much. It’s not practical to have one database store both long- and short-term data, nor is it appropriate to have a one-size-fits-all “purge days” for all entities.
It’s great to see the database improvements in 2022.4. That means someone in the Dev community is finally looking at the recorder. There are so many potential improvements which any knowledgeable database analyst could see. I trust the Dev team; I assume this is the first in a series of database improvements.
The common theme here, and in lots of other threads, is that the HA database is being asked to do too much. It’s not practical to have one database store both long- and short-term data, nor is it appropriate to have a one-size-fits-all “purge days” for all entities.
But that is exactly what I do not want to do. That is what I am trying to prevent.
I would like the internal SQLLite database to only store short-term data, just the way it is doing right now.
And if a user wants long-term data, they can install any external database such as InfluxDB, PostgresSQL or whatever database they want.
The missing feature is the automatic internal re-routing once the internal database stops recording.
So HA would only need to refer any data query to the external database IF the data is not contained in the internal, short-term database.
Bascially
IF querydate less than 14 days in the past THEN
internal database
ELSEIF querydate more than 14 days in the past THEN
external database
END IF
Instead of the query date it would of course be more elegant to do a search and find. So
IF data not found in internal database THEN
look in external database
END IF
The main advantage being, that every card, every add-on etc. in HA can then use the same syntax.
Simply something like “entitiy: sensor.bla.bla” and it will always get the data it is looking for without manually stating the database with needlessly complex code.