使用插件pg_repack解决PG数据库表和索引膨胀问题

使用插件pg_repack解决PG数据库表和索引膨胀问题

目录

• 使用插件pg_repack解决PG数据库表和索引膨胀问题
  • 一 pg_repack简介
    • 什么是 pg_repack？
    • 工作原理
  • 二 pg_repack部署
  • 使用案例与命令详解
    • 核心约束
    • 典型使用示例
  • 常见问题
  • 注意事项（关于greenplum）：
    • 方案一：ALTER TABLE ... REPACK（Greenplum 7 推荐）
    • 方案二：gpreload（Greenplum 6 及更早版本）
    • 方案三：使用 VACUUM 和 VACUUM FULL

一 pg_repack简介

PostgreSQL数据库通过数据多版本实现MVCC，删除数据并不会真正删除数据，而是修改标识。更新是通过删除+插入的方式进行。所以在频繁更新的系统，如果不进行处理，数据膨胀倍数可能达到十几倍。

pg_repack 是一款非常有用的 PostgreSQL 扩展，它可以帮助你在不锁表的情况下，在线清理表和索引的碎片空间，解决因频繁更新、删除导致的性能下降和磁盘空间膨胀问题 。

什么是 pg_repack？

pg_repack 的核心功能是以最小的锁开销来重组表和索引。与需要长时间锁表的 VACUUM FULL 或 CLUSTER 不同，pg_repack 在整个数据重组过程中，仅在初始设置和最终交换的极短时间内持有排他锁，其余大部分时间只持有允许并发读写的共享锁，因此非常适合在生产环境中在线使用。

pg_repack --no-order 和 VACUUM FULL 在核心目的上非常相似——都是为了回收表和索引中因更新、删除而产生的膨胀空间，并将空间返还给操作系统（或重组数据页以提高空间利用率）。

工作原理

它对全表的操作流程如下：

• 1.创建日志表：创建一个日志表，用于记录在重组期间对原表发生的所有数据变更 。
• 2.设置触发器：在原表上创建触发器，将 INSERT、UPDATE、DELETE 操作实时记录到日志表中 。
• 3.创建新表并导入数据：创建一个与原表结构相同的新表，并将原表的全部数据复制进去 。
• 4.创建索引：在新表上创建与原表相同的索引 。
• 5.应用增量变更：将日志表中记录的、在数据复制期间产生的变更应用到新表上 。
• 6.交换并清理：在数据库系统目录中，用新表原子性地交换旧表，然后删除旧表 。

二 pg_repack部署

下载地址
https://github.com/reorg/pg_repack/

# 安装编译依赖 (CentOS/Rocky Linux 示例)
sudo yum install postgresql-devel make gcc redhat-rpm-config libpq-devel openssl-devel readline-devel lz4-devel -y

# 下载源码 (以1.5.2版本为例)
wget https://github.com/reorg/pg_repack/archive/refs/tags/ver_1.5.2.tar.gz
tar -zxvf ver_1.5.2.tar.gz
cd pg_repack-ver_1.5.2

# 编译并安装，指定 pg_config 路径（根据你的 PostgreSQL 安装位置调整）
export PATH=/usr/pgsql-16/bin:$PATH  # 或 export PG_CONFIG=/usr/pgsql-16/bin/pg_config
make
sudo make install

使用案例与命令详解

安装完成后，就可以使用 pg_repack 命令来清理空间了。

核心约束

在使用前，请确保目标表满足以下条件，否则操作会失败 ：

• 1.表必须有主键，或者在 NOT NULL 列上有一个唯一索引。
• 2.剩余磁盘空间至少是目标表（含索引）大小的2倍，因为需要创建一份副本。
• 3.不能在临时表或带有 GiST 索引的表上执行。

常用命令选项

选项	含义
-d, --dbname=DBNAME	指定要连接的数据库名。
-h, --host=HOSTNAME	指定数据库服务器主机。
-p, --port=PORT	指定数据库服务器端口。
-U, --username=USERNAME	指定连接用户名。
-k, --no-superuser-check	非常常用。跳过客户端超级用户检查，在使用云数据库或非超级用户时加上此选项 。
-t, --table=TABLE	仅重组指定的表（格式为 schema.table）。
-c, --schema=SCHEMA	仅重组指定 schema 中的所有表。
-a, --all	重组所有数据库。
-j, --jobs=NUM	并行处理，为每个表开启 NUM 个连接来并发重建索引，可加快速度 。
-n, --no-order	执行类似 VACUUM FULL 的操作（只整理空间，不按索引排序），而不是默认的 CLUSTER（按索引排序）。
--dry-run	仅打印将会被重组的对象，但不实际执行任何操作 。
-D, --no-kill-backend	如果在等待锁超时后，不强制终止阻塞的后端进程，而是跳过该表的操作 。
-T, --wait-timeout=SECS	等待表锁的超时时间（秒），默认60秒。配合 -D 使用 。

