Re: "Compacting" a relation

On Nov 29, 2006, at 2:29 AM, Hannu Krosing wrote:
> Ü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.

Larger or smaller?

There's a TODO about allowing control over what pages in a relation
you get back from FSM that would make this a lot easier. In the case
of a bloated table, you'd want to have the FSM favor handing out
pages at the beginning of the heap. If you combined that with a
special mode where new tuples would not be created on any page in the
last X percent of the heap, it would be trivial to clean up a bloated
table. Theoretically, you might be able to apply the same kind of
technique to cleaning up a bloated index.

BTW, the other reason to allow selecting where the FSM hands out data
is for keeping a table clustered. You might also be able to keep
indexes in a more optimal order on-disk (as I understand it, over
time the physical order of an index can become very different from
the index order).
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)