You might try and dig a bit deeper, this may help you discover where the size issues are at a more granular level.
Just seeing the meta number of your database size growing is enough info I think to diagnose.
Below are a couple things you could try and run over a couple days and see it give you a better view. Maybe a grow in one or more will point out the cause. Start with 1st item below, be comfortable with understanding how many events and states you are adding per day.
Then the second set of queries, make sure you HA purge is really running each night. You can turn on the debug logging for recorder as well and perhaps it will give additional info, if the recorder is not doing the purge. Then beyond that, below a couple of queries that will give you a more granular sense if a table or index is growing.
in configuration.yaml:
logger:
default: warning
logs:
homeassistant.components.recorder: debug
I keep 60+ days in Postgresql, I’ve not found a need to use the HA’s ‘repack’ option in recorder, I’m not really sure what it would do. Postgresql has some pretty good automatic clean ups that run without you having to do anything. HA is really not doing much hard stuff in the database, just adding records and the end of states and events, and with purge deleting them from the other end, not something that such cause wasted space in Postgresql :
# database
recorder:
purge_keep_days: 65
db_url: !secret postgres_url
auto_purge: true
I would start by installing a SQL query tool and getting it attached to your postgresql server.
The command line psql is solid if you are okay with command line. I use this, but I also use TablePlus, a nice free GUI query tool that runs on many platforms. I use on MacOS all the time against my Postgresql 12.3 server running on Ubuntu 20.04 in docker next to my Home Assistant instance running in docker on same Ubuntu machine.
https://tableplus.com/
- Find out how many records are being added to your Home Assistant database during time intervals, here is and example of the number of records being added to my Home Assistant database per hour over the last three days. You probably want a number considerably smaller than mine
SELECT
date_trunc('hour', last_updated),
count(1)
FROM
states
WHERE
last_updated > now() - interval '3 day'
GROUP BY
1
- Check the oldest records in your states and events tables to make sure the recorder purge is occurring as you have it setup.
SELECT
event_id,
created
FROM
events
ORDER BY
created
LIMIT 10;
SELECT
state_id,
created
FROM
states
ORDER BY
created
LIMIT 10;
- Find the size of your Home Assistant tables:
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 5;
- Find the size of indexes of your Home Assistant tables:
SELECT
relname,
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch,
pg_size_pretty(pg_relation_size(indexrelname::regclass)) as size
FROM
pg_stat_all_indexes
WHERE
schemaname = 'public'
AND indexrelname NOT LIKE 'pg_toast_%'
AND idx_scan = 0
AND idx_tup_read = 0
AND idx_tup_fetch = 0
ORDER BY
pg_relation_size(indexrelname::regclass) DESC;
