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
[EMAIL PROTECTED]
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
