Обсуждение: auto-vacuum vs. full table update
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
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
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
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