直观了解PostgreSQL中表膨胀的原理

以前写过一些文章，都是说明如何避免膨胀，以及如何处理膨胀的。PG中的膨胀是由于MVCC机制和存储引擎决定的，偶然看到一篇文章，非常简单明了，今天做实例说明一下，PG中膨胀的原理。

#创建测试表
CREATE TABLE tbl_bloat ( c1 int primary key);

#插入5条数据
INSERT INTO tbl_bloat values (1);
INSERT INTO tbl_bloat values (2);
INSERT INTO tbl_bloat values (3);
INSERT INTO tbl_bloat values (4);
INSERT INTO tbl_bloat values (5);
 
#在一个事物中，再次插入5条数据
BEGIN;
 INSERT INTO tbl_bloat SELECT generate_series(6,10);
COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

查看xmin,xmax，由于都是插入，所以只有xmin有数据，而且1~5每条都有单独的事物ID，6 ~ 10因为在一个事物，所以有一样的事物ID。

SELECT xmin, xmax, * FROM tbl_bloat ;
  xmin   | xmax | c1 
---------+------+----
 2254085 |    0 |  1
 2254086 |    0 |  2
 2254087 |    0 |  3
 2254088 |    0 |  4
 2254089 |    0 |  5
 2254090 |    0 |  6
 2254090 |    0 |  7
 2254090 |    0 |  8
 2254090 |    0 |  9
 2254090 |    0 | 10
