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