[GENERAL] Clustering with minimal locking

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
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 ;-)

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general