PostgreSQL将普通表转换为分区表插件之pg_rewrite

胖达李七七

已于 2023-08-16 14:37:17 修改

阅读量881

点赞数

分类专栏： postgresql 文章标签： postgresql 数据库

于 2023-08-16 14:33:01 首次发布

本文链接：https://blog.youkuaiyun.com/forrest_hu/article/details/132318960

版权

30 篇文章

订阅专栏

说到分区，我们首先想到的是表为啥要分区，分区有什么好处？

当前版本并不能对单表或者单个分区并行进行垃圾回收，包括单表的freeze也只能是单进程进行冻结和回收。但是对于多表或者多个分区就可以并行执行上述操作，所以在单表数据量过大会发生什么？

1. 当进行autovacuum垃圾回收时，如果该表的DML操作非常频繁的话，可能导致垃圾回收不过来，进而导致表迅速膨胀，占用过多的空间从而导致故障。
2. 单表过大会导致单表的freeze时间过长，可能会导致xid耗尽，此时就只能停库操作降低年龄之后才能正常运行。在PG 9.6以前的版本，大表的freeze带来的IOPS影响较大，体现在数据文件读写、WAL日志大量产生。但9.6及以后版本freeze有大幅改进，并不会产生大量的WAL日志了。
3. 表没分区的话，当要清理历史数据时就只能delete了，大批量的delete会产生大量的wal日志，从而导致从库延迟。并且大批量数据的delete事务时间过长，可能会导致表膨胀发生。如果大表分区了，我们可以通过drop历史分区或者truncate历史分区的方式清理历史数据，不用担心大量wal日志的产生，而且执行时间很快。
4. 单表的只能位于单个表空间，对应到单个目录，并不能像分区表那样将各个分区放到不同的表空间，不同的目录上去。这样就可能导致某个目录对应的盘IO很繁忙，但其他目录就很空闲的情况。IO不能分散从而导致性能问题的发生。

单表的逻辑备份恢复无法并行执行，数据全量同步时会很慢，并且异常中断后又需要重新开始这个大表的同步。

当然，pg_rewrite使用是有限制的：

pg_rewrite是开源的，需要pg 13或更高版本才能安装。

下载路径如下：

https://github.com/cybertec-postgresql/pg_rewrite

1. 设置PG_CONFIG环境变量，安装时，我们必须确保路径中的pg_config 版本正确。

2. 编译安装：

3. 修改postgresql.conf 参数文件并重启生效。

wal_level = logical
max_replication_slots = 1             #或者在当前值上加1。
shared_preload_libraries = 'pg_rewrite'    #将pg_rewrite添加到现有库中

4. 使用超级用户创建扩展 pg_rewrite，目前该扩展只包含一个函数partition_table()。它可以将非分区表转换为分区表。

1. 创建普通表及结构相同的分区表

2. 普通表插入测试数据

3. 运行 partition_table() 函数将普通表的数据复制至分区表，并将普通表的表名修改成自定义表名用于备份。并将分表区的表名修改成与原普通表一致的表名。

1. rewrite.check_constraints

在开始复制数据之前，它会检查目标表是否与源表具有相同的约束，如果发现差异则抛出错误。如果目标表上缺少约束，一旦处理完成，违反源表约束的数据将被允许出现在目标表中。甚至对目标表的额外约束也是一个问题，因为扩展只假设它复制的所有数据确实满足源表上的约束，但是它不会根据目标表上的额外约束来验证它们。

默认值是true，通过将 rewrite.check_constraints 设置为 false，用户可以关闭约束检查。但是不建议这么做，最好是提前检查目标表与源表的结构是否一致。

2. rewrite.max_xlock_time

尽管大多数时候正在处理的表可用于其他事务的读写操作，但需要排他锁来完成处理。如果过多地阻止对表的访问，请考虑设置“rewrite.max_xlock_time”参数。

例如：

set rewrite.max_xlock_time to 100;

表示排他锁的持有时间不应超过 0.1 秒（100 毫秒）。如果最后阶段需要更多时间，则特定函数会释放排他锁，处理中间其他事务提交的更改并再次尝试最后阶段。多次超过锁定时间会报错。如果发生这种情况，您应该增加设置或稍后在写入活动较低时尝试处理有问题的表。

默认值为 0，这意味着最后阶段可以根据需要花费尽可能多的时间。