On 4/24/15 2:04 PM, Alvaro Herrera wrote:
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?
Honestly, I'd prefer we exposed some way to influence where a new tuple
gets put, and perhaps better ways of accessing tuples on a specific
page. That would make it a lot easier to handle this in userspace, but
it would also make it easier to do things like concurrent clustering. Or
just organizing a table however you wanted.
That said, why not just pull what Heikki did into contrib, and add the
necessary mode to heap_update?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
