Use pg_repack to remove table bloat - ApsaraDB RDS

Heavy UPDATE workloads cause table bloat — dead tuples accumulate and inflate table size even after VACUUM runs. The pg_repack extension reclaims that space in ApsaraDB RDS for PostgreSQL without holding an exclusive lock on the table for the duration of the operation, making it less disruptive than CLUSTER or VACUUM FULL.

Prerequisites

Before you begin, make sure that:

The instance runs PostgreSQL 10 or later
The minor engine version is 20240229 or later (for PostgreSQL 17, version 20241030 or later is required)

To update the minor engine version, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.

Note

If the minor engine version is earlier than 20240229, running any pg_repack function returns ERROR: must be superuser to use xxx function.

Limitations

Cannot repack temporary tables or GiST indexes
During a full-table repack, DDL statements on the table are blocked. pg_repack holds an ACCESS SHARE lock to enforce this
A full-table repack requires available storage at least twice the size of the table you want to repack
A full-table repack is I/O-intensive. On an instance with an Enhanced SSD (ESSD) at performance level 1 (PL1), repacking a 100 GB table can saturate the maximum I/O throughput of 250 MB/s — run during off-peak hours

How it works

Full-table repack

pg_repack rebuilds the table in seven steps:

Creates a log table to capture changes made to the original table during the repack
Adds triggers to the original table to write every INSERT, UPDATE, and DELETE into the log table
Copies all rows into a new table
Builds indexes on the new table
Applies the accumulated log table changes to the new table
Swaps the original and new tables in the system catalog
Drops the original table

Note

pg_repack holds an ACCESS EXCLUSIVE lock in steps 1, 2, 6, and 7. In all other steps it holds only an ACCESS SHARE lock, so INSERT, UPDATE, and DELETE operations on the original table continue uninterrupted.

Index-only repack

Builds new indexes concurrently
Swaps the original and new indexes in the system catalog
Drops the original indexes

Enable or disable pg_repack

Run these statements using the privileged account.

-- Enable
CREATE EXTENSION pg_repack;

-- Disable
DROP EXTENSION pg_repack;

Install the pg_repack client utility

The pg_repack command-line client runs on your Elastic Compute Service (ECS) instance and connects to the RDS instance remotely. The following steps apply to ECS instances running Alibaba Cloud Linux 3.2104 or later.

Install the build dependencies:

sudo yum install postgresql* redhat-rpm-config libpq* openssl-devel readline-devel -y

Add the PostgreSQL binaries to your path:
Note
The path and PostgreSQL version in the command above are examples. Replace them with the values for your environment.
```
export PATH=$PATH:/usr/lib64/pgsql/postgresql-12/bin
```

Download, compile, and install the client:

wget https://github.com/reorg/pg_repack/archive/refs/tags/ver_1.4.6.tar.gz
tar zxvf ver_1.4.6.tar.gz
cd pg_repack-ver_1.4.6
make && make install

Repack a table

Check available storage

A full-table repack requires available storage at least twice the size of the table. Before repacking, verify that you have enough free space.

Run the following queries to check table size and estimate required storage:

-- Check total table size (including TOAST and indexes)
SELECT pg_size_pretty(pg_total_relation_size('schema1.table1')) AS total_size;

-- Check heap size only
SELECT pg_size_pretty(pg_relation_size('schema1.table1')) AS heap_size;

Important

Provision at least 2x the current total table size as free storage before starting a full-table repack.

Dry run (recommended)

Run with --dry-run to verify the operation without making any changes:

pg_repack --dry-run --no-superuser-check --echo --no-order \
  -h <endpoint> -p <port> -d <database> -U <user> \
  --table schema1.table1

Full-table repack

pg_repack --no-superuser-check --echo --no-order \
  -h <endpoint> -p <port> -d <database> -U <user> \
  --table schema1.table1

Replace the placeholders with actual values:

Placeholder	Description	Example
`<endpoint>`	RDS instance endpoint	`rm-xxx.pg.rds.aliyuncs.com`
`<port>`	Port number	`5432`
`<database>`	Database name	`mydb`
`<user>`	Database username	`myuser`

Verify the result

Check the table size before and after the repack to confirm that space was reclaimed.

Before the repack:

SELECT pg_size_pretty(pg_relation_size('schema1.table1'));

After the repack, run the same query. A successful repack shows a smaller value.

Troubleshooting

Error: `pg_repack failed with error: You must be a superuser to use pg_repack`

Add -k (or --no-superuser-check) to skip the superuser check:

pg_repack -k --no-superuser-check ...

Important

When you repack a full table, you cannot use this method to obtain permissions.

Reference

Reorganize tables in PostgreSQL databases with minimal locks — pg_repack official documentation