Re: Is vacuum full lock like old's vacuum's lock?

Andrew Sullivan <andrew(at)libertyrms(dot)info> writes:
> I thought that, in the case Mr Reyes is talking about, Postgres would
> again use the freed disk space. It's just that the space would not
> be available to other applications. I thought what VACUUM FULL did
> was just free the disk space _absolutely_.
> If I'm right, does that also mean that performance is actually
> (marginally) _better_ in these types of cases, because the system
> doesn't need to request new disk blocks from the OS?

For situations where your turnover between vacuums is a small part
of the table (say up to 10% or so), I think VACUUM is a clear win
over VACUUM FULL. As you say, there's little percentage in doing
a lot of tuple-shuffling in order to return some disk blocks to
the OS, if you're only going to need the space back again soon.
Might as well accept some steady-state space overhead.

However, in Francisco's case he wants to completely replace the
table contents --- and if he wants to maintain service to clients
while he does it, then there's no way around the fact that the
peak space consumption is going to be twice the nominal table size.
(Can't invalidate the old tuples till you've loaded all the new
ones.) So if he just does VACUUMs then he's going to have a
steady-state space consumption 2x larger than minimum, not a few
percent larger than minimum. That might be annoying --- particularly
if he's got queries that do sequential scans of the table. Might be
worth a VACUUM FULL to knock the space usage back down.

(On the other hand, if the goal is "continuous service" then I
think VACUUM FULL is out of the question anyway; it'll lock down
the table for too long.)

regards, tom lane