PostgreSQL解决数据膨胀问题pg_repack

2023-11-20 11:24:17 浏览数 (3)

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工具包的版本。

代码语言：javascript复制
sudo apt install postgresql-12-repack

pg_repack执行前的准备

  1. 磁盘空间要预留出足够的空间，假设你重构的表占用空间是10GB，起码你要留出10GB的空间（这里还不考虑数据一直在写入磁盘空间可用越来越少的情况）
  2. 修改数据库的idle_in_transaction_session_timeout参数，如果你有很大的表需要repack，请一定要调大该参数。

idle_in_transaction_session_timeout用来控制事务执行时长，单位是ms，当事务闲置（状态为idle in transaction）时间超过该参数的设置时，会被PostgresSQL杀掉。

pg_repack执行

代码语言：javascript复制
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）。

  1. 创建一个新的数据类型
代码语言：javascript复制
-- pg_后面的数字一般就是表的oid
 
CREATE TYPE repack.pk_195075 AS (id bigint)

2. 创建一个新的表，用来记录表在重构时被重构表的增量数据，这里需要独占锁，但时间很短

代码语言：javascript复制
-- row列就是变更的行记录
 
CREATE TABLE repack.log_195075 (id bigserial PRIMARY KEY, pk repack.pk_195075, row public.表名)

3. 创建一个触发器，用来捕获repack期间表的所有的变更记录，这里需要独占锁，但时间很短，并且启动触发器

代码语言：javascript复制
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_trigger

4. 创建一个新表，用来往其中拷贝被repack表的数据，oids=false表示不为表分配oid，表所在的表空间为pg_default，only 用来表示只扫描被repack表的本身，不扫描其后代表（pg的表实现了继承）

代码语言：javascript复制
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工具用来处理大表的数据膨胀问题，由于我这里没有真正操作过，就不在这里详细描述了。

数据库 postgresql 数据 索引 重构

0 人点赞