pg_repack

pg_repack Overview

The pg_repack extension is used to clear the table space online, thereby effectively solving the problem of table expansion caused by the operation that a large amount of data in a table is updated. pg_repack does not need to acquire an exclusive lock, which is more lightweight than CLUSTER or VACUUM FULL.

Install pg_repack

For details, please refer to gs_install_plugin or gs_install_plugin_local.

Use pg_repack

1. Connect to the database and create a user.

   gsql postgres -r -p 5001

   create user test password 'Test123456' login sysadmin;

2. In the database, create extension pg_repack.

   create extension pg_repack;

3. Create the pg_repack table which must contain a primary key.

   create table repack_test(id int primary key, name name);

4. Run \dx to check whether pg_repack is successfully created.

   \dx

5. Insert 2,000,000 rows of data into the table repack_test.

   insert into repack_test select generate_series(1,2000000),'a';

6. Check the size of the table repack_test.

   select pg_size_pretty(pg_relation_size('repack_test'));

7. Delete some data and then check the table size.

   Delete from repack_test where id>1000000;

   The figure shows that the table size does not change after 1,000,000 rows of data is deleted. In this case, pg_repack needs to be used.

8. Use the pg_repack tool.

   pg_repack -d postgres -t test.repack_test -h 127.0.0.1 -U test -p 5001

   Parameter introduction:

   • -d dbname (Database name)
   • -t test.repack_test -h hostip (Server IP address)
   • -U user (Username)
   • -p port (Port)
   • -e (Output all SQLs executed when pg_repack is used)
   • -E DEBUG (Set the log level of the output information to DEBUG)

   Check the size of the table repack_test.

   select pg_size_pretty(pg_relation_size('repack_test'));