On Dec 10, 2007 3:48 AM, Charles.Hou <ivan.hou@xxxxxxxxxxxxx> 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? ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
