pg_repack: Physical storage optimization and maintenance
pg_repack is a PostgreSQL extension to remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes. Unlike CLUSTER and VACUUM FULL it works online, without holding an exclusive lock on the processed tables during processing. pg_repack is efficient to boot, with performance comparable to using CLUSTER directly.
pg_repack provides the following methods to optimize physical storage:
- Online CLUSTER: ordering table data by cluster index in a non-blocking way
- Ordering table data by specified columns
- Online VACUUM FULL: packing rows only in a non-blocking way
- Rebuild or relocate only the indexes of a table
- Only superusers can use the utility.
- Target table must have a PRIMARY KEY, or a UNIQUE total index on a NOT NULL column.
- Performing a full-table repack requires free disk space about twice as large as the target table and its indexes.
Enable the extension#
Get started with pg_repack by enabling the extension in the Supabase Dashboard.
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "pg_repack" and enable the extension.
_10pg_repack [OPTION]... [DBNAME]
It's useful for performance to support tables data ordered on disk and physically remove deleted data that remain otherwise.
Perform an online CLUSTER of all the clustered tables in the database
db, and perform an online
VACUUM FULL of all the non-clustered tables:
Perform an online
VACUUM FULL on the tables
table2 in the database
db (an eventual cluster index is ignored):
_10pg_repack --no-order --table table1 --table table2 db
Moving indexes to a tablespace on a faster volume increases performance of
SELECT queries using these indexes
UPDATEs of a table with indexes on a fast volume are also faster. This is very useful
when the fast volume is small and can not accommodate all tables, as indexes are much smaller than tables.
Move all indexes of table
table1 to tablespace
_10pg_repack -d db --table table1 --only-indexes --tablespace tbs
Move the specified index
idx to tablespace
_10pg_repack -d db --index idx --tablespace tbs
See the official pg_repack documentation for the full list of options.
- pg_repack cannot reorganize temp tables.
- pg_repack cannot cluster tables by GiST indexes.
- You cannot perform DDL commands of the target tables except VACUUM or ANALYZE while pg_repack is working. pg_repack holds an ACCESS SHARE lock on the target table to enforce this restriction.