Re: vacuuming - doubt

On Wed, Dec 11, 2013 at 1:08 AM, Jayadevan M
<maymala(dot)jayadevan(at)gmail(dot)com> wrote:
> Hi,
> Another theory question -
> PostgreSQL documentation says that -
> "There are two variants of VACUUM: standard VACUUM and VACUUM FULL. VACUUM
> FULL can reclaim more disk space "
> I created a table, inserted 1000 records and deleted them. The size after a
> vacuum and a vacuum full are given -
> select pg_total_relation_size('myt');;
> pg_total_relation_size
> ------------------------
> 65536
> (1 row)
>
> accounts=> vacuum myt;
> VACUUM
>
> accounts=> select pg_total_relation_size('myt');;
> pg_total_relation_size
> ------------------------
> 16384
> (1 row)
>
> accounts=> vacuum full myt;
> VACUUM
> accounts=> select pg_total_relation_size('myt');;
> pg_total_relation_size
> ------------------------
> 0
> (1 row)
>
> So what was the 65536 bytes left behind after standard vacuum?

30 second vacuum lesson:

regular vacuum marks empty space for re-use, and reclaims the empty
space at the end of a relation. So if the empty space is in the middle
etc it can't be reclaimed just reused.

vacuum full compacts a relation reclaiming all the empty space. It
requires an exclusive lock and is bad for production systems in
general because of this.

So the purpose of REGULAR vacuum is not to reclaim space from the
tables but to make it available for reuse later.
The purpose of vacuum FULL is to reclaim all wasted space at the
expense of an exclusive lock and db performance while it's happening.

So, since regular vacuum isn't designed to reclaim everything you
shouldn't be surprised that it did not, in fact, reclaim everything.
Try your experiment on larger datasets with more random deletions etc
to see the difference between regular and full vacuum.