（文档）第132讲：物化视图管理利器 ——pg_repack插件

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命令选项

选项

功能说明

-t, --table

指定要重组的表名，仅对该表执行在线重组

-i, --index

只重组指定的索引，无需重组整张表

-j, --jobs

指定并行工作进程数，用于加速索引创建

-n, --no-order

不按指定列排序，仅消除碎片（无主键表可用）

-T, --wait-timeout=SECS

等待超时，会kill引起冲突的相关后端进程

-D, --no-kill-backend

当超时发生，不中断引起冲突的相关后端进程

pg_repack 命令选项

选项

功能说明

-o, --order-by

按指定列对数据进行物理排序重组

-d, --dbname

指定要连接的数据库名

--dry-run

试运行，仅检查条件不实际执行重组

-s, --schema

重组指定 schema 下的所有表

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支持的特性

特性描述表重组

在线重组表，消除碎片，多数时间不阻塞读写

索引重组

单独重组指定索引，无需重组整张表

按列排序

支持 --order-by 按指定列物理排序，提升范围查询性能

并行处理

支持 -j 多进程并行构建索引，加速重组

按 Schema 重组

支持 -s 重组整个 schema 下所有表，批量处理

试运行

支持 --dry-run 检查条件不实际执行重组

PostgreSQL中文社区认证

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