资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

立即前往
首页/文章专栏/PostgreSQL在线将普通表转换为分区表插件之pg_rewrite

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

IT那活儿 / 2782人阅读
PostgreSQL在线将普通表转换为分区表插件之pg_rewrite
点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

分区的原因

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

当前版本并不能对单表或者单个分区并行进行垃圾回收，包括单表的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使用是有限制的：

  • 不支持外部表分区。
  • 非分区表一定要有PK。
  • 分区表建议约束和非分区表保持一致, 例如not null,default value 等约束。

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()。它可以将非分区表转换为分区表。

pg_rewrite用法测试案例

1. 创建普通表及结构相同的分区表
2. 普通表插入测试数据
3. 运行 partition_table() 函数将普通表的数据复制至分区表，并将普通表的表名修改成自定义表名用于备份。并将分表区的表名修改成与原普通表一致的表名。

pg_rewrite相关的变量

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，这意味着最后阶段可以根据需要花费尽可能多的时间。


本文作者：魏 斌（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

GPU云服务器 云服务器 js将图片转换为数据 将公网ip转换为eip postgresql 表空间 postgresql表空间不足

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129240.html

相关文章

  • 大佬为你揭秘微信支付的系统架构，你想知道的都在这里了

    摘要：年之前，微信支付业务快速发展，需要一款数据库能够安全高效的支撑微信支付商户系统核心业务，这个重任落在了腾讯数据库团队自研上。由于是用于微信支付的核心数据库，腾讯被定位为安全高效，稳定，可靠的数据库集群。 欢迎大家前往腾讯云+社区，获取更多腾讯海量技术实践干货哦~ 本文由李跃森发表于云+社区专栏李跃森，腾讯云PostgreSQL首席架构师，腾讯数据库团队架构师，负责微信支付商户系统核心数...

    Terry_Tai 评论0 收藏0

  • PostgreSQL 自动分区分表维护管理插件 pathman 基础使用

    摘要：使用数据库会自动的根据从某几个片中读取数据。更加详细的请参考德哥文章 官方地址：https://github.com/postgrespr...关于pathman的原理和优化问题，请移步至https://yq.aliyun.com/article... 检查环境变量如果直接执行psql命令提示command not found则执行下面的命令设置环境变量 root@host# PA...

    MASAILA 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<