how many dml tuples will trigger auto vacuum or analyze

开启autovacuum的前提条件是打开track_counts。因为系统需要根据跟踪到的tuples计数和阀值进行比较来决定是否出发autovacuum 或 autoanalyze。
相关参数:
autovacuum 是否开启自动vacuum , analyze.
log_autovacuum_min_duration 阀值，用于度量是否需要记录下autovacuum的动作到log里面.(-1表示禁止，0表示记录所有)
autovacuum_max_workers 用于指定整个数据库机器同一时间点允许的autovacuum后台进程(不包括lanucher进程)
autovacuum_naptime 两个autovacuum或autoanalyze允许周期间的间隔时间。为了看效果，我们在下面的例子把这个值设置为1S。
autovacuum_vacuum_threshold    阀值条件1：最小触发vacuum的度量值(计数器记录update,delete的tuples)
autovacuum_analyze_threshold   阀值条件1：最小触发analyze的度量值(计数器记录insert,update,delete的tuples)
autovacuum_vacuum_scale_factor   阀值条件2：当前reltuples乘以autovacuum_vacuum_scale_factor
autovacuum_analyze_scale_factor 阀值条件2：当前reltuples乘以autovacuum_analyze_scale_factor
注意autovacuum和autoanalyze是分别计数的，不要混淆。
每一轮autovacuum,autoanalyze后，相应的计数器将归零。

假设x为update的tuples计数值,y为delete的tuples计数值.
假设a为update的tuples计数值,b为delete的tuples计数值,c为insert的tuples计数值.
假设当前设置的以下参数值:
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
当表上面也设置了类似值的时候将覆盖数据库参数给出的值。详见create table语法。


何时发生autoanalyze ?
(a+b+c) > (0.1*reltuples + 50)


何时发生autovacuum ?
(x+y) > (0.2*reltuples + 50)


例:
digoal=> truncate table tbl_user;
TRUNCATE TABLE
digoal=> insert into tbl_user select generate_series(1,100000),'zhou','digoal','sky-mobi',27;
INSERT 0 100000
digoal=> select * from (select reltuples from pg_class where relname='tbl_user')t,(select last_autovacuum,last_autoanalyze from pg_stat_user_tables where relname='tbl_user') t2,(select now()) t3;
 reltuples |        last_autovacuum        |       last_autoanalyze        |              now             
-----------+-------------------------------+-------------------------------+-------------------------------
    100000 | 2011-02-28 15:56:27.841113+08 | 2011-02-28 15:56:31.964444+08 | 2011-02-28 15:56:32.544487+08
(1 row)
digoal=> update  tbl_user set age=28 where id<=5000;
UPDATE 5000
digoal=> select * from (select reltuples from pg_class where relname='tbl_user')t,(select last_autovacuum,last_autoanalyze from pg_stat_user_tables where relname='tbl_user') t2,(select now()) t3;
 reltuples |        last_autovacuum        |       last_autoanalyze        |             now             
-----------+-------------------------------+-------------------------------+------------------------------
    100000 | 2011-02-28 15:56:27.841113+08 | 2011-02-28 15:56:31.964444+08 | 2011-02-28 15:56:43.44112+08
(1 row)
digoal=> delete from tbl_user where id<=4999;
DELETE 4999
digoal=> select * from (select reltuples from pg_class where relname='tbl_user')t,(select last_autovacuum,last_autoanalyze from pg_stat_user_tables where relname='tbl_user') t2,(select now()) t3;
 reltuples |        last_autovacuum        |       last_autoanalyze        |              now             
-----------+-------------------------------+-------------------------------+-------------------------------
    100000 | 2011-02-28 15:56:27.841113+08 | 2011-02-28 15:56:31.964444+08 | 2011-02-28 15:57:07.145726+08
(1 row)
digoal=> delete from tbl_user where id<=5000;
DELETE 1
digoal=> select * from (select reltuples from pg_class where relname='tbl_user')t,(select last_autovacuum,last_autoanalyze from pg_stat_user_tables where relname='tbl_user') t2,(select now()) t3;
 reltuples |        last_autovacuum        |       last_autoanalyze        |              now             
-----------+-------------------------------+-------------------------------+-------------------------------
    100000 | 2011-02-28 15:56:27.841113+08 | 2011-02-28 15:56:31.964444+08 | 2011-02-28 15:57:15.339859+08
(1 row)
digoal=> delete from tbl_user where id<=5001;
DELETE 1
digoal=> select * from (select reltuples from pg_class where relname='tbl_user')t,(select last_autovacuum,last_autoanalyze from pg_stat_user_tables where relname='tbl_user') t2,(select now()) t3;
 reltuples |        last_autovacuum        |       last_autoanalyze        |              now             
