PostgreSQL的pg_repack的使用

PostgreSQL的pg_repack的使用

目录

• PostgreSQL的pg_repack的使用
  • 概述
  • 安装配置
    • 依赖安装
    • 软件安装编译
  • 原理介绍
    • 关于锁说明：
    • 参数详解
    • Repack普通表和分区表分区
    • 限制
    • 案例
    • Repack分区表和继承表
    • Repack索引
    • 常见问题
  • pg_repack在新版本的pg中的使用场景
    • 1. 原生功能的替代性进步
    • 原生仍然缺失的能力（pg_repack 的优势域）
    • 2. 具体场景对比分析
      • 场景 A：常规表膨胀治理（GB 级）
      • 场景 B：TB 级大表在线瘦身
      • 场景 C：带归档的数据清理（如删除 3 年前数据并收缩表）
    • 新功能和pg_repack的对比
    • 结论
      • 现状：为什么现在仍有必要使用？
      • 未来：面临的挑战与替代品

概述

pg_repack插件对表空间进行重新“包装”，回收碎片空间，有效解决因对表大量更新、删除等操作引起的空间膨胀问题。pg_repack获取排它锁的时间较短，多数时间不阻塞读写，相比CLUSTER或VACUUM FULL操作更加轻量化。

安装配置

依赖安装

yum install lz4-devel

软件安装编译

wget https://api.pgxn.org/dist/pg_repack/1.5.3/pg_repack-1.5.3.zip
unzip pg_repack-1.5.3.zip
export PG_CONFIG=/data/postgresql/pg16/bin/pg_config
make
make install 

安装扩展
psql
CREATE EXTENSION pg_repack;

原理介绍

pg_repack插件支持对全表和索引进行repack操作。
对全表进行repack的实现原理如下：

- 1.创建日志表，记录repack期间对原表的变更。
- 2.在原表上创建触发器，将原表的INSERT、UPDATE和DELETE操作记录到日志表中。
- 3.创建原表结构相同的新表并将原表数据导入其中。
- 4.在新表中创建与原表相同的索引。
- 5.将日志表里的变更（即repack期间表上产生的增量数据）应用到新表。
- 6.在系统catalog交换新旧表。
- 7.删除旧表。

关于锁说明：

pg_repack会在第1、2、6、7步短暂持有原表的排它锁并阻塞读写。其余步骤pg_repack只需要持有原表的ACCESS SHARE锁，不阻塞对原表的INSERT、UPDATE和DELETE操作，但会阻塞DDL操作。
对索引进行repack的实现原理如下：

• 1.以CREATE INDEX CONCURRENTLY方式创建新索引。
• 2.在系统catalog交换新旧索引（需持有排它锁，短暂阻塞读写）。
• 3.以DROP INDEX CONCURRENTLY的方式删除旧索引。

说明：
pg_repack效果等同于REINDEX CONCURRENTLY，但是比REINDEX CONCURRENTLY更为复杂，如果使用REINDEX CONCURRENTLY，只需要一步就能完成。pg_repack支持该能力的原因是老版本的PostgreSQL不支持REINDEX CONCURRENTLY，从而只能借助pg_repack来实现。

参数详解

通用选项
-a, --all: 重组所有数据库。
-t, --table=TABLE: 仅重组特定表。
-I, --parent-table=TABLE: 重组特定父表及其继承者。
-c, --schema=SCHEMA: 仅重组特定模式中的表。
-s, --tablespace=TBLSPC: 将重组后的表移动到新的表空间。
-S, --moveidx: 将重组后的索引也移动到新的表空间。
-o, --order-by=COLUMNS: 按指定列排序而不是按聚簇键排序。
-n, --no-order: 执行 VACUUM FULL 而不是 CLUSTER。
-N, --dry-run: 显示将要重组的内容，但不执行实际操作。
-j, --jobs=NUM: 为每个表使用指定数量的并行任务。
-i, --index=INDEX仅移动指定的索引。
-x, --only-indexes: 仅移动指定表的索引。
-T, --wait-timeout=SECS: 在冲突时取消其他后端的超时时间。
-D, --no-kill-backend: 超时时不杀死其他后端。
-Z, --no-analyze: 结束时不执行 ANALYZE。
-k, --no-superuser-check: 在客户端跳过超级用户检查。
-C, --exclude-extension: 不重组属于特定扩展的表。
--error-on-invalid-index: 当发现无效索引时不进行重组。
--apply-count: 在回放期间每次事务应用的元组数。
--switch-threshold: 当剩余的元组数达到该阈值时切换表。
连接选项
-d, --dbname=DBNAME: 要连接的数据库名称。
-h, --host=HOSTNAME: 数据库服务器主机或套接字目录。
-p, --port=PORT: 数据库服务器端口。
-U, --username=USERNAME: 连接用户名称。
-w, --no-password: 从不提示输入密码。
-W, --password: 强制提示输入密码。
通用选项
-e, --echo: 回显查询。
-E, --elevel=LEVEL: 设置输出消息级别。
--help: 显示帮助信息，然后退出。
--version: 显示版本信息，然后退出。

