Database Optimizations in 2022.4

If you are trying the 2022.4 (currently beta), please be sure to keep track of your database size before and after you update, and over the purge interval you have configured.

If you need to report a problem with the changes during the beta period (March 30-April 6), please do so in the #beta channel on discord. Thanks :+1:

Database Performance

This release features write reductions intended to preserve SD card lifetimes, performance improvements designed to get data to the frontend faster, and database size reduction ranging from 10-35% for most installs.

When the same set of state attributes already exists in the database, newly recorded state changes now only store a reference to the state attributes instead of saving the whole set of state attributes.

As data stored in the database before 2022.4 does not get updated, the reduction in database size will occur over time with the recording of new data and the purging of old data.

Most entity history graphs no longer need to fetch the attributes from the backend, and as a result, they load much faster after 24 hours of running on 2022.4. This change is especially noticeable for energy sensors.

For a few a complete set of changes to the underlying database, check out the Data Science Portal

Previous discussion from last time we did a database size optimization: How large is your database (Database Optimization)?

6 Likes

Goodness there are a lot of things that people will grump about in there (I mean the draft release notes as a whole, not the database stuff specifically).

I wonder if it would be a good idea to put some of the breaking stuff like the removal of zwave and openzwave more prominently (like at the very top). I know these things have been previously deprecated, but no one listens.

Please keep the discussion on topic Nick.

Before: 2447MB

I ran a purge and repack to get:

2168MB

So about a 13% reduction.

1 Like

I see that the old TEXT field with the json dump was replaced by an ID pointing to an attribute table. This is a pretty big deal actually. While it might not be as huge as some people might have expected in terms of size reduction, it should definitely have an impact on loading times.

And SD card life.

Before:

  • 2022.3.8
  • 1 day purge/repack
  • DB: 322MB
  • 1310 entities

After:

  • 2022.4.0b0
  • 1 day purge/repack
  • DB: 327MB
  • 1313 entities

Thank you, thank you, THANK YOU!

For me, this is the single bestest moment in Home Assistant since I started using it around .30.

This will go an enormous way to solving many reliability and performance problems which have plagued the database and therefore HA.

For SQLite, the database generally won’t decrease in size on disk without a repack/vacuum since deleted data is simply marked as such and not actually removed from the file until the repack happens.

Still trending down:

Looks like about a 20-25% improvement so far.

1 Like