Re: Incremental clustering?

A long time ago, in a galaxy far, far away, siracusa@mindspring.com (John Siracusa) wrote:
> The docs say:
>
> "Clustering is a one-time operation: when the table is subsequently
> updated, the changes are not clustered. That is, no attempt is made
> to store new or updated rows according to their index order. If one
> wishes, one can periodically recluster by issuing the command
> again."
>
> and
>
> "During the cluster operation, a temporary copy of the table is
> created that contains the table data in the index order. Temporary
> copies of each index on the table are created as well. Therefore,
> you need free space on disk at least equal to the sum of the table
> size and the index sizes."
>
> I don't relish totally locking and making a temporary copy of my
> biggest table, but that's exactly the table that seems to need
> clustering the most.  Will subsequent cluster command also make a
> complete copy?  Some form of "incremental clustering" would be
> nice...

Unfortunately, making this work in a manner that allows concurrency is
likely to be really troublesome.

The cluster operation potentially has to reorder all the tuples, and
the fact that the table is already _partially_ organized only
diminishes the potential.  If the new data, generally added "at the
end," has values that are fairly uniformly distributed across the
index, then the operation really will have to reorder all of the
tuples...

It would be pretty sweet to have a process analagous to 'non-blocking
VACUUM' as opposed to 'VACUUM FULL.'  But there's no particularly easy
way, and, to do so, you'd essentially have to throw away a fair chunk
of the benefits of the "clustered" properties.
--
"cbbrowne","@","acm.org"
http://www.ntlug.org/~cbbrowne/rdbms.html
"When you have eliminated the impossible, whatever remains, however
improbable, must be the truth." -- Sir Arthur Conan Doyle (1859-1930),
English author. Sherlock Holmes, in The Sign of Four, ch. 6 (1889).
[...but see the Holmesian Fallacy, due to Bob Frankston...
<http://www.frankston.com/public/Essays/Holmesian%20Fallacy.asp>]