Repack普通表和分区表分区

pg_repack支持对普通表或者分区表的某个分区进行repack，其作用类似于CLUSTER或VACUUM FULL操作，清理表中多余的空闲空间，同时重建表上的索引，适用于表空间膨胀的场景。

限制

- repack表必须有主键或唯一索引。
- 不支持对临时表进行repack操作。
- 不支持对带有Global Index的分区进行repack操作。

案例

通过--table参数指定表名，默认情况下效果等同于CLUSTER，repack过程中对之前执行过CLUSTER操作的列进行排序：
/opt/pg_repack/bin/pg_repack -U postgres -h 127.0.0.1 -p 5432 -W -d postgres --no-superuser-check --echo --table public.saas3

如果希望对指定的列进行排序，可以使用--order-by参数来指定列名：
pg_repack -U postgres -h 127.0.0.1 -p 5432 -W -d postgres --order-by name --no-superuser-check --echo --table public.saas3
postgres=# select * from saas3;
 id |   name    |   qq   
----+-----------+--------
  2 | New Test2 | 
  4 | New Test4 | 
  1 | Test1     | 
  3 | Test3     | 
  5 | Test3     | qq1223
(5 rows)
pg_repack -U postgres -h 127.0.0.1 -p 5432 -W -d postgres --order-by id --no-superuser-check --echo --table public.saas3
postgres=# select * from saas3;
 id |   name    |   qq   
----+-----------+--------
  1 | Test1     | 
  2 | New Test2 | 
  3 | Test3     | 
  4 | New Test4 | 
  5 | Test3     | qq1223
(5 rows)

如果不希望进行排序，即希望pg_repack的效果等同于VACUUM FULL，可以使用--no-order参数：
pg_repack -U postgres -h 127.0.0.1 -p 5432 -W -d postgres --no-order --no-superuser-check --echo --table public.saas3

如果数据库集群的CPU和I/O资源充裕，可以使用--jobs参数加速repack操作，它会启动多个进程并发重建索引，适用于表上有多个索引的场景：
pg_repack -U postgres -h 127.0.0.1 -p 5432 -W -d postgres --no-order --no-superuser-check --echo --jobs 3 --table public.saas3

锁冲突时等待最多 30 秒。如果在这 30 秒内锁仍未释放，pg_repack 不会终止其他后端进程，而是放弃重组操作并返回错误
pg_repack -U postgres -h 127.0.0.1 -p 5432 -W -d postgres --no-superuser-check --echo --table public.saas3 --wait-timeout 30 --no-kill-backend

结束时不执行 ANALYZE 操作
pg_repack -U postgres -h 127.0.0.1 -p 5432 -W -d postgres --no-order --no-superuser-check --echo --jobs 3 --parent-table public.saas3 --no-analyze

重组所有数据库
pg_repack -U postgres -h 127.0.0.1 -p 5432 -W -d postgres --no-order --no-superuser-check --echo --jobs 3 --all

Repack分区表和继承表

pg_repack支持对分区表（包括声明式分区表和继承式分区表）进行操作，它会自动找到父表的所有分区，并对每个分区依次进行repack操作。适用于分区表的所有分区都存在空间膨胀的场景。

语法说明二
通过--parent-table参数指定分区表的表名：

