Database & Recorder integration: Overhaul UI to permit reduced DB size and ease of entity exclusion from statistical tables

[twcau]

Describe your core improvement

1. Prompt at instance setup to configure settings for proper DB maintenance from the outset: As part of the onboarding/instance first time setup process, or at a later stage by way of repair, give the user an informed prompt (inc. links to relevant resources) to help the user set the following information for recorder::

   • auto_purge: <true or false>
   • purge_keep_days: <numerical value>
   • commit_interval: <numerical value>

2. Offer a UI panel for ongoing maintenance of recorder: configuration: Allow the user to easily update all parameters of recorder: from the UI, including:

   • Extend the UI to ensure a user can easily setup both domains: and entity_globs: for include: and exclude:,
   • That such changes provide support for wildcards that are done in the current YAML methods, and
   • Where wildcards are used, the user can access a validation tool (on screen output, or some other method) so they can check what entities will be included, excluded, and/or ignored by their wildcards before applying and committing the change.

3. Enhance UI when adding integrations and devices to support proper DB maintenance: Allow users to easily manage if an integration/device/entity is included or excluded from statistical databased by way of adding functionality to the UI, that will allow the user to exclude an integration/device/entity from both the statistics_short_term and statistics tables.

   Places for this to be added would include, but not necessarily limited to:

   • As part of the New Integration and Add Device workflows
   • /config/integrations/integration/
   • /config/devices/device/
   • Entity pop-up (not sure of the correct name, but this is what I call the window that's displayed when you click on an entity name from anywhere in the Lovelace UI).

4. Apps and Integrations allowed to signal defaults during setup: Allow both Apps and HACS plugins, to provide methods in their packages to signal to HA if an integration/device/entity/attribute should be excluded (as a default) from any statistical databases; whilst ensuring the user has the ability to override in UI if they choose.

5. Update /config/storage to explicitly separate out the size of the HA database: This is self explainatory.

6. Make filesize integration part of core: Bring the existing Filesize integration - or something like it - in HA Core, and ensure the DB size metric is a standard entity available on all instances (and whilst setting defaults to exclude this from long-term storage, as this is - for the most part - an ephemeral statistic).

7. Make DBStats HACS integration part of core: Bring the existing DBStats HACS integration - or something like it - into core, so users can easily visualise and understand what parts of the database are taking up space, and more importantly which entries account for it.

8. Make possible and easier for moving of older short and long term statistics, already in the db, into other database platforms: It should not rely on the user to figure thus out on their own to export such statistics into more efficient time series database options like InfluxDB.

Current limitations

1. HA not designed to store only what is needed from the beginning: At present, Home Assistant does not ensure appropriate database efficiencies from the beginning.

2. Users won't identify the problem or impacts until it's too late: Users can end up with databases that are bloated, contain unnecessary or ephemeral metrics, that are of zero value to long-term analysis.

3. Impacts to, and performance hits, from unnecessary read/write operations to flash memory and/or single-board computers: I suspect that the Basic analytics HA gets would support this, but I'm willing to wager that a significant number of HA instances are running on the types of devices and/or types of storage hardware (Flash storage inc. memory cards and SSD's) whose service life and capabilities are impacted from unnecessary read/write IO operations. If HA can be designed for efficiency from the outset, this improves instance performance, device and storage lives, creating better user outcomes.

4. Level of learning needed to address: To manage this, users have to spend a lot of time on the following activities:

   • Learning if they can limit what statistics are stored in the first place;
   • Learning if they can analyse their instance to determine what entities are generating the most data;
   • Learning how to update recorder: in configuration.yaml to either include, or exclude items from going into statistics in the first place; and
   • Testing and validating these changes, to ensure that things they want to keep aren't accidentally being excluded, and visa-versa.
   • Getting into their database, and safely removing all the old entries for things they don't want manually, that are already in short-term and long-term statistics that recorder.purge does not clean up (covered more at Point 6).

