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

大妮哟

于 2024-08-08 14:20:15 发布

阅读量1.2k

点赞数 19

CC 4.0 BY-SA版权

文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/weixin_44847119/article/details/141025814

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 
--------+-------------+-------+----------+--------+-------------
 public | test