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

Requirements#

• 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.

Usage#

Enable the extension#

Get started with pg_repack by enabling the extension in the Supabase Dashboard.

Syntax#

pg_repack [OPTION]... [DBNAME]

Examples#

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:

pg_repack db

Perform an online VACUUM FULL on the tables table1 and table2 in the database db (an eventual cluster index is ignored):

pg_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 drastically. INSERTs and 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 tbs:

pg_repack -d db --table table1 --only-indexes --tablespace tbs

Move the specified index idx to tablespace tbs:

pg_repack -d db --index idx --tablespace tbs

See the official pg_repack documentation for the full list of options.

Restrictions#

• 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.

Resources#

• Official pg_repack documentation