Recorder does not purge

Hi,

I try to understand why my postgresql database is growing and finally I’ve found that hass does not purge anything.
My oldest event actually is date on 21/8 in the events table but when I run a purge here’s what I see in the debug log:

2018-09-24 17:22:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging events before 2018-09-22 15:22:12.343414+00:00
2018-09-24 17:22:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 0 states
2018-09-24 17:22:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 0 events
2018-09-24 17:22:12 DEBUG (Recorder) [homeassistant.components.recorder.purge] DB engine driver: psycopg2

and in the database:

hass=# select event_id,event_type,created from events order by created limit 1;
event_id | event_type | created
----------±--------------±------------------------------
2115 | state_changed | 2018-08-21 07:06:54.409343+02
(1 row)

Any idea what’s wrong in my config ?

To be able to restore state on startup, the last known state for each entity is never deleted. This is true even if you remove an entity.

So I guess that the number of old records is quite small and will match entities that you no longer use.

2 Likes

It’s so obvious when you say it…
I ran the query one more time today with keep_days = 1 and…

2018-09-25 13:32:42 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 12193 states
2018-09-25 13:32:43 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 16086 events

Now I have to find what I need to exclude to avoid large database.

Query sorted by most entries:

SELECT
 entity_id,
 COUNT(entity_id)
FROM
 states
GROUP BY
 entity_id
ORDER BY COUNT(entity_id) DESC
LIMIT 20;

Thanks, I’ve run something similar and found that my cpu and mermory monitoring no the host was the most expensive consumer here.
And I don’t need local history because I already have influxdb.