Re: Autovacuum after bulk data insert(millions!)

2017-10-17 4:59 GMT-02:00 Günce Kaya <guncekaya14(at)gmail(dot)com>:
>
> Hi all,
>
> We have a database that is used for CRM. During day there are many
process like bulk loading large amounts of data(not thousand like
millions!). And all day we observe auto vacuum in server status. It takes
long time. I'm curious what happens if we disable autovacuum for a database
that process bulk insert? After insert data a table, what autovacuum will
do for that table?
>
> I've read some article like; "The autovacuum process takes care of
several maintenance chores inside your database that you really need.
Generally, if you think you need to turn regular vacuuming off because it's
taking too much time or resources, that means you're doing it wrong. The
answer to almost all vacuuming problems is to vacuum more often, not less,
so that each individual vacuum operation has less to clean up.
> However, it's acceptable to disable autovacuum for short periods of time,
for instance when bulk loading large amounts of data."
>
> The reason why I'm asking, millions of data is inserting to CRM database
all day. What is your advice for that scenario? Should I disable autovacuum
during bulk process or disable autovacuum totally and autovacuum tables in
a some period?
>

One option is:
1) Disable autovacuum in target tables
2) Run bulk load process
3) Run VACUUM ANALYZE manually
4) Enable autovacuum in target tables

Regards,

--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento