Jeff Davis <list-pgsql-general@dynworks.com> writes:
> I was wondering the same thing, so I looked at the development docs
> and it appears that regular VACUUM frees the dead tuples so that the
> space on a page may be reused. This approach doesn't actually reduce
> the number of pages allocated though, it reduces the chances that more
> pages will be allocated (because the pages have free space to make
> tuples in).
Maybe the docs still need some work on this point. Plain VACUUM will
still try to reduce the number of pages in a table, but it does so only
by removing wholly-empty end pages. (And it won't move tuples across
pages to make end pages empty, which turns out to have been the single
slowest, most complex action old-style VACUUM performs.) Also, it
can't remove any pages unless it can secure a temporary exclusive lock
on the table while it does so --- but unlike old-style VACUUM, it
doesn't insist on being able to do so. If there are concurrent
readers/writers then it just forgets about truncating the table and
moves on.
Bottom line is that it's a pretty laid-back approach to reclaiming
disk space. I believe that it will work pretty well for maintaining
a steady-state average disk usage of heavily updated tables, but in
cases such as having just deleted 80% of the tuples in a table (that
you're not planning to refill just as fast) a VACUUM FULL might still
be appropriate.
I expect we'll be experimenting with the behavior for awhile to come.
regards, tom lane