Re: VACUUM FULL name is very confusing to some people (or to most nonexpert people) - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: VACUUM FULL name is very confusing to some people (or to most nonexpert people)
Date
Msg-id CAFj8pRABkFwiaqcGq6+YESr2Su_bCnjTXN7kz8FL1J4wr_VjgA@mail.gmail.com
Whole thread Raw
In response to Re: VACUUM FULL name is very confusing to some people (or to most nonexpert people)  (Lætitia Avrot <laetitia.avrot@gmail.com>)
List pgsql-hackers


2018-02-26 9:56 GMT+01:00 Lætitia Avrot <laetitia.avrot@gmail.com>:
Although VACUUM and VACUUM FULL is different, then result is same (depends on detail level) - the data files are optimized for other processing. You should to see a VACUUM like family of commands that does some data files optimizations. VACUUM, VACUUM FULL, VACUUM FREEZE, VACUUM ANALYZE, ... Personally I don't think so we need to implement new synonym command for this case.

Here's how I understand what you wrote : "Each and every vacuum operations are different flavours for files optimization so it's legitimate to use similar names". I agree for VACUUM ANALYZE and VACUUM FREEZE that can be seen as options to do more things than a simple VACUUM.

But I disagree for VACUUM FULL that isn't an option to do one more thing than VACUUM does. VACUUM FULL is a complete different process.

Let's take an example:
In a production server with average production load, if you're already running a VACUUM, you can change it to a VACUUM ANALYZE without many risks. But I wouldn't dare try a VACUUM FULL without pg_repack.

VACUUM and VACUUM ANALYZE does same VACUUM work.

The VACUUM FREEZE is different too.
 
 
Why you you cannot to say your students - "VACUUM FULL is like SHRINK in SQL Server"?

I do explain that to my students but I'm not sure they memorize it, because they do have a lot to memorize in a training session.

Maybe the core of this issue is fact so VACUUM FULL, VACUUM FREEZE is statements based on two keywords commands. Lazy VACUUM uses just keyword. From this perspective the lazy VACUUM "should be" renamed, because it is inconsistent - and some databases uses some names like OPTIMIZE table (and it sound much more optimistic :)). I teach PostgreSQL more than ten years - and I have not the problem with this topic - the hard part is explain of VACCUM FREEZE - but VACUUM and VACUUM FULL can be explained simply (with some detail reduction). VACUUM recuces MVCC costs, VACUUM FULL reduces bloating. ANALYZE is orthogonal - calculate column statistic.
 

I keep meeting customers to who I have to explain that a simple VACUUM doesn't rebuild indexes. Am I the only one facing that problem ?

simple VACUUM (lazy VACUUM does a tasks that don't needs aggressive locks) - rebuild indexes needs strong locks.

I agree, it is not pretty clean, because VACUUM FULL share some work with REINDEX, but introduction new command change nothing.

 
 
Regards

Pavel
 
Regards

Lætitia

pgsql-hackers by date:

Previous
From: Ildus Kurbangaliev
Date:
Subject: Re: [HACKERS] Custom compression methods
Next
From: Vasundhar Boddapati
Date:
Subject: Re: Use of term hostaddrs for multiple hostaddr values