MVCC的工作机制

更新和删除数据时，并不是直接删除行的数据，而是更新行的头部信息中的xmax

1. 创建测试表

postgres=# create table iso\_test(id int,info text);

2. 插入数据

postgres=# truncate iso_test ;
TRUNCATE TABLE
postgres=# begin;
BEGIN
postgres=# select txid_current();
 txid_current 
--------------
          562
(1 row)

postgres=# insert into iso_test values(1,'test');
INSERT 0 1
postgres=# SELECT
postgres-# lp AS "行指针",
postgres-# lp_off AS "行指针偏移量",
postgres-# t_xmin AS "插入事务的ID",
postgres-# t_xmax AS "删除事务的ID",
postgres-# t_ctid AS "插入命令的ID",
postgres-# t_data AS "数据本身"
postgres-# FROM heap_page_items(get_raw_page('iso_test',0));
 行指针 | 行指针偏移量 | 插入事务的ID | 删除事务的ID | 插入命令的ID |       数据本身       
--------+--------------+--------------+--------------+--------------+----------------------
      1 |         8152 |          562 |            0 | (0,1)        | \x010000000b74657374
(1 row)
postgres=# end;
COMMIT

3. 删除数据

   会将xmax的值设置为当前事务的xid

postgres=# begin;
BEGIN
postgres=# select txid_current();
 txid_current 
--------------
          563
(1 row)

postgres=# delete from iso_test where id=1;
DELETE 1
postgres=# SELECT
postgres-# lp AS "行指针",
postgres-# lp_off AS "行指针偏移量",
postgres-# t_xmin AS "插入事务的ID",
postgres-# t_xmax AS "删除事务的ID",
postgres-# t_ctid AS "插入命令的ID",
postgres-# t_data AS "数据本身"
postgres-# FROM heap_page_items(get_raw_page('iso_test',0));
 行指针 | 行指针偏移量 | 插入事务的ID | 删除事务的ID | 插入命令的ID |       数据本身       
--------+--------------+--------------+--------------+--------------+----------------------
      1 |         8152 |          562 |          563 | (0,1)        | \x010000000b74657374
(1 row)

postgres=# end;
COMMIT

4. 更新数据，实际上就是先删除原来的行，再插入新行

postgres=# insert into iso_test values(2,'new');
INSERT 0 1
postgres=# begin;
BEGIN
postgres=# select txid_current();
 txid_current 
--------------
          565
(1 row)

postgres=# select * from iso_test; 
 id | info 
----+------
  2 | new
(1 row)

postgres=# SELECT
postgres-# lp AS "行指针",
postgres-# lp_off AS "行指针偏移量",
postgres-# t_xmin AS "插入事务的ID",
postgres-# t_xmax AS "删除事务的ID",
postgres-# t_ctid AS "插入命令的ID",
postgres-# t_data AS "数据本身"
postgres-# FROM heap_page_items(get_raw_page('iso_test',0));
 行指针 | 行指针偏移量 | 插入事务的ID | 删除事务的ID | 插入命令的ID |       数据本身       
--------+--------------+--------------+--------------+--------------+----------------------
      1 |         8152 |          562 |          563 | (0,1)        | \x010000000b74657374
      2 |         8120 |          564 |            0 | (0,2)        | \x02000000096e6577
(2 rows)

postgres=# update iso_test set info='NEW' where id=2;
UPDATE 1
postgres=# SELECT                                    
lp AS "行指针",
lp_off AS "行指针偏移量",
t_xmin AS "插入事务的ID",
t_xmax AS "删除事务的ID",
t_ctid AS "插入命令的ID",
t_data AS "数据本身"
FROM heap_page_items(get_raw_page('iso_test',0));
 行指针 | 行指针偏移量 | 插入事务的ID | 删除事务的ID | 插入命令的ID |       数据本身       
--------+--------------+--------------+--------------+--------------+----------------------
      1 |         8152 |          562 |          563 | (0,1)        | \x010000000b74657374
      2 |         8120 |          564 |          565 | (0,3)        | \x02000000096e6577
      3 |         8088 |          565 |            0 | (0,3)        | \x02000000094e4557
(3 rows)

postgres=# end;
COMMIT

如果一张表中，反复进行插入和删除操作，表很容易被撑大

VACUUM

回收页面上已经执行UPDATE和DELETE操作的空间

postgres=# VACUUM iso_test ;
VACUUM

查看页面结构

执行UPDATE和DELETE操作后的空间被回收，但是空间并未释放，等下一条数据插入占用空间

postgres=# begin;
BEGIN
postgres=# select txid_current();
 txid_current 
--------------
          566
(1 row)

postgres=# insert into iso_test values(1,'test');
INSERT 0 1
postgres=# end;
COMMIT

观察页面结构

再次插入数据

postgres=# begin;
BEGIN
postgres=# select txid_current();
 txid_current 
--------------
          567
(1 row)

postgres=# insert into iso_test values(3,'postgres');
INSERT 0 1
postgres=# end;
COMMIT

再次观察表结构

• 新发现：

第二条数据的空间未被使用，而是新分配了空间，通过进行多次实验发现，执行VACUUM操作之后，UPDATE的数据空间无法被重用，DELETE的数据空间可以被重用
那么在实际生产中，如果一张表频繁进行UPDATE操作，只进行VACUUM是无法进行空间重用的