PostgreSQL表膨胀监控(精确计算)

膨胀率的精确计算

PostgreSQL自带了pgstattuple模块，可用于精确计算表的膨胀率。譬如这里的tuple_percent字段就是元组实际字节占关系总大小的百分比，用1减去该值即为膨胀率。 

#插入1000W数据
postgres=# insert into t select id,id from generate_series(1,10000000) as id;
INSERT 0 10000000

#表膨胀系数为0.097
postgres=# select *, 1.0 - tuple_len::numeric / table_len as bloat from pgstattuple('t');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent |         bloat          
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+------------------------
 442818560 |    10000001 | 400000040 |         90.33 |                0 |              0 |                  0 |    1304976 |         0.29 | 0.09669540499838127833
(1 row)

#占用54055个page
postgres=# select *  from pg_relpages('t');
 pg_relpages 
-------------
       54055
(1 row)


#删除数据
postgres=# delete from t where id<>10000000;
DELETE 9999999

#仍然占用54055个page
postgres=# select *  from pg_relpages('t');
 pg_relpages 
-------------
       54055
(1 row)


#膨胀率已经为0.999999
postgres=# select *, 1.0 - tuple_len::numeric / table_len as bloat from pgstattuple('t');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent |           bloat            
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+----------------------------
 442818560 |           2 |        80 |             0 |          9999999 |      399999960 |              90.33 |    1304976 |         0.29 | 0.999999819339099065766349


#vacuum表
postgres=# vacuum (verbose,full,analyze) t;
INFO:  vacuuming "public.t"
INFO:  "t": found 5372225 removable, 2 nonremovable row versions in 54055 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.89 s, system: 0.00 s, elapsed: 0.89 s.
INFO:  analyzing "public.t"
INFO:  "t": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
VACUUM