收缩膨胀表和索引（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');

注意事项

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

• 执行清理时会消耗大量的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的身份（必须是高权限用户）清理数据库database1和database2：

     说明

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

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

相关文档

更多信息请参见pg_squeeze。