pg_repack 概述

pg_repack 是 PostgreSQL 数据库的一个扩展插件，可以对表的物理存储进行在线"重新包装"，回收碎片空间，有效解决因对表大量UPDATE、DELETE 等操作引起的空间膨胀问题。

pg_repack 获取排他锁的时间很短，多数时间不阻塞读写，相比 CLUSTER 或 VACUUM FULL 操作更加轻量化。它通过创建临时表和触发器来捕获重组期间的增量变更，实现几乎零停机的表重组。

pg_repack 工作原理

pg_repack 通过创建临时表、触发器捕获增量变更的方式实现在线重组，主要步骤如下：

1、创建与原表结构相同的新表（临时表）

2、在原表上创建触发器，捕获重组期间的 INSERT/UPDATE/DELETE 操作

3、将原表数据复制到新表（可按指定列排序，类似 CLUSTER）

4、在新表上并行创建索引

5、将重组期间捕获的增量变更应用到新表

6、短暂获取排他锁，交换新旧表并删除旧表，完成重组

pg_repack 命令选项

pg_repack 命令选项

pg_repack 安装

1、下载

https://github.com/reorg/pg_repack

2、编译安装

cd pg_repack

make

make install

3、修改配置文件 postgresql.conf

shared_preload_libraries = 'pg_repack'

4、安装插件

CREATE EXTENSION IF NOT EXISTS pg_repack;

pg_repack使用技巧

消除表碎片（在线重组表）

1、查看表的碎片情况

SELECT schemaname, relname, n_dead_tup, n_live_tup,

round(n_dead_tup::numeric/n_live_tup*100,2) AS bloat_pct

FROM pg_stat_user_tables where relname='pgbench_accounts' ORDER BY bloat_pct DESC;

2、执行 pg_repack 重组表

pg_repack -d pg_bench -t pgbench_accounts

3、验证重组后的表大小

SELECT pg_size_pretty(pg_relation_size('pgbench_accounts'));

注意：pg_repack 要求目标表必须有主键或唯一非空索引，否则无法执行在线重组。

重组期间会创建临时表和触发器，请确保有足够的磁盘空间（约为表大小）。

VACUUM FULL VS pg_repack

传统 VACUUM FULL 方式

1、对表执行 VACUUM FULL

test=# VACUUM FULL pgbench_accounts;

2、VACUUM FULL 期间全程持有排他锁

阻塞所有读写操作，业务停摆

Time: 12580.456 ms

3、查看表大小

SELECT pg_size_pretty(pg_relation_size('pgbench_accounts'));

VACUUM FULL VS pg_repack

pg_repack 在线重组

1、使用 pg_repack 重组表

pg_repack -d pg_bench -t pgbench_accounts

2、重组期间不阻塞读写

仅在开始和结束阶段短暂加锁

INFO: repacking table "pgbench_accounts"

Time: 3560.123 ms

3、查看重组后大小

SELECT pg_size_pretty(pg_relation_size('pgbench_accounts'));

pg_repack 与索引

pg_repack 与索引的关系

pg_repack 不仅可以重组表，还可以单独重组索引。当索引因大量 DML 操作产生膨胀时，可以使用 pg_repack 重建索引，而无需重组整张表。

重组索引时使用 -i 参数指定索引名。pg_repack 会创建新的索引，构建完成后替换旧索引，整个过程对业务影响极小。

对于大型表，推荐使用 -j 参数开启并行工作进程，加快索引重建速度。同时可以使用 --dry-run 先进行试运行，检查重组条件是否满足。

pg_repack 按列排序重组

按列排序重组的优势

pg_repack 支持在重组时按照指定列对数据进行物理排序，效果类似于 CLUSTER 命令，但不需要长时间持有排他锁。

使用 --order-by 指定排序列，可以提升范围查询的 I/O 性能；使用 --no-order 则仅消除碎片而不改变数据顺序，速度更快。

对于有主键或唯一索引的表，pg_repack 默认会按该列排序。也可以通过 -o 参数自定义排序列，使热点查询的数据在磁盘上连续分布。

pg_repack 按列排序重组

按列排序重组演示

1、查看重组前查询性能

test=# EXPLAIN ANALYZE SELECT * FROM orders

WHERE order_date BETWEEN '2025-01-01' AND '2025-03-31';

2、按日期列重组

pg_repack -d testdb -t orders --order-by "order_date"

INFO: repacking table "orders" USING order by "order_date"

Time: 4520.678 ms

3、查看重组后查询性能

test=# EXPLAIN ANALYZE SELECT * FROM orders

WHERE order_date BETWEEN '2025-01-01' AND '2025-03-31';

pg_repack 按列排序重组

按列排序重组效果

4、对比重组前后执行计划

重组前：Seq Scan on orders  (cost=0.00..154.30 rows=5000)

重组后：Index Scan on orders  (cost=0.42..8.45 rows=5000)

查询性能显著提升

Time: 0.087 ms

通过按查询条件列进行物理排序，使得范围扫描时数据在磁盘上连续分布，大幅减少随机 I/O，提升查询效率。

pg_repack 注意事项

pg_repack 注意事项

1、目标表必须有主键或唯一非空索引

    否则需使用 --no-order 参数重组

2、确保足够的磁盘空间

    重组期间会创建临时表，需要约等于表大小的额外空间

3、重组期间 DML 操作会被触发器捕获

    高并发写入场景下会有额外性能开销

4、重组大表时建议使用 -j 并行参数

加快数据复制和索引重建速度

pg_repack 所支持的特性

pg_repack 支持的特性

PostgreSQL中文社区认证

CUUG与工信部人才交流中心合作，推出PostgreSQL初/中/高级证书培训考证服务，证书中明确指定适用于信息技术应用创新人才岗位能力评定要求。