How to optimize DB storage of power-related entities?

Energy
,
1

So my database for HA, 2 months is 3.3GB big. I took a look at the DB and saw that most of it is the states table and its indexes. I then run a count of rows of all the states entries grouped by entity_id and found that the basically 10-20 energy sensors account for +50% of all database rows (so i presume correlates to database size).

Given that these sensors tend to fluctuate a lot (as in decimal of watts is easy to fluctuate in normal idle devices)

  • Do you see similar patterns?
  • Is there any way to optimize, or reduce the amount of state changes?

I mostly have zigbee and shelly devices to track energy. But my main ‘culprit’ is a basic helper entity which sums the power of the devices so I can see the real-time total power used at home and see if i need to adapt usage patterns. Because that’s a helper entity, I have no way to manage it, but I understand it will update as many times as each of the underlying sensors does. Not sure if I can limit that to once per 2 seconds or something like that?

Many thanks!

PS: I opened in Energy as those are the main entities culprits, but feel free to move to other categories.

2

The size of your DB depends on the setting of your recorder: in configuration.yaml.

The energy mesures are stored in the long-term table of the database.

My recorder settings are set like this:

recorder:
  purge_keep_days: 10

My DB is runing on mySQL Server and i have a huge amount of energy sensors like you.
My DB size is just 1.6GB after running over 2 years now.

3

I agree reducing the amount of days would be the most-straight forward solution. But that would purge all sensors data, not only energy. (Ideally one should be able to set a purge_days for entities with statistics enabled to be lower and a ‘default’ higher for the rest).

But what I mean is that energy sensors account for the most in the states table (this is, not the long-term one AFAIK)
The following chart I made shows a % of count of rows in the state table and for which entity they are for and among the ~15Milion rows in total for all entities, power accounts for the most of it.

image
image1544×177 15.6 KB

So if there was some energy-specific optimization that could be done to reduce those it would be a quick win, without sacrificing the remaining entities.