tencent cloud

Fix Table Bloat
Download PDF
Last updated:2026-03-11 15:07:27
Fix Table Bloat
Last updated: 2026-03-11 15:07:27
Download PDF
TencentDB for PostgreSQL supports online tablespace cleanup using the pg_repack plugin, effectively resolving table bloat caused by operations like heavy full-table updates. Unlike CLUSTER and VACUUM, pg_repack runs online without requiring exclusive locks on processed tables during the process. Meanwhile, pg_repack starts up with high efficiency, performing comparably to direct use of CLUSTER. This document details how to handle table bloat with pg_repack. For more usage scenarios, refer to the official documentation.
Note:
Note: pg_repack requires additional storage space. For a full table repack, the remaining storage space must be at least twice the table size. Please upgrade your TencentDB for PostgreSQL instance configuration in advance.
pg_repack cannot operate temporary tables or GiST indexes.
pg_repack holds an ACCESS SHARE lock during operation, preventing DDL execution on tables being repacked.
Rebuilding tables and indexes occupies considerable disk I/O. Please execute during off-peak hours.
The target table must have a PRIMARY KEY or at least a UNIQUE index on NOT NULL columns.

Prerequisites

There is one TencentDB for PostgreSQL instance. To create one, see Create a PostgreSQL instance.
The instance has installed the pg_repack plug-in. For installation, refer to How to create a plug-in in a TencentDB for PostgreSQL instance.
There is one Cloud Virtual Machine (CVM), which is in the same VPC as the TencentDB for PostgreSQL instance. For Linux CVM configuration, see Quick Configuration for Linux CVM.
The CVM instance has installed the psql client. For detailed installation instructions, refer to PostgreSQL official documentation.

Installing Client

Before using, install the client on the CVM. Click Installation package download link, upload the installation package, and install.
unzip pg_repack-1.5.2.zip
cd pg_repack-1.5.2
make && make install

Clearing a Single Table

-- Check but not execute
pg_repack -k --dry-run --no-order -h 10.*.*.* -p 5432 -U user -d database -t schema1.table1
-- Check and execute
pg_repack -k --no-order -h 10.*.*.* -p 5432 -U user -d database -t schema1.table1

Cleaning a Single Schema

-- Check but not execute
pg_repack -k --dry-run --no-order -h 10.*.*.* -p 5432 -U user -d database -c schema
-- Check and execute
pg_repack -k --no-order -h 10.*.*.* -p 5432 -U user -d database -c schema

Clearing a Single Database

-- Check but not execute
pg_repack -k --dry-run --no-order -h 10.*.*.* -p 5432 -U user -d database
-- Check and execute
pg_repack -k --no-order -h 10.*.*.* -p 5432 -U user -d database

Clearing the Entire Instance (Only Databases with Pg_repack Plug-In Installed)

-- Check but not execute
pg_repack -k --dry-run --no-order -h 10.*.*.* -p 5432 -U user -a
-- Check and execute
pg_repack -k --no-order -h 10.*.*.* -p 5432 -U user -a

Checking the Cleanup Effect

-- View the top library size, sql as follows:
select datname,pg_size_pretty(pg_database_size(datname))  from pg_database order by pg_database_size(datname) desc;
-- View the top table size in the database, sql as follows:
SELECT relname AS table_name, pg_size_pretty(pg_total_relation_size(relid)) AS total_size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
-- View the size of a specific table, sql as follows:
select pg_size_pretty(pg_total_relation_size('pgtest'));
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback