Re: full vacuum of a very large table - Mailing list pgsql-admin

From Shrinivas Devarkonda
Subject Re: full vacuum of a very large table
Date
Msg-id AANLkTimMR5+CxERJ51u0fnESzy68HeoYOH-wcOW6d_N4@mail.gmail.com
Whole thread Raw
In response to Re: full vacuum of a very large table  ("Plugge, Joe R." <JRPlugge@west.com>)
List pgsql-admin
yeah , This is the best solution, Where you dont need  much resources and DB to do more work, You will save a lot here.

Just simple inserts from base table to new table with your condition,
verify ,
rename base table to some other, new table to base tablename.
analyze this table.





On Tue, Mar 29, 2011 at 9:34 PM, Plugge, Joe R. <JRPlugge@west.com> wrote:
Personally, provided you have the room, I would build a new table off to the side and then migrate what you need to keep to the new table, when done, and satisfied that you have all of the candidate rows, ranem the original to table to "x_tablename" and rename the newly created table into place to take over.... if all is good .. simply drop the x_tablename table.

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Nic Chidu
Sent: Tuesday, March 29, 2011 10:56 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] full vacuum of a very large table

Got a situation where a 130 mil rows (137GB) table needs to be brought down in size to  10 mil records (most recent) with the least amount of downtime.

Doing a full vacuum would be faster on:
 - 120 mil rows deleted and 10 mil active (delete most of them then full vacuum)
 - 10 mil deleted and 120 mil active. (delete small batches and full vacuum after each delete).

Any other suggestions?

Thanks,

Nic

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

pgsql-admin by date:

Previous
From: Bob Lunney
Date:
Subject: Re: full vacuum of a very large table
Next
From: Steve Crawford
Date:
Subject: Re: full vacuum of a very large table