PolarDB for PostgreSQL(Compatible with Oracle) allows you to use the pg_repack extension to deframent tablespaces. This prevents table bloating due to frequent updates to entire tables. When you use this extension to handle table bloating, this extension does not need to acquire an exclusive lock on the bloated table. This extension is more lightweight than the CLUSTER and VACUUM FULL statements.

Precautions

  • The pg_repack extension requires additional storage space. A full-table repack requires the free storage space to be at least twice the size of the table that you want to repack.
  • The pg_repack extension cannot be used to repack temporary tables.
  • The pg_repack extension cannot be used to repack GiST indexes.
  • When a table is being repacked by the pg_repack extension, you cannot execute DDL statements on the table. The pg_repack extension holds an ACCESS SHARE lock on the bloated table to prohibit the execution of DDL statements on the bloated table.
  • The pg_repack extension consumes a large number of disk I/O resources to repack tables and indexes. Before you use the pg_repack extension, you must evaluate whether the repack operation can interrupt your workloads. For example, if you repack a table of 100 GB for a PL1 enhanced SSD, the maximum I/O throughput is 250 Mbit/s.
  • The pg_repack extension needs the client. You must first install the pg_repack client and then use the pg_repack extension on the command line.

How it works

The pg_repack extension cannot be used to repack full tables and indexes.
  • The following procedure shows how the extension repacks an entire table:
    1. Create a logging table. The logging table is used to record the changes that are made to the original table during the repack process.
    2. Create triggers on the original table. The triggers are used to record the INSERT, UPDATE, and DELETE statements that are executed on the original table and insert the records into the log table during the repack process.
    3. Create a table and import the same schema as the original table.
    4. Create the indexes of the original table in the new table.
    5. Apply the data changes in the log table to the new table.
    6. Switch the original and new tables in the system catalog.
    7. Delete the original table.
    Note The pg_repack extension holds an ACCESS EXCLUSIVE lock on the original table to prohibit operations on the original table in Steps 1, 2, 6, and 7. However, in the other steps, the extension holds an ACCESS SHARE lock. The lock does not prohibit the execution of INSERT, UPDATE, and DELETE statements on the original table.
  • The following procedure shows how the extension repacks the indexes on a table:
    1. Create indexes in CONCURRENTLY mode.
    2. Switch the original and new indexes in the system catalog.
    3. Delete the original indexes.

Enable or disable the pg_repack extension

  • Enable the pg_repack extension.
    CREATE EXTENSION pg_repack;
  • Disable the pg_repack extension.
    DROP EXTENSION pg_repack;

Install the client utility of the pg_repack extension

You must install the client of the pg_repack extension. The following methods can be used:

  • CentOS 7
    sudo yum install centos-release-scl-rh
    sudo yum install rh-postgresql12-pg_repack
  • CentOS 8
    sudo dnf install pg_repack
  • Ubuntu 20.04/22.04
    sudo apt install postgresql-11-repack

Sample code

-- Only check whether the pg_repack extension is correct, but do not perform the repack operation:--dry-run
$ pg_repack --dry-run \
    --no-superuser-check --echo --no-order \
    -h Endpoint -p Port -d database1 -U user --table schema1.table1

-- perform the repack operation.
$ pg_repack \
    --no-superuser-check --echo --no-order \
    -h Endpoint -p Port -d database1 -U user --table schema1.table1
            
Note PolarDB for PostgreSQL(Compatible with Oracle) does not allow you to directly create tablespaces. Therefore, you cannot use the -s/--tablespace option of the pg_repack extension to repack a specified tablespace.

References

For more information about the pg_repack extension, see Reorganize tables in PostgreSQL databases with minimal locks.