pg_repack

Scenarios

pg_repack can reorganize tables and indexes with minimal locks to restore the physical order. Unlike CLUSTER and VACUUM FULL, it works online, without holding an exclusive lock on the processed tables during processing.

Constraints

• Only the root user can use pg_repack.
• The target table must have a primary key or at least one unique index on a NOT NULL column.
• Available storage equal to at least twice the size of the target table (and its indexes) is required.
• No operations can be performed on temp tables or tables with GiST indexes.
• While pg_repack is running, no DDL commands other than VACUUM and ANALYZE can be executed on target tables.
• You must deploy a client locally to use pg_repack. For details, see the official documentation at https://reorg.github.io/pg_repack/.

How to Use

• Install the extension.

  select control_extension('create', 'pg_repack');

• Delete the extension.

  select control_extension('drop', 'pg_repack');

For more information, see Installing and Uninstalling an Extension on the RDS Console and Installing and Uninstalling an Extension Using SQL Commands.

Example

Use pg_repack to repack a table.

1. Create a test table.

   create table pg_repack_test(id bigint primary key, name varchar); 

   insert into pg_repack_test select i , to_char(random()*100000, 'FM000000') from generate_series(1, 1000000) i;

   delete from pg_repack_test where id in (select i  from generate_series(1, 600000, 2) i);

   select pg_size_pretty(pg_relation_size('pg_repack_test'));

2. Repack the test table.

   pg_repack --host=<RDS_ADDRESS> --port=<DB_PORT> --dbname=<DB_NAME> --username=root --no-superuser-check --no-kill-backend -t pg_repack_test 

   • RDS_ADDRESS: IP address of the RDS DB instance.
   • DB_PORT: Port of the RDS DB instance.
   • DB_NAME: Name of the database where the pg_repack_test table is located.
3. Check the size of the repacked table.

   select pg_size_pretty(pg_relation_size('pg_repack_test'));

FAQs