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

#插入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

create index concurrently idx_tbl_2 on tbl(id);

drop index idx_tbl_1;

create unique index concurrently user_info_username_key_1 on user_info(username);

begin ;

alter table user_info drop constraint user_info_username_key;

alter table user_info add constraint user_info_username_key unique using index user_info_username_key_1;

end ;

create unique index concurrently user_info_pkey_1 on user_info(id);

begin ;

alter table user_info drop constraint user_info_pkey;

alter table user_info add constraint user_info_pkey primary key using index user_info_pkey_1;

end ;