Skip to main content

Use the PostgreSQL® pg_repack extension

pg_repack is a PostgreSQL® extension that allows you to efficiently reorganize tables to remove any excess bloat the tables have accumulated. Reorganizing a table may take some time, but pg_repack tries to minimize the locks required to continue online operations.


Before you install the pg_repack extension, verify the version of the extension is supported on the PostgreSQL version you are using. For information on supported versions, see the pg_repack documentation.


The following variables need to be substituted when running the commands.

HOSTNAMEHostname for PostgreSQL connection
PORTPort for PostgreSQL connection
DATABASENAMEDatabase Name of your Aiven for PostgreSQL connection
TABLENAMEName of the table you want to reorganize

Use pg_repack extension

To use the pg_repack extension:

  1. Connect to the database as avnadmin user, and run the following command to create the extension:

    CREATE EXTENSION pg_repack;
  2. Run the pg_repack command on the table to reorganize it.

    pg_repack -k -U avnadmin -h <HOSTNAME> -p <PORT> -d <DATABASENAME> -t <TABLENAME>
  • Using -k skips the superuser checks in the client. This setting is useful when using pg_repack on platforms that support running it as non-superusers.
  • The target table must have a PRIMARY KEY, or at least a UNIQUE total index on a NOT NULL column.