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.

note

Before you install the pg_repack extension, verify the version of the extension is supported on the PostgreSQL version you are using. See the supported versions.

Variables​

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

VariableDescription
HOSTNAMEHostname for PostgreSQL connection
PORTPort for PostgreSQL connection
DATABASENAMEDatabase Name of your Aiven for PostgreSQL connection
TABLENAMEName of the table 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>
note
  • 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.