batpred
batpred copied to clipboard
Published
20 hours ago •
springfall2008
springfall2008
Predbat appears to be putting a lot of data into the HA database
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 through a similar journey with finding out what is using space in my HA database and then finding ways to reduce it.
For me its a 2 track activity, excluding entities entirely from recorder that don't need to retain ANY history, and purging entities regularly where some history is needed either for Predbat to operate or just for normal enquiries.
The first part is easier to share, and covers a number of the same entities you identified. I have givenergy inverters so am including these in the below. I don't have sensorbox (not sure what it is). For me the Octopus entities fall into the second classification of being needed but can be regularly purged.
Here's my recorder configuration from configuration.yaml (I actually have it in a separate recorder.yaml file):
# Decrease raw history retention from 10 to 9 days and exclude keeping statistics for some entities
purge_keep_days: 9
exclude:
entity_globs:
# weather, sun position and watchman
- sensor.sun*
- weather.*
- sensor.watchman_*
# Predbat base10, best and best10 sensor families
- predbat.base10*
- predbat.best*
- predbat.*best
# Everything Presence Lite target motion sensors
- sensor.*epl_target*
entities:
# Google drive backup sensor and sun
- sensor.backup_state
- sun.sun
# Raw energy sensors that have replacement time-based filter sensors
- sensor.ashp_energy_total_raw
- sensor.ashp_power_raw
- sensor.extension_energy_total_raw
- sensor.extension_power_raw
- sensor.fit_solar_energy_total_raw
- sensor.fit_solar_power_raw
- sensor.freezer_th_battery_raw
- sensor.fridge_energy_total_raw
- sensor.hot_tub_energy_total_raw
- sensor.hot_tub_power_raw
- sensor.internet_ha_energy_today_raw
- sensor.internet_ha_month_energy_raw
- sensor.internet_ha_power_raw
- sensor.toby_bedroom_sockets_energy_total_raw
- sensor.toby_bedroom_sockets_power_raw
- sensor.toby_bedroom_light_energy_total_raw
- sensor.toby_bedroom_light_power_raw
# GivTCP completion time sensors
- sensor.givtcp_xxxx_charge_completion_time
- sensor.givtcp_xxxx_discharge_completion_time
- sensor.givtcp_xxxx_charge_time_remaining
- sensor.givtcp_xxxx_discharge_time_remaining
# Predbat baseline forecast sensors
- predbat.battery_cycle
- predbat.battery_hours_left
- predbat.battery_power
- predbat.car_soc
- predbat.charge_end
- predbat.charge_limit
- predbat.charge_limit_kw
- predbat.charge_start
- predbat.duration
- predbat.export_energy
- predbat.grid_power
- predbat.import_energy
- predbat.import_energy_battery
- predbat.import_energy_house
- predbat.load_energy
- predbat.load_power
- predbat.metric
- predbat.pv_energy
# - predbat.pv_power
- predbat.record
- predbat.soc_kw
- predbat.soc_min_kwh
# Predbat base10, best and best10 sensors
- predbat.soc_kw_base10
- predbat.soc_kw_best10
# Predbat other sensors
- predbat.plan_html
# Predbat In-day load adjustment sensors
- predbat.load_energy_actual
- predbat.load_energy_adjusted
- predbat.load_energy_predicted
- predbat.load_inday_adjustment
# Predbat PV sensors (duplicates of Solcast sensors)
- sensor.predbat_pv_today
- sensor.predbat_pv_tomorrow
- sensor.predbat_pv_d2
- sensor.predbat_pv_d3
# - sensor.predbat_pv_forecast_h0
# predbat rate and cost sensors
- predbat.cost_today_export
- predbat.cost_today_import
- predbat.low_rate_cost
- predbat.low_rate_duration
- predbat.low_rate_end
- predbat.low_rate_start
- predbat.low_rate_cost_2
- predbat.low_rate_end_2
- predbat.low_rate_start_2
- predbat.high_rate_export_cost
- predbat.high_rate_export_duration
- predbat.high_rate_export_end
- predbat.high_rate_export_start
- predbat.high_rate_export_cost_2
- predbat.high_rate_export_end_2
- predbat.high_rate_export_start_2
# Predbat inverter statii sensors
- sensor.predbat_ge_0_scheduled_discharge_enable
- sensor.predbat_ge_1_scheduled_discharge_enable
- sensor.predbat_ge_0_idle_start_time
- sensor.predbat_ge_1_idle_start_time
- sensor.predbat_ge_0_idle_end_time
- sensor.predbat_ge_1_idle_end_time
It may not be perfect, would need to double check against the current database contents, but it will make quite a difference if you implement this
@gcoan Thank you for your reply and the detail for your recorder excludes. I've copied all the predbat ones over to mine and had a quick review and the only two I've commented out are sensor.predbat_pv_today and sensor.predbat_pv_tomorrow because I use these in the apex charts for showing the actual against forecast for today and tomorrow (I probably could replace with some solcast sensors but for now I don't want to break anything).
I suspect I have a fair bit of noise from my Fox inverter/battery connected via modbus which is probably updating a number of sensors every 10 seconds or so but I'll deal with that in another pass. My usual approach to HA DB size reduction is to find the worst offenders and deal with them first, hence this issue being created.
Sensorbox is a 3d printer air quality monitoring project link. The default ESPHome configuration for it had most sensors sending updates every 5 or 10 seconds which I've now changed to 5 minutes and was why a few of those were appearing in my top 20 despite having barely any attribute data.
Hello again. I found that pretty much all of the predbat output entities don't need history being retained (so hence are included in the excludes above) and the Apex charts generally take the latest value, so a lot of guff is being stored unnecessarily.
I exclude the predbat PV sensors because I have the solcast sensors as input to predbat, and just changed the Apex charts to match. Did find an issue with the PV chart on the predbat web console which rely on the PV history, so there are a couple of lines commented out, and on my to-do list is to change the predbat code to use the inverter input rather than the predbat output sensors.
The Octopus event sensors may well be able to be safely excluded as well, and at the moment I can't remember why I elected to retain them but purge, it might have been because I felt it useful to see that they are being updated, but can't remember for sure. As I said I have an automation that runs every night that aggressively purges sensors so I only retain entity history of what I need for dashboards or adhoc queries.
Sorting out noisy sensors is a whole different topic, which I have written up and shared on the givenergy community. Its not really predbat specific so maybe I ought to share it on the HA community at some point. A lot of my shelly energy sensors were very noisy with lots and lots of updates filling the database, I changed these to filter sensors so sample them say every minute rather than getting a state change for every single watt of power consumption change.
Similar issue with ESPhome but that at least is easy to change in the config. The sensorbox looks an interesting project. I have a similar Chinese air quality monitor in my workshop. Not HA connected though.
If it is at all interesting, here is my purge script. I purge different sensors at different intervals (1 day, 2 days, 8 days, etc). I was only looking at the number of rows in the states table so I had no idea so much was being taken up in the attributes. So looks like I have some work to do.
On Mondays, and the 1st of the month, I just compress the database and don't purge. That's due to my backup schedule which retains weekly & monthly backups for longer.
Normal history retention period is 21 days, database size is 2.9GB - 3.1GB, backup size is about 1.1GB. I have a lot of busy sensors.
alias: HA_periodic_database_purge
description: >-
Clean off old statistics from uninteresting sensors. SELECT sm.entity_id,
COUNT(s.metadata_id) as count_records FROM states s JOIN states_meta sm ON
s.metadata_id = sm.metadata_id GROUP BY sm.entity_id ORDER BY count_records
DESC;
triggers:
- at: "01:40:00"
trigger: time
conditions: []
actions:
- if:
- condition: or
conditions:
- condition: time
weekday:
- mon
- condition: template
value_template: "{{ now().day == 1 }}"
then:
- data:
repack: true
action: recorder.purge
else:
- data:
keep_days: 1
entity_id:
- automation.foxess_set_environmental_charge_limit
- automation.foxess_recalculate_battery_charge
- binary_sensor.foxess_battery_high_temp
- binary_sensor.foxess_battery_low_temp
- binary_sensor.foxess_inverter_high_temp
- binary_sensor.pv2_active
- binary_sensor.pv3_active
- binary_sensor.pv_active
- camera.front_door
- camera.front_door_live_view
- climate.foxess_inverter_fan_control
- climate.foxess_battery_fan
- climate.attic_east_ventilation
- climate.attic_west_ventilation
- climate.test_pv3_cooling
- input_number.pv_remaining_delta
- input_number.foxess_battery_soc_exact
- media_player.mike_s_echo_show_8_bedroom
- sensor.feed_in_energy_riemann
- sensor.feed_in_energy_riemann_today
- sensor.fire_tablet_free_memory
- sensor.fire_tablet_internal_storage_free_space
- sensor.foxess_battery_soc_from_invbatpower
- sensor.foxess_battery_temp_delta
- sensor.foxess_invbatpower_adjusted
- sensor.foxess_inverter_temp_delta
- sensor.grid_consumption_w
- sensor.grid_ct_shelly_delta
- sensor.invbatvolt
- sensor.invbatcurrent
- sensor.load_delta
- sensor.load_delta_avg
- sensor.load_delta_energy
- sensor.meross_3_voltage
- sensor.meross_9_voltage
- sensor.meross_10_voltage
- sensor.meross_11_voltage
- sensor.meross_12_voltage
- sensor.meross_13_voltage
- sensor.meross_14_voltage
- sensor.meross_15_voltage
- sensor.meross_3_current
- sensor.meross_9_current
- sensor.meross_10_current
- sensor.meross_11_current
- sensor.meross_12_current
- sensor.meross_13_current
- sensor.meross_14_current
- sensor.meross_15_current
- sensor.meross_1_signal_strength
- sensor.meross_2_signal_strength
- sensor.meross_3_signal_strength
- sensor.meross_4_signal_strength
- sensor.meross_5_signal_strength
- sensor.meross_6_signal_strength
- sensor.meross_7_signal_strength
- sensor.meross_8_signal_strength
- sensor.meross_9_signal_strength
- sensor.meross_10_signal_strength
- sensor.meross_11_signal_strength
- sensor.meross_12_signal_strength
- sensor.meross_13_signal_strength
- sensor.meross_14_signal_strength
- sensor.meross_15_signal_strength
- sensor.pv_power_w
- sensor.pv_remaining_adjusted
- sensor.pv1_current
- sensor.pv1_energy_today
- sensor.pv1_energy_total
- sensor.pv1_power
- sensor.pv1_voltage
- sensor.rvolt
- sensor.rfreq
- sensor.rcurrent
- sensor.shelly_3em_c8_consumer_unit_channel_a_current
- sensor.shelly_3em_c8_consumer_unit_channel_a_power_factor
- sensor.shelly_3em_c8_consumer_unit_channel_a_voltage
- sensor.shelly_3em_c8_consumer_unit_channel_b_current
- sensor.shelly_3em_c8_consumer_unit_channel_b_power_factor
- sensor.shelly_3em_c8_consumer_unit_channel_b_voltage
- sensor.smart_switch_2307048220526551080148e1e9ce8294_current
- sensor.solar_energy_riemann
- sensor.solar_energy_riemann_today
- sensor.ss1tp_switch_1_battery_fan_voltage
- sensor.ss1tp_switch_1_battery_fan_current
- sensor.ss1tp_switch_2_inverter_fan_current
- sensor.ss1tp_switch_2_inverter_fan_voltage
- sensor.ss1tp_switch_2_kiosk_charger_current
- sensor.ss1tp_switch_2_kiosk_charger_voltage
- sensor.ss1tp_switch_3_heater_voltage
- sensor.ss1tp_switch_3_heater_current
- sensor.ss1tp_switch_4_pedestal_fan_voltage
- sensor.ss1tp_switch_4_pedestal_fan_current
- sensor.ss2tp_switch_1_voltage
- sensor.ss2tp_switch_1_current
- sensor.ss2tp_switch_2_voltage
- sensor.ss2tp_switch_2_current
- sensor.ss2tp_switch_3_voltage
- sensor.ss2tp_switch_3_current
- sensor.ss2tp_switch_4_voltage
- sensor.ss2tp_switch_4_current
- sensor.ss3tp_switch_1_voltage
- sensor.ss3tp_switch_1_current
- sensor.ss3tp_switch_2_voltage
- sensor.ss3tp_switch_2_current
- sensor.ss3tp_switch_3_voltage
- sensor.ss3tp_switch_3_current
- sensor.ss3tp_switch_4_voltage
- sensor.ss3tp_switch_4_current
- sensor.tonyhoyle_bms_actual_kwh_remaining
- sensor.watchman_missing_entities
- sensor.watchman_last_updated
- sensor.x60_data_received
- sensor.x60_data_sent
- sensor.x60_download_speed
- sensor.x60_upload_speed
- sensor.x60_uptime
- switch.predbat_active
- >-
device_tracker.woan_technology_shenzhen_co_ltd_f3_34_57_9d_f9_e0_bermuda_tracker
- sensor.foxess_system_status_battery
- sensor.foxess_system_status_grid_now
- sensor.foxess_system_status_household
- sensor.foxess_system_status_solar
- sensor.sb_indoor_outdoor_meter_entrance_distance
- >-
sensor.sb_indoor_outdoor_meter_entrance_distance_to_bluetooth_proxy_98e26c_attic
- >-
sensor.sb_indoor_outdoor_meter_entrance_distance_to_bluetooth_proxy_990eb0
- >-
sensor.sb_indoor_outdoor_meter_entrance_distance_to_bluetooth_proxy_992958
- >-
sensor.sb_indoor_outdoor_meter_entrance_distance_to_hci0_minipc_motherboard_bluetooth_3c_3b_ad_6c_07_bb
- sensor.shortcut_labs_d7_07_c2_7a_70_c9_distance
- >-
sensor.shortcut_labs_d7_07_c2_7a_70_c9_distance_to_bluetooth_proxy_98e26c
- >-
sensor.shortcut_labs_d7_07_c2_7a_70_c9_distance_to_bluetooth_proxy_990eb0
- >-
sensor.shortcut_labs_d7_07_c2_7a_70_c9_distance_to_bluetooth_proxy_992958
- >-
sensor.shortcut_labs_d7_07_c2_7a_70_c9_distance_to_hci0_minipc_motherboard_bluetooth_3c_3b_ad_6c_07_bb
- sensor.woan_technology_shenzhen_co_ltd_b0_e9_fe_ec_bd_b8_distance
- >-
sensor.woan_technology_shenzhen_co_ltd_b0_e9_fe_ec_bd_b8_distance_to_bluetooth_proxy_98e26c_attic
- >-
sensor.woan_technology_shenzhen_co_ltd_b0_e9_fe_ec_bd_b8_distance_to_bluetooth_proxy_990eb0
- >-
sensor.woan_technology_shenzhen_co_ltd_b0_e9_fe_ec_bd_b8_distance_to_bluetooth_proxy_992958
- >-
sensor.woan_technology_shenzhen_co_ltd_b0_e9_fe_ec_bd_b8_distance_to_hci0_minipc_motherboard_bluetooth_3c_3b_ad_6c_07_bb
- sensor.woan_technology_shenzhen_co_ltd_d0_9d_69_ee_ea_c7_distance
- >-
sensor.woan_technology_shenzhen_co_ltd_d0_9d_69_ee_ea_c7_distance_to_bluetooth_proxy_98e26c_attic
- >-
sensor.woan_technology_shenzhen_co_ltd_d0_9d_69_ee_ea_c7_distance_to_bluetooth_proxy_990eb0
- >-
sensor.woan_technology_shenzhen_co_ltd_d0_9d_69_ee_ea_c7_distance_to_bluetooth_proxy_992958
- >-
sensor.woan_technology_shenzhen_co_ltd_d0_9d_69_ee_ea_c7_distance_to_hci0_minipc_motherboard_bluetooth_3c_3b_ad_6c_07_bb
- sensor.woan_technology_shenzhen_co_ltd_d4_0e_84_86_25_18_distance
- >-
sensor.woan_technology_shenzhen_co_ltd_d4_0e_84_86_25_18_distance_to_bluetooth_proxy_98e26c_attic
- >-
sensor.woan_technology_shenzhen_co_ltd_d4_0e_84_86_25_18_distance_to_bluetooth_proxy_990eb0
- >-
sensor.woan_technology_shenzhen_co_ltd_d4_0e_84_86_25_18_distance_to_hci0_minipc_motherboard_bluetooth_3c_3b_ad_6c_07_bb
- sensor.woan_technology_shenzhen_co_ltd_dd_42_05_86_19_6f_distance
- >-
sensor.woan_technology_shenzhen_co_ltd_dd_42_05_86_19_6f_distance_to_bluetooth_proxy_98e26c_bedroom
- >-
sensor.woan_technology_shenzhen_co_ltd_dd_42_05_86_19_6f_distance_to_bluetooth_proxy_990eb0
- >-
sensor.woan_technology_shenzhen_co_ltd_dd_42_05_86_19_6f_distance_to_bluetooth_proxy_992958_entrance
- >-
sensor.woan_technology_shenzhen_co_ltd_dd_42_05_86_19_6f_distance_to_hci0_minipc_motherboard_bluetooth_3c_3b_ad_6c_07_bb
- sensor.woan_technology_shenzhen_co_ltd_ee_ae_7f_5b_dc_d9_distance
- >-
sensor.woan_technology_shenzhen_co_ltd_ee_ae_7f_5b_dc_d9_distance_to_bluetooth_proxy_98e26c_attic
- >-
sensor.woan_technology_shenzhen_co_ltd_ee_ae_7f_5b_dc_d9_distance_to_bluetooth_proxy_990eb0
- >-
sensor.woan_technology_shenzhen_co_ltd_ee_ae_7f_5b_dc_d9_distance_to_bluetooth_proxy_992958
- >-
sensor.woan_technology_shenzhen_co_ltd_ee_ae_7f_5b_dc_d9_distance_to_hci0_minipc_motherboard_bluetooth_3c_3b_ad_6c_07_bb
- sensor.woan_technology_shenzhen_co_ltd_f3_34_57_9d_f9_e0_distance
- >-
sensor.woan_technology_shenzhen_co_ltd_f3_34_57_9d_f9_e0_distance_to_bluetooth_proxy_98e26c_attic
- >-
sensor.woan_technology_shenzhen_co_ltd_f3_34_57_9d_f9_e0_distance_to_bluetooth_proxy_990eb0
- >-
sensor.woan_technology_shenzhen_co_ltd_f3_34_57_9d_f9_e0_distance_to_bluetooth_proxy_992958
- >-
sensor.woan_technology_shenzhen_co_ltd_f3_34_57_9d_f9_e0_distance_to_hci0_minipc_motherboard_bluetooth_3c_3b_ad_6c_07_bb
- sensor.woan_technology_shenzhen_co_ltd_fd_c7_0b_af_69_a0_distance
- >-
sensor.woan_technology_shenzhen_co_ltd_fd_c7_0b_af_69_a0_distance_to_bluetooth_proxy_98e26c_attic
- >-
sensor.woan_technology_shenzhen_co_ltd_fd_c7_0b_af_69_a0_distance_to_bluetooth_proxy_990eb0
- >-
sensor.woan_technology_shenzhen_co_ltd_fd_c7_0b_af_69_a0_distance_to_hci0_minipc_motherboard_bluetooth_3c_3b_ad_6c_07_bb
action: recorder.purge_entities
- delay:
hours: 0
minutes: 10
seconds: 0
milliseconds: 0
- data:
keep_days: 2
entity_id:
- device_tracker.bike_bag
- device_tracker.car_2
- device_tracker.id_badge
- device_tracker.id_badge_replaced
- device_tracker.keys
- device_tracker.laptop
- device_tracker.other
- device_tracker.overnight_bag
- device_tracker.overnight_bag_replaced
- device_tracker.s10
- device_tracker.s22
- device_tracker.wallet
- device_tracker.wallet_2
- predbat.battery_cycle_best
- predbat.soc_kw_best
- predbat.soc_kw_best10
- predbat.battery_cycle
- predbat.best_load_energy
- predbat.record
- predbat.base10_load_energy
- predbat.best10_load_energy
- predbat.best10_import_energy
- predbat.best10_metric
- predbat.best_import_energy
- predbat.best_metric
- predbat.best_import_energy_battery
- predbat.carbon_best
- predbat.base10_metric
- predbat.soc_kw_base10
- sensor.attic_daily_energy
- sensor.calc_inverter_losses
- sensor.eon_next_drive_power_grid_cheap
- sensor.foxess_battery_soc_exact
- sensor.foxess_export_estimate_today
- sensor.foxess_highest_vs_forecast_today_spread
- sensor.foxess_highest_vs_main_forecast_today_spread
- sensor.foxess_highest_vs_forecast_tomorrow_spread
- sensor.inverter_losses_avg
- sensor.load_energy_riemann_total
- sensor.load_energy_riemann_today
- sensor.meross_3_power
- sensor.meross_9_power
- sensor.meross_10_power
- sensor.meross_11_power
- sensor.meross_12_power
- sensor.meross_13_power
- sensor.meross_14_power
- sensor.meross_15_power
- sensor.meross_3_consumption
- sensor.meross_9_consumption
- sensor.meross_10_consumption
- sensor.meross_11_consumption
- sensor.meross_12_consumption
- sensor.meross_13_consumption
- sensor.meross_14_consumption
- sensor.meross_15_consumption
- sensor.pv2_current
- sensor.pv2_energy_today
- sensor.pv2_energy_total
- sensor.pv2_panel_temp
- sensor.pv2_panel_temp_avg
- sensor.pv2_voltage
- sensor.pv2_voltage_avg
- sensor.pv2_power_vs_forecast_perc
- sensor.pv2_temp_losses
- sensor.pv3_temp_losses
- sensor.pv3_power_vs_forecast_perc
- sensor.pv3_current
- sensor.pv3_energy_today
- sensor.pv3_energy_total
- sensor.pv3_panel_temp
- sensor.pv3_panel_temp_avg
- sensor.pv3_voltage
- sensor.pv3_voltage_avg
- sensor.rpower
- sensor.room_dining_daily_energy
- sensor.room_dining_energy
- sensor.room_kitchen_daily_energy
- sensor.room_kitchen_energy
- sensor.room_living_daily_energy
- sensor.room_living_energy
- sensor.room_main_bedroom_daily_energy
- sensor.room_main_bedroom_energy
- sensor.room_other_daily_energy
- sensor.room_other_energy
- sensor.room_other_power
- sensor.room_spare_bedroom_daily_energy
- sensor.room_spare_bedroom_energy
- sensor.shelly_plug_attic_switch_0_energy
- sensor.shelly_plug_bedroom_daily_energy
- sensor.shelly_plug_bedroom_switch_0_energy
- sensor.shelly_plug_filing_heat_pad_daily_energy
- sensor.shelly_plug_filing_heat_pad_switch_0_energy
- sensor.shelly_plug_ikea_daily_energy
- sensor.shelly_plug_ikea_switch_0_energy
- sensor.shelly_plug_laptop_daily_energy
- sensor.shelly_plug_laptop_switch_0_energy
- sensor.shelly_plug_pc_daily_energy
- sensor.shelly_plug_pc_switch_0_energy
- sensor.shelly_plug_router_daily_energy
- sensor.shelly_plug_router_switch_0_energy
- sensor.shelly_plug_attic_switch_0_power
- sensor.shelly_plug_bedroom_switch_0_power
- sensor.shelly_plug_ikea_switch_0_power
- sensor.shelly_plug_laptop_switch_0_power
- sensor.shelly_plug_pc_switch_0_power
- sensor.shelly_plug_router_switch_0_power
- sensor.shelly_pm_cu_monitoring_power
- sensor.shelly_pm_kitchen_fridge_power
- sensor.shelly_pm_kitchen_sink_power
- sensor.shelly_pm_kitchen_washing_machine_power
- sensor.shelly_pm_living_room_laptop_power
- sensor.shelly_pm_cu_monitoring_daily_monitoring
- sensor.shelly_pm_cu_monitoring_energy
- sensor.shelly_pm_kitchen_fridge_daily_energy
- sensor.shelly_pm_kitchen_fridge_energy
- sensor.shelly_pm_kitchen_sink_daily_energy
- sensor.shelly_pm_kitchen_sink_energy
- sensor.shelly_pm_kitchen_washing_machine_daily_energy
- sensor.shelly_pm_kitchen_washing_machine_energy
- sensor.shelly_pm_living_room_laptop_daily_energy
- sensor.shelly_pm_living_room_laptop_energy
- sensor.ss1tp_switch_1_battery_fan_current_consumption
- sensor.ss1tp_switch_2_inverter_fan_current_consumption
- sensor.ss1tp_switch_2_kiosk_charger_current_consumption
- sensor.ss1tp_switch_3_heater_current_consumption
- sensor.ss1tp_switch_4_pedestal_fan_current_consumption
- sensor.ss2tp_switch_1_current_consumption
- sensor.ss2tp_switch_2_current_consumption
- sensor.ss2tp_switch_3_current_consumption
- sensor.ss2tp_switch_4_current_consumption
- sensor.ss3tp_switch_1_current_consumption
- sensor.ss3tp_switch_2_current_consumption
- sensor.ss3tp_switch_3_current_consumption
- sensor.ss3tp_switch_4_current_consumption
- sensor.tigo_a1_current
- sensor.tigo_a2_current
- sensor.tigo_a3_current
- sensor.tigo_a4_current
- sensor.tigo_a5_current
- sun.sun
action: recorder.purge_entities
- if:
- condition: time
weekday:
- tue
- wed
- thu
- fri
- sat
- sun
then:
- delay:
hours: 0
minutes: 10
seconds: 0
milliseconds: 0
- data:
keep_days: 8
entity_id:
- binary_sensor.foxess_inverter_high_temp
- sensor.battery_charge
- sensor.battery_discharge
- sensor.bms_cell_temp_high
- sensor.bms_cell_temp_low
- sensor.foxess_charge_speed_for_7am
- sensor.foxess_discharge_speed_for_midnight
- sensor.foxess_forecast_arbitrage_kwh
- sensor.foxess_estimated_load_today
- sensor.foxess_predbat_estimated_remaining_load_until_midnight
- sensor.foxess_estimated_remaining_load_until_midnight
- sensor.grid_ct
- sensor.grid_consumption
- sensor.grid_consumption_energy_today
- sensor.invbatpower
- sensor.invtemp
- sensor.pv2_power_panel_avg
- sensor.pv2_voltage_panel_avg
- sensor.pv2_panel_temp_avg
- sensor.pv3_panel_temp_avg
- sensor.room_dining_power
- sensor.room_kitchen_power
- sensor.room_living_power
- sensor.room_main_bedroom_power
- sensor.room_spare_bedroom_power
- sensor.shelly_3em_c8_consumer_unit_channel_a_power
- sensor.shelly_3em_c8_consumer_unit_channel_b_power
- sensor.tado_home_temp_median
- sensor.tigo_active_panel_voltage_average
- sensor.tigo_active_panel_voltage_average_over_time
- sensor.tigo_a1_power
- sensor.tigo_a2_power
- sensor.tigo_a3_power
- sensor.tigo_a4_power
- sensor.tigo_a5_power
- sensor.tigo_a1_voltage
- sensor.tigo_a2_voltage
- sensor.tigo_a3_voltage
- sensor.tigo_a4_voltage
- sensor.tigo_a5_voltage
action: recorder.purge_entities
- delay:
hours: 0
minutes: 10
seconds: 0
milliseconds: 0
- data:
repack: true
action: recorder.purge
mode: single
Here in return then is my database purge automation. I do my purges in themed sections with delays in between, not dissimilar to yours.
One comment on yours, you can use entity_globs in the recorder.purge action to specify wildcards which will cutdown the number of individual lines you need.
I've written a whole long article on how to investigate and tackle the size of the HA database including the SQL I used to identify large number of attributes (which in my case was by far the biggest contributor to database size). I've also identified ways of reducing the noise from sensors, particularly Shelly energy monitors. Got my database down to 640Mb and backups to ~260Mb.
I'll copy it to the public HA community forum because at the moment its in the GivEnergy users forum.
alias: Purge unwanted sensors to reduce database size
description: ""
triggers:
- trigger: time
at: "02:00:00"
actions:
- alias: Purge all system monitor sensors to 0 days
action: recorder.purge_entities
data:
keep_days: 0
entity_globs:
- sensor.system_monitor_*
entity_id:
- sensor.givtcp_beta_cpu_percent
- sensor.predbat_cpu_percent
- sensor.studio_code_server_cpu_percent
- sensor.givtcp_beta_memory_percent
- sensor.predbat_memory_percent
- sensor.studio_code_server_memory_percent
- delay:
seconds: 20
- action: recorder.purge_entities
alias: Purge home power sensors to 0 days
data:
keep_days: 0
entity_id:
- sensor.cooker_power
- sensor.dishwasher_power
- sensor.extension_power
- sensor.fridge_power
- sensor.hot_tub_power
- sensor.kettle_power
- sensor.tumble_dryer_power
- sensor.washing_machine_power
- sensor.internet_ha_power
- sensor.tv_power
- sensor.toby_bedroom_light_power
- sensor.toby_bedroom_sockets_power
- sensor.workshop_heater_power
- sensor.horse_shed_heater_power
- sensor.tasmota_spare_1_power
- sensor.tasmota_spare_2_power
- sensor.tasmota_spare_3_power
- sensor.g_sd2237g182_eps_power
- sensor.h_sd2237g395_grid_power
- delay:
seconds: 20
- alias: Purge Home energy_total sensors to 0 days
action: recorder.purge_entities
data:
keep_days: 0
entity_id:
- sensor.ashp_energy_total
- sensor.cooker_energy_total
- sensor.dishwasher_energy_total
- sensor.extension_energy_total
- sensor.fridge_energy_total
- sensor.hot_tub_energy_total
- sensor.fit_solar_energy_total
- sensor.kettle_energy_total
- sensor.tumble_dryer_energy_total
- sensor.washing_machine_energy_total
- sensor.tv_energy_total
- sensor.toby_bedroom_sockets_energy_total
- sensor.toby_bedroom_light_energy_total
- sensor.tasmota_spare_1_energy_total
- sensor.tasmota_spare_2_energy_total
- sensor.tasmota_spare_3_energy_total
- delay:
seconds: 20
- action: recorder.purge_entities
alias: Purge monthly energy sensors to 0 days
data:
keep_days: 0
entity_id:
- sensor.internet_ha_month_energy
- sensor.workshop_heater_energy_month
- sensor.horse_shed_heater_month_energy
- delay:
seconds: 20
- action: recorder.purge_entities
alias: Keep power grid to battery sensor data (TEMP)
data:
keep_days: 10
entity_id:
- sensor.g_sd2237g182_grid_to_battery
- sensor.h_sd2237g395_grid_to_battery
- sensor.ashp_power
- sensor.g_sd2237g182_pv_power_string_1
- sensor.g_sd2237g182_pv_power_string_2
- sensor.h_sd2237g395_pv_power_string_1
- sensor.h_sd2237g395_pv_power_string_2
- action: recorder.purge_entities
alias: Purge GivTCP energy sensors to 0 days
data:
keep_days: 0
entity_id:
- sensor.g_sd2237g182_ac_charge_energy_today_kwh
- sensor.h_sd2237g395_ac_charge_energy_today_kwh
- sensor.g_sd2237g182_battery_charge_energy_today_kwh
- sensor.h_sd2237g395_battery_charge_energy_today_kwh
- sensor.g_sd2237g182_battery_charge_energy_total_kwh
- sensor.h_sd2237g395_battery_charge_energy_total_kwh
- sensor.g_sd2237g182_battery_discharge_energy_today_kwh
- sensor.h_sd2237g395_battery_discharge_energy_today_kwh
- sensor.g_sd2237g182_battery_discharge_energy_total_kwh
- sensor.h_sd2237g395_battery_discharge_energy_total_kwh
- sensor.g_sd2237g182_battery_throughput_today_kwh
- sensor.g_sd2237g182_battery_throughput_total_kwh
- sensor.h_sd2237g395_battery_throughput_today_kwh
- sensor.h_sd2237g395_battery_throughput_total_kwh
- sensor.g_sd2237g182_export_energy_total_kwh
- sensor.h_sd2237g395_export_energy_today_kwh
- sensor.h_sd2237g395_export_energy_total_kwh
- sensor.g_sd2237g182_import_energy_total_kwh
- sensor.h_sd2237g395_import_energy_today_kwh
- sensor.h_sd2237g395_import_energy_total_kwh
- sensor.g_sd2237g182_invertor_energy_today_kwh
- sensor.g_sd2237g182_invertor_energy_total_kwh
- sensor.h_sd2237g395_invertor_energy_today_kwh
- sensor.h_sd2237g395_invertor_energy_total_kwh
- sensor.g_sd2237g182_battery_to_grid
- sensor.h_sd2237g395_battery_to_grid
- sensor.g_sd2237g182_battery_to_house
- sensor.h_sd2237g395_battery_to_house
- sensor.g_sd2237g182_solar_to_battery
- sensor.h_sd2237g395_solar_to_battery
- sensor.g_sd2237g182_solar_to_grid
- sensor.h_sd2237g395_solar_to_grid
- sensor.g_sd2237g182_solar_to_house
- sensor.h_sd2237g395_solar_to_house
- delay:
seconds: 20
- action: recorder.purge_entities
alias: Purge Octopus sensors and events to 0 days
data:
keep_days: 0
entity_id:
- binary_sensor.octopus_energy_a_xxxx_octoplus_saving_sessions
- event.octopus_energy_a_xxx_octoplus_saving_session_events
- >-
event.octopus_energy_electricity_xxxx_current_day_rates
- >-
event.octopus_energy_electricity_xxxx_next_day_rates
- >-
event.octopus_energy_electricity_xxxx_previous_day_rates
- >-
event.octopus_energy_electricity_xxxx_export_current_day_rates
- >-
event.octopus_energy_electricity_xxxx_export_next_day_rates
- >-
event.octopus_energy_electricity_xxxx_export_previous_day_rates
- >-
sensor.octopus_energy_electricity_xxxx_current_rate
- sensor.octopus_energy_electricity_xxxx_next_rate
- >-
sensor.octopus_energy_electricity_xxxx_previous_rate
- >-
sensor.octopus_energy_electricity_xxxx_export_current_rate
- >-
sensor.octopus_energy_electricity_xxxx_export_next_rate
- >-
sensor.octopus_energy_electricity_xxxx_export_previous_rate
- sensor.octopus_energy_a_xxxx_octoplus_points
- sensor.octopus_energy_a_xxxx_greenness_forecast_current_index
- >-
sensor.octopus_energy_electricity_xxxx_current_accumulative_cost
- >-
sensor.octopus_energy_electricity_xxxx_export_previous_accumulative_cost
- delay:
seconds: 20
- action: recorder.purge_entities
alias: Purge Other misc sensors to 0 days
data:
keep_days: 0
entity_id:
- sensor.inverter_1_fans_on_today
- sensor.inverter_2_fans_on_today
- switch.predbat_active
- delay:
seconds: 20
- action: recorder.purge_entities
alias: >-
Purge Predbat selects, cost, power forecast and today energy sensors to 1
day
data:
keep_days: 1
entity_globs:
- select.predbat_*
entity_id:
- predbat.cost_today
- predbat.cost_hour
- predbat.ppkwh_hour
- predbat.ppkwh_today
- predbat.rates
- predbat.rates_export
- predbat.soc_kw_h0
- predbat.soc_kw_best_h1
- predbat.soc_kw_best_h8
- predbat.soc_kw_best_h12
- predbat.export_energy_h0
- predbat.import_energy_h0
- predbat.load_energy_h0
- predbat.pv_energy_h0
- delay:
seconds: 20
- action: recorder.purge_entities
alias: Purge Solar GivTCP sensors to 3 days
data:
keep_days: 3
entity_id:
- sensor.fit_solar_power
- sensor.total_solar_power
- sensor.g_sd2237g182_export_power
- sensor.g_sd2237g182_grid_power
- sensor.g_sd2237g182_grid_voltage
- sensor.g_sd2237g182_import_power
- sensor.g_sd2237g182_pv_power
- sensor.h_sd2237g395_pv_power
- delay:
seconds: 20
- action: recorder.purge_entities
alias: Purge GivTCP sensors to 1 day
data:
keep_days: 1
entity_id:
- sensor.g_sd2237g182_charge_power
- sensor.h_sd2237g395_charge_power
- sensor.g_sd2237g182_discharge_power
- sensor.h_sd2237g395_discharge_power
- sensor.g_sd2237g182_grid_current
- sensor.g_sd2237g182_grid_frequency
- sensor.g_sd2237g182_invertor_power
- sensor.h_sd2237g395_invertor_power
- sensor.g_sd2237g182_invertor_temperature
- sensor.h_sd2237g395_invertor_temperature
- sensor.g_sd2237g182_invertor_time
- sensor.h_sd2237g395_invertor_time
- sensor.g_sd2237g182_last_updated_time
- sensor.h_sd2237g395_last_updated_time
- sensor.g_sd2237g182_load_power
- sensor.h_sd2237g395_load_power
- sensor.g_sd2237g182_load_energy_today_kwh
- sensor.h_sd2237g395_load_energy_today_kwh
- action: recorder.purge_entities
alias: Purge SoC sensors to 1 day - temp disabled for BMS investigation
data:
keep_days: 1
entity_id:
- sensor.g_sd2237g182_soc
- sensor.h_sd2237g395_soc
enabled: false
- delay:
seconds: 20
- alias: Purge Climate, Temperature, Pressure and Humidity sensors to 1 day
action: recorder.purge_entities
data:
keep_days: 1
domains: climate
entity_globs:
- sensor.*_humidity
- sensor.*_th_battery
entity_id:
- sensor.porch_temperature
- sensor.freezer_temperature
- sensor.hall_temperature
- sensor.kitchen_temperature
- sensor.living_room_temperature
- sensor.bedroom_temperature
- sensor.study_temperature
- sensor.workshop_temperature
- sensor.horse_shed_temperature
- sensor.loft_temperature
- sensor.dhw_temperature
- sensor.workshop_pressure
- sensor.loft_pressure
- delay:
seconds: 20
- alias: >-
Purge house load today, import today, export today and pv today used by
Predbat for historic forecast to 8 days
action: recorder.purge_entities
data:
keep_days: 9
entity_id:
- sensor.house_load_today
- sensor.g_sd2237g182_export_energy_today_kwh
- sensor.g_sd2237g182_import_energy_today_kwh
- sensor.grid_import_today
- sensor.total_solar_energy_today
enabled: false
- delay:
seconds: 20
- alias: >-
Purge signal strength, template battery charge/discharge and all other
total_today sensors to 1 day
action: recorder.purge_entities
data:
keep_days: 1
entity_id:
- sensor.horse_shed_th_signal_strength
- sensor.battery_charge_today
- sensor.battery_discharge_today
- sensor.g_battery_charge_today
- sensor.g_battery_discharge_today
- sensor.h_battery_charge_today
- sensor.h_battery_discharge_today
- sensor.total_battery_charge_today
- sensor.total_battery_discharge_today
- sensor.g_sd2237g182_export_energy_total_kwh_compensation
- sensor.g_solar_energy_today
- sensor.h_solar_energy_today
- sensor.house_net_load_today
- sensor.extension_load_today
- sensor.extension_load_today_net
- sensor.total_ge_solar_energy_today
- delay:
seconds: 20
- action: recorder.purge_entities
alias: Automation and Solcast sensors to 7 days
data:
keep_days: 7
domains: automation
entity_globs:
- sensor.solcast_pv_*
- delay:
seconds: 20
- action: recorder.purge_entities
alias: Purge Home GivTCP PV sensors to 7 days
data:
keep_days: 7
entity_id:
- sensor.g_sd2237g182_pv_energy_today_kwh
- sensor.h_sd2237g395_pv_energy_today_kwh
- sensor.g_sd2237g182_pv_energy_total_kwh
- sensor.h_sd2237g395_pv_energy_total_kwh
- delay:
seconds: 20
- action: recorder.purge_entities
alias: Purge Home GivTCP inverter G battery cell & temperature sensors to 7 days
data:
keep_days: 7
entity_id:
- sensor.g_battery_stack_1_bms_temperature
- sensor.g_dx2327g139_battery_temperature
- sensor.g_dx2327g139_battery_voltage
- sensor.g_dx2327g139_battery_cell_1_voltage
- sensor.g_dx2327g139_battery_cell_2_voltage
- sensor.g_dx2327g139_battery_cell_3_voltage
- sensor.g_dx2327g139_battery_cell_4_voltage
- sensor.g_dx2327g139_battery_cell_5_voltage
- sensor.g_dx2327g139_battery_cell_6_voltage
- sensor.g_dx2327g139_battery_cell_7_voltage
- sensor.g_dx2327g139_battery_cell_8_voltage
- sensor.g_dx2327g139_battery_cell_9_voltage
- sensor.g_dx2327g139_battery_cell_10_voltage
- sensor.g_dx2327g139_battery_cell_11_voltage
- sensor.g_dx2327g139_battery_cell_12_voltage
- sensor.g_dx2327g139_battery_cell_13_voltage
- sensor.g_dx2327g139_battery_cell_14_voltage
- sensor.g_dx2327g139_battery_cell_15_voltage
- sensor.g_dx2327g139_battery_cell_16_voltage
- sensor.g_dx2327g139_battery_cell_1_temperature
- sensor.g_dx2327g139_battery_cell_2_temperature
- sensor.g_dx2327g139_battery_cell_3_temperature
- sensor.g_dx2327g139_battery_cell_4_temperature
- delay:
seconds: 20
- action: recorder.purge_entities
alias: Purge Home GivTCP inverter H battery cell & temperature sensors to 7 days
data:
keep_days: 7
entity_id:
- sensor.h_battery_stack_1_bms_temperature
- sensor.h_bj2238g271_battery_temperature
- sensor.h_bj2238g271_battery_voltage
- sensor.h_bj2238g271_battery_cell_1_voltage
- sensor.h_bj2238g271_battery_cell_2_voltage
- sensor.h_bj2238g271_battery_cell_3_voltage
- sensor.h_bj2238g271_battery_cell_4_voltage
- sensor.h_bj2238g271_battery_cell_5_voltage
- sensor.h_bj2238g271_battery_cell_6_voltage
- sensor.h_bj2238g271_battery_cell_7_voltage
- sensor.h_bj2238g271_battery_cell_8_voltage
- sensor.h_bj2238g271_battery_cell_9_voltage
- sensor.h_bj2238g271_battery_cell_10_voltage
- sensor.h_bj2238g271_battery_cell_11_voltage
- sensor.h_bj2238g271_battery_cell_12_voltage
- sensor.h_bj2238g271_battery_cell_13_voltage
- sensor.h_bj2238g271_battery_cell_14_voltage
- sensor.h_bj2238g271_battery_cell_15_voltage
- sensor.h_bj2238g271_battery_cell_16_voltage
- sensor.h_bj2238g271_battery_cell_1_temperature
- sensor.h_bj2238g271_battery_cell_2_temperature
- sensor.h_bj2238g271_battery_cell_3_temperature
- sensor.h_bj2238g271_battery_cell_4_temperature
- delay:
seconds: 20
- action: recorder.purge_entities
alias: Purge Home energy_today sensors to 1 days
data:
keep_days: 1
entity_id:
- sensor.ashp_energy_today
- sensor.fit_solar_energy_today
- sensor.extension_energy_today
- sensor.hot_tub_energy_today
- sensor.toby_bedroom_sockets_energy_today
- sensor.toby_bedroom_light_energy_today
- sensor.tasmota_spare_1_energy_today
- sensor.tasmota_spare_2_energy_today
- sensor.tasmota_spare_3_energy_today
- sensor.kettle_voltage
- delay:
seconds: 20
- action: recorder.purge_entities
alias: >-
Purge GivTCP inverter charge/discharge start/end time slots to 1 day (may
reduce further)
data:
keep_days: 2
entity_id:
- select.g_sd2237g182_charge_end_time_slot_1
- select.h_sd2237g395_charge_end_time_slot_1
- select.g_sd2237g182_charge_start_time_slot_1
- select.h_sd2237g395_charge_start_time_slot_1
- select.g_sd2237g182_discharge_end_time_slot_1
- select.h_sd2237g395_discharge_end_time_slot_1
- select.g_sd2237g182_discharge_start_time_slot_1
- select.h_sd2237g395_discharge_start_time_slot_1
- delay:
seconds: 20
- action: recorder.purge_entities
alias: Purge sensors required for battery charge/discharge curves to 4 days
data:
keep_days: 4
entity_id:
- predbat.status
- number.g_sd2237g182_battery_charge_rate
- number.h_sd2237g395_battery_charge_rate
- number.g_sd2237g182_battery_discharge_rate
- number.h_sd2237g395_battery_discharge_rate
- sensor.g_sd2237g182_battery_power
- sensor.h_sd2237g395_battery_power
- sensor.g_sd2237g182_soc_kwh
- sensor.h_sd2237g395_soc_kwh
- delay:
seconds: 20
- action: recorder.purge_entities
alias: Purge Home energy_today sensors to 7 days
data:
keep_days: 7
entity_id:
- sensor.cooker_energy_today
- sensor.dishwasher_energy_today
- sensor.fridge_energy_today
- sensor.kettle_energy_today
- sensor.tumble_dryer_energy_today
- sensor.washing_machine_energy_today
- sensor.internet_ha_energy_today
- sensor.tv_energy_today
- sensor.horse_shed_heater_energy_today
- sensor.workshop_heater_energy_today
- delay:
seconds: 20
- action: recorder.purge_entities
alias: >-
Purge Companion App mobile sensors and Person records to 1 day (may reduce
sensors retained further)
data:
keep_days: 1
domains:
- person
- device_tracker
entity_globs:
- binary_sensor.geoffrey*
- binary_sensor.liz*
- sensor.geoffrey*
- sensor.liz*
- if:
- condition: time
alias: If today is a Sunday
weekday:
- sun
then:
- alias: Wait for 5 minutes for previous purges to have all completed
delay:
minutes: 5
- action: recorder.purge
alias: Repack the database to save space
data:
repack: true
trace:
stored_traces: 10
mode: single
That's great thank you. I did not know about the entity_globs, looks like it will save quite a bit of effort!
-- Edit: Now I've added the predbat entries, it's down to 1.8GB rather than 2.9-3.1GB. Nice!