On Wed, Sep 2, 2009 at 6:57 PM, Kevin Grittner<kevin.gritt...@wicourts.gov> wrote: > Greg Stark <gsst...@mit.edu> wrote: > >> I don't think we want to cluster on the primary key. I think we just >> want to rewrite the table keeping the same physical ordering. > > Well if that's what you want to do, couldn't you do something like?: > > Lock the table. > Prop all indexes > Pass the heap with two pointers, one to the first available empty > space and one to the first non-dead row past that, and move inside the > existing file. > Rebuild the indexes. > Release the lock.
Well dropping the indexes and moving tuples are both "hard" if you care about crash-safety and transactional integrity. The way we rewrite tables now is: Lock table Create new filenode. Scan old table and copy each record into the new filenode keeping update chains intact. Rebuild all indexes for the table (using a similar strategy with new relfilenodes) Commit the transaction If the transaction aborts at any point you still have the old pg_class record which points to the old relfilenode and all the old indexes are still valid. We have all the pieces we need to do this, it's just a matter of putting them together with a command to call them. A big part of what VACUUM FULL is annoying is the complexity of moving tuples in place. VACUUM FULL has to mark the old tuples and the new copies with its xid. It can't truncate the relation until it commits that xid. Actually I wonder how much performance improvement would come on normal DML just from not having to check xvac in the visibility checks. It's probably not much but... -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
