以前写过一些文章，都是说明如何避免膨胀，以及如何处理膨胀的。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;查看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)在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 | | | \x0a000000lp可以理解行的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)解释一下发生了什么，由于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)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)可以看到，新插入的行(事务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)vacuum full后可以看到没有可重复利用的空行了，因为vacuum full相当于重建了表。
vacuum后看下索引page的信息：
#更新数据后，13行数据都有索引引用
SELECT * FROM index_page('tbl_bloat_pkey',1);
itemoffset | ctid
------------+--------
1 | (0,1)
2 | (0,2)
3 | (0,3)
4 | (0,4)
5 | (0,5)
6 | (0,13)
7 | (0,6)
8 | (0,7)
9 | (0,8)
10 | (0,9)
11 | (0,10)
12 | (0,11)
13 | (0,12)
(13 rows)
#进行普通的vacuum后，相应的索引引用没了
#而且进行了page pruning,可以看到行是unused状态，索引也不会指向它们。
vacuum tbl_bloat ;
SELECT * FROM heap_page('tbl_bloat',0);
ctid | state | xmin | xmax | hhu | hot | t_ctid
--------+--------+------------+-------+-----+-----+--------
(0,1) | normal | 2254085 (c) | 0 (a) | | | (0,1)
(0,2) | normal | 2254086 (c) | 0 (a) | | | (0,2)
(0,3) | normal | 2254087 (c) | 0 (a) | | | (0,3)
(0,4) | normal | 2254088 (c) | 0 (a) | | | (0,4)
(0,5) | unused | | | | |
(0,6) | normal | 2254090 (c) | 0 (a) | | | (0,6)
(0,7) | normal | 2254090 (c) | 0 (a) | | | (0,7)
(0,8) | normal | 2254090 (c) | 0 (a) | | | (0,8)
(0,9) | normal | 2254090 (c) | 0 (a) | | | (0,9)
(0,10) | normal | 2254090 (c) | 0 (a) | | | (0,10)
(0,11) | unused | | | | |
(0,12) | unused | | | | |
(0,13) | normal | 2254095 (c) | 0 (a) | | | (0,13)
#以下说明了索引引用为10条，具有未使用状态的指针被视为空闲指针，这些空闲指针还是存在的，可以在新的行版本中重用。
SELECT * FROM index_page('tbl_bloat_pkey',1);
itemoffset | ctid
------------+--------
1 | (0,1)
2 | (0,2)
3 | (0,3)
4 | (0,4)
5 | (0,13)
6 | (0,6)
7 | (0,7)
8 | (0,8)
9 | (0,9)
10 | (0,10)
SELECT * FROM bt_page_stats('tbl_bloat_pkey', 1);
blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo | btpo_flags
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
1 | l | 10 | 0 | 16 | 8192 | 7948 | 0 | 0 | 0 | 3以上的例子直观明了的说明了膨胀的原理，当然导致表膨胀的原因很多，我们可以通过有效的手段解决相关膨胀的问题。
附： heap_page和index_page函数：
#heap_page
CREATE OR REPLACE FUNCTION public.heap_page(relname text, pageno integer)
RETURNS TABLE(ctid tid, state text, xmin text, xmax text, hhu text, hot text, t_ctid tid)
LANGUAGE sql
AS $function$
SELECT (pageno,lp)::text::tid AS ctid,
CASE lp_flags
WHEN 0 THEN 'unused'
WHEN 1 THEN 'normal'
WHEN 2 THEN 'redirect to '||lp_off
WHEN 3 THEN 'dead'
END AS state,
t_xmin || CASE
WHEN (t_infomask & 256) > 0 THEN ' (c)'
WHEN (t_infomask & 512) > 0 THEN ' (a)'
ELSE ''
END AS xmin,
t_xmax || CASE
WHEN (t_infomask & 1024) > 0 THEN ' (c)'
WHEN (t_infomask & 2048) > 0 THEN ' (a)'
ELSE ''
END AS xmax,
CASE WHEN (t_infomask2 & 16384) > 0 THEN 't' END AS hhu,
CASE WHEN (t_infomask2 & 32768) > 0 THEN 't' END AS hot,
t_ctid
FROM heap_page_items(get_raw_page(relname,pageno))
ORDER BY lp;
$function$
#index_page
CREATE OR REPLACE FUNCTION public.index_page(relname text, pageno integer)
RETURNS TABLE(itemoffset smallint, ctid tid, dead boolean)
LANGUAGE sql
AS $function$
SELECT itemoffset,
ctid,
dead --13版本新增此字段
FROM bt_page_items(relname,pageno);
$function$参考：
https://www.percona.com/blog/illustration-of-postgresql-bloat/
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
