I have many sensors that measure the power consumption, voltage and so on. Every state change is stored in the database in the table states and in the table events.
My events table is huge (30 GB). I do not see any reason to store events of type ‘state_changed’ twice, therefore, I deleted them in table ‘events’. But to my surprise this has no effect to Home Assistant. Even the history still works.
Why are events of type ‘state_changes’ stored in table ‘events’?
How can I prevent ‘state_changes’ to be stored in the table ‘events’ in the first place? (They should be stored only in table ‘states’. I could run a script that deletes these events from table events (e.g. every day), but this is not a good solution.)
States are not events. Stage changes are. You can exclude various domains or entities from being recorded, but having evidence of a state changing is important for some entities, like logins, or doors being opened, or alarm keypads, etc. I get that most don’t care that the temperature just changed…only what the temperature currently is. However, running statistical analysis on stage changes can help determine future trends which makes for a better automation experience overall down the line…provided you analyze it for any purpose.
The ‘state_changed’ event entry in the table ‘events’ has no additional information than the corresponding entry in table ‘states’. I do statistical analysis - but for that the table ‘states’ is sufficient.
The recorder component will not solve my problem. If I exclude some entities/domains there will be no more ‘state_change’ events written to table ‘events’ but there will also be no more data written to table ‘states’.
I am still looking for a way to store the data only in one table (‘states’ or ‘events’) and not to store the same data in both tables.
This excludes state changes for both tables (states and events).
The current version of Home Assistant does no longer store the state in the table ‘events’. Therefore, the table ‘events’ is no longer growing that fast any more. But there are still two entries for every state change (one in table ‘states’ and one in table ‘events’).
According to the code I don’t think it’s possible to save states without events at the moment.
I’m also in a situation where my database is getting rather large because I have a long retention. I think I’ll go with the cleanup script way, as I don’t really need old events.
One other thing bdraco alerted me to was that after his recent optimisations of the database unneeded rows are marked for deletion but are not actually deleted during the nightly purge. Call the purge service with the repack option set to true ( developer tools services) and they will be deleted. This halved the size of my database.