PostgreSQL 磁盘空间的保护伞 PG_repack VS 表膨胀

PG 最近的使用中，发现这个数据库确确实实是一个无底洞，东西太多了，但学习一样东西都是通过主干和分支的方式来学习，后续的学习其实有的时候是靠自觉和运气。

今天要说的pg_repack，这个插件，如果您是第一次看到这篇文字，并且没有接触过PG，那的确可能看上去，PG 的操作没有ORACLE 或SQL SERVER 那样简单化，因为PG 的很多功能是通过插件的方式来进行的，当然这也和MYSQL 插件方式不同。

话归正题，PG 中通常会存在一些需要管理的问题如下：

删除大量记录后，从表中回收到磁盘的空闲空间 重新构建一个表来重新排序记录，并将它们压缩/打包到更少的页面。这可能让查询只从磁盘获取一个页面(或< n个页面)，而不是n个页面。换句话说，IO越少，性能越好。 从由于不正确的auto vaccum设置而导致大量膨胀的表中不能回收空闲空间。

安装 pg_repack 是并不是一件难事，正常的编译，create extensiton pg_repack ，然后在配置文件中 shared_preload_libraries = 'pg_repack'

重新启动PG 即可

下面我们就是要模拟一个表膨胀的案例，然后再用 pg_repack 来解决一些问题

1 我们在postgres 数据库中创建一张表

CREATE TABLE large_test (id serial primary key,num1 bigint, num2 double precision, num3 double precision);

2 插入测试数据

INSERT INTO large_test (num1, num2, num3)

SELECT round(random()*10), random(), random()*142

FROM generate_series(1, 2000000) s(i);

3 我们查看这个表到底在机器物理那个文件上体现

select oid,datname from pg_database ;

OK 我们确认我们的表的物理文件应该在 13287 这个文件夹里面，在这里我们通过oid2name 命令来查看到底你的这个表在哪里文件里面，

oid2name -t large_test 下图中我们可以锁定物理的表在 16455 这个文件中

这张表现在有200万的记录，大小是115MB

下面我们就开始进行一个表膨胀的操作，我们开启两个事物

1第一个事物往表里面在插入 200万的数据

2 第二个事物更新表里面的某个字段的值

我们可以看一下表的大小瞬间就从 115MB 暴涨到 345 MB

如果按照逻辑来说，其实表的大小不应该是在 230MB 左右，怎么这么夸张的到达了345MB.

其实这就的从PG 的表的结构设计来说了，（之前写过一篇文字在4个月前），主要是PG 的 undo log 其实是在糅合到表的物理设计中，每次UPDATE 其实都不会进行真正的数据修改，而是重新插入一个新的行，（这然我想起 cassandra），所以，更新了多少行，占用的数据的空间就是 *2 ,所以就造成了表膨胀，以及 vaccum 和 auto vaccum 这两个事情。（vaccum 也是写过了，大约是2个月前），所以有的时候我们就的祭出我们的神器，（注：请在非工作时间进行维护操作）PG_REPACK 工具，来收缩一下我们的膨胀过分的表，当然auto vaccum 也是可以解决的，但如果你的表膨胀的比较大，并且在非工作时间，其实一次性解决这个问题，也是一个好的办法。

我们下面就开始repack

pg_repack -d postgres --table public.large_test;

在经过了10几秒的工作后，我们查看 large_test 表的物理文件在哪里，我看可以看到，在经过repack后，物理文件的名字更改了。

我们在看看这个物理的文件多大 230 MB 对比刚才的 磁盘占用率吗，可以很清楚的知道刚才那些被废弃的行的空间已经释放给了系统。

那么问题来了，repack 到底做了什么，原理是什么，其实热 repack 的原理很简单， 和 MYSQL 的 alter table table engine=innodb是一个意思（如果你是MYSQL的 DBA 估计会很快明白）。当然如果你是 SQL SERVER 的DBA ，shinrk database 的功能 你懂得哈

这相当于重新写了一个新的文件，将原来的物理文件踢掉，重新对表进行了一次整理。

那这样的好处不光是表的占用空间变小了，收益的还有访问表的速度也会更快。最后这个命令还可以并行运行，后面加参数 J 和你的并行数。

最后如果你安装pg_repack 报了一些莫名奇怪的错误，你可以尝试安装

sudo yum -y install postgresql-static.x86_64

最后如果你想远程操作这个命令，是可以的，但你远程的机器也必须安装这个插件，不能说你本地安装，远程操作一个没有插件的PG ，那是不可以的。