Thread: Re: BUG #14938: ALTER TABLE hang/ poor performance
On Thu, Nov 30, 2017 at 3:30 PM, <dipesh.kamdar@gmail.com> wrote:
The following bug has been logged on the website:
Bug reference: 14938
Logged by: Dipesh Kamdar
Email address: dipesh.kamdar@gmail.com
PostgreSQL version: 9.5.3
Operating system: Linux
Description:
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.
Cheers,
Jeff