Re: random observations while testing with a 1,8B row

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