I've been recently trying to work out why my HA database is so big (3.5GB) and after adding some filters so recorder doesn't store state data for a number of sensors which I don't need the state history for I have moved onto looking at state attributes and trying to find the worst offenders there. I store 10 days of data in HA.
It appears (and I can't be confident how accurate it is as ChatGPT was assisting with generating some of the queries for me) that predbat state attributes occupy almost all of the top 20 worst offenders.
The query I've run is:
SELECT
sm.entity_id,
COUNT(s.state_id) AS total_states,
COUNT(DISTINCT s.attributes_id) AS unique_attr_rows,
SUM(LENGTH(sa.shared_attrs)) AS total_attr_bytes,
(COUNT(s.state_id) * 50 + SUM(LENGTH(sa.shared_attrs))) AS approx_bytes
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
LEFT JOIN state_attributes sa ON s.attributes_id = sa.attributes_id
GROUP BY sm.entity_id
ORDER BY approx_bytes DESC
LIMIT 20;
Results from my database:
| entity_id |
total_states |
unique_attr_rows |
total_attr_bytes |
approx_bytes |
| predbat.best_metric |
4485 |
2850 |
53395850 |
53620100 |
| predbat.soc_kw_best10 |
4476 |
2954 |
49986775 |
50210575 |
| predbat.best10_metric |
4484 |
2824 |
49676160 |
49900360 |
| predbat.soc_kw_best |
4476 |
4462 |
49051765 |
49275565 |
| predbat.battery_cycle_best |
4476 |
3209 |
48770143 |
48993943 |
| predbat.base10_metric |
4258 |
2987 |
43332654 |
43545554 |
| predbat.grid_power_best |
3220 |
3206 |
39642984 |
39803984 |
| predbat.base10_import_energy |
4240 |
2985 |
38957350 |
39169350 |
| predbat.best10_import_energy |
4471 |
3216 |
38837926 |
39061476 |
| sensor.sensorbox_sgp30_tvoc |
178019 |
1 |
29907192 |
38808142 |
| predbat.soc_kw |
3982 |
2704 |
36614947 |
36814047 |
| predbat.battery_cycle |
3972 |
2235 |
36211541 |
36410141 |
| predbat.battery_power |
3053 |
2765 |
34986644 |
35139294 |
| predbat.soc_kw_base10 |
3475 |
2849 |
34140110 |
34313860 |
| event.octopus_energy_electricity_[redacted]_200001 ... |
5661 |
26 |
33584606 |
33867656 |
| event.octopus_energy_electricity_[redacted]_200001 ... |
5661 |
48 |
33315681 |
33598731 |
| sensor.sensorbox_sgp30_eco2 |
163964 |
1 |
25250456 |
33448656 |
| predbat.best_pv_energy |
3099 |
3028 |
32436856 |
32591806 |
| predbat.base10_pv_energy |
3098 |
3027 |
31358180 |
31513080 |
| predbat.best10_pv_energy |
3098 |
3027 |
31358180 |
31513080 |
Predbat version
v8.24.2
I noticed in issue #2634 that you mention some guide on reducing what predbat stores in the HA database and this seems to align nicely with this issue. Until you've had time to work on such a guide, can you confirm if I can safely exclude all the above predbat entities from my recorder storing them and it won't impact the way Predbat works?
I've been recently trying to work out why my HA database is so big (3.5GB) and after adding some filters so recorder doesn't store state data for a number of sensors which I don't need the state history for I have moved onto looking at state attributes and trying to find the worst offenders there. I store 10 days of data in HA.
It appears (and I can't be confident how accurate it is as ChatGPT was assisting with generating some of the queries for me) that predbat state attributes occupy almost all of the top 20 worst offenders.
The query I've run is:
SELECT
sm.entity_id,
COUNT(s.state_id) AS total_states,
COUNT(DISTINCT s.attributes_id) AS unique_attr_rows,
SUM(LENGTH(sa.shared_attrs)) AS total_attr_bytes,
(COUNT(s.state_id) * 50 + SUM(LENGTH(sa.shared_attrs))) AS approx_bytes
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
LEFT JOIN state_attributes sa ON s.attributes_id = sa.attributes_id
GROUP BY sm.entity_id
ORDER BY approx_bytes DESC
LIMIT 20;
Results from my database:
Predbat version
v8.24.2
I noticed in issue #2634 that you mention some guide on reducing what predbat stores in the HA database and this seems to align nicely with this issue. Until you've had time to work on such a guide, can you confirm if I can safely exclude all the above predbat entities from my recorder storing them and it won't impact the way Predbat works?