前言

光头强及熊大、熊二的StrongBear公司，客户使用PG的体量非常大，尤其是在云上，涉及SAP BTP的公有云，还有一些是其它厂商提供的PG RDS。

PostgreSQL中数据库管理与维护的相关工作中，重要的一项就是要经常对一些膨胀的大表进行空间回收。尤其是云上，存储空间都是要计算成本的。

于是，这项任务就交给熊二去负责了，熊二是公司元老，他想了想，就把工作交给新来的员工小强（与光头强同名，但不同姓）。小强是那种很随性、但也很努力的小伙子，立马接过来。算起来，熊二也算是他的半个老师。

实作

1、前期准备

SAP BTP上大都要通过cf ssh tunnel建立到目标数据库的通道。示例：

cf ssh service-app -L 
8522:postgres-<global_id>.domain:8522 -T -N

这里头，service-app就是一个目标app， 它自身建立了与postgres-这个DB实例的绑定。这样，在本地，你就可以通过localhost:8522，使用该绑定实例需要的数据库用户与密码，建立到DB的连接了。原理很简单。几乎所有的云平台都提供了类似的机制。

但是，这个PG实例提供的用户名，它的role只是dbo，它是普通的role，只对DB下边有表的CRUD权限，但是没有monitor、replicate相关权限。SAP BTP提供了相关的rest API,  可以创建一个具备monitor权限的role的用户：monitoring_admin，只有那个用户，才能有效执行：pgcompatible以及pg_repack的相关操作。这是小强在仔细阅读相关文档的情况下慢慢搞明白的。

在前期，他使用默认提供的数据库用户，做了几次实验全都失败。

为了对比性能，小强准备了下边的表和数据：

create table test(id int primary key, col2 text);
insert into test select n, md5(random()::text) 
    || n from generate_series(1, 500000) as n;

select pg_size_pretty(pg_total_relation_size('test'));
pg_size_pretty|
--------------+
47 MB         |

2、使用pgcompacttable

下载：https://github.com/dataegret/pgcompacttable/
CentOS下安装准备：yum install perl-Time-HiRes perl-DBI perl-DBD-Pg

进入：
[03:58:24-postgres@sean-rh2:/iihero/plugins/pgcompacttable/bin]$ ls
pgcompacttable

-- pgcompacttable 依赖扩展：pgstattuple，装一下
create extension pgstattuple;

delete from test where id between 200001 and 400000;
select pg_size_pretty(pg_total_relation_size('test'));
pg_size_pretty|
--------------+
47 MB         |

执行一下回收，看看花多长时间：

[03:58:25-postgres@sean-rh2:/iihero/plugins/pgcompacttable/bin]$ ./pgcompacttable -h localhost -p 8522 -n public -t test -U <admin user> -W <password> -d Hhjjkfldasjk
[Sat May 11 04:01:58 2024] (Hhjjkfldasjk) Connecting to database
[Sat May 11 04:01:58 2024] (Hhjjkfldasjk) Cannot set ionice 3 for the process. It is recommended to set ionice -c 3 for pgcompacttable. Error: ionice: ioprio_set failed: No such process
[Sat May 11 04:01:58 2024] (Hhjjkfldasjk) Postgres backend pid: 30683
[Sat May 11 04:01:58 2024] (Hhjjkfldasjk) It is recommended to set ionice -c 3 for pgcompacttable: ionice -c 3 -p 30683
[Sat May 11 04:01:58 2024] (Hhjjkfldasjk) Handling tables. Attempt 1
[Sat May 11 04:01:59 2024] (Hhjjkfldasjk:public.test) Statistics: 4672 pages (6055 pages including toasts and indexes), it is expected that ~40.090% (1872 pages) can be compacted with the estimated space saving being 14.632MB.
[Sat May 11 04:02:59 2024] (Hhjjkfldasjk:public.test) Progress: 49%,  930 pages completed.
[Sat May 11 04:03:59 2024] (Hhjjkfldasjk:public.test) Reindex: public.test_pkey, initial size 1374 pages(10.734MB), has been reduced by 39% (4.289MB), duration 0 seconds.
[Sat May 11 04:03:59 2024] (Hhjjkfldasjk:public.test) Processing results: 2804 pages left (3634 pages including toasts and indexes), size reduced by 14.594MB (18.891MB including toasts and indexes) in total.
[Sat May 11 04:03:59 2024] (Hhjjkfldasjk) Processing complete.
[Sat May 11 04:03:59 2024] (Hhjjkfldasjk) Processing results: size reduced by 14.594MB (18.891MB including toasts and indexes) in total.
[Sat May 11 04:03:59 2024] (Hhjjkfldasjk) Disconnecting from database
[Sat May 11 04:03:59 2024] Processing complete: 1 retries to process has been done
[Sat May 11 04:03:59 2024] Processing results: size reduced by 14.594MB (18.891MB including toasts and indexes) in total, 14.594MB (18.891MB) Hhjjkfldasjk.

总计，大概2分1秒。最后表的空间大小为：