典型使用示例

假设我们的数据库 mydb 中有一个 orders 表（位于 public schema 下），由于频繁更新产生了膨胀，现在要对其进行清理。
1. 检查但不执行
首先使用 --dry-run 安全地预览一下操作，确认不会误操作其他表

pg_repack -k --dry-run --no-order -h 192.168.1.xx -p 5432 -U myuser -d mydb -t public.orders

eg：
使用密码：
pg_repack -k --dry-run --no-order -h 192.168.201.51 -p 5432 -U postgres -d test -t public.sales

不使用密码：
pg_repack -k --dry-run --no-order  -U postgres -d test -t public.sales -w

2. 执行在线清理
确认无误后，实际执行清理。这里使用了 --no-order 来执行类似 VACUUM FULL 的操作，只回收空间，不改变数据的物理顺序。

pg_repack -k --no-order -h 192.168.1.100 -p 5432 -U myuser -d mydb -t public.orders

3. 其他常用场景
清理整个 schema 下的所有表：

pg_repack -k --no-order -h 192.168.1.100 -p 5432 -U myuser -d mydb -c public

并行加速清理（适合索引多的表，能有效提升速度 ）：

pg_repack -k --no-order -h 192.168.1.100 -p 5432 -U myuser -d mydb -t public.orders -j 4

安全地处理锁冲突：如果担心 pg_repack 等待锁时会强制终止其他查询，可以加上 -D 让它超时后自动跳过 。

pg_repack -k --no-order -h 192.168.1.100 -p 5432 -U myuser -d mydb -t public.orders --wait-timeout 30 --no-kill-backend

常见问题

• 1.报错 "You must be a superuser to use pg_repack"：在命令中加入 -k 或 --no-superuser-check 选项即可 。
• 2.报错 "permission denied for schema repack"：说明执行命令的数据库用户权限不足，需要确保该用户有创建对象的权限，或使用高权限用户执行 。
• 3.命令卡住，提示 "Waiting for 1 transactions to finish..."：说明有其他长事务在操作目标表，pg_repack 正在等待它们完成。可以视情况等待，或设置 --wait-timeout 和 --no-kill-backend 来调整行为 。
• 4.版本不匹配错误：确保客户端工具 (pg_repack --version) 和服务端扩展 (SELECT installed_version FROM pg_available_extensions WHERE name = 'pg_repack'😉 的版本一致

注意事项（关于greenplum）：

pg_repack 是 PostgreSQL 生态中常用的在线表重组工具，用于消除表膨胀。但对于 Greenplum 数据库，不能直接使用。以下是详细解释和替代方案。

方案一：ALTER TABLE ... REPACK（Greenplum 7 推荐）

Greenplum 7 引入了内置的在线表重组命令，无需第三方工具：

ALTER TABLE table_name REPACK BY COLUMNS (column1 ASC, column2 DESC);

作用：按指定列的顺序重新物理组织数据，提高压缩率、改善数据分布。
优点：原生支持，自动在 Master 和 Segment 上协调执行，支持 AO/CO 表。
注意：需要足够的磁盘空间（因为会重写表）。

方案二：gpreload（Greenplum 6 及更早版本）

在 Greenplum 6 中，可以使用 gpreload (必须要指定表的列)工具对表进行重组：

gpreload -d dbname -t schema.table

eg:
gpreload -d gpcopy_test --table-file gpreload_table.txt

cat gpreload_table.txt
public.dws_amb_allzb_co: id,name

作用：按索引顺序或指定列顺序重新加载表数据。

缺点：需要停机或锁表（取决于配置），不如 REPACK 灵活。

方案三：使用 VACUUM 和 VACUUM FULL

VACUUM：回收空间，但不会重组表（仅标记空间可复用）。

VACUUM FULL：重写表并回收空间，但会长时间锁表（Greenplum 中慎用，尤其在大表上）。