Re: about the performance of autovacuum and vacuumdb?

On Dec 10, 2007 3:48 AM, Charles.Hou <ivan(dot)hou(at)msa(dot)hinet(dot)net> wrote:
> i try to compare two methods of garbage-collect.(Postgresql Ver:8.1.3)

You need to update RIGHT NOW to 8.1.10. seriously, there are crash /
data eating bugs in 8.1.3.

> 1. enable autovacuum without using vacuumdb -f mydb in crontab.
> 2. using crontab to vacuumdb in every 10 min (autovacuum disable)
>
> the method 2 got more free space than method 1.
>
> the disk size of database still increased in method 1.
> so, what's the advantage of autovacuum?

I think you might misunderstand the best way to reclaim space.

With proper vacuuming, what you are shooting for is for updates /
inserts to use the dead space at about the same rate that it's marked
free by REGULAR vacuums. This keeps the tables from having to be
re-written over and over, as they are with vacuum full.

With autovacuum daemon, assuming it's setup right, you'll have some
small percentage of each table be free at any given time, and your db
will reuse that space as it goes along. If autovacuum isn't aggresive
enough, or your Free Space Map is too small, then the size of dead
space will slowly grow until it dominates the table and causes poor
performance.

Vacuum full requires much stronger table level locks to work, and
rewrites the whole table each time. Since it's reading and writing
the whole table, it tends to suck up all your I/O and cause other
processes to run slower.

Generally speaking, if you find yourself having to run vacuum full
very often, you're likely doing something wrong. This is especially
true on transactional loads. For batch oriented systems, which might
rewrite a whole table two or three times in rapid sucession, vacuum
full makes perfect sense.

So, what kind of load are you running?