pg_repack -U postgres -h 127.0.0.1 -p 5432 -W -d postgres --no-order --no-superuser-check --echo --jobs 3 --parent-table public.saas1

说明
除了--parent-table参数以外，分区表的其他参数用法与普通表基本相同。
如果只是单个分区存在空间膨胀，则无需对整个分区表进行repack，使用语法说明一中（--table参数）对单个分区进行repack操作即可。
不支持对带有Global Index的分区表进行repack操作。

Repack索引

pg_repack支持仅对索引进行repack操作，它的作用是重建索引，清理索引中的空闲空间，适用于索引空间膨胀的场景。
说明
如果索引空闲空间过多，推荐使用REINDEX CONCURRENTLY进行在线索引重建，无需使用pg_repack。pg_repack支持该能力的原因是老版本的PostgreSQL不支持REINDEX CONCURRENTLY，从而只能借助pg_repack来实现。
由于pg_repack社区的特性，暂不支持对声明式分区表进行repack索引的操作，同样可以使用REINDEX CONCURRENTLY来代替。
语法说明三
使用--index参数指定需要repack的索引名(saas3_pkey)：

常见问题

Dry Run
正式执行pg_repack之前建议使用--dry-run选项运行一次，该选项不操作表中的数据，仅验证命令是否合法、流程是否可以跑通。待命令验证成功后，再去掉该选项正式运行pg_repack。

pg_repack -U postgres -h 127.0.0.1 -p 5432 -W -d postgres --no-order --no-superuser-check --echo --jobs 3 --parent-table public.saas3 --dry-run

权限问题

• 必须使用高权限账号运行pg_repack，不能以普通账号身份运行，否则会报错：must be polar_superuser or superuser to use xx function。
• 如果遇到You must be a superuser to use pg_repack报错，则需要在pg_repack命令中增加--no-superuser-check选项来绕过超级用户检查。

残留对象清理
如果pg_repack在执行过程中异常退出，则repack失败，被repack的表上可能残留了repack过程中创建的对象，需要及时清理，否则可能影响表的使用：

• 被repack的表上可能残留repack_trigger触发器，需要使用DROP TRIGGER命令删除。
• 被repack的表上可能残留临时索引index_，需要使用DROP INDEX CONCURRENTLY命令删除。
• repack模式下残留临时表repack_与日志表log_，需要使用DROP TABLE命令删除。
• repack模式下残留新的类型pk_，需要使用DROP TYPE命令删除。

pg_repack在新版本的pg中的使用场景

pg_repack 仍然有其不可替代的价值，但必要性确实在下降，特别是在 PostgreSQL 14+ 版本中。它的生存空间正在从"必需工具"转变为"特定场景优化工具"。

1. 原生功能的替代性进步

PostgreSQL 原生已具备的能力

PostgreSQL 版本	功能	对 pg_repack 的影响
PG 12	REINDEX CONCURRENTLY	替代 pg_repack 的索引重建场景
PG 12	VACUUM INDEX_CLEANUP	减少索引膨胀
PG 14	VACUUM (PARALLEL)	加速大规模 vacuum
PG 14	VACUUM (SKIP_LOCKED)	避免锁冲突
PG 14	REINDEX 支持分区表	分区表维护更便捷
PG 14	VACUUM FULL 改进（更快、锁粒度优化）	部分替代 pg_repack 的表重建

原生仍然缺失的能力（pg_repack 的优势域）

┌─────────────────────────────────────────────────────────────────┐
│  pg_repack 独有的核心能力                                        │
├─────────────────────────────────────────────────────────────────┤
│  1. 在线表重组（非阻塞 DDL）                                      │
│     - 无需 ACCESS EXCLUSIVE 锁完成表重写                          │
│     - 业务零中断的存储优化                                        │
│                                                                 │
│  2. 处理被阻断的 VACUUM FULL                                      │
│     - 长事务持有行锁时，原生 VACUUM FULL 无法执行                 │
│     - pg_repack 通过逻辑复制方式绕过                               │
│                                                                 │
│  3. 细粒度控制                                                    │
│     - 按条件重组（WHERE 子句）                                    │
│     - 部分数据归档 + 表收缩一体操作                               │
│                                                                 │
│  4. 逻辑复制友好                                                  │
│     - 不改变 relfilenode，避免复制槽失效                          │
│     - 物理复制无感知                                              │
│                                                                 │
│  5. 索引同时重建                                                  │
│     - 表 + 所有索引一次性在线重建                                  │
└─────────────────────────────────────────────────────────────────┘

