Database already huge

So im at 94.5 % of my SD card size. and i did a compete reinstall around 20th Feb and started from scratch.
How can i tell whats takig up ther most space?
What should i do first??
Delete blueprints, front end HACS im not using etc??

My advice is to move from using the standard sqllite to maybe mysql or mariaDB or postgres and also move some to influxDB for stats.

no idea how to that, just seems alot of work, more moving parts, but ill look into it

  • Remove what ever you don’t need “history”(state/events history) from
  • Don’t have “infinity” purge/retention policies

I.E. in August 21"Update", it was announced in “Major” Changes, that Long_term_statistics was implemented (and could cause rapid increasing in DB size)
Few month ago, it was announced in " “Major” Changes, That Recorder would start to Record ALL, if not other-vice specified(configured in configuration.yaml)

If you don’t Read “Update” Release Notes, You’ll never know what’s coming
… And you’ll never know how to deal with it, or how it would/could affect you specific Installation

How to tell what’s taken up most space is hard, i’m sure your DB is most likely not taken i.e.10GB, , log-file is usually “cleaned” after updates/restart, but some never checks whether their log-file is “spewing” errors and growing rapidly

  • Backup files " Clean-up !

Huge amount of integration, add-ons, AND the ever increasing “new features/integrations/support for new product” in HA, will also increase the “default” installation media.

Yes Delete what you don’t use, go through your “entities” delete/disable/remove what you’ll never going to use ( and never “include” every “sensor/entities” when installing new devices) only choose the ones you want ( as they per Default now will be “tracked/logged/recorded” )

PS: 32GB should now be consider as “Not sufficient”, increase to 64GB, or SSD

yep im just looking into this recorder now. its one thing to read, but its another to fully grasp how much of an impact it is.

Yes SSD should be the only thing recommended. Im going to try and reduce my filesize just so i can do a back up lol

1 Like

btw my database is 7gb!

I’m not clear if you think the size issue is in your recorder database size or somewhere else. Also, I not sure what kind of hardware/os setup you are running.

Here is one thing to give a view size of files in your home assistant directory. At a command prompt move into your HA directory and using the following command with example output, this should size of top 20 largest files and directory starting from current directory :

user@macmini2012:~/homeassistant$ sudo du -a . 2>/dev/null | sort -n -r | head -n 20
84604	.
58532	./www
19824	./www/zigzag
18356	./www/zigzag/plugins
16768	./www/custom-lovelace
15080	./custom_components
10236	./www/custom-lovelace/apexcharts
7000	./www/images
5928	./custom_components/icloud3
5876	./www/history-card
5608	./www/history-card/
4708	./custom_components/icloud3/
4168	./www/sounds
4160	./www/zigzag/plugins/
4152	./tts
4056	./www/zigzag/plugins/
3476	./custom_components/hacs
3388	./www/sounds/cuckoo
3128	./.storage
3008	./www/images/sb01.jpg

upps ! :slight_smile: … yea, you do have some “work” ahead of you, and “decisions” makings :wink:

im running pi4, using sonoff zigbee.

Doesn’t say much about your environment, i.e how many devices/entities , or whether they updates every second etc. … every second is 5 times more than every 5 seconds, and motion-detections/temperature/humid/air-pressure-entities etc i’ve never figured out why people want “updated” in seconds! , nor less 1.
Whether a “device” i.e plugs etc is “present”, depending of “protocol” and your environment (increase) , you most likely not, have any needs for “updates” in rapid speed, for most of your devices

oh man ok, um i have 718 entities. how do you get things like temperature to update less often??

so i figure i can stop recording my lights

im still new to all this, and when i think i get yaml, i dont. below is the start, which i have copied from the examples, but when i go to check it it gives me an error

Invalid config for [recorder]: expected a dictionary for dictionary value @ data['recorder']['exclude']. Got 'entities 
      - light.aarlo_dog_hotel_camera
      - light.aarlo_driveway_left_camera
      - light.aarlo_driveway_right_camera

I don’t know about sonoff integration, but most likely in the integration, under each device … some have settings for this, and i actually wrote this, because i’ve seen people trying to “flash software” to get “default” lower (i.e.) every second :slight_smile: … then again, i also believe that certain “cards” will generate “states/events updates” depending on “setup/configurations” there

does having a alot dashboard’s contrite??


You missed simi :
But you could also use entity_globs or domain, if you want to exclude ALL, using * somewhere in between to define specific

i.e entity_globs: ```
- light.aarlo_*

ahh sigh. thank you

No. You are storing too much unvaluable information with recrder.

After doing your initial “cleaning” you could install SQLite_Web ADD-ON … there you’ll see which states/events is logged in DB
I.E, In SQLite_Web, under “States” / Content ( you’ll probably have “few” rows :slight_smile: … But you would be able to see if there are any sensors/entities that you want to “exclude”

PS: don’t forget to have a look under /configuration/backups … clean out, can give you “another” GB free

I was where you were a while ago. It’s frustrating to get hit by this, after following all the beginner documentation, just as you think things are running smoothly. I think this needs to be emphasized very early in the instructions.

Just to add a note of encouragement, my recorder database, using SQLite, on an SD card on a RPi 3B+, is hovering just over 20M. That’s M, not G.

The first thing I did was follow @tom_I’s guide (linked above) to identify the worst offenders, and exclude them. If you don’t want to do this on a separate computer, you can install the SQLite Web add on and run the SQL commands right from HA. Even easier, you can go to your HA “history” page and see which entities change frequently. I found I could exclude lots of entities.

Next I reduced my purge_keep_days to 4. It’s very rare I’d need anything older than that, and frankly HA isn’t the best place to store long-term data anyway. Instead I write summary data about the run time of various heating system components to a flat file every day. This way I can analyze it using much better tools on my laptop.

boheme61 gave a good overview of other things which have been tacked onto the Recorder database lately. Once you get your entities under control, you might want to look into the statistics tables and see if those meet your needs or if they’re something you can purge manually on some schedule. I’ve now gotten into a routine where I manually (using DB Browser on my laptop) purge statistics and re-pack the database each time I do a version update of HA.

You can also purge and re-pack the database, or individual entities, right within HA. In Developer / Tools / Services, look for Recorder: Purge and Recorder Purge Entities. As you identify the worst offenders and exclude them, you can go back here and purge them for some instant gratification as you see your database shrink.