Re: "Compacting" a relation

[Hannu Krosing <hannu(at)skype(dot)net>]

Ühel kenal päeval, K, 2006-11-29 kell 11:19, kirjutas Peter Eisentraut:
> vacuumlazy.c contains a hint "Consider compacting this relation" but AFAICT, 
> there is no indication anywhere how "compacting" is supposed to be achieved.  
> I guess this means VACUUM FULL or CLUSTER, but I don't think the hint can be 
> processed effectively by a user.

I once had an online/concurrent/non-locking compacting script, which did
for each const_pk_col_with_largest_ctid staring starting from end of
relation 

UPDATE rel 
   SET pk_col=pk_col 
 WHERE pk_col = const_pk_col_with_largest_ctid

until the tuple moved to another page as determined by 

SELECT ctid FROM rel where pk_col=const_pk_col_with_largest_ctid

if the tuple moved to a larger page number then it was time for another
lazy vacuum.

this compacted the live data in the table and if done enough times, the
lazy vacuum did actually shorten the file.

-- 
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.com