Contact UsTry it for free
© 2025 Direct Cursus Technology L.L.C.

Using pg_repack in Managed Service for PostgreSQL

Written by
Updated at December 29, 2025

PostgreSQL tables and indexes may be given to bloating. During transactions that update data in tables and indexes, the old data is kept to allow for rollback if needed. This causes tables and indexes to bloat during bulk data updates. You can use the pgstattuple extension or the pgsql-bloat-estimation queries to assess bloat.

The system does not automatically purge old data versions. To free up storage space and eliminate the bloat, you can delete the data you no longer need using the VACUUM FULL or CLUSTER commands. These commands require an exclusive table lock, which may not be convenient or acceptable.

The pg_repack extension allows you to remove bloat from tables and indexes by repacking them. pg_repack does not require an exclusive table lock, unlike other methods. For more information, see the extension page.

Installing pg_repack in a PostgreSQL cluster

  1. Enable the pg_repack extension in your database.

  2. Assign the mdb_admin role to the owner of this database, if not assigned yet.

    You can get the owner's name from the cluster’s database list.

  3. Set the following PostgreSQL cluster settings to zero:

    Warning

    Assigning other values to these settings may cause termination of long-running commands and idle transactions. This may result in pg_repack failure.

Installing the pg_repack client

To manage the extension you need the pg_repack client. You need to install the client on a host with connectivity to the PostgreSQL cluster.

To install the client:

  1. Check the pg_repack version installed in the PostgreSQL cluster.

  2. Install the pg_repack client.

    Warning

    The client and extension versions must match; otherwise, the connection will terminate with this error:

    ERROR: pg_repack failed with error: program 'pg_repack ...' does not match database library 'pg_repack ...'

    Depending on your operating system and enabled repositories, you can install the client via a package manager, e.g., apt or yum. If the required client version is missing from your repositories, you can build it from source.

    How to build the pg_repack client on Ubuntu 22.04 LTS

    1. Add the PostgreSQL Apt repository, which contains some of the required dependencies:

      sudo apt install -y postgresql-common && \
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y $(lsb_release -cs)

    2. Install the dependencies.

      Select the postgresql-server-dev-* package version matching the version of your target PostgreSQL cluster for the client connection. This will improve the stability of the compiled client.

      Example command installing this package for PostgreSQL 16 alongside other packages:

      sudo apt install git build-essential \
                 zlib1g-dev libzstd-dev liblz4-dev \
                 libreadline-dev \
                 postgresql-server-dev-16

    3. Clone the pg_repack Git repository using the relevant version tag, then navigate to your local repository directory.

      Example command using the ver_1.4.8 tag:

      git clone https://github.com/reorg/pg_repack.git \
    --branch ver_1.4.8 --depth 1 && \
cd pg_repack

    4. Build the project:

      make

      Wait for the build to complete.

    5. Place the pg_repack binary in any directory listed in the current user’s PATH environment variable.

      Example command for moving the file to /usr/local/bin:

      sudo install bin/pg_repack /usr/local/bin

    6. Check the client version:

      pg_repack --version

      You should see the client version matching your previously selected tag, e.g.:

      pg_repack 1.4.8

Running pg_repack

Tip

Run pg_repack when the PostgreSQL cluster load is minimal: repacking database objects will put extra load on your cluster.

Cluster and host status data is available in the management console.

To run pg_repack and repack database objects:

  1. Make sure the cluster has enough free storage space: at least twice the total size of the tables and indexes to repack.

    During a repack, pg_repack works with copies of tables and indexes, which requires additional storage space.

  2. Run the pg_repack client with your preferred options.

    Here are example commands with frequently used option combinations for the client. For all supported options, see the extension page.

    Tip

    Add the --dry-run flag to dry-run pg_repack in order to review the planned updates.

    You will see the list of objects that will be repacked when pg_repack is run in regular mode.

    Run one of the following commands:

    • Repacking the specified database tables:

      pg_repack -k -h c-<cluster_ID>.rw.mdb.yandexcloud.net -p 6432 \
          -U <username> \
          -d <DB_name> \
          -t <table_name>

      PGSSLMODE='verify-full' \
pg_repack -k -h c-<cluster_ID>.rw.mdb.yandexcloud.net -p 6432 \
          -U <username> \
          -d <DB_name> \
          -t <table_name>

      To repack multiple tables, provide one -t argument per table.

    • Repacking the specified database indexes:

      pg_repack -k -h c-<cluster_ID>.rw.mdb.yandexcloud.net -p 6432 \
          -U <username> \
          -d <DB_name> \
          -i <index_name>

      PGSSLMODE='verify-full' \
