Re: Autovacuum after bulk data insert(millions!)

Hello Fabrízio,

Thank you for your advice but I'm not asking for only a specific table and
not looking for temporary solution. Previous post may not clear to much and
I'd explain again.

So autovacuum process takes long time for that database. I don't know how
can I estimate timing for a vacuum after insert millions of bulk data to a
table. So there are many table inserting bulk data daily. This solution not
for only a table. I'm looking a solution for a database. Data volume of
this database is around 2GB so many transactions in a day.

This is only suggestion and I'm really curious because can not estimate
what do you think about that, what if disable autovacuum and vacuum all
tables in every month?

Regards,
Gunce

On Tue, Oct 17, 2017 at 2:16 PM, Fabrízio de Royes Mello <
fabrizio(at)timbira(dot)com(dot)br> wrote:

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

--
Gunce Kaya

Linkedin <https://tr.linkedin.com/in/guncekaya> - Twitter
<https://twitter.com/gguncesi> - Blog
<http://www.guncekaya.blogspot.com.tr/>