From: David Kerr <dmk@mr-paradox.net>
To: Roberto Grandi <roberto.grandi@trovaprezzi.it>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Kevin Grittner <kgrittn@ymail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: COPY TO and VACUUM
Date: Thu, 5 Sep 2013 09:24:17 -0700
Message-ID: <20130905162417.GA78703@mr-paradox.net> (raw)
In-Reply-To: <2031102275.783807.1378397108337.JavaMail.root@trovaprezzi.it>
References: <535715181.700956.1378223895627.JavaMail.root@trovaprezzi.it>
<441706222.701556.1378224973704.JavaMail.root@trovaprezzi.it>
<1378240470.79541.YahooMailNeo@web162905.mail.bf1.yahoo.com>
<1680047223.718929.1378275308275.JavaMail.root@trovaprezzi.it>
<CAMkU=1ytUAZbDcXcBC6MAN+wkP=La7duTuEAYe+yU9UZJFQAgg@mail.gmail.com>
<2031102275.783807.1378397108337.JavaMail.root@trovaprezzi.it>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi Roberto,
Yes you could partition by vendor and then truncate the partition before loading.
Truncate reclaims space immediately and is generally much faster than delete.
On Thu, Sep 05, 2013 at 06:05:08PM +0200, Roberto Grandi wrote:
- Hi Jeff,
-
- the proble is that when continously updloading vendors listing on our "big" table the autovacuum is not able to free space as 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 scenario is:
- >
- > - 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
-
-
- --
- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
- To make changes to your subscription:
- http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dmk@mr-paradox.net, roberto.grandi@trovaprezzi.it, jeff.janes@gmail.com, kgrittn@ymail.com
Subject: Re: COPY TO and VACUUM
In-Reply-To: <20130905162417.GA78703@mr-paradox.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox