Kept recorder data for too long? Purge taking a while? Here's a workaround

,
1

I’ve seen the same issue in a few other places - the scenario is as follows:

  • You start setting up homeassistant
  • You decide on an external DB
  • You set the recorder to keep history indefinitely
  • You realise that a time-series database is better for long term statistics
  • You setup Influx/TimescaleDB/VictoriaMetrics and get the old data across
  • You try to purge the recorder history using the built in service
  • Writes to your database stop, leaving gaps in history, and the purge takes AGES

If that’s you, as it was me (280m state entries going back 1 year) then there is a solution, but it’s going to need you to manually run some queries.

NB: I take no responsibility for borking your database, please take a FULL backup before you start, or do as I did, take a full backup, restore that into a separate DB, test it all out there first, and THEN go back and do it on the real thing

Unfortunately it’s not as simple as just DELETE FROM states because states can have a link to another row in the states table to denote the previous state, and this will lead to foreign key constraints preventing the delete.

So, first of all, you need to set old_state_id to NULL for the rows you’re going to delete. In all of the following queries change the timestamp with time zone to equal the date you want to keep data FROM, in my case, a month ago:

UPDATE states
SET old_state_id = NULL
WHERE
    old_state_id IS NOT NULL
AND
    last_updated_ts < EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2024-08-08T00:00:00.000Z');

We can’t delete data yet, because some of the rows we’re keeping will refer to rows we’re going to delete, so a next step is to update THOSE old_state_id to NULL, notice the >= for rows to keep, and < for rows to delete:

UPDATE states
SET old_state_id = NULL
WHERE
    last_updated_ts >= EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2024-08-08T00:00:00.000Z')
AND
    old_state_id IS NOT NULL
AND
    old_state_id IN (
	    SELECT
	        state_id
	    FROM states
	    WHERE last_updated_ts < EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2024-08-08T00:00:00.000Z')
	);

Finally, we can delete the rows from the states table:

DELETE FROM states
WHERE
    last_updated_ts < EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2024-08-08T00:00:00.000Z');

Now, you’ll notice on PostgreSQL that the disk usage doesn’t go down at all - that’s usually fine as a database that has frequent deletes that match insertion rate will reach a steady state, but that steady state for our table is a LOT less than the steady state for just 30 days, so we need to do a full vacuum … NB this takes an exclusive lock on the table, so any writes and queries to it will fail until it’s completed. On an SSD this isn’t usually an issue, my table was 110GB and after a full vacuum it’s about 10 … so the vacuum only took about 30s.

VACUUM FULL states;

You could stop here, however, the HA database stores the attribute values in a separate table, and this can also be very large if you have devices where state change values have a high cardinality, so let’s clean up and then vacuum THAT table:

DELETE FROM state_attributes
WHERE
	attributes_id NOT IN (
	    SELECT DISTINCT
	        attributes_id
	    FROM states
	);

VACUUM FULL state_attributes

And finally, if you’ve removed some entities since the start of your history, may as well clear up the metadata table which contains entity_id’s as well:

DELETE FROM states_meta
WHERE
	metadata_id NOT IN (
	    SELECT DISTINCT
	        metadata_id
	    FROM states
	);

VACUUM FULL states_meta;

These queries will take a long time to run (except the VACUUM FULL, on an SSD). In my case, using a Core i3 9100T with 32GB of ECC RAM and the DB being on a SATA SSD the UPDATE and DELETE queries took over 2 hours to run for 280m state values.

You can stop there, but if you want to go the whole way and tidy up the events tables as well, here’s the SQL queries you can run to do that:

DELETE FROM events
WHERE
	time_fired_ts < EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2024-08-08T00:00:00.000Z');

VACUUM FULL events;

DELETE FROM event_data
WHERE
	data_id NOT IN (
	    SELECT DISTINCT
	        data_id
	    FROM events
	);

VACUUM FULL event_data;

And voila … your database will now be a fraction of the size, and recorder.purge should complete in a reasonable timeframe. Don’t forget to set recorder_keep_days and recorder_auto_purge settings as appropriate.

1 Like