文档

收缩膨胀表和索引(pg_squeeze)

更新时间:

RDS PostgreSQL支持使用pg_squeeze插件,该插件提供了一种在线重新组织和清理表的功能,在不影响在线读写的前提下,减少表和索引占用的空间,提高空间利用率。

前提条件

  • 已完成如下参数配置。如何修改参数,请参见设置实例参数

    • wal_level参数设置为logical。

    • 依赖shared_preload_libraries参数预加载,需要将pg_squeeze配置到shared_preload_libraries参数值中。

  • 使用pg_squeeze插件收缩膨胀表和索引需要额外的磁盘空间,请确保空间满足要求。

    执行全表压缩需要的空闲磁盘空间约为目标表及其索引的两倍。例如,如果要压缩的表和索引的总大小为1 GB,则需要额外2 GB的磁盘空间。

  • 实例内核小版本需要在20240229及以上。查看和升级内核小版本,请参见升级内核小版本

背景信息

PostgreSQL使用MVCC(Multi-Version Concurrency Control)来允许多个事务同时读取数据库中的同一数据而不发生冲突。在此模型中,当数据被更新(UPDATE)或删除(DELETE)时,原始数据不会被直接覆盖或删除,而是会被标记为死元组(dead tuple)。这些死元组对于新事务是不可见的,但是它们仍旧占据着磁盘空间,导致了表的膨胀。

使用autovacuum等方式清理完死元组后,表膨胀并不会完全消失,因为死元组之前占用的空间并未立即被重新使用。当这些空闲空间非常多时,也会引起表膨胀。目前社区提供了vacuum full命令来回收这些空闲空间,然而这一操作会使用最高级别的互斥锁来锁定表,导致在vacuum full期间无法访问该表。

功能介绍

pg_squeeze插件通过创建表的一个压缩副本来重建表,可以去除表中未使用的磁盘空间,具有如下优势:

  • 在线清理,不阻塞读写。

  • 无需通过客户端,直接执行SQL即可操作。

应用场景

对表进行了频繁的增删改操作,导致表的膨胀率较高。

通过以下SQL语句,可以检测表的膨胀率:

CREATE EXTENSION pgstattuple; --create extension
select *, 1.0 - tuple_len::numeric / table_len as bloat from pgstattuple('your_relation');
说明
  • 表膨胀率指表中未使用空间占总空间的比例,计算方式为:1 - 活元组空间/表的总空间

  • 执行此操作会进行全表扫描。

注意事项

  • 被清理的表必须具备唯一键。

  • 执行清理时会消耗大量的IO,建议在业务低峰期运行。

创建和删除插件

  • 创建插件:

    CREATE EXTENSION pg_squeeze;
    说明

    如果需要清理多个数据库下的表,需要在每个目标数据库中创建该插件。

  • 删除插件:

    DROP EXTENSION pg_squeeze;

使用示例

清理前,需要在目标数据库中创建插件。

临时清理

例如,清理public schema下的表bar

SELECT squeeze.squeeze_table('public', 'bar');

自动检测并清理

  1. 创建自动清理任务。

    例如,在每周三和周五的22:30,自动清理public schema下的表foo

    说明
    • 创建自动清理任务的详细语法请参见pg_squeeze

    • 只能清理当前数据库下的表。

    INSERT INTO squeeze.tables (tabschema, tabname, schedule)
    VALUES ('public', 'foo', ('{30}', '{22}', NULL, NULL, '{3, 5}'));
  2. 开启自动清理任务。

    • 手动开启自动清理任务

      执行以下SQL手动开启自动清理任务,只能清理当前数据库下的表。

      SELECT squeeze.start_worker();  -- 开启

      关闭自动清理任务。

      SELECT squeeze.stop_worker();   -- 关闭
    • 自动开启自动清理任务

      在RDS控制台配置参数并重启数据库,自动开启清理任务。配置参数的详细操作请参见设置实例参数

      例如,以rds_superuser的身份(必须是高权限用户)清理数据库database1database2

      说明

      清理前,已分别在数据库database1database2中创建了pg_squeeze插件,详情请参见创建和删除插件

      squeeze.worker_autostart = 'database1 database2'
      squeeze.worker_role = rds_superuser

相关文档

更多信息请参见pg_squeeze

  • 本页导读 (1)