pg_repack -k -h c-<cluster_ID>.rw.mdb.yandexcloud.net -p 6432 \
          -U <username> \
          -d <DB_name> \
          -i <index_name>

      To repack multiple indexes, provide one -i argument per index.

    • Repacking all database tables and indexes:

      pg_repack -k -h c-<cluster_ID>.rw.mdb.yandexcloud.net -p 6432 \
          -U <username> \
          -d <DB_name>

      PGSSLMODE='verify-full' \
pg_repack -k -h c-<cluster_ID>.rw.mdb.yandexcloud.net -p 6432 \
          -U <username> \
          -d <DB_name>

Usage example

Note

This example was tested in the following environment:

  • PostgreSQL 16 cluster containing the following resources:

    • Database db1 owned by user1.
    • pg_repack``1.4.8 extension installed for database db1.

  • Yandex Cloud VM running Ubuntu 22.04 LTS, with the following configuration:

This example uses pgbench, a PostgreSQL load-testing tool, allowing you to automate the following operations:

  • Creating test tables and indexes, and populating tables with test data.
  • Running multiple queries against test tables, including INSERT and DELETE queries.

After pgbench finishes, the test tables and indexes remain bloated from the high query load during its run, and because pgbench executes the bloat-removing VACUUM command at the beginning rather than the end of its operation. In our example, to demonstrate the operation of pg_repack, we will first create bloated tables and indexes by running pgbench.

To test pg_repack on the test tables and indexes used by pgbench, do the following:

  1. Get the PostgreSQL cluster ID.

  2. Enable the pgstattuple extension in the db1 database.

    This extension will let us assess the table and index bloat, allowing us to see the results of pg_repack operation.

  3. Install pgbench on the VM:

    sudo apt install postgresql-contrib

    Note

    postgresql-contrib package is available in the PostgreSQL Apt repository.

    If you deleted this repository from the virtual machine after building the pg_repack client on Ubuntu 22.04 LTS, please add it again.

  4. Connect to the database db1 as its owner user1 and create test tables and indexes:

    PGSSLMODE='verify-full' \
pgbench -h c-<cluster_ID>.rw.mdb.yandexcloud.net -p 6432 \
        -U user1 -i -s 1 db1

    This command will create the following tables and indexes:

    Table Index
    pgbench_accounts pgbench_accounts_pkey
    pgbench_branches pgbench_branches_pkey
    pgbench_tellers pgbench_tellers_pkey
    pgbench_history —

  5. Check the statistics for the pgbench_* tables and indexes:

    1. Connect to the database db1 as its owner user1. Use psql to connect.

    2. Query the table statistics:

      SELECT * FROM pgstattuple('pgbench_accounts');
SELECT * FROM pgstattuple('pgbench_branches');
SELECT * FROM pgstattuple('pgbench_tellers');
SELECT * FROM pgstattuple('pgbench_history');

    3. Query the index statistics:

      SELECT * FROM pgstattuple('pgbench_accounts_pkey');
SELECT * FROM pgstattuple('pgbench_branches_pkey');
SELECT * FROM pgstattuple('pgbench_tellers_pkey');

    The dead_tuple_count columns should contain zeros for all query results, indicating that there are no bloated tables and indexes.

    Partial output example for the pgbench_accounts table:

     table_len | tuple_count | ... | dead_tuple_count | ... | free_space | free_percent
-----------+-------------+-...-+------------------+-...-+------------+--------------
  13434880 |      100000 | ... |                0 | ... |     188960 |         1.41

  6. Run pgbench once:

    PGSSLMODE='verify-full' \
pgbench -h c-<cluster_ID>.rw.mdb.yandexcloud.net -p 6432 \
        -U user1 -c 5 -j 2 -t 1000 db1

    Wait for pgbench to finish. This may take several minutes.

  7. Check the pgbench_* table and index statistics once more.

    Non-zero values in the dead_tuple_count columns indicate table and index bloat. Among the test tables and indexes created by pgbench, the pgbench_tellers table will show the greatest bloat.

  8. Dry run pg_repack to review the planned updates:

    PGSSLMODE='verify-full' \
pg_repack -k -h c-<cluster_ID>.rw.mdb.yandexcloud.net -p 6432 \
          -U user1 \
          -d db1 \
          --dry-run

    Since some indexes and tables are not bloated, the output will not include all database objects.

    Output example:

    INFO: repacking table "public.pgbench_accounts"
INFO: repacking table "public.pgbench_branches"
INFO: repacking table "public.pgbench_tellers"

  9. Run pg_repack in regular mode to repack tables and indexes:

    PGSSLMODE='verify-full' \
pg_repack -k -h c-<cluster_ID>.rw.mdb.yandexcloud.net -p 6432 \
          -U user1 \
          -d db1

    Wait for pg_repack to finish. This may take several minutes.

  10. Check the pgbench_* table and index statistics once more.

    The dead_tuple_count columns should contain zeros for all query results. This means that pg_repack has run correctly, debloating tables and indexes.

Previous
pg_cron
Next
pgaudit