postgres收缩工具两种工具的使用对比

postgres收缩工具安装和使用

第一章 需要使用插件处理膨胀的原因

Postgresql通过数据多版本实现MVCC，现象是删除数据并不会真正删除数据，而是修改标识，更新是通过删除+插入的方式进行，所以在频繁更新的OLTP系统，会造成数据膨胀。

PG数据库本身有处理膨胀问题的vacuum工具，该工具有三种类型，具体如下：

vacuum 表名字	vacuum full　表名字	autovacuum
不能回收空间	可以回收空间
运行时可以正常访问数据表（共享锁）	运行时不允许访问数据表（排他锁）
执行效率高	执行效率低
作用是把表中的dead tuples进行删除标记变成可以使用的状态，并没有真正的物理删除，并且会更新统计信息和相关系统表	作用是物理删除dead tuples，并且把释放的空间重新交给操作系统。 本质时生成一个新的数据文件，然后把原来的表的live tuples 存放到新的数据文件中	只是个监控作用是用来启动自动清理进程的，autovacuum,autovacuum worker,vacuum+analyze

如果autovacuum清理速度赶不上dead元组产生速度，就会造成表膨胀。如表格所示，vacuum治标不治本，vacuum full会锁表，所以需要一种用最少的锁重新组织PG数据库中的表资源的工具，pg_repack或者pg_squeeze工具便可以实现

第二章：使用pg_repack

一 使用pg_repack的原理

1 总体思路：

新建一个文件，然后将老文件数据拷贝过来，然后进行文件切换，它不阻塞读写的秘诀就是新建文件和拷贝的过程是在线做的，在没有完成拷贝之前，原来的文件还是可以读写的，只有在切表那一瞬间有可能有影响。在线拷贝过程：源库的数据文件一直在变，所以表文件其实分为两部分，一部分是基础数据，一部分是增量数据，基础数据的拷贝就是正常的拷贝，增量数据是通过创建触发器来捕获在该表上的读写操作来实现的，待基础数据拷贝完后再将trigger捕获的增量sql进行应用，达到最终效果。

2 具体原理步骤如下：

对表执行全表repack：

• 创建一个记录表，用于记录原表中的记录的修改

• 在原表上创建一个触发器，记录插入、更新、和删除操作到日志表

• 创建一个新表，包含原表中的所有的记录

• 在新表上创建索引

• 将日志表中的变更应用到新表

• 使用system catalogs将原表和新表进行swap，包含索引和toast表

• 删除原先的表

对索引执行repack

• 使用concurrently创建新索引

• 将新的索引和老的索引进行swap

• 删除原先的索引

二 如何使用

1 安装

• 下载安装包：

  https://pgxn.org/dist/pg_repack/

• 解压缩并编译

cp pg_repack-1.4.8.zip /home/postgres

su - root

unzip pg_repack-1.4.8.zip

cd pg_repack-1.4.8

make

make install

• 创建拓展

psql

create extension pg_repack;

2 使用举例

postgres=# create table test(id int primary key,name varchar(255));
postgres=# insert into test select generate_series(1,50000000),'a';
postgres=# select * from pgstattuple('test');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 181239808 |     5000000 | 150000000 |         82.76 |                0 |              0 |                  0 |     620336 |         0.34
postgres=# \dt+ test
                       List of relations
 Schema |    Name     | Type  |  Owner   |  Size  | Description