【赵渝强老师】PostgreSQL中表的碎片

在PostgreSQL中删除行的时候，这些行只是被标记为“dead”，而不是真正从物理存储上进行删除了，因而空间也没有真的被释放回收。在PostgreSQL中除非进行自动的auto vacuum或者是手动的vacuum，否则数据块所占用的物理空间不会被回收。因此在物理存储空间被回收之前，会导致存储空间中存在很多空洞。如果表结构中包含动态长度字段，那么这些空洞甚至可能不能被PostgreSQL重新用来存储新的行。因此，大量随机的DELETE操作，必然会在数据文件中造成不连续的空白空间。而当插入数据时,这些空白空间也不会被利用起来，于是造成了数据的存储位置不连续。物理存储顺序与逻辑上的排序顺序不同,这种就是数据碎片。
对于大量的UPDATE，也会产生文件碎片化 ， PostgreSQL的最小逻辑存储分配单位是数据块(Block)，其默认值是8K。因此大量的更新操作也可能导致数据块的分裂（Block Split），即：同一个字段的数据可能存储在不同的数据块中。频繁的数据块分裂，会使得数据的存储变得稀疏，并且被不规则的数据填充，所以最终数据会有碎片。

点击这里查看视频讲解：【赵渝强老师】PostgreSQL中表的碎片下面通过具体的步骤来演示如何清理表的碎片。
（1）创建一张新的表，并往表中插入5000万条记录

1. scott=# create table testfragement(tid int,tname varchar(20));
2. scott=# insert into testfragement select n,'myname_'||n from generate_series(1,50000000) n;

复制代码

（2）查看表testfragement占用的容量大小。

1. scott=# select pg_size_pretty(pg_relation_size('testfragement'));
3. # 输出的结果如下：
4. pg_size_pretty
5. ----------------
6. 2488 MB
7. (1 row)

复制代码

（3）删除表中所有数据。

1. scott=# delete from testfragement;

复制代码

（4）再次查看表testfragement占用的容量大小。

1. scott=# select pg_size_pretty(pg_relation_size('testfragement'));
3. # 输出的结果如下：
4. pg_size_pretty
5. ----------------
6. 2488 MB
7. (1 row)# 从输出的结果可以看出，尽管删除了表中的数据，表所占用的空间依然没有释放。

复制代码

（5）查看表的状态信息。

1. scott=# \x
2. scott=# select * from pg_stat_user_tables where relname = 'testfragement';
4. # 输出的结果如下：
5. -[ RECORD 1 ]-------+------------------------------
6. relid               | 16574
7. schemaname          | public
8. relname             | testfragement
9. seq_scan            | 1
10. seq_tup_read        | 50000000
11. idx_scan            |
12. idx_tup_fetch       |
13. n_tup_ins           | 50000000
14. n_tup_upd           | 0
15. n_tup_del           | 50000000
16. n_tup_hot_upd       | 0
17. n_live_tup          | 0
18. n_dead_tup          | 49999426
19. n_mod_since_analyze | 0
20. n_ins_since_vacuum  | 0
21. last_vacuum         |
22. last_autovacuum     | 2023-04-28 09:14:26.066678+08
23. last_analyze        |
24. last_autoanalyze    | 2023-04-28 09:14:46.677939+08
25. vacuum_count        | 0
26. autovacuum_count    | 1
27. analyze_count       | 0
28. autoanalyze_count   | 1
30. 其中：
31. n_live_tup 的数量是当前表的数据量。
32. n_dead_tup 的数据量是未回收的空间。
34. # 从参数n_dead_tup输出结果上看，表testfragement仍然占用了很多 ”空闲“ 数据块，其空间没有被回收。

复制代码

（6）手动进行一下碎片的清理。

1. scott=# vacuum testfragement;
3. # 此时会产生后台相应的进程：
4. [root@mydb ~]# ps -ef|grep VACUUM
5. postgres  6649  3540 .... postgres: postgres [local] VACUUM

复制代码

（7）再次查看一下表的状态

1. scott=# \x
2. scott=# select * from pg_stat_user_tables where relname = 'testfragement';
4. # 输出的信息如下：
5. -[ RECORD 1 ]-------+------------------------------
6. relid               | 16574
7. schemaname          | public
8. relname             | testfragement
9. seq_scan            | 1
10. seq_tup_read        | 50000000
11. idx_scan            |
12. idx_tup_fetch       |
13. n_tup_ins           | 50000000
14. n_tup_upd           | 0
15. n_tup_del           | 50000000
16. n_tup_hot_upd       | 0
17. n_live_tup          | 0
18. n_dead_tup          | 0
19. n_mod_since_analyze | 0
20. n_ins_since_vacuum  | 0
21. last_vacuum         | 2023-04-28 09:23:05.463206+08
22. last_autovacuum     | 2023-04-28 09:18:11.434888+08
23. last_analyze        |
24. last_autoanalyze    | 2023-04-28 09:14:46.677939+08
25. vacuum_count        | 1
26. autovacuum_count    | 2
27. analyze_count       | 0
28. autoanalyze_count   | 1
30. # 参数n_live_tup和n_dead_tup都变成了0，这说明表所占用的空间已经被释放回收。

复制代码

（8）重新查看表testfragement占用的容量大小。

1. scott=# select pg_size_pretty(pg_relation_size('testfragement'));
3. # 输出的结果如下：
4. pg_size_pretty
5. ----------------
6. 0 bytes
7. (1 row)

复制代码

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！