也聊聊PostgreSQL中的空间膨胀与AutoVacuum

微信公众号：数据库杂记   个人微信: _iihero
我是iihero. 也可以叫我Sean.
iihero@CSDN(https://blog.csdn.net/iihero) 
Sean@墨天轮 (https://www.modb.pro/u/16258)
iihero@zhihu (https://www.zhihu.com/people/iihero)
数据库领域的资深爱好者一枚。SAP数据库技术专家与架构师，PostgreSQL ACE.
水木早期数据库论坛发起人db2@smth. 早期多年水木论坛数据库版版主。
国内最早一批DB2 DBA。前后对Sybase, PostgreSQL, HANA, 
Oracle, DB2, SQLite均有涉猎。曾长期担任CSDN相关数据库版版主。
三本著作：<<Java2网络协议内幕>> <<Oracle Spatial及OCI高级编程>> 
<<Sybase ASE 15.X全程实践>>
兴趣领域：数据库技术及云计算、GenAI

业余专长爱好：中国武术六段 陈式太极拳第13代传人（北京陈式太极拳第5代传人）
职业太极拳教练，兼任北京陈式太极拳研究会副秘书长。
如果想通过习练陈式太极拳强身健体，也可以与我联系。

1、前言介绍

PostgreSQL数据库，由于其自身特有的MVCC机制，它没有自己的Undo Log。更新操作相当于一个删除叠加一个插入操作，反映在表数据文件当中就是内部的一条新增记录，加上原有记录的删除。那些已被删除的元组(死元组)所占的空间如果得不到及时的回收，那么表数据文件所用空间将会不断膨胀，查询及其它CUD操作也会变慢，直到系统不可承受。这就是AutoVaccum存在的原因。

这也是PostgreSQL MVCC背后的基本思想之一,因为它允许更大并发,在不同的进程之间最小的锁定.这个MVCC实现的缺点是留下了已删除的元组,即使在所有可能看到这些版本的事务完成之后也是如此.可以想见，为何PostgreSQL在并发读写的测试中，很多场景下为何性能异常优秀。

如果是一名DB Ops或DBA，对这种空间膨胀缺乏必要的异常监控，一旦出问题，系统也将变得容易出问题，有时候会酿成大问题。

2、并发控制中的增删改

为了方便理解空间的膨胀以及死元组，我们可以用小实验来简单体会一下。
我们建一张两个字段的表，并插入一行值：

CREATE TABLE t1 (id int, col2 varchar(32));
insert into t1 values(1, 'wang');

查询一下它的基本情况，包括一些常规的内置字段：

mydb=# select tableoid, ctid, xmin, xmax, cmin, cmax, * from t1;
 tableoid | ctid  | xmin | xmax | cmin | cmax | id | col2
----------+-------+------+------+------+------+----+------
    21377 | (0,1) | 1395 |    0 |    0 |    0 |  1 | wang
(1 row)

看一下，会有点小吃惊，多达6个内置字段。回想，我们在PG中有400多个keyword. (用SELECT word FROM pg_get_keywords()可以得，略过），但是那些keyword里并不包含tableoid之类的。但是你如果想建表，字段名称与上边的内置字段相重，建表会失败的。如果遇到别的数据库，刚好有这几个字段中的几个，移植过来，就得想办法了。我们来看看这6个字段的含义：

tableoid：指的是table t1的对象标识id (OID)的值，它在表对象一级是全局唯一的。使用下边的查询一样可以得到它的值。并且顺便得到table对应的文件路径(base/21371/21465)：

mydb=# select oid, pg_relation_filepath(oid), relname, relkind, relpages from pg_class where relname = 't1';
  oid  | pg_relation_filepath | relname | relkind | relpages
-------+----------------------+---------+---------+----------
 21377 | base/21371/21465     | t1      | r       |        0
ctid: 元组的id, 它相当于一个坐标值(pair), 第一个元素描述的是页号，
第二个元素描述的是元组在页内的序号。

xmin, xmax: 最小最大事务号, 这里有一定的规则。我们任意时刻都可以

使用txid_current()得到当前的事务号。

\1) 如果是插入操作，xmin取的是当前的事务号, xmax置为0

\2) 如果是删除操作，使用xmax标识当前的事务号

\3) 如果是更新操作，相当于旧行做一个删除，然后插入一个新行

cmin, cmax: 最小最大command id，记录的是事务当中的第几个命令和命令范围。

大致明白了这些字段含义，我们可以进一步往下处理。这里要用到pageinspect

内置插件。大家可以自行安装：(create extension pageinspect)，需要

用到函数：heap_page_items以及get_raw_page。

做一次删除操作：

mydb=*# select txid_current();
 txid_current
--------------
         1400
(1 row)
mydb=*# delete from t1;
DELETE 1
mydb=*# select tableoid, ctid, xmin, xmax, cmin, cmax, * from t1;
 tableoid | ctid | xmin | xmax | cmin | cmax | id | col2
----------+------+------+------+------+------+----+------
(0 rows)
mydb=*# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_data
FROM heap_page_items(get_raw_page('t1', 0));
 tuple | t_xmin | t_xmax | t_cid | t_ctid |        t_data
-------+--------+--------+-------+--------+----------------------
     1 |   1395 |   1400 |     0 | (0,1)  | \x010000000b77616e67
(1 row)
我们会发现，t_xmax的值变了，变成1400了。它是新的txid。比t_xmin大。
再插入一条新记录，然后更新一下那条记录看下：
mydb=# begin;
BEGIN
mydb=*# select pg_current_xact_id(), txid_current();
 pg_current_xact_id | txid_current
--------------------+--------------
               1401 |         1401
(1 row)

mydb=*# delete from t1;
DELETE 1
mydb=*# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_data
FROM heap_page_items(get_raw_page('t1', 0));
 tuple | t_xmin | t_xmax | t_cid | t_ctid |        t_data
-------+--------+--------+-------+--------+----------------------
     1 |   1395 |   1401 |     0 | (0,1)  | \x010000000b77616e67
(1 row)

mydb=*# insert into t1 values(2, 'aaaa');
INSERT 0 1
mydb=*# select tableoid, ctid, xmin, xmax, cmin, cmax, * from t1;
 tableoid | ctid  | xmin | xmax | cmin | cmax | id | col2
----------+-------+------+------+------+------+----+------
    21377 | (0,2) | 1401 |    0 |    1 |    1 |  2 | aaaa
(1 row)

mydb=*#  SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_data
mydb-*# FROM heap_page_items(get_raw_page('t1', 0));
 tuple | t_xmin | t_xmax | t_cid | t_ctid |        t_data
-------+--------+--------+-------+--------+----------------------
     1 |   1395 |   1401 |     0 | (0,1)  | \x010000000b77616e67
     2 |   1401 |      0 |     1 | (0,2)  | \x020000000b61616161
(2 rows)

mydb=*# update t1 set col2='bbbb';
UPDATE 1
mydb=*# select tableoid, ctid, xmin, xmax, cmin, cmax, * from t1;
 tableoid | ctid  | xmin | xmax | cmin | cmax | id | col2
----------+-------+------+------+------+------+----+------
    21377 | (0,3) | 1401 |    0 |    2 |    2 |  2 | bbbb
(1 row)

mydb=*#  SELECT l