-----------+-------------------------------+-------------------------------+-------------------------------
    100000 | 2011-02-28 15:56:27.841113+08 | 2011-02-28 15:56:31.964444+08 | 2011-02-28 15:57:25.626393+08
(1 row)
digoal=> delete from tbl_user where id<=5050;
DELETE 1
digoal=> select * from (select reltuples from pg_class where relname='tbl_user')t,(select last_autovacuum,last_autoanalyze from pg_stat_user_tables where relname='tbl_user') t2,(select now()) t3;
 reltuples |        last_autovacuum        |       last_autoanalyze        |              now             
-----------+-------------------------------+-------------------------------+-------------------------------
    100000 | 2011-02-28 15:56:27.841113+08 | 2011-02-28 15:56:31.964444+08 | 2011-02-28 15:57:53.702822+08
(1 row)
digoal=> delete from tbl_user where id<=5051;
DELETE 1
digoal=> select * from (select reltuples from pg_class where relname='tbl_user')t,(select last_autovacuum,last_autoanalyze from pg_stat_user_tables where relname='tbl_user') t2,(select now()) t3;
 reltuples |        last_autovacuum        |       last_autoanalyze        |              now             
-----------+-------------------------------+-------------------------------+-------------------------------
     94949 | 2011-02-28 15:56:27.841113+08 | 2011-02-28 15:57:59.126327+08 | 2011-02-28 15:57:59.661962+08
(1 row)
匹配条件 (5000(update)+5051(delete)) > 100000*0.1+50

例二:
digoal=> truncate table tbl_user;
TRUNCATE TABLE
digoal=> insert into tbl_user select generate_series(1,100000),'zhou','digoal','sky-mobi',27;
INSERT 0 100000
digoal=> select * from (select reltuples from pg_class where relname='tbl_user')t,(select last_autovacuum,last_autoanalyze from pg_stat_user_tables where relname='tbl_user') t2,(select now()) t3;
 reltuples |        last_autovacuum        |       last_autoanalyze        |              now             
-----------+-------------------------------+-------------------------------+-------------------------------
    100000 | 2011-02-28 16:02:39.745418+08 | 2011-02-28 16:03:26.717123+08 | 2011-02-28 16:03:27.275454+08
(1 row)
digoal=> update  tbl_user set age=28 where id<=10000;
UPDATE 10000
digoal=> select * from (select reltuples from pg_class where relname='tbl_user')t,(select last_autovacuum,last_autoanalyze from pg_stat_user_tables where relname='tbl_user') t2,(select now()) t3;
 reltuples |        last_autovacuum        |       last_autoanalyze        |              now             
-----------+-------------------------------+-------------------------------+-------------------------------
    100000 | 2011-02-28 16:02:39.745418+08 | 2011-02-28 16:03:26.717123+08 | 2011-02-28 16:03:40.223518+08
(1 row)

digoal=> update tbl_user set age=29 where id<=5000;
UPDATE 5000
digoal=> select * from (select reltuples from pg_class where relname='tbl_user')t,(select last_autovacuum,last_autoanalyze from pg_stat_user_tables where relname='tbl_user') t2,(select now()) t3;
 reltuples |        last_autovacuum        |       last_autoanalyze        |              now             
-----------+-------------------------------+-------------------------------+-------------------------------
    100000 | 2011-02-28 16:02:39.745418+08 | 2011-02-28 16:03:59.872549+08 | 2011-02-28 16:04:01.153831+08
(1 row)

digoal=> delete from tbl_user where id<=5050;
DELETE 1
digoal=> select * from (select reltuples from pg_class where relname='tbl_user')t,(select last_autovacuum,last_autoanalyze from pg_stat_user_tables where relname='tbl_user') t2,(select now()) t3;
 reltuples |        last_autovacuum        |       last_autoanalyze        |              now             
-----------+-------------------------------+-------------------------------+-------------------------------
    100000 | 2011-02-28 16:02:39.745418+08 | 2011-02-28 16:03:59.872549+08 | 2011-02-28 16:05:06.075632+08
(1 row)
digoal=> delete from tbl_user where id<=5051;
DELETE 1
digoal=> select * from (select reltuples from pg_class where relname='tbl_user')t,(select last_autovacuum,last_autoanalyze from pg_stat_user_tables where relname='tbl_user') t2,(select now()) t3;
 reltuples |        last_autovacuum        |       last_autoanalyze        |              now             
-----------+-------------------------------+-------------------------------+-------------------------------
     94949 | 2011-02-28 16:05:11.068057+08 | 2011-02-28 16:03:59.872549+08 | 2011-02-28 16:05:11.606445+08
(1 row)
匹配条件 (15000(update)+5051(delete)) > 100000*0.2+50


notice:
比较时取的是当前的reltuples，当先发生autoanalyze之后，reltuples可能会发生变化，如reltuples变小，从而导致不满足条件的autovacuum有可能变成满足条件。