On Tue, Mar 29, 2011 at 9:26 PM, Nic Chidu <nic@chidu.net> wrote:
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.
What version of PG are you with ?
If PG>8.2 and have disk space twice the size of target table and indexes, you can reorganize the table using pg_reorg without locking table with concurrent selections and updations.
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).