Re: Clustering with minimal locking

On May 28, 2008, at 11:21 AM, Scott Ribe wrote:
> If I'm not totally off-base, here's one way to enable clustering on
> systems
> that run 24/7:
>
> 1 cluster current rows
> 1.1 note current last committed transaction
> 1.2 copy all visible rows to new table in cluster order
> 1.3 build indexes on new table
> 2 add changes
> 2.1 note current last committed transaction
> 2.2 apply to new table (& indexes) all changes committed since 1.1
> 3 put new table into service
> 3.1 take exclusive lock on table

BOOM! Deadlock.

> 3.2 apply to new table (& indexes) all changes committed since 2.1
> 3.3 switch in new table
> 3.4 release lock
> 3.5 clean up old table storage
>
> I don't know enough about pg internals to know how big a project
> this would
> be, but it seems to me that the WAL provides many of the pieces
> needed to
> support steps 1.1 and 2.2, for instance. (Even so, I know it's
> still not
> trivial, just perhaps not huge.)
>
> - I guess there's still the possibility that 3.1 could stall in the
> presence
> of long-lived transactions--but this is certainly no worse than the
> current
> situation where it would stall before starting the cluster operation.
>
> - By "apply changes" I mean insert, update, delete rows--of course
> schema
> changes would be locked out during the cluster, even if it takes
> days ;-)

What you're describing is possible; it's done for CREATE INDEX
CONCURRENT. But it's not very easy to add. I think what makes a lot
more sense is to have a form of clustering that puts effort into
placing tuples in the correct location. If you had that, you could
effectively migrate stuff into proper cluster order in userland; or
just let it take care of itself. Presumable the table would
eventually end up clustered if rows are updated often enough.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828