Re: Feedback on getting rid of VACUUM FULL - Mailing list pgsql-hackers

From Robert Haas
Subject Re: Feedback on getting rid of VACUUM FULL
Date
Msg-id CA+TgmoaS_ymtTdGmTP15CTgMYKxEppDy_1SsGy+oqLbJyxKmhA@mail.gmail.com
Whole thread Raw
In response to Re: Feedback on getting rid of VACUUM FULL  (Alvaro Herrera <alvherre@2ndquadrant.com>)
Responses Re: Feedback on getting rid of VACUUM FULL
Re: Feedback on getting rid of VACUUM FULL
Re: Feedback on getting rid of VACUUM FULL
List pgsql-hackers
On Fri, Apr 24, 2015 at 3:04 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> 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.

Yeah.  The problem with solving this with an update is that a
concurrent "real" update may not see the expected behavior, especially
at higher isolation levels.  Tom also complained that the CTID will
change, and somebody might care about that.  But I think it's pretty
clear that a lot of people will be able to live with those problems,
and those who can't will be no worse off than now.

> 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.

Why do you need to do anything other than update the tuples and let
autovacuum clean up the mess?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



pgsql-hackers by date:

Previous
From: Andrew Dunstan
Date:
Subject: Re: Allow SQL/plpgsql functions to accept record
Next
From: Robert Haas
Date:
Subject: Re: parallel mode and parallel contexts