select pg_size_pretty(pg_total_relation_size('test'));
pg_size_pretty|
--------------+
28 MB         |

47 - 28 = 19MB （差不多加收了19MB)

pgcompacttable的特点是，它不需要额外的存储空间来过渡，而是通过内部捣腾记录行的顺序，利用末尾的空页可以被vacuum来自然回收的特点进行回收。功能上来讲还是蛮不错的。

3、pg_repack

小强一鼓作气，接着又开始捣腾pg_repack,  SAP BTP上的PG,  安装pg_repack，也是要通过monitor_admin用户来安装的。当然，它省去了你帮它编译安装的步骤，编译那一步都是它来控制的。比如，这里安完以后：

create extension pg_repack;
Updated Rows    0
Query    create extension pg_repack
Start time    Sat May 11 12:10:54 CST 2024
Finish time    Sat May 11 12:10:54 CST 2024

select * from pg_catalog.pg_extension where extname ~ 'repack'
oid    |extname  |extowner|extnamespace|extrelocatable|extversion|extconfig|extcondition|
-------+---------+--------+------------+--------------+----------+---------+------------+
3744114|pg_repack|      10|        2200|false         |1.4.7     |NULL     |NULL        |

得知道安装完以后的版本，这里是1.4.7.  pg_repack跟pgcompacttable不一样，它除了server端要安装扩展以外，需要客户端机器同时有一个二进制程序pg_repack。pgcompacttable就只是一个客户端程序，在server端，只需要你安装好了pgstattuple扩展就行了。

我们再看看pg_repack的用法：

-- 表数据重新来一遍：
truncate test;
insert into test select n, md5(random()::text) 
    || n from generate_series(1, 500000) as n;
select pg_size_pretty(pg_total_relation_size('test'));
pg_size_pretty|
--------------+
47 MB         |

delete from test where id between 200001 and 400000;
  Updated Rows    200000
  Query    delete from test where id between 200001 and 400000
  Start time    Sat May 11 12:15:26 CST 2024
  Finish time    Sat May 11 12:15:26 CST 2024
select pg_size_pretty(pg_total_relation_size('test'));
pg_size_pretty|
--------------+
47 MB         |

删完200000条，仍然是47MB。

然后看看pg_repack的操作，我们要下载编译一个相同版本的1.4.7的pg_repack.

https://github.com/reorg/pg_repack/releases/tag/ver_1.4.7

然后编译，生成二进制， 解压，直接make即可。

再看运行过程：

[04:20:04-postgres@sean-rh2:/iihero/plugins/pg_repack-ver_1.4.7/bin]$ ./pg_repack -h localhost -p 8522 -U <admin user> -W  -d Hhjjkfldasjk -t public.test -j 2  -k -D
Password: 
NOTICE: Setting up workers.conns
INFO: repacking table "public.test"
[04:20:33-postgres@sean-rh2:/iihero/plugins/pg_repack-ver_1.4.7/bin]$ 

我去：总共花不到30秒就完成了。
select pg_size_pretty(pg_total_relation_size('test'));
pg_size_pretty|
--------------+
28 MB         |

这里只用到了 -j 2，两个并发。实际使用过程中，可能用到更大的值。

调查完以后，熊二前来进行“复盘”总结。SAP BTP下的几个hyperscaler PG，都推荐使用pg_repack，看来是有道理的。只需要额外的表的存储空间，就可以快速收回膨胀的空间。当最后别忘了：drop extension pg_repack 才算是真正的彻底回收。

总结：

相对来说，pgcompacttable更简单，服务器端不需要配置啥，只需要内置的插件：pgstattuple安装上就行。也不需要多少额外的存储空间，坏处就是慢一些，或者说慢很多。但是这个操作本身对业务冲击并不大，因此，大多数情况下也可以接受。当然, pg_repack还有一个限制条件，就是表本身必须有主键或者非NULL值的unqiue键。

正如它的readme里声称：

• does not requires lots of free space （不需要额外的大量存储空间）

• tables are processed in-place （就地处理）

• indexes are rebuild one by one, from smallest to largest therefore maximum space required is the size of the largest index （索引一个个的重建，从最小到最大的来建，因此最大空间需求也就是最大的索引所需要的空间）

• tables are processed with adaptive delays to prevent heavy IO and replication lag spikes (see --delay-ratio option)
  可以通过灵活适配的延迟来阻止大量IO和大量复制滞后。（--delay-ratio参数来控制）

对小强来说，实际生产环境下，需要先调查下数据库的剩余可用空间够不够目标表的总大小，如果不够的话，就不能草率使用pg_repack了。然后还有一个问题，就是通过ssh登录到客户机，得确保这个连接是畅通的，否则，只能以后台进程的方式运行。否则，网络一旦中断，那相关的pg_repack或者pgcompacttable进程可能就死掉了。留下的可能是一堆垃圾待处理。

总体来说，这玩意儿真心够用。就是在云环境下，有些缚手缚脚，得慢慢习惯。想速度快点儿，不在意存储空莘，用pg_repack， 不太在意速度，特别在意存储空间的额外开销，那就用pgcompacttable。