Heikki Linnakangas wrote: > Hmm, AFAICT pg_reorg is much more complex, writing stuff to a temp table > and swapping relfilenodes afterwards. More like the VACUUM REWRITE > that's been discussed. > > For the kicks, I looked at what it would take to write a utility like > that. It turns out to be quite trivial, patch attached. It uses the same > principle as VACUUM FULL, scans from the end, moving tuples to > lower-numbered pages until it can't do it anymore. It requires a small > change to heap_update(), to override the preference to store the new > tuple on the same page as the old one, but other than that, it's all in > the external module.
More than five years have passed since Heikki posted this, and we still haven't found a solution to the problem -- which neverthless keeps biting people to the point that multiple "user-space" implementations of similar techniques are out there. I think what we need here is something that does heap_update to tuples at the end of the table, moving them to earlier pages; then wait for old snapshots to die (the infrastructure for which we have now, thanks to CREATE INDEX CONCURRENTLY); then truncate the empty pages. Of course, there are lots of details to resolve. It doesn't really matter that this runs for long: a process doing this for hours might be better than AccessExclusiveLock on the table for a much shorter period. Are there any takers? -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers