数据库内核月报

背景

PostgreSQL HEAP引擎，同一张表的记录的有效版本、垃圾版本存储在同一个数据文件中。ZHEAP引擎将垃圾版本从数据文件剥离，存储在UNDO文件中。

由于HEAP引擎的垃圾版本会存储在数据文件中，所以可能会导致表的膨胀，PG有垃圾回收的功能可以回收这些垃圾版本，使得膨胀尽可能可控（包括索引中的垃圾版本）。但是配置或使用不当时，还是可能导致表和索引的膨胀。

那么到底如何避免，到底应该如何设置参数？哪些操作可能导致膨胀？膨胀了怎么处理？

多版本机制

1、更新记录会产生新版本，旧版本保留在原地不动，仅在记录头信息中标记删除记录的事务号(xmax)。

2、删除记录，旧版本存储在原地不动。仅在记录头信息中标记删除记录的事务号(xmax)。

例如

postgres=# create table t1(id int, info text);  
CREATE TABLE  
postgres=# insert into t1 values (1,'test');  
INSERT 0 1  

会话1

postgres=# begin;  
BEGIN  
  
开启事务  
  
postgres=# select txid_current();  
 txid_current   
--------------  
       749507  
(1 row)  
  
查询当前记录行号，版本号  
  
  
postgres=# select ctid,xmin,xmax,* from t1;  
 ctid  |  xmin  | xmax | id | info   
-------+--------+------+----+------  
 (0,1) | 749506 |    0 |  1 | test  
(1 row)  

会话2

更新记录  
  
postgres=# update t1 set info='test1';  
UPDATE 1  

会话1

查询记录版本，已生成第2个版本。

postgres=# select ctid,xmin,xmax,* from t1;  
 ctid  |  xmin  | xmax | id | info    
-------+--------+------+----+-------  
 (0,2) | 749509 |    0 |  1 | test1  
(1 row)  

会话2

更新记录  
postgres=# update t1 set info='test2';  
UPDATE 1  

会话1

查询记录版本，已生成第3个版本。

postgres=# select ctid,xmin,xmax,* from t1;  
 ctid  |  xmin  | xmax | id | info    
-------+--------+------+----+-------  
 (0,3) | 749510 |    0 |  1 | test2  
(1 row)  

会话2

更新记录  
  
postgres=# update t1 set info='test3';  
UPDATE 1  

会话1

查询记录版本，已生成第4个版本。

postgres=# select ctid,xmin,xmax,* from t1;  
 ctid  |  xmin  | xmax | id | info    
-------+--------+------+----+-------  
 (0,4) | 749511 |    0 |  1 | test3  
(1 row)  

最老存在的事务快照之后产生的垃圾版本，不会被回收。所以可以看到一条记录在更新后一直在新增新的版本。

HOT技术

HOT 技术实际上是解决更新时索引放大(产生新的索引item)的问题，只要一笔更新中没有包含任何索引字段的更新，同时TUPLE新版本在同一个HEAP PAGE内时，索引不需要新增item。

《PostgreSQL Heap Only Tuple - HOT (降低UPDATE引入的索引写IO放大)》

垃圾回收机制

《PostgreSQL 垃圾回收代码分析 - why postgresql cann’t reclaim tuple is HEAPTUPLE_RECENTLY_DEAD》

《PostgreSQL 垃圾回收原理以及如何预防膨胀 - How to prevent object bloat in PostgreSQL》

数据库只能回收实例中现存最老的事务快照之前产生的dead tuple。

最老的事务快照可能来自：

1、最老的未结束事务

2、最老的未结束SQL

3、最老的未结束的2pc（两阶段提交事务）

4、接收慢的wal sender slot

5、有standby，并且standby开启了feedback，同时standby的事务快照信息会反馈给主库，那么这个事务快照也会被主库的垃圾回收关照。

6、开启了强制延迟回收vacuum_defer_cleanup_age

7、开启了snapshot too old。

可以回收哪些版本？取决于几个因素

1、不管是什么地方残留的事务快照信息，以最老的快照为准。凡是这个快照之后产生的垃圾版本，都无法被回收。

2、达到snapshot too old后的版本，可以被回收，不管约束1。

3、设置了延迟回收vacuum_defer_cleanup_age时，只有年龄大于设定延迟年龄的垃圾版本才会被回收。

所以，膨胀的一种原因可能来自以上使用不当或设置不当。

