膨脹率的精確計算
PostgreSQL自帶了pgstattuple模塊，可用于精確計算表的膨脹率。譬如這里的tuple_percent字段就是元組實際字節占關系總大小的百分比，用1減去該值即為膨脹率。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
#插入1000W數據postgres=# insert into t select id,id from generate_series(1,10000000) as id;INSERT 0 10000000 #表膨脹系數為0.097postgres=# 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個pagepostgres=# select * from pg_relpages('t'); pg_relpages ------------- 54055(1 row) #刪除數據postgres=# delete from t where id<>10000000;DELETE 9999999 #仍然占用54055個pagepostgres=# select * from pg_relpages('t'); pg_relpages ------------- 54055(1 row) #膨脹率已經為0.999999postgres=# 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 pagesDETAIL: 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 rowsVACUUM |
補充：pg索引膨脹問題---重建索引
問題：
發現數據庫中很多表的索引大小超過數據大小。經檢查，生產CA、CZ、MU、HU、PSG、RIUE庫都存在這個現象。
原因：據運行同事介紹索引膨脹問題無法避免，頻繁更新就會帶來這個問題。
解決方法：
對于大的索引可以采用重建的方式解決。以下兩種方法推薦第一種。
方法一：停止應用（這個操作會鎖表），重建索引（注：重建完索引名稱不變）
sql：reindex index 索引名稱
時間：速度較快。2G大小的表，基本上1分鐘左右可以建完索引。
還可以針對表重建索引，這個操作會加排他鎖 ：
reindex table 表名
方法二：在線建新索引，再把舊索引刪除
sql：根據不同索引采用不同的建索引命令，例如：
普通索引
|
1
2
|
create index concurrently idx_tbl_2 on tbl(id);drop index idx_tbl_1; |
唯一索引
|
1
2
3
4
5
|
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; |
主鍵索引
|
1
2
3
4
5
|
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; |
時間：不停應用的話，業務忙的時候可能會非常長的時間。
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持服務器之家。如有錯誤或未考慮完全的地方，望不吝賜教。
原文鏈接：https://blog.csdn.net/shipeng1022/article/details/108735494
