Postgres : 9.5.3 Kernal : 3.10.0-327.13.1.el7.x86_64 Linux : x86_64 x86_64 x86_64 GNU/Linux RAM 128GB DISK : 1.5TB
We have nightly job that removed millions of records from multiple table.
We had following approach. 1. Delete data from table in batch of 50000
Problem with above approach many time autovacuum on table and delete statement on table create deadlock.
This is hard to believe. Are these detected deadlocks (which are resolved by one process getting served an ERROR) or are they undetected deadlocks, which simply freeze the system until someone manually kills something? If they are detected deadlocks, please share the part of the server log file which describes the deadlock.
In order to avoid above problem, we have taken following approach.
1 Turn off autovacuum on table by using ALTER TABLE <tablename> SET ( autovacuum_enabled=false); 2. Delete data from table in batch of 50000 3. Turn On autovacuum on table by using ALTER TABLE <tablename> SET ( autovacuum_enabled=true);
It seems to me that your "solution" to the problem is what is causing the problem.
