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 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. Installing Client
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'));