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

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.

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);