9.PG表的磁盘碎片清理

1.磁盘碎片

• 在PG中删除行时，这些行为只是被标记为 'dead' , 并不是真正从物理存储上进行删除，因为空间也没有被释放回收。
• 在PG中，除非进行自动的清理(vacuum)或者手工的清理，否则数据块所占用的物理空间是不会释放，在物理存储空间被回收之前，会导致存储空间中存在很多空洞。
• 大量的更新操作会产生文件碎片化，PG最小逻辑存储分配单位是数据块，默认是8KB,因此，大量的更新操作也可能导致数据块的分裂(Block split)，这个和mysql是一样的，即同一字段的数据可能存在不同的数据块中。
• 频繁的数据分裂会使数据的存储变得稀疏，并且被不规则的数据填充，所以最终数据会有磁片。

2.示例：

#1.创建一张test1表
postgres=# create table test1(tid int,tname varchar(20));
CREATE TABLE#2.插入500w数据
postgres=# insert into test1 select n,'myname_'||n from generate_series(1,5000000) n;
INSERT 0 5000000
#3.查看这张表的大小，有 2488MB
postgres=# select pg_size_pretty(pg_relation_size('test1'));
 pg_size_pretty 
----------------
 2488 MB
(1 row)
#4.delete删除这张表的数据
postgres=# delete from test1;
DELETE 50000000#5.再次查看这种表的大小，发现还是249MB,说明 delete时只是打了一个删除标记，但是数据并没有实际被删除。 
postgres=# select pg_size_pretty(pg_relation_size('test1'));
 pg_size_pretty 
----------------
 2488 MB
(1 row)

5.再看看这个表的状态信息

postgres=# select * from pg_stat_user_tables where relname='test1';
-[ RECORD 1 ]-------+------------------------------
relid               | 16397
schemaname          | public
relname             | test1
seq_scan            | 28
last_seq_scan       | 2023-12-24 14:22:20.928472+08
seq_tup_read        | 55000005
idx_scan            | 
last_idx_scan       | 
idx_tup_fetch       | 
n_tup_ins           | 84832533
n_tup_upd           | 0
n_tup_del           | 55000000
n_tup_hot_upd       | 0
n_tup_newpage_upd   | 0
n_live_tup          | 12674745   #表示当前表的数据量，
n_dead_tup          | 37325572   #表示为回收的空间
n_mod_since_analyze | 0
n_ins_since_vacuum  | 0
last_vacuum         | 
last_autovacuum     | 2023-12-24 14:21:54.68686+08
last_analyze        | 
last_autoanalyze    | 2023-12-24 14:22:34.001211+08
vacuum_count        | 0
autovacuum_count    | 5
analyze_count       | 0
autoanalyze_count   | 2

由n_dead_tup的输出结果可知，test1表仍然占用了很多'空闲'数据块，其空间没有被回收。

6.手工进行碎片的清理。

postgres=# vacuum test1;
VACUUM

7.再次查看test1表的状态信息

postgres=# analyze test1;
ANALYZE
postgres=#  select * from pg_stat_user_tables where relname='test1';
-[ RECORD 1 ]-------+------------------------------
relid               | 16397
schemaname          | public
relname             | test1
seq_scan            | 29
last_seq_scan       | 2023-12-24 14:27:51.948177+08
seq_tup_read        | 55000005
idx_scan            | 
last_idx_scan       | 
idx_tup_fetch       | 
n_tup_ins           | 84832533
n_tup_upd           | 0
n_tup_del           | 55000000
n_tup_hot_upd       | 0
n_tup_newpage_upd   | 0
n_live_tup          | 0   #这里发现为0
n_dead_tup          | 0   #这里发现为0
n_mod_since_analyze | 0
n_ins_since_vacuum  | 0
last_vacuum         | 2023-12-24 14:26:58.599143+08
last_autovacuum     | 2023-12-24 14:21:54.68686+08
last_analyze        | 2023-12-24 14:28:43.193874+08
last_autoanalyze    | 2023-12-24 14:22:34.001211+08
vacuum_count        | 1
autovacuum_count    | 5
analyze_count       | 1
autoanalyze_count   | 2

8.查看test1表大小

postgres=# select pg_size_pretty(pg_relation_size('test1'));
-[ RECORD 1 ]--+--------
pg_size_pretty | 0 bytes