Re: remove flatfiles.c

On Wed, Sep 2, 2009 at 3:30 PM, Greg Stark<gsstark(at)mit(dot)edu> wrote:
> On Wed, Sep 2, 2009 at 8:10 PM, Robert Haas<robertmhaas(at)gmail(dot)com> wrote:
>> I confess to being a little fuzzy on the details of how this
>> implementation (seq-scanning the source table for live tuples) is
>> different/better from the current VACUUM FULL implementation.  Can
>> someone fill me in?
>
>
> VACUUM FULL is a *lot* more complex.
>
> It scans pages *backwards* from the end (which does wonderful things
> on rotating media). Marks each live tuple it finds as "moved off",
> finds a new place for it (using the free space map I think?). Insert
> the tuple on the new page and marks it "moved in" and updates the
> indexes.
>
> Then it commits the transaction but keeps the lock. Then it has to
> vacuum all the indexes of the references to the old tuples at the end
> of the table. I think it has to commit that too before it can finally
> truncate the table.
>
> The backwards scan is awful for rotating media. The reading from the
> end and writing to the beginning is bad too, though hopefully the
> cache can help that.
>
> A lot of the complexity comes in from other parts of the system that
> have to be aware of tuples that have been "moved off" or "moved in".
> They have to be able to check whether the vacuum committed or not.

Ugh.

> That reminds me there was another proposal to do an "online" vacuum
> full similar to our concurrent index builds. Do noop-updates to tuples
> at the end of the table, hopefully finding space for them earlier in
> the table. Wait until those transactions are no longer visible to
> anyone else and then truncate. (Actually I think you could just not do
> anything and let regular lazy vacuum do the truncate). That might be a
> good practical alternative for sites where copying their entire table
> isn't practical.

I don't have a strong opinion about whether it's better to take an
exclusive lock on everything and reorganize freely, or whether it's
better to try to use MVCC semantics to reduce the locking impact (at a
cost of difficulty in the presence of long-running transactions). But
I think it would be really nice to have an incremental way to reduce
table bloat. Locking a table (or even better, part of a table) for a
couple of seconds once an hour for several days or weeks figures to be
practical in some (many?) environments where locking a table for
minutes or hours is not.

...Robert