Important:
pg_repack is not a JFrog tool, and as such, it cannot be supported by JFrog. This guide is provided for informational purposes only.
We can use the pg_repack extension to remove table and index bloat in PostgreSQL with minimal database locking. This extension enables us to reclaim space from tables and indexes, and optionally restore the physical order of clustered indexes, while holding an exclusive lock for only a brief moment at the start and end of the process.
While VACUUM FULL effectively reclaims space, it requires an Access Exclusive lock, meaning the table is inaccessible during the entire operation. VACUUM FULL is the preferred option only when a maintenance window allows for complete downtime, as it requires no external setup.
However, for production environments where downtime is not an option, pg_repack is the standard solution. It performs the rebuild "online", allowing the application to continue reading and writing to the database.
To use pg_repack, you must install the package that matches your specific PostgreSQL version and operating system.
Example: CentOS 9 and PostgreSQL 17
sudo dnf install pg_repack_17
For more download options, please refer to the official documentation: https://reorg.github.io/pg_repack/#download
Before you can use the command-line tool, the extension must be registered inside the specific database you intend to maintain. Connect to your database and run:
CREATE EXTENSION pg_repack;
Once the extension is enabled, you can execute the command-line utility. It is recommended to perform a "dry run" first to verify the target tables and ensure no unexpected errors occur.
/usr/pgsql-17/bin/pg_repack --dry-run -d your_database_name
For additional usage examples and flags, please refer to the documentation: https://reorg.github.io/pg_repack/#usage
Considerations
- The target table must have a PRIMARY KEY or a UNIQUE index on a NOT NULL column. pg_repack relies on this key to identify rows while synchronizing changes during the rebuild. It cannot process tables without one.
- You must have enough free disk space to store a second copy of the target table. For example, if a table is 100GB, you need at least 100GB of available space to accommodate the transient "new" table during the process.