膨脹率的精確計算

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