Re: Vacuum Full - Mailing list pgsql-admin

On Wed, Apr 1, 2009 at 4:10 PM, Tino Schwarze <postgresql@tisc.de> wrote:
> Hi Rafael,
>
> On Wed, Apr 01, 2009 at 06:51:00PM -0300, Rafael Domiciano wrote:
>
>> > > I have some doubts about Vacuum Full. There We go:
>> > > 1) The Only thing that Vacuum Full (Only Full, not Analyze) is to clean
>> > > "dead space" on the disc, and reorganize the relation at the physical
>> > level?
>> > > If it's true, so doing this may speed up select's, while the Postgres
>> > will
>> > > going to do less hit in the disc. I'm right?
>> >
>> > Generally speaking, vacuum full should not normally be needed.
>> > Regular vacuuming should free up enough free space that the table
>> > reaches an equilibrium where it has some small percentage of available
>> > space (5 to 15% or so) and stay there.
>>
>> So, why Vacuum Full should not nomally be needed? Vacuum Analyze is enough
>> for Maintenance Base?
>
> You should normally be using autovacuum, possibly slightly tuned, to
> keep everything nice.
>
>> One more question, If I understood Vacuum Full it's similar to Defrag Tool
>> like Defrag Windows. So, like defraging Windows, it's speed up a little (the
>> disc is going to read the blocks faster) , Vacuuming Full Postgres should
>> have the same behavior?!
>
> VACUUM FULL should be renamed to "I totally bloated my database, please
> fix it" or something like that. It is a very intrusive operation
> (prevents write access to the table IIRC), so it's nothing for regular
> operation. If you need VACUUM FULL, something went wrong before.
>
> Maybe a command name like "UNBLOAT DATABASE" or "REBUILD TABLES"
> would be more suited. @Developers: I'm serious. People perceive VACUUM
> FULL as just a more intense version of VACUUM.

Yeah, I think rebuild is a much better name for vacuum full.  Vacuum
full is just an historical name now, from the days when vacuum was ony
available in the full flavor.  After regular vacuum was introduced,
the old behaviour was renamed to vacuum full.  At the time this made a
lot of sense.  If you had been running vacuum before that, you just
ran it now, and in much less time and much less intrusively, it
cleaned up dead rows and made them available.  And if you needed the
old behaviour, you just amped up vacuum with full.

Nowadays, with many many new users, and no historical context, they do
just take it for "a better kind of vacuum" when in fact it is really
like reindex to indexes.  I'd vote for rebuild [table]; as the new way
to spell vacuum full;