Using pg_repack in Managed Service for PostgreSQL

PostgreSQL tables and indexes may get bloated. When running transactions that update data in tables and indexes, its older version is retained in case you need to roll back the transactions. This causes tables and indexes to bloat during bulk data updates. You can use the pgstattuple extension or the pgsql-bloat-estimation queries to estimate the amount of bloat.

Older data versions are not deleted automatically. To free up storage space and remove bloat, you can delete the data you no longer need using the built-in VACUUM FULL or CLUSTER commands. However, these commands require an exclusive lock on the processed tables, which may not be convenient or possible.

The pg_repack extension allows you to remove bloat in tables and indexes by repacking them. Unlike other methods, pg_repack works without holding an exclusive lock on the tables. For more information, see the extension page.

Installing pg_repack in a PostgreSQL clusterInstalling pg_repack in a PostgreSQL cluster

1. Add the pg_repack extension to the selected database.

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

   You can get the owner's name with the list of databases in the cluster.

3. Set the following PostgreSQL cluster parameters to zero:

   • statement_timeout
   • idle_in_transaction_session_timeout

   Warning

   Other values for these settings may cause long-running commands to interrupt or idle transactions to terminate. If so, a pg_repack run may fail to complete correctly.

Installing the pg_repack clientInstalling the pg_repack client

You need the same-name client to use the extension. The client must be installed on a host that can connect 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 return the following error:

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

   Depending on your operating system and connected repositories, you can install the client using a package manager, such as apt or yum. If the required client version is not available in the repositories, build it from the source files yourself.

   How to build the pg_repack client on Ubuntu 22.04 LTS

   1. Connect 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 that matches the version of the PostgreSQL cluster you plan to connect to using the client. This will improve the stability of the client you built.

      Here is an example of the command to install such a package for PostgreSQL 16 and 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 by selecting the relevant version tag and open the local directory containing the repository.

      Example of the command for 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. Run the build:

      make
      

      Wait for the build process to complete.

   5. Place the pg_repack executable file in any directory listed in the PATH environment variable for the current user.

      Example of the command to move the file to /usr/local/bin:

      sudo install bin/pg_repack /usr/local/bin
      

   6. Check the client version:

      pg_repack --version
      

      This should output the version matching the tag you selected earlier, e.g.:

      pg_repack 1.4.8
      

Running pg_repackRunning pg_repack

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.

   pg_repack works with copies of tables and indexes, which requires additional storage space for repacking.

2. Run the pg_repack client with the required parameters.

   Below are examples of commands for running the client with common parameter combinations. For a description of all supported parameters, see the extension page.

   Tip

   Add the --dry-run parameter to dry run pg_repack and review the planned updates.

   This will output the list of objects to repack when running pg_repack in the regular mode.

   Run the command you need:

   • Command to repack the specified tables in the database:

     Repacking with connection without SSL

     Repacking with connection via SSL

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

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

     If you need to repack multiple tables, provide the required number of -t parameters, one for each table.

   • Command to repack the specified indexes in the database:

     Repacking with connection without SSL

     Repacking with connection via SSL

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

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

     If you need to repack multiple indexes, provide the required number of -i parameters, one for each index.

   • Command to repack all tables and indexes in the database:

     Repacking with connection without SSL

     Repacking with connection via SSL

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

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

Usage exampleUsage example

This example uses pgbench, a tool designed for PostgreSQL load testing which allows you to automate the following operations:

• Create test tables and indexes and populate the tables with test data.
• Running multiple queries to test tables, including INSERT and DELETE queries that update the table contents.

After thepgbench operation is complete, test tables and their relevant indexes become bloated as a result of running a large number of queries. Also, pgbench runs the VACUUM command, which removes table and index bloat, at the beginning rather than the end of its operation. So we will use pgbench to create bloated tables and indexes to demonstrate how pg_repack works.

To test pg_repack on the pgbench test tables and indexes:

1. Get the PostgreSQL cluster ID.

2. Add the pgstattuple extension to the db1 database.

   This extension allows you to estimate table and index bloat to see how pg_repack works.

3. Install pgbench on your VM:

   sudo apt install postgresql-contrib
   

   Note

   The PostgreSQL Apt repository contains the postgresql-contrib package.

   If you deleted this repository from the virtual machine after building the pg_repack client on Ubuntu 22.04 LTS, reconnect the repository.

4. Create test tables and indexes in the db1 database by connecting to it as its owner (user1):

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

   This 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. View the statistics for the pgbench_* tables and indexes:

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

   2. Request table statistics:

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

   3. Request 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 must show zero for all query results. This means there are no bloated tables and indexes.

   Here is an example of partial output 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 the pgbench run to complete. This may take several minutes.

7. Recheck statistics for the pgbench_* tables and indexes:

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

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 the 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 the pg_repack run to complete. This may take several minutes.

10. Recheck statistics for the pgbench_* tables and indexes:

    The dead_tuple_count columns must show zero for all query results. This means there are no bloated tables or indexes and pg_repack did its job correctly.