This topic describes how to clear the tablespaces of an ApsaraDB RDS for PostgreSQL instance by using the pg_repack plug-in. If you perform a large number of operations, such as UPDATE, on an entire table, the table may be bloated. In this case, you can use this plug-in to mitigate the table bloat. This plug-in does not hold an exclusive lock on the affected table during processing. It is more lightweight than the CLUSTER and VACUUM FULL statements.

Precautions

  • The pg_repack plug-in 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 plug-in cannot remove bloat from temporary tables.
  • The pg_repack plug-in cannot remove bloat from GiST indexes.
  • You cannot execute DDL statements on the table that is being repacked by the pg_repack plug-in. The pg_repack plug-in holds an ACCESS SHARE lock on the affected table, and the lock prohibits DDL execution.

Features

The pg_repack plug-in supports full-table repack and index repack.
  • The following procedure provides further details about how the plug-in repacks an entire table:
    1. Creates a log table. The log table is used to record the changes that are made to the original table during the repack process.
    2. Creates 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. Creates a table. The new table has the same rows and columns as the original table.
    4. Creates indexes in the new table.
    5. Applies the data from the log table to the new table.
    6. Switches the original and new tables in the system catalog.
    7. Deletes the original table.
    Note The pg_repack plug-in holds an ACCESS EXCLUSIVE lock on the original table and prevents all operations on the original table in Steps i, ii, vi, and vii. However, in the other steps, the plug-in holds an ACCESS SHARE lock and does not prevent the INSERT, UPDATE, DELETE statements that are scheduled to run on the original table.
  • The following procedure provides further details about how the plug-in repacks the indexes on a table:
    1. Concurrently creates indexes.
    2. Switches the original and new indexes in the system catalog.
    3. Deletes the original indexes.

Use the pg_repack plug-in

  • Enable the pg_repack plug-in.
    CREATE EXTENSION pg_repack;
  • Disable the pg_repack plug-in.
    DROP EXTENSION pg_repack;
  • Repack all the tables in the test database.
    pg_repack test
  • Repack the foo and bar tables in the test database.
    pg_repack --no-order --table foo --table bar test
  • Move all the indexes of the foo table in the test database to the tbs tablespace.
    pg_repack -d test --table foo --only-indexes --tablespace tbs
  • Move the specified index in the test database to the tbs tablespace.
    pg_repack -d test --index idx --tablespace tbs

FAQ

What do I do if the "ERROR: pg_repack failed with error: You must be a superuser to use pg_repack" message is reported?

Use the -k or --no-superuser-check option to skip the superuser permission check. This prevents such permission errors.

References

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