5. Prevalence of integrations storing ephemeral data: These issues are especially prevalent when users have integrations and entities that cover areas such as:

   • Weather forecasting
   • Network hardware information and performance

   This is generally going to be data the user doesn't need to keep beyond a limited window; but to know they should exclude it, or for integration authors to have the ability to easily signal and ensure its excluded, just isn't offered in HA - it's all reliant on the user spending a lot of time figuring it out.

6. Cleaning up existing data you don't need is difficult: Even after updating recorder: and running recorder.purge, there's always still a lot of stuff in the DB that doesn't need to be there (ask me how I know, as I've been living this for the last week).

   So the user will once again need to go off and learn queries in SQLite, along with installing another app, so you can really get your hands dirty and clear out the junk - hoping you don't accidentally break something because you don't have backups, or didn't run a dry-run query first, or failed to properly understand the database schema and got rid of things you actually wanted and/or needed to keep.

Technical benefits

Improve the friendliness and efficiency of HA from day one, especially for non-technical users, by:

1. Privacy-first design, by making it exponentially easier for the user to control what is stored and kept from day one.
2. Keep database sizes on device under control from day one, by reducing what and how much is written to them.
3. Reduce IO operations impacting lifespan of flash and solid state memory devices.
4. Improved onboarding for new integrations/devices/entities that allows for more optimised database use.

Additional context

• A Mastodon thread - sadly by me - where I vent about these problems (my database grew another 400 Mb in the six days since that screenshot, and I'm still working hard to deep cull data), which also includes replies from other HA users who are equally frustrated and impacted by this.
• Blog post from me outlining my learnings and process on the full level of effort and steps needed by a user in the current model to properly optimise and clean up their HA database.

[twcau]

Open related requests:

• UI interface to manage my HA database / recorder settings #25
• GUI Option to Exclude Entities from Recorder (History) #2849
• Add ability to set recorder purge_keep_days per entity. Alternate / additional request -- allow us to set max number of values persisted per some unit of time per entity #219
• Allow per-entity recorder configuration (e.g., different purge or inclusion behavior) #380
• Split statistics for longterm and shortterm #2181

Requests that might benefit in one or more ways from this change:

• long term statistics for binary sensors. #2063
• Make backups more incremental friendly #613
• Exclude/Include entity attributes from Recorder #166
• Backup/restore history only #2534
• Energy Dashboard - Data Load & Improvements #2240
• Add context fields to history and logbook APIs #2882
• Energy Dashboard: Add configurable sampling interval for energy sources to reduce recorder writes #2808
• Add min_time and max_time timestamps to recorder.get_statistics #177

[CaptTom9]

Very nice work! Well thought-out. It would be great to see Recorder get some developer love.

I've made a few similar suggestions over on the community forum. Specifically, I think the changes below would avoid the frustration you (and many other users) felt when they discovered that the default Recorder values set them up for failure.

1. Recorder retention period by entity. Not every entity needs data saved forever, or even for the one-size-fits-all "purge_keep_days." Ideally, we should be able to choose, for each entity, how long it needs to be retained.
2. Make Recorder includes and excludes available for editing in the UI. This one is fairly self-explanatory.
3. Prompt the user for Recorder status when device is added. We already prompt the user to select an "area" when bringing in a new device. Wouldn't it be great if they could also select to include or exclude the entities for Recorder? Better still, after implementing 1, above, we could prompt for purge_keep_days per entity.

[twcau]

Concur, with 1) and that was something I should have allowed for and missed.

2 and 3 are already covered in the initial request, at points 2 and 4 respectively :)

[Misiu]

Architecture discussion got approved: home-assistant/architecture#1106
There was a core PR for this: home-assistant/core#121377, but it got closed, and a frontend PR: home-assistant/frontend#27022

The ability to easily see and edit which entities are stored and how long would be awesome. Now I often forget to add things to yaml.