Using 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 a unique total index on a NOT NULL column.
• Performing a full-table repack requires free disk space about twice as large as the target table and its indexes.
• pg_repack cannot reorganize temp tables or cluster tables by GiST indexes.
• You will not be able to perform DDL commands of the target table except VACUUM or ANALYZE while pg_repack is working.
• pg_repack can be used only after a client is deployed locally. For details, see the official documentation at https://reorg.github.io/pg_repack/.

How to Use

• Install the plugin.

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

• Delete the plugin.

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

Example

Use pg_repack to repack a table.

1. Create a test table pg_repack_test.

   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