PostgreSQL解决数据膨胀问题pg_repack
PostgreSQL解决数据膨胀问题pg_repack
Hi~朋友，关注置顶防止错过消息
pg自带工具
为了解决数据膨胀，pg自身提供了vacuum工具，vacuum分为普通vacuum和vacuum full。
普通vacuum会清理死元组，但是不会进行空间重组，磁盘上的空间不会释放，会释放死元组的空间，后续的插入会根据空闲空间管理fsm优先插入空闲空间。
vacuum full会清理释放磁盘空间，但是获取的锁级别较高，它是通过新建一个表空间，然后从老表拷贝数据到新文件中，整个过程会阻塞select。
为什么需要重构表
在我们的使用场景中，有部分表记录了大量的数据，而且在列上会有大字端，导致磁盘占用量很大，我们通过delete将部分数据进行删除，用来释放一部分磁盘空间，同时由于这些表重构时间比较长，我们不可能选择vacuum full的方式，因为这会导致我们的服务长期不可用。
如何避免重构表期间对业务的长时间影响
为了避免影响业务，pg社区开发了pgrepack 工具，该工具以extension的形式存在，用户可以通过create extensiton pg_repack命令在数据库中安装该插件。
pg_repack安装
首先你需要准备一台可以连接数据库的服务器，在服务器上安装repack工具包，这个地方安装的repack工具包需要和数据库的插件对应，如果对应不正确，在执行pg_repack命令的时候会报错，这个时候大家就需要调整repack工具包的版本。
sudo apt install postgresql-12-repackpg_repack执行前的准备
- 磁盘空间要预留出足够的空间，假设你重构的表占用空间是10GB，起码你要留出10GB的空间（这里还不考虑数据一直在写入磁盘空间可用越来越少的情况）
- 修改数据库的idle_in_transaction_session_timeout参数，如果你有很大的表需要repack，请一定要调大该参数。
idle_in_transaction_session_timeout用来控制事务执行时长，单位是ms，当事务闲置（状态为idle in transaction）时间超过该参数的设置时，会被PostgresSQL杀掉。
pg_repack执行
pg_repack -h 数据库地址 -p 数据库端口号 --jobs 2--table 表名 --no-order -d 数据库名称 -U 数据库用户 -k上面的命令是我在实际执行pg_repack的时候操作，具体参数的含义大家可以参考官方文档。
pg_repack的执行过程
pg_repack插件会在库里面创建repack的schema，里面有两张表：
- tables：记录创建trigger以及一些要执行的SQL语句
- primary_keys：里面包含indrelid和indexrelid，分别代表表的oid和主键或者唯一索引的 oid
在这里可以看出，被repack的表至少存在一个主键或者唯一索引。
在tables里面的SQL顺序代表pg_repack所要执行的SQL的顺序，整个repack过程只有极少阶段需要独占锁，其他步骤只需要在原始表上增加一个ACCESS SHARE锁，DML可以正常执行，但DDL不可以执行（除了VACUUM和ANALYZE）。
- 创建一个新的数据类型
-- pg_后面的数字一般就是表的oid
CREATE TYPE repack.pk_195075 AS (id bigint)2. 创建一个新的表，用来记录表在重构时被重构表的增量数据，这里需要独占锁，但时间很短
-- row列就是变更的行记录
CREATE TABLE repack.log_195075 (id bigserial PRIMARY KEY, pk repack.pk_195075, row public.表名)3. 创建一个触发器，用来捕获repack期间表的所有的变更记录，这里需要独占锁，但时间很短，并且启动触发器
CREATE TRIGGER repack_trigger
AFTER INSERT OR DELETE OR UPDATE
ON public.表名
FOR EACH ROW
EXECUTE PROCEDURE repack.repack_trigger(
'INSERT INTO repack.log_195075(pk, row) VALUES( CASE WHEN
1
I
S
N
U
L
L
T
H
E
N
N
U
L
L
E
L
S
E
(
R
O
W
(
1.id)::repack.pk_195075) END,
--启动触发器
ALTER TABLE public.表名 ENABLE ALWAYS TRIGGER repack_trigger4. 创建一个新表，用来往其中拷贝被repack表的数据，oids=false表示不为表分配oid，表所在的表空间为pg_default，only 用来表示只扫描被repack表的本身，不扫描其后代表（pg的表实现了继承）
CREATE TABLE repack.table_195075 WITH (oids = false)
TABLESPACE pg_default
AS
SELECT id,
xxx,
xxx,
xxx,
create_time
FROM ONLY public.表名
INSERT INTO repack.table_195075
SELECT id,
xxx,
xxx,
xxx,
create_time
FROM ONLY public.表名5. 在这张新表上建立索引，当索引建立完毕以后会将repack.log_195075表中记录的日志变更应用到新表上
6. 使用系统目录交换表，包括索引和toast表，这里需要一个独占锁，但时间时间较短
7. 删除原始表
其他的大表重构方案
除了pgrepack，PostgreSQL社区还有另一款pgsqueeze工具用来处理大表的数据膨胀问题，由于我这里没有真正操作过，就不在这里详细描述了。