垃圾回收相关参数

分为全局、表级设置。

《PostgreSQL 自动vacuum(垃圾回收)与手动vacuum的参数详解》

1、HIT：垃圾回收时阶段性工作量统计。

2、COST：工作量阶段性阈值，超出后DELAY

3、delay：暂停垃圾回收的WORKER工作，防止垃圾回收时耗费大量资源影响业务。smooth化的一种手段，但是会拉长整个VACUUM的周期，也是膨胀原因之一。

4、naptime：每个数据库的垃圾统计信息扫描间隔。

5、worker：最多允许开启多少个AUTOVACUUM WORKER进程同时进行垃圾回收。如果worker很少，也可能导致回收不及时，膨胀。

6、autovacuum memory：垃圾回收时，记录垃圾版本的行号，用于对应索引指向这些垃圾TUPLE的ITEM的回收。如果内存很小，可能导致回收过程中，索引要被多次扫描。

《PostgreSQL 垃圾回收参数优化之 - maintenance_work_mem , autovacuum_work_mem》

7、freeze相关参数（导致IO增大的原因），什么时候触发冻结（扫描全表），冻结哪些记录（修改TUPLE头信息）。

自动垃圾回收相关参数

同上

无法回收垃圾引发的额外问题

由于各种原因导致垃圾无法回收，会导致表的垃圾比例可能一直高于阈值水位，在开启了autovacuum的情况下，那么autovacuum会不停唤起，去执行垃圾回收，扫描表（产生大量读IO），CPU也会升高，但是无法回收（所以几乎不产生写IO）。

HEAP PAGE复用机制，HEAP PAGE删除机制

HEAP PAGE内的dead tuple清除后，会整理PAGE，空间立即就可以被复用。

HEAP PAGE什么时候可以从磁盘删除？

在数据文件末尾，并且整个PAGE里面全部都是DEAD TUPLE时，这种PAGE可以从磁盘删除，缩小数据文件的大小。（而中间的数据块无法删除，因为TUPLE的寻址地址会随之变化，索引无法根据ctid寻址(blockid, offset)访问到原来的TUPLE。）

《PostgreSQL 12 preview - heap table末端垃圾page是否在vacuum时被回收可控 - vacuum_truncate》

《PostgreSQL 单表并行bulkload的extend file lock 冲突问题解决 - 数据块预分配》

《parallel blocking|waiting by slow BLOCK extend relation , ExclusiveLock on extension of relation》

《PostgreSQL bulk COPY load Bottleneck by extend lock waiting》

INDEX PAGE复用机制，INDEX PAGE删除机制

索引页的复用与HEAP PAGE不一样，因为索引的内容是有序结构，只有复合顺序的ITEM才能插入对应的PAGE。不像HEAP TUPLE，只要有空间就可以插入。

index page无论在任何位置，都不能从磁盘删除，索引变大以后，不可能变小，除非vacuum full。因此索引膨胀后，通常需要重建索引来缩小索引大小。

postgres=# create table t2(id int);  
CREATE TABLE  
postgres=# create index idx_t2 on t2(id);  
CREATE INDEX  
postgres=# insert into t2 select generate_series(1,1000000);  
INSERT 0 1000000  
postgres=# \dt+ t2  
                   List of relations  
 Schema | Name | Type  |  Owner   | Size  | Description   
--------+------+-------+----------+-------+-------------  
 public | t2   | table | postgres | 35 MB |   
(1 row)  
  
postgres=# \di+ idx_t2  
                        List of relations  
 Schema |  Name  | Type  |  Owner   | Table | Size  | Description   
--------+--------+-------+----------+-------+-------+-------------  
 public | idx_t2 | index | postgres | t2    | 21 MB |   
(1 row)  
  
postgres=# delete from t2 where id<>1;  
DELETE 999999  
postgres=# vacuum t2;  
VACUUM  
postgres=# \dt+ t2  
                   List of relations  
 Schema | Name | Type  |  Owner   | Size  | Description   
--------+------+-------+----------+-------+-------------  
 public | t2   | table | postgres | 40 kB |   
(1 row)  
  
postgres=# \di+ idx_t2  
                        List of relations  
 Schema |  Name  | Type  |  Owner   | Table | Size  | Description   
--------+--------+-------+----------+-------+-------+-------------  
 public | idx_t2 | index | postgres | t2    | 21 MB |   
(1 row)  