What is pg_repack in PostgreSQL

Today, I will introduce you a nice extension that we can use instead of VACUUM FULL in PostgreSQL. I recommend using it in your prod environments.

VACUUM FULL command rewrites the entire table unlike the regular VACUUM command. So, you should make sure that there is enough space on your disk to write the new table. VACUUM FULL requests ACCESS EXCLUSIVE lock on every table it processes. After VACUUM FULL has locked the table, no operation can be done on that table until the VACUUM FULL operation is finished. If you don’t pass the table name as parameter to this command, VACUUM FULL will be performed on all tables in the database.

The table must be lockable for VACUUM FULL to start. So if another transaction has locked the table even with ACCESS SHARE lock, VACUUM FULL will lock other tables and wait for the locks in that table to be released. In some cases this situation will cause locking in tables forever.

For this reason, it is very difficult and risky to use Vacuum Full in our systems with heavy workload. In this case, you can use the PG_repack extentsion instead of Vacuum Full.

Pg_Repack Requirements

There are two requirements to use the Pg_Repack extension.
1) Only superuser users can use this extension.
2) There must be PRIMARY KEY in the table, or Unique Index on a non-Null column in the table.

Install pg_repack

Let’s install pg_repack. (Since we will install on PostgreSQL 11 version, we download pg_repack11.)

Then we switch to psql and create the Extension.

Now, let’s repack all the tables in the pagila database.

Run pg_repack for a specific table

We started the repack process on all tables in Pagila. What command would we run if we just wanted to repack the rental table?

We can specify the table to repack with the -t command.

You can also see other parameters to be used in repack with the –help command.

NOTE: You can set the number of workers with the -j parameter and run parallel workers when you want to use more than one worker in large tables.

Thank you.