Re: Autovacuum after bulk data insert(millions!)

From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Günce Kaya
Sent: Tuesday, October 17, 2017 2:57 PM
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Autovacuum after bulk data insert(millions!)

Hi Scott,

Thank you for your response. I sent this post because I want to make sure as to whether we need continuous autovacuum exactly for that database. I read your answer and consider on it well. I'm gonna keep your suggest "disabling autovacuum on those tables and then vacuuming after the bulk upload" on my mind this fastest way for specific tables.

The problem is not only about autovacuum is taking long time, I've read some documents about autovacuum, the point is that disable autovacuum and vacuuming monthly even after millions of bulk data load to a database is reasonable or not? I'm not insist on vacuuming monthly just I'm confusing about that.

Regards,
Gunce

Gunce,

Autovacuum improved a lot in latest PG releases. Don’t rely on what you read in oldpostings, try/test it yourself.
Disabling autovacuum is rather radical step. You should do it only if you observe specific problems caused by autovacuum, and even in these cases more often than not tuning autovacuum parameters is a better than disabling it.
Switching from autovacuum to manual vacuuming may cause more problems than solve. You should have very strong reasons to do that.

So, first see if there is a problem with autovacuum, then try to tune it, and only if this doesn’t help, try manual vacuuming.

Regards,
Igor Neyman