On Wed, Sep 2, 2009 at 6:57 PM, Kevin
Grittner<Kevin.Grittner@wicourts.gov> wrote:
> Greg Stark <gsstark@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
