My recorder database is constantly at 23.2 GB

Configuration
1

Hi everyone,

since months I run into issues with my huge DB (cannot install updates, add-ons and such).
I read tons of threads about DB growing big (and will continue to do so), but most of them mention 5GB to be huge.
Mine is at 23 GB though:

image
image808×145 15.6 KB

So, I wonder if I need to go from 32 GB drive to 64 (or more), but assume, there is something wrong and I will use up the space quickly again.

I am using an NUC7CJYH2 with 32GB SSD and 8GB RAM running

Home Assistant 2023.10.0
Supervisor 2023.10.0
Operating System 10.5
Frontend 20231002.0 - latest

Along the way I have added filters to the recorder which look like this:

recorder:
  purge_keep_days: 21
  auto_repack: true
  auto_purge: true
  include: # Configure which entity domains should be included in recordings. If set, all other entities will NOT be recorded.
    domains:
      - alarm_control_panel
      - automation
      - binary_sensor
      - cover
      - device_tracker
      - input_boolean
      - input_select
      - light
      - lock
      - person
      - script
      - sensor
      - switch
      - sun
      - timer
  exclude:
    event_types:
      - service_removed
      - service_executed
      - platform_discovered
      - homeassistant_start
      - homeassistant_stop
      - feedreader
      - service_registered
      - call_service
      - component_loaded
      - logbook_entry
      - system_log_event
      - automation_triggered
      - script_started
      - timer_out_of_sync

I also have purged the DB to14 days including repack but the DB seems to ignore this.
At least the size does not change.

In one of the threads I read about the SQLite Web to look into the DB (don’t know if this will shed some light though), but I cannot install it due to limited space.

I am fine with the generic HA statistics so history at the moment (InfluxDB to be explored), but I would love to have at least 30 days historic data (if realistic with the internal default recorder SQLite).

Questions:

  1. I read in one older thread, that the Energy dashboard will store the data in the default DB as well. Is this the case already and maybe the culprit?
  2. Is there any other option to reduce the DB size without losing too much data?

Any suggestion is greatly appreciated

2

I’ll start with question 2. Check out this thread. There are many others if you search around.

To your first question, yes, HA throws everything in that one database, both real-time, transient data and long-term, archival data. Yes, that’s a poor design. Yes, it’s made worse by the one-size-fits-all retention period for all entities, whether they’re something you only want to keep for an hour, or forever. There are FR’s about this. Please feel free to up-vote if you agree.

None of this is explained well in the “getting started” documentation. Excluding entities from Recorder is not an option in the UI. It requires that the user (1) knows it’s something they need to do, and (2) edits YAML to do it.

3

This was one of the threads I was referring to, but I was confused because my DB did not shrink.

However (I don’t know what I did differently), but the DB went down to 1.7 GB.
I will play a little to pump it up a little :wink:

Thank you.

4

I’m thinking maybe the Recorder Purge/Repack just took that long to get from 23G to under 2G.

Another possibility, and I’m only throwing this out there for future users stumbling onto this thread, is that you didn’t realize you had to both check the “Repack” check box, and turn on the “slider” switch over on the right, or the repack won’t happen. That one tripped me up at first.

IMHO, that’s a very unusual UI configuration which sets new users up for failure. I’ll leave it there.

5

Absolutely! I agree 100%