Events and states tables

Development
1

Hello,

Could you please clarify what kind of entries I should be expecting under events and states tables in my local db?

I was under the impression that events records whatever events happen to be reported in hass, so it is natural to have thousands of entries there. On the other hand, I would expect states to only keep the current/latest state of a device, so probably just a couple of hundred entries. However, on my local test installation I see events having close to 1M rows and states close to 0.9M rows, so clearly my understanding is wrong :slight_smile:.

Thanks!

2

Can anybody elaborate on the difference between these tables? I noticed that the history graphs uses values from the states table.

I’m only interested in actual sensor values over time. Can I safely cleanup the events table without affecting somethiing in Home Assistant?

3

The states table contains a history of all state changes and their new state value and attributes. They are foreign keyed to a state_changed event in the events table.

The state_changed event in the events table also contains a json serialized copy of the new state (and attributes) for the entity as well as the previous state (and previous attributes) for the entity. So there’s a lot of data being duplicated there.

It’s been a while since I’ve looked into the code, but I believe history is looking at the states table, while the logbook is looking at the events table. I’ve wanted to clean it up so that state_changed events never get written to the events table but I’m not familiar enough with the codebase to pull it off. IMO there’s no reason to write state_changed events to the events table, since each entry in the states table is a state_changed event, and all of the extra information that is written to the events table with a state_changed event can be pulled from the states table. It would significantly reduce the size of the database, which should help speed up some of the queries.

1 Like