Creating a sensor to calculate Home Assistant database size
With the relatively new long-term statistics feature in Home Assistant, there is less of a need to roll your own long-term time series DB. And if you keep your recorder period relatively short (a week or two), there is also less of a need to track the DB size. However, for monitoring the overall health of your Home Assistant instance, it's helpful to make sure nothing is unnecessarily populating the database.
db-sensor.yaml
sensor:
- platform: sql
unique_id: d350b376-f09a-4f06-b2b8-9cd47c8b627c
icon: mdi:database
db_url: !secret mariadb_url
scan_interval: 3600
name: MariaDB Database Size
query: 'SELECT table_schema "homeassistant", Round(Sum(data_length + index_length) / POWER(1024,2), 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;'
column: "value"
unit_of_measurement: MB
state_class: "measurement"
device_class: data_size