2. 具体场景对比分析

场景 A：常规表膨胀治理（GB 级）

-- PostgreSQL 14+ 推荐做法
VACUUM (VERBOSE, ANALYZE, PARALLEL 4) large_table;
-- 若无效，且可接受短暂锁表（秒级）：
VACUUM FULL large_table;  -- PG14+ 已大幅提速

-- pg_repack 仅在以下情况必需：
-- - 表持续高并发写入，无法承受 VACUUM FULL 的锁
-- - 表大小 TB 级，VACUUM FULL 时间不可接受

结论：pg_repack 非必需，原生方案优先。

场景 B：TB 级大表在线瘦身

-- 原生方案困境
VACUUM FULL tb_table;  -- 锁表时间 = 小时级，业务不可接受

-- pg_repack 方案
pg_repack -d dbname -t big_table --jobs=4
-- 锁表时间 = 毫秒级（最后的 relfilenode swap）

结论：pg_repack 仍然必需

场景 C：带归档的数据清理（如删除 3 年前数据并收缩表）

-- pg_repack 的独特价值
pg_repack -d dbname -t events_table \
  --where="created_at < '2021-01-01'" \
  --no-order \
  --tablespace=archive_ts

-- 等效原生操作需要：
-- 1. CREATE TABLE new_table AS SELECT * FROM events_table WHERE ...;
-- 2. 重建所有索引、约束、触发器、权限
-- 3. 逻辑复制槽重新同步（若使用流复制）
-- 4. 重命名表（ACCESS EXCLUSIVE LOCK）
-- 5. 旧表 DROP

结论：pg_repack 大幅简化操作，仍有显著价值

新功能和pg_repack的对比

特性	pg_repack	pg_squeeze	内置 REPACK CONCURRENTLY
核心功能	在线清理膨胀，可重建表、索引、物理排序。	功能相似，可清理空间、按索引排序。	目标是集成在线表重组功能。
实现方式	触发器（记录并发变更）。	逻辑解码（更现代、低负载）。	基于pg_squeeze代码，采用逻辑解码。
主要优势	成熟稳定，特性丰富（如仅重建索引、移动表空间）。	完全服务端，易于自动化，对业务影响更小。	未来作为原生命令，无需安装扩展。
主要局限	触发器会带来额外负载；需要主键或唯一索引。	需要 wal_level = logical。	仍在开发中，尚未发布。

结论

现状：为什么现在仍有必要使用？

• 填补核心功能空白：PostgreSQL官方目前仍缺少一个在线（不长时间阻塞读写） 执行彻底表重组（类似 VACUUM FULL 或 CLUSTER 效果）的命令。pg_repack通过触发器等技术填补了这一关键空缺，允许你在不长时间阻塞读写的情况下重组表。
• 成熟性与兼容性：pg_repack经过长时间的生产环境检验，在需要仅重建索引、将表移动到新表空间等特定操作时，它提供了更精细的控制。
• 功能全面：除了整理表，它还能恢复聚集索引的物理顺序。对于要求严格物理存储顺序的场景（如基于范围查询的表），这很重要。

未来：面临的挑战与替代品

• 即将到来的官方竞争者：PostgreSQL社区已正式讨论并提出了一个名为 REPACK CONCURRENTLY 的内置命令。该提案明确提到，其目标就是提供原生的在线表重组功能，以减少对pg_repack这类第三方工具的依赖。

• 现代替代方案已出现：pg_squeeze 扩展作为更现代的设计出现，其开发者明确表示其目标是“尝试替换 pg_repack 扩展”。它采用逻辑解码（Logical Decoding）捕获变更，而非触发器，对数据库负载影响更小，且更易于自动化。一位PostgreSQL核心开发者曾公开表示更喜欢pg_squeeze，认为它“更现代、更值得信赖”。