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 [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
