pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedauto-vacuum vs. full table update
4+ messages / 4 participants
[nested] [flat]
* auto-vacuum vs. full table update
@ 2012-04-26 19:49 Craig James <cjames@emolecules.com>
0 siblings, 3 replies; 4+ messages in thread
From: Craig James @ 2012-04-26 19:49 UTC (permalink / raw)
To: pgsql-performance
An update to our system means I'm going to be rewriting every row of
some large tables (20 million rows by 15 columns). In a situation
like this, can auto-vacuum take care of it, or should I plan on
vacuum-full/reindex to clean up?
This is 8.4.4.
Thanks,
Craig
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: auto-vacuum vs. full table update
@ 2012-04-26 19:53 Joshua D. Drake <jd@commandprompt.com>
parent: Craig James <cjames@emolecules.com>
2 siblings, 0 replies; 4+ messages in thread
From: Joshua D. Drake @ 2012-04-26 19:53 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-performance
On 04/26/2012 12:49 PM, Craig James wrote:
>
> An update to our system means I'm going to be rewriting every row of
> some large tables (20 million rows by 15 columns). In a situation
> like this, can auto-vacuum take care of it, or should I plan on
> vacuum-full/reindex to clean up?
>
If you rewrite the whole table, you will end up with a table twice the
size, it will not be compacted but as the table grows, the old space
will be reused.
jD
> This is 8.4.4.
>
> Thanks,
> Craig
>
--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: auto-vacuum vs. full table update
@ 2012-04-26 20:00 Steve Crawford <scrawford@pinpointresearch.com>
parent: Craig James <cjames@emolecules.com>
2 siblings, 0 replies; 4+ messages in thread
From: Steve Crawford @ 2012-04-26 20:00 UTC (permalink / raw)
To: pgsql-performance
On 04/26/2012 12:49 PM, Craig James wrote:
> An update to our system means I'm going to be rewriting every row of
> some large tables (20 million rows by 15 columns). In a situation
> like this, can auto-vacuum take care of it, or should I plan on
> vacuum-full/reindex to clean up?
>
If you want to reclaim the space, a vacuum-full/reindex will do it. But
you are probably better off using cluster. Way faster and you get new
indexes as a by-product. Both methods require an exclusive lock on the
table. If you can't afford the downtime, check out pg_reorg
(http://pgfoundry.org/projects/reorg/)
Cheers,
Steve
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: auto-vacuum vs. full table update
@ 2012-04-26 20:11 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Craig James <cjames@emolecules.com>
2 siblings, 0 replies; 4+ messages in thread
From: Kevin Grittner @ 2012-04-26 20:11 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; pgsql-performance
Craig James <cjames@emolecules.com> wrote:
> An update to our system means I'm going to be rewriting every row
> of some large tables (20 million rows by 15 columns). In a
> situation like this, can auto-vacuum take care of it, or should I
> plan on vacuum-full/reindex to clean up?
>
> This is 8.4.4.
If there is any way for you to update in "chunks", with a vacuum
after each chunk, that will prevent the extreme bloat.
-Kevin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-04-26 20:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-04-26 19:49 auto-vacuum vs. full table update Craig James <cjames@emolecules.com>
2012-04-26 19:53 ` Joshua D. Drake <jd@commandprompt.com>
2012-04-26 20:00 ` Steve Crawford <scrawford@pinpointresearch.com>
2012-04-26 20:11 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
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