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. For
information on supported versions, see the pg_repack
documentation.
Variables
The following variables need to be substituted when running the commands.
Variable | Description |
---|---|
HOSTNAME | Hostname for PostgreSQL connection |
PORT | Port for PostgreSQL connection |
DATABASENAME | Database Name of your Aiven for PostgreSQL connection |
TABLENAME | Name of the table you want to reorganize |
Use pg_repack
extension
To use the pg_repack
extension:
-
Connect to the database as
avnadmin
user, and run the following command to create the extension:CREATE EXTENSION pg_repack;
-
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.