TIP 1 - understand what's stored in your Home Assistant database
Firstly install the SQLite Web add-on to HA which enables you to run SQL queries on your HA database.
The key tables in the HA database are:
- states_meta: holds the meta data for HA entities, that links the visible entity name to the internal HA id
- states: holds every entity state value and the history of the state changes
- state_attributes: holds the entity attributes and their history
- statistics - holds long term statistics captured hourly for HA entities, comprising max, mean, min, state and sum values
- statistics_short_term - similar statistics captured every 10 minutes
- statistics_meta - meta data for HA statistics tables
I found https://community.home-assistant.io/t/how-to-keep-your-recorder-database-size-under-control/295795 which is an excellent HA Community article that gave me the basis for the journey, finding out through SQL queries that there were an awful lot of entities with a lot of records being written to the state and state_attributes tables, and a lot of which I really didn't need retaining with this much detail.
For example, states table:
cnt cnt_pct entity_id
176220 3 sensor.total_solar_power
126528 2 sensor.fit_solar_power
126519 2 sensor.ashp_power
119023 2 sensor.bedroom_sockets_power
114565 2 sensor.extension_power
107619 2 sensor.hot_tub_power
80343 1 sensor.system_monitor_load_1m
75128 1 sensor.system_monitor_load_5m
69513 1 sensor.system_monitor_memory_usage
69022 1 sensor.system_monitor_processor_use
60135 1 sensor.system_monitor_load_15m
49728 1 sensor.sum_ge_solar_power
44143 0 sensor.givtcp_[inv id]_last_updated_time
44141 0 sensor.givtcp_[inv id]_invertor_time
44078 0 sensor.givtcp_[inv id]_time_since_last_update
The columns being number of rows, percentage of the database and entity id.
state_attributes was a very similar pattern.
So the entity 'total_solar_power' which measures my total solar power generation summed across my 3 solar arrays contains 176,000 records and is occupying 3% of the entire states table database storage! This sensor has now been reduced to just 127 data rows!
I ended up creating 3 useful SQL queries to analyse the database table contents. In SQLite Web click on Query in the top right corner, paste the query contents in and hit 'Execute' to run the query. You can also click the '+' symbol next to 'Bookmarks' to save the query as a favourite bookmarked query for later re-use.
Query 1, analyse number of rows in the states table:
SELECT
COUNT(*) AS cnt,
COUNT(*) * 100 / (SELECT COUNT(*) FROM states) AS cnt_pct,
states_meta.entity_id
FROM states
INNER JOIN states_meta ON states.metadata_id=states_meta.metadata_id
WHERE states_meta.entity_id LIKE '%'
GROUP BY states_meta.entity_id
ORDER BY cnt DESC
Query 2, analyse number of rows in the state_attributes table:
SELECT
COUNT(*) AS cnt,
COUNT(*) * 100 / (SELECT COUNT(*) FROM state_attributes) AS cnt_pct,
states_meta.entity_id
FROM states
INNER JOIN states_meta ON states.metadata_id=states_meta.metadata_id
INNER JOIN state_attributes ON states.attributes_id=state_attributes.attributes_id
WHERE states_meta.entity_id LIKE '%'
GROUP BY states_meta.entity_id
ORDER BY cnt DESC
Query 3, analyse number of bytes being stored in the state_attributes table:
SELECT
COUNT(state_id) AS cnt,
COUNT(state_id) * 100 / (
SELECT
COUNT(state_id)
FROM
states
) AS cnt_pct,
SUM(
LENGTH(state_attributes.shared_attrs)
) AS bytes,
SUM(
LENGTH(state_attributes.shared_attrs)
) * 100 / (
SELECT
SUM(
LENGTH(state_attributes.shared_attrs)
)
FROM
states
JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id
) AS bytes_pct,
states_meta.entity_id
FROM
states
LEFT JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id
LEFT JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id LIKE '%'
GROUP BY
states.metadata_id, states_meta.entity_id
ORDER BY
bytes DESC;
All of these queries can be changed to just focus on certain entities, replace the "LIKE '%'" with "LIKE '%power%'" for example to only report on the entity names that contain the word 'power', or 'givtcp', 'energy', 'temperature', etc etc. The percent symbol means match any characters.
I started with query 1 and got my database trimmed an awful lot just by understanding (and reducing) the amount of entity history I was retaining. Query 2 is of less use because each state record has a corresponding state_attribute record, but query 3 was a real eye-opener as I learnt just how the attributes of a entity can fill the database up. Predbat in particular.
e.g. the below extract from an early iteration of query 3, its ordered by cnt (number of records) but you can see that some of the predbat sensors have less history records but are taking up a lot more bytes of storage:
cnt cnt_pct bytes bytes_pct entity_id
12998 1 1468774 0 sensor.g_[inv id]_battery_power
11482 1 1297466 0 sensor.h_[inv2 id]_battery_power
9211 1 2315274 0 sensor.extension_energy_today
7055 0 1920190 0 sensor.toby_bedroom_sockets_energy_today
6758 0 1745699 0 sensor.fit_solar_energy_today
6260 0 63271800 4 predbat.best_metric
6253 0 68033112 4 predbat.soc_kw_best
6231 0 1234721 0 predbat.record
6227 0 33148401 2 predbat.best_import_energy
6218 0 12436 0 predbat.load_energy
6218 0 12436 0 predbat.best_load_energy
6211 0 968858 0 sensor.h_[batt2 id]_battery_voltage
6184 0 964646 0 sensor.g_[batt id]_battery_voltage
6143 0 1091312 0 sensor.internet_ha_energy_today
More on this later...
