Database

pg_repack: Physical storage optimization and maintenance

pg_repack is a Postgres extension to remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes. Unlike CLUSTER and VACUUM FULL, pg_repack runs "online" and does not hold a exclusive locks on the processed tables that could prevent ongoing database operations. pg_repack's efficiency is 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

pg_repack has 2 components, the database extension and a client-side CLI to control it.

Requirements

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

pg_repack requires the Postgres superuser role by default. That role is not available to users on the Supabase platform. To avoid that requirement, use the -k or --no-superuser-check flags on every pg_repack CLI command.

The first version of pg_repack with full support for non-superuser repacking is 1.5.2. You can check the version installed on your Supabase instance using


_10
select default_version
_10
from pg_available_extensions
_10
where name = 'pg_repack';

If pg_repack is not present, or the version is < 1.5.2, upgrade to the latest version of Supabase to gain access.

Usage

Enable the extension

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

  1. Go to the Database page in the Dashboard.
  2. Click on Extensions in the sidebar.
  3. Search for "pg_repack" and enable the extension.

Install the CLI

Select an option from the pg_repack docs to install the client CLI.

Syntax

All pg_repack commands should include the -k flag to skip the client-side superuser check.


_10
pg_repack -k [OPTION]... [DBNAME]

Example

Perform an online VACUUM FULL on the tables public.foo and public.bar in the database postgres:


_10
pg_repack -k -h db.<PROJECT_REF>.supabase.co -p 5432 -U postgres -d postgres --no-order --table public.foo --table public.bar

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

Limitations

  • pg_repack cannot reorganize temporary 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