Here is my recorder related stuff in configuration.yaml , I do NOT exclude ANYthing from recorder, pictures of queries at bottom of post show number of state and event records per hour getting inserted into postgresql db, it looks like it averages about 40k records of each, so 80,000 records are being added to postgresql per hour:
# database
recorder:
purge_keep_days: 65
db_url: !secret postgres_url
auto_purge: true
# Enables support for tracking state changes over time
history:
# View all events in a logbook
logbook:
I wonder if your recorder purge function is running wild???
I would review this guys bug report again. Text below where frenck closed this issue, as it goes off into MSSQL… so that part at end might not be relevant, but first part, I’m wondering if your database is still not setup in best way.
https://github.com/home-assistant/core/issues/44736
'The above seems to prevent timely removal of data, causing extensive time to run DELETE, second crashing this DELETE due to FK violations.
I run the following commands to remedy and alter DB structure, and after doing this deletes are successfully executed.'
show running queries in postgresql on homeassistant database
psql -U postgres -h 192.168.2.242
192.168.2.242 postgres@postgres=# select * from pg_stat_activity where datname='homeassistant';
Here is some stuff, if you want to do a comparison between my homeassistant postgresql database and yours. You could do a schema dump of your database and do a diff against the attach dump of my schema and see if you can see sometime significant. Ignore the ‘mikrotik_device_history’ table it has nothing to do with HA, and of course I have my archive tables, triggers, scripts and views, so ignore these differences.
pg_dump -U postgres -h <pgserver ipaddress> --create -s homeassistant > homeassistant_schema.sql
diff homeassistant_schema.sql daves_haschema.sql
You can create a HA test database from my schema or yours from stuff below…
You will need to your super admin postgresql account and pw to create a new test database.
pg_dump -U postgres -h 192.168.2.242 --create -s homeassistant > ha_schema.sql
Create a test copy of your home assistant database. You can edit this file and replace all instances of ‘homeassistant’ with something like ‘hatest’ to create a test environment. MAKE sure you get ALL of the references to ‘homeassistant’ there are some at the bottom.
psql -U postgres -h 192.168.2.242 --set ON_ERROR_STOP=on -f hatest.sql
You can ignore and drop the mikrotik_device_history table, it has nothing to do with HA and again, I have my archive stuff in there…
drop table mikrotik_device_history;
To remove the test database.
psql -U postgres -h 192.168.2.242
\l
drop database hatest;
\l
You can dump your current home assistant database schema with above command, the dump the schema of the test ha database from my schema and do a diff on the two schema dumps to see the differences. Picture below.
pg_dump -U postgres -h 192.168.2.242 --create -s homeassistant > homeassistant_schema.sql
pg_dump -U postgres -h 192.168.2.242 --create -s hatest > hatest_schema.sql
diff homeassistant_schema.sql hatest_schema.sql
