Re: BUG #14938: ALTER TABLE hang/ poor performance

On Thu, Nov 30, 2017 at 3:30 PM, <dipesh(dot)kamdar(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14938
> Logged by: Dipesh Kamdar
> Email address: dipesh(dot)kamdar(at)gmail(dot)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