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

1. Access Download page of the MogDB official website, download the pg_repack extension for the version you need.

2. Unpack the package, for example:

   tar -xzvf pg_repack-1.4.6-3.1.0-01-CentOS-x86_64.tar.gz

3. Go to the directory where the extension is located and execute the make install command.

   cd pg_repack/
   make install

4. Connect to the database and execute the create extension pg_repack; to use it.

   MogDB=# create extension pg_repack;
   CREATE EXTENSION

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

   Parameters：

   • -d dbname

     Specifies the database name.

   • -t test.repack_test -h hostip

     Specifies the server IP address.

   • -U user

     Specifies the username.

   • -p port

     Specifies the port.

   • -e

     Outputs all SQL statements executed during repack.

   • -E DEBUG

     Sets the log level to DEBUG.

   • -a, --all

     Specifies all tables and indexes that are to be cleared.

   • -t, --table=TABLE

     Specifies a specified table to be cleared.

   • -I, --parent-table=TABLE repack

     Specifies the parent table and all sub-tables that inherit the parent table.

   • -c, --schema=SCHEMA repack

     Specifies all tables and indexes in a specified schema.

   • -s, --tablespace=TBLSPC

     Specifies the tablespace to which a single or multiple tables that are cleared is to be migrated.

   • -S, --moveidx

     Specifies the tablespace to which the index that is cleared is to be migrated.

   • -o, --order-by=COLUMNS

     Replaces cluster keys with order by columns.

   • -n, --no-order

     Performs vacuum full to replace cluster.

   • -N, --dry-run

     Outputs all content that is cleared by pg_repack.

   • -j, --jobs=NUM

     Specifies the number of concurrent jobs. If indexes are processed, pg_repack chooses the smaller value between the number of indexes and number of concurrent jobs specified by the parameter -j as the actual number of concurrent jobs.

   • -i, --index=INDEX

     Processes the specified index.

   • -x, --only-indexes

     Processes all indexes of a specified table.

   • -T, --wait-timeout=SECS

     Sets the life cycle of a backend conflict. If there is a conflict, the program is directly ended upon timeout (s).

   • -D, --no-kill-backend

     Specifies that other backed processes and threads are not killed when the extension times out.

   • -Z, --no-analyze

     Specifies that the analyze table operation is not performed after the clearing operation.

   • -k, --no-superuser-check

     Skips the super user check on the client.

   • -C, --exclude-extension

     Skips tables that belong to a specified extension.

   • -e, --echo

     Outputs all SQL statements executed by pg_repack.

   • -E, --elevel=LEVEL

     Outputs the log level, including debug and warning.

9. Check the size of the table repack_test. The table space is released successfully.

   select pg_size_pretty(pg_relation_size('repack_test'));