Lots of empty "state_changed" events in home-assistant_v2.db

Configuration
1

Hmm hello there,

I have 216 487 empty rows polluting my database file.
I turned off HA and copied the file on my local computer to inspect as querying the data only after a week takes quite some time. The file is almost 1.2GB in size.

Is there a reason for those events? Can I delete them? Can I make HA stop recording such events?

Thanks!

image
image840×580 80.9 KB

2

What version of home assistant are you using?

The schema was updated recently to reduce this but the old rows are only marked for deletion and are not purged unless you perform a repack.

1 Like
3

I’m on 0.114.3 [Docker ARM64 RPi4]. I should’ve included this in the thread.

Thanks for the tip, the db file size is down to 132Mb, but I still have 197556 empty ‘state_changed’ events. At least it reduced the file size! I may try to update to 0.114.4 today and fix what will be broken and repack again to make sure.

I might migrate to postgresql so I can keep lots of days and still have some performance and take advantage of ram caching, it’s too bad I cannot set 3 months for some sensors and 5 days for others. Db is running on a USB 2.5" HDD attached to the Pi, so I’m trying to make this as efficient as possible.

EDIT:
I updated to 0.114.4, nothing broke, yay! But the stupid events are still there. I don’t think they do much, they just grinds my gears lol

4

@bdraco apologies for the @ but are you able to explain why these empty state_changed rows exist?

5

The rest of the state data is in the states table. https://data.home-assistant.io/docs/events

If you don’t want the stated_changed events to be saved in the database, the recorder allows excluding events.

2 Likes
6

Excellent. Thank you.

@Extarys check this out:

The old_state and new_state are no longer stored in the events table to avoid duplicating data in the database. An example in Example Queries shows how to join the states table to find the old and new state.

7

Oh I understand now, thanks to both of you. It was quite intriguing.