Postgres在一种极端数据分布情况下vacuum full的替代方式

2019独角兽企业重金招聘Python工程师标准>>>

前言

vacuum full是困扰PG DBA的一个重要问题。

本文章分析了一种特殊情况下，vacuum full的替代方案。

极端情况描述

创建测试数据

# 插入测试数据
postgres=# insert into t2 (i,j,k) select generate_series(1,157 * 131072),10,'qazwsxedcr';
INSERT 0 20578304
postgres=# insert into t2 (i,j,k) select generate_series(1,157 * 131072),11,'qazwsxedcr';
INSERT 0 20578304
postgres=# insert into t2 (i,j,k) select generate_series(1,157),1,'qazwsxedcr';
INSERT 0 157
postgres=# select relfilenode from pg_class where relname ='t2';
 relfilenode 
-------------
       16904
(1 row)
postgres=# 

[lchch@yfslcentos71 13211]$ ll 16904*
-rw------- 1 lchch lchch 1073741824 Aug 26 19:26 16904
-rw------- 1 lchch lchch 1073741824 Aug 26 19:27 16904.1
-rw------- 1 lchch lchch       8192 Aug 26 19:26 16904.2
-rw------- 1 lchch lchch     548864 Aug 26 19:26 16904_fsm
-rw------- 1 lchch lchch          0 Aug 26 19:24 16904_vm
[lchch@yfslcentos71 13211]$ 

# 数据删除
postgres=# delete from t2 where j = 11;
DELETE 20578304
postgres=# 

# 这时还是有2G+8K的数据
[lchch@yfslcentos71 13211]$ ll 16904*
-rw------- 1 lchch lchch 1073741824 Aug 26 19:28 16904
-rw------- 1 lchch lchch 1073741824 Aug 26 19:29 16904.1
-rw------- 1 lchch lchch       8192 Aug 26 19:28 16904.2
-rw------- 1 lchch lchch     548864 Aug 26 19:26 16904_fsm
-rw------- 1 lchch lchch          0 Aug 26 19:24 16904_vm
[lchch@yfslcentos71 13211]$ 

   此时，有用的数据只是在第一个文件和最后一个文件中（后面的数据特别少），怎么能把有用的数据挪到前面的文件，且把无效page删除呢？现行的处理方式是vacuum full。但是如果把这个场景放大，前面的有效数据是TB级别的，使用vacuum full花费巨久的时间只为了最后一个page的数据...貌似会很有问题。

对这种情况（数据量特别大，但是需要整理的数据特别少）可以有取巧的方法：

postgres=# create table temp_t2(i int,j int, k varchar);
CREATE TABLE
postgres=# insert into temp_t2 select * from t2 where j = 1;
INSERT 0 157
postgres=# delete from t2 where j = 1;
DELETE 157
postgres=# vacuum t2;
VACUUM
postgres=# insert into t2 select * from temp_t2;
INSERT 0 157
postgres=# 
postgres=# select ctid,* from t2 where j = 1;
     ctid     |  i  | j |     k      
--------------+-----+---+------------
 (131072,1)   |   1 | 1 | qazwsxedcr
 (131072,2)   |   2 | 1 | qazwsxedcr
 (131072,3)   |   3 | 1 | qazwsxedcr
 (131072,4)   |   4 | 1 | qazwsxedcr
 (131072,5)   |   5 | 1 | qazwsxedcr
 (131072,6)   |   6 | 1 | qazwsxedcr
 (131072,7)   |   7 | 1 | qazwsxedcr
 (131072,8)   |   8 | 1 | qazwsxedcr
 (131072,9)   |   9 | 1 | qazwsxedcr


# 见证奇迹的时候到了，数据移到了前面的文件中，而且后面的文件被截断。
[lchch@yfslcentos71 13211]$ ll 16904*
-rw------- 1 lchch lchch 1073741824 Aug 26 19:39 16904
-rw------- 1 lchch lchch       8192 Aug 26 19:50 16904.1
-rw------- 1 lchch lchch          0 Aug 26 19:47 16904.2
-rw------- 1 lchch lchch     286720 Aug 26 19:50 16904_fsm
-rw------- 1 lchch lchch      40960 Aug 26 19:50 16904_vm
[lchch@yfslcentos71 13211]$ 

 以上操作替代了vacuum full的功能（当然这个操作需要暂时禁止用户连接到这个表）。

方法通用性

上述举例中使用'j'属性作为'需要移动的文件'的筛选条件。

实际操作可以使用ctid最为筛选条件：

insert into temp_t2 select * from t2 where ctid > '(n,0)';

 

问题讨论

    其实不用这么极端的情况，只要在数据库的有效数据大都分布在前'半'部分，而后'半'部分数据分布极为疏松，那么就可以使用此方法替代vacuum full。然而，现在还有一个未解决的问题，那就是如何能知道数据库内部数据的分布呢？如何能知道这样的操作会不会节省时间呢？

如下链接介绍一款工具可以分析数据文件使用情况。

https://my.oschina.net/lcc1990/blog/1934261

后记

作者是内核开发，现场维护经验不足，写这篇文章只是因为看代码的时候有一点'野路子'的想法，如有不妥请指出。

转载于:https://my.oschina.net/lcc1990/blog/1934262