On Sat, Mar 11, 2006 at 10:21:43PM +0200, Hannu Krosing wrote:
> > table partitioning, I keep wondering whether it would be possible
> > to move rows near the end of the table to the beginning in one, non-
> > locking
> > phase (vacuum to populate FSM with free space near beginning of table,
> > touch rows starting at end of table, repeat) and then finish off with a
> > vacuum full to tidy up the remainder and truncate the files (or a
> > simpler
> > "lock the table and truncate anything unused at the end").
>
> At some point I had to compress a very busily updated table. I used the
> following approach:
>
> 1) VACUUM buzytable; (lazy not full)
>
> 2) SELECT primary_key_value, ctid FROM buzytable;
>
> 3) Extract N last records from there and for each keep repeating
>
> 3A) UPDATE buzytable
> SET primary_key_value = primary_key_value
> WHERE primary_key_value = extracted_value
>
> 3B) SELECT ctid FROM buzytable
> WHERE primary_key_value = extracted_value
>
> until the tuple is moved to another pages, hopefully nearer to
> the beginning of table
>
> repeat from 1) until the page for last row (extracted from ctid) is
> smaller than some thresold.
BTW, this is what the following TODO would hopefully fix:
Allow FSM to return free space toward the beginning of the heap file, in
hopes that empty pages at the end can be truncated by VACUUM
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461