Hi Jeff,
the proble is that when continously updloading vendors listing on our "big" table the autovacuum is not able to free
spaceas we would.
Secondarly, if we launch a Vacuum after each "upload" we collide with other upload taht are running in parallel.
Is it possible, form your point of view, working with isolation levels or table partitioning to minimize table space
growing?
Thanks again for all your help.
BR,
Roberto
----- Messaggio originale -----
Da: "Jeff Janes" <jeff.janes@gmail.com>
A: "Roberto Grandi" <roberto.grandi@trovaprezzi.it>
Cc: "Kevin Grittner" <kgrittn@ymail.com>, pgsql-performance@postgresql.org
Inviato: Mercoledì, 4 settembre 2013 18:29:13
Oggetto: Re: [PERFORM] COPY TO and VACUUM
On Tue, Sep 3, 2013 at 11:15 PM, Roberto Grandi
<roberto.grandi@trovaprezzi.it> wrote:
> Hi kevin
>
> first of all thanks for your help. I did a mistake we are using postgres 8.3.
>
> I didn't expect COPY TO frees space but I was wondering Autovacumm delete dead rows as soon as possible, in fact my
scenariois:
>
> - Delete all products record for a vendor
> - Reload all products record (from new listing) for the same vendor.
>
> Obviously we repeat this process continously and table space is growing really fast.
It isn't obvious to me why you would do this continuously. Surely
your vendors don't change their catalogs exactly as fast as your
database can digest them!
In any event, I'd probably just incorporate a manual vacuum statement
into the delete/reload cycle. Since delete and copy are not
throttled, while autovacuum is throttled by default to a rather low
level, it is quite possible that default autovacuum can never keep up
with the workload you are generating. Rather than trying to tune
autovacuum to fit this special case, it would be easier to just throw
in some manual vacuuming. (Not instead of autovac, just as a
supplement to it)
Cheers,
Jeff
