Any way to tell what's using space in the database?

I use the default sqlite database for my recorder: component. I have it set to remember 10 days worth of stuff:

recorder:
  purge_keep_days: 10  
  purge_interval: 1    

With my setup this results in a consistent database size of about 1.7 GB (even with doing a manual purge/repack). From reading the forums, there are hints about certain things taking up space like if you have a “time” sensor or the like. Is there a simple way to know what items take up lots of database space?

A little while ago I came across this info but didn’t have enough time yet to actually do anything about it:
https://www.renedohmen.nl/blog/2014/05/determine-sqlite-table-size-on-disk/

Not sure if it even goes into the direction of what you’re looking for.

Most likely events are taking up the most space. My database sits around 2GB with events taking up 800MB.

I recommend moving to a different DB, potentially on a server if possible. Then the 2GB won’t matter.

The easiest way is to look at your history display and see how many color changes (state changes) there are in each bar.

Also, if you know there are things you don’t need history for, then you might as well just exclude them from the database outright. E.g., here’s how I have my recorder configured:

recorder:
  exclude:
    domains:
      - group
      - history_graph
      - zone
      - zwave
    entities:
      - sensor.date
      - sensor.time
      - sensor.date__time

The sun component causes a lot of state changes, too, due to elevation/azimuth changes throughout the day. So if you don’t need sun history you might want to exclude it, too. (Of course, I’m using my custom sun component where I can disable those since I don’t use/need them. :slight_smile:)

I don’t think that 100% works… for example, I was able to use one of the SQLite database viewers (which still don’t let me see the sizes - or I can’t figure it out). And I noticed that I have sever z-wave plugs that report power, current, etc. Those get reported pretty frequently, but are not shown in my history. I’m sure it’s because I’ve hidden the sensor.

I guess the thing to do is use the lovelace UI (which I do) and unhide everything. then at least my history would show all the sensors going into the database

thanks @pnbruckner - that’s exactly what I want to do - I just need to figure out which ones are causing the large size. I guess it doesn’t matter too much - I run on a NUC.

However, when I go to “Logbook” or, it takes about 15-20s for the page to load. When I go to “History” it’s a little faster than that - about 5s. I’d like to get that down to something more reasonable if I could.

There probably are some things you want to go into the database, but yet you don’t necessarily want to see them on the history page. So, for those things you can configure history to not show those things on the history page. E.g., I have:

history:
  exclude:
    domains:
      - automation
      - script