On Mon, 20 Nov 2006 13:28:01 +0000 (GMT), Raf wrote:
> Now, to me this says little about the vacuuming policy, however, I
> have seen the there are some very large files under the postgres
> data device. It seems that the size of these files exceeds the
> typical size of a database dump, which to me suggests that there is
> still a physical history of stale tuples in the DB and that it may
> need a full vacuum. Googling has led me to believe that auto_vacuum
> only rebuilds stats / does a vacuum analyse. Thus, I don't believe
> that this would free up space on the device?

Here's my limited understanding:
* "analyze"  is what rebuilds the stats
* "vacuum" is like defragging a disk
* "vacuum analyze" combines the first two
* "vacuum full" is like vacuum, but also truncates the files to the
  minimum possible size

pg_autovacuum does the first two. The theory is that if you vacuum at
an appropriate frequency (pg_autovacuum is supposed to figure this out
for itself, with the help of some config parameters to set your
expectations), the middle vacuum is enough - you don't minimize the
file size, but any "empty" bits should be enough to hold most of the
changes until the next vacuum. This is better than a full vacuum,
which requires exclusive locks on the tables.

Please note: my understanding is limited because where I work, we
haven't yet moved from a daily full vacuum to just setting up
pg_autovacuum properly, so this could all be wrong.

-- 
	Peter Haworth	pmh@edison.ioppublishing.com
"Please note the `tongue-stuck-out smilie' above, indicating the
 Author's intention to disregard well-reasoned counterarguments."
		-- Kurt Starsinic