(10 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

在PG中，表是堆表，数据是无序的，是从page底部逐个tuple填充的。默认page大小为8KB。为了查看page的结构，我们需要安装插件pageinspect。

#安装插件
CREATE extension pageinspect;

#查看tbl_bloat表的0号page
SELECT * FROM heap_page_items(get_raw_page('tbl_bloat',0));
 lp | lp_off | lp_flags | lp_len | t_xmin  | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   
----+--------+----------+--------+---------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 | 2254085 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 | 2254086 |      0 |        0 | (0,2)  |           1 |       2304 |     24 |        |       | \x02000000
  3 |   8096 |        1 |     28 | 2254087 |      0 |        0 | (0,3)  |           1 |       2304 |     24 |        |       | \x03000000
  4 |   8064 |        1 |     28 | 2254088 |      0 |        0 | (0,4)  |           1 |       2304 |     24 |        |       | \x04000000
  5 |   8032 |        1 |     28 | 2254089 |      0 |        0 | (0,5)  |           1 |       2304 |     24 |        |       | \x05000000
  6 |   8000 |        1 |     28 | 2254090 |      0 |        0 | (0,6)  |           1 |       2304 |     24 |        |       | \x06000000
  7 |   7968 |        1 |     28 | 2254090 |      0 |        0 | (0,7)  |           1 |       2304 |     24 |        |       | \x07000000
  8 |   7936 |        1 |     28 | 2254090 |      0 |        0 | (0,8)  |           1 |       2304 |     24 |        |       | \x08000000
  9 |   7904 |        1 |     28 | 2254090 |      0 |        0 | (0,9)  |           1 |       2304 |     24 |        |       | \x09000000
 10 |   7872 |        1 |     28 | 2254090 |      0 |        0 | (0,10) |           1 |       2304 |     24 |        |       | \x0a000000
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

lp可以理解行的ID号
t_xmin是插入的事物ID
t_ctid为指向行的指针ID,由(page,tupleid)构成，也可以理解为物理序号。
t_data是真实数据

更新三次，逻辑上保持行数据没变化，实际我们可见的应该还是10行数据。

#更新三次，保持表数据不变化
UPDATE tbl_bloat SET c1 = 20 WHERE c1 = 5;
UPDATE tbl_bloat SET c1 = 30 WHERE c1 = 20;
UPDATE tbl_bloat SET c1 = 5 WHERE c1 = 30;

#查看page内容，发现会有13行
SELECT * FROM heap_page_items(get_raw_page('tbl_bloat',0));
 lp | lp_off | lp_flags | lp_len | t_xmin  | t_xmax  | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   
----+--------+----------+--------+---------+---------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 | 2254085 |       0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 | 2254086 |       0 |        0 | (0,2)  |           1 |       2304 |     24 |        |       | \x02000000
  3 |   8096 |        1 |     28 | 2254087 |       0 |        0 | (0,3)  |           1 |       2304 |     24 |        |       | \x03000000
  4 |   8064 |        1 |     28 | 2254088 |       0 |        0 | (0,4)  |           1 |       2304 |     24 |        |       | \x04000000
  5 |   8032 |        1 |     28 | 2254089 | 2254093 |        0 | (0,11) |        8193 |       1280 |     24 |        |       | \x05000000
  6 |   8000 |        1 |     28 | 2254090 |       0 |        0 | (0,6)  |           1 |       2304 |     24 |        |       | \x06000000
  7 |   7968 |        1 |     28 | 2254090 |       0 |        0 | (0,7)  |           1 |       2304 |     24 |        |       | \x07000000
  8 |   7936 |        1 |     28 | 2254090 |       0 |        0 | (0,8)  |           1 |       2304 |     24 |        |       | \x08000000
  9 |   7904 |        1 |     28 | 2254090 |       0 |        0 | (0,9)  |           1 |       2304 |     24 |        |       | \x09000000
 10 |   7872 |        1 |     28 | 2254090 |       0 |        0 | (0,10) |           1 |       2304 |     24 |        |       | \x0a000000
 11 |   7840 |        1 |     28 | 2254093 | 2254094 |        0 | (0,12) |        8193 |       9472 |     24 |        |       | \x14000000
 12 |   7808 |        1 |     28 | 2254094 | 2254095 |        0 | (0,13) |        8193 |       9472 |     24 |        |       | \x1e000000
 13 |   7776 |        1 |     28 | 2254095 |       0 |        0 | (0,13) |           1 |      10496 |     24 |        |       | \x05000000

SELECT COUNT(*) FROM tbl_bloat;
 count
-------
    10
(1 row)


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

解释一下发生了什么，由于PG的MVCC机制，更新的一行的时候，实际上是删除原来的行，然后插入新的行。

t_xmin (2254093)

UPDATE tbl_bloat SET c1 = 20 WHERE c1 = 5;
逻辑上删除tuple ID 为5的行
物理上插入tuple ID 为11的行
更新行指针( (t_tcid) )从tuple ID 5 指向tuple ID 11
当tuple ID 5的t_xmax被设置为由事务2254093发起的新事务ID时，它变成死行(dead tuple)。

t_xmin (2254094)

UPDATE tbl_bloat SET c1 = 30 WHERE c1 = 20;
逻辑删除tuple ID 为11的行
物理插入tuple ID为12的行
更新行指针( (t_tcid) )从tuple ID 11 指向tuple ID 12
同样tuple ID 11的行变为死行(dead tuple)。

t_xmin (2254095)

UPDATE tbl_bloat SET c1 = 5 WHERE c1 = 30;
逻辑删除tuple ID 为12的行
物理插入tuple ID为13的行
更新行指针( (t_tcid) )从tuple ID 12 指向tuple ID 13
Tuple ID 13是对其他事物可见的，并且没有t_xmax，而且t_ctid (0,13)指向它本身。

从以上的例子可以看到，表数据没变，但是我们存储设备却多存了3行。也就是现在实际存储了13行。所以数据库久而久之，需要检测表的膨胀率，进行清理，提高存储和查询的效率。

通过普通的vacuum进程垃圾回收

#对表执行普通的vacuum,并查看page数据。
vacuum tbl_bloat ;
SELECT * FROM heap_page_items(get_raw_page('tbl_bloat',0));
 lp | lp_off | lp_flags | lp_len | t_xmin  | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   
----+--------+----------+--------+---------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 | 2254085 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 | 2254086 |      0 |        0 | (0,2)  |           1 |       2304 |     24 |        |       | \x02000000
  3 |   8096 |        1 |     28 | 2254087 |      0 |        0 | (0,3)  |           1 |       2304 |     24 |        |       | \x03000000
  4 |   8064 |        1 |     28 | 2254088 |      0 |        0 | (0,4)  |           1 |       2304 |     24 |        |       | \x04000000
  5 |      0 |        0 |      0 |         |        |          |        |             |            |        |        |       | 
  6 |   8032 |        1 |     28 | 2254090 |      0 |        0 | (0,6)  |           1 |       2304 |     24 |        |       | \x06000000
  7 |   8000 |        1 |     28 | 2254090 |      0 |        0 | (0,7)  |           1 |       2304 |     24 |        |       | \x07000000
  8 |   7968 |        1 |     28 | 2254090 |      0 |        0 | (0,8)  |           1 |       2304 |     24 |        |       | \x08000000
  9 |   7936 |        1 |     28 | 2254090 |      0 |        0 | (0,9)  |           1 |       2304 |     24 |        |       | \x09000000
 10 |   7904 |        1 |     28 | 2254090 |      0 |        0 | (0,10) |           1 |       2304 |     24 |        |       | \x0a000000
 11 |      0 |        0 |      0 |         |        |          |        |             |            |        |        |       | 
 12 |      0 |        0 |      0 |         |        |          |        |             |            |        |        |       | 
 13 |   7872 |        1 |     28 | 2254095 |      0 |        0 | (0,13) |           1 |      10496 |     24 |        |       | \x05000000
(13 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

vacuum后，我们看到tuple 5,11,12可以重复使用了，我们插入一条数据看一下。

postgres=# insert into tbl_bloat values (11);
INSERT 0 1
postgres=#  SELECT * FROM heap_page_items(get_raw_page('tbl_bloat',0));
 lp | lp_off | lp_flags | lp_len | t_xmin  | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   
----+--------+----------+--------+---------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 | 2254085 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 | 2254086 |      0 |        0 | (0,2)  |           1 |       2304 |     24 |        |       | \x02000000
  3 |   8096 |        1 |     28 | 2254087 |      0 |        0 | (0,3)  |           1 |       2304 |     24 |        |       | \x03000000
  4 |   8064 |        1 |     28 | 2254088 |      0 |        0 | (0,4)  |           1 |       2304 |     24 |        |       | \x04000000
  5 |   7840 |        1 |     28 | 2254096 |      0 |        0 | (0,5)  |           1 |       2048 |     24 |        |       | \x0b000000
  6 |   8032 |        1 |     28 | 2254090 |      0 |        0 | (0,6)  |           1 |       2304 |     24 |        |       | \x06000000
  7 |   8000 |        1 |     28 | 2254090 |      0 |        0 | (0,7)  |           1 |       2304 |     24 |        |       | \x07000000
  8 |   7968 |        1 |     28 | 2254090 |      0 |        0 | (0,8)  |           1 |       2304 |     24 |        |       | \x08000000
  9 |   7936 |        1 |     28 | 2254090 |      0 |        0 | (0,9)  |           1 |       2304 |     24 |        |       | \x09000000
 10 |   7904 |        1 |     28 | 2254090 |      0 |        0 | (0,10) |           1 |       2304 |     24 |        |       | \x0a000000
 11 |      0 |        0 |      0 |         |        |          |        |             |            |        |        |       | 
 12 |      0 |        0 |      0 |         |        |          |        |             |            |        |        |       | 
 13 |   7872 |        1 |     28 | 2254095 |      0 |        0 | (0,13) |           1 |      10496 |     24 |        |       | \x05000000
(13 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

可以看到，新插入的行(事务ID为2254096)重新使用了 tuple 5的行，并指向它自己。

vacuum full操作后的效果

#执行vacuum full，并且查看page信息
vacuum full tbl_bloat;

SELECT * FROM heap_page_items(get_raw_page('tbl_bloat',0));
 lp | lp_off | lp_flags | lp_len | t_xmin  | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   
----+--------+----------+--------+---------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 | 2254085 |      0 |        0 | (0,1)  |           1 |       2816 |     24 |        |       | \x01000000
  2 |   8128 |        1 |     28 | 2254086 |      0 |        0 | (0,2)  |           1 |       2816 |     24 |        |       | \x02000000
  3 |   8096 |        1 |     28 | 2254087 |      0 |        0 | (0,3)  |           1 |       2816 |     24 |        |       | \x03000000
  4 |   8064 |        1 |     28 | 2254088 |      0 |        0 | (0,4)  |           1 |       2816 |     24 |        |       | \x04000000
  5 |   8032 |        1 |     28 | 2254096 |      0 |        0 | (0,5)  |           1 |       2816 |     24 |        |       | \x0b000000
  6 |   8000 |        1 |     28 | 2254090 |      0 |        0 | (0,6)  |           1 |       2816 |     24 |        |       | \x06000000
  7 |   7968 |        1 |     28 | 2254090 |      0 |        0 | (0,7)  |           1 |       2816 |     24 |        |       | \x07000000
  8 |   7936 |        1 |     28 | 2254090 |      0 |        0 | (0,8)  |           1 |       2816 |     24 |        |       | \x08000000
  9 |   7904 |        1 |     28 | 2254090 |      0 |        0 | (0,9)  |           1 |       2816 |     24 |        |       | \x09000000
 10 |   7872 |        1 |     28 | 2254090 |      0 |        0 | (0,10) |           1 |       2816 |     24 |        |       | \x0a000000
 11 |   7840 |        1 |     28 | 2254095 |      0 |        0 | (0,11) |           1 |      11008 |     24 |        |       | \x05000000
(11 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

vacuum full后可以看到没有可重复利用的空行了，因为vacuum full相当于重建了表。

以上的例子直观明了的说明了膨胀的原理，当然导致表膨胀的原因很多，我们可以通过有效的手段解决相关膨胀的问题。

参考：
https://www.percona.com/blog/illustration-of-postgresql-bloat/

相关阅读:
Nodejs-1
逻辑漏洞——忘记密码、重置密码（案例分析、原理）
Java│蓝桥杯省赛真题星期一问题
 Spring 容器的启动过程探秘
 「Python实用秘技10」深度比较Python对象间差异
 realtek高清晰音频管理器怎么关闭的方法
 framework通信机制—LiveData使用方法及原理
 Netty实践 -- Netty处理粘包拆包
 uniapp subNvue 写的视频播放
 C语言入门实战(13)：十进制数转二进制
原文地址：https://blog.csdn.net/dazuiba008/article/details/126269365