Use pg_repack to do a VACUUM FULL without holding an exclusive lock during processing

You can use pg_repack to do a VACUUM FULL without holding an exclusive lock during processing.

There is still a need of one exclusive lock at the beginning and the end of the repacking process. But in the time between, you can use the affected table.

Warning

pg_repack writes a copy of the whole table. The database server needs to have at least ($size_of_biggest_table * 2) + some buffer free disk space available to have enough space for this copy and also the generated WALs.

To use it you have to do the following:

1. Install pg_repack, e.g. for Ubuntu install the package according to your PostgreSQL Server version. There is no need to restart the PostgreSQL Server.

Copy$ sudo apt-get install postgresql-16-repack

2. Create the extension in the database in which you want to use pg_repack

Copy$ psql
postgres=# \c example_db
example_db=# CREATE EXTENSION pg_repack;

3. Run pg_repack on your desired table(s)

For most cases we wan't to use -D to not interfere with the applications using the database:

-D, --no-kill-backend don't kill other backends when timed out

It can happen that applications use a table so frequently that pg_repack isn't able to get a short lock to start the process. Before we run pg_repack without -D we need to speak to the developers to let them confirm it's ok to kill other backends (connections).

Copy# e.g. run this as `postgres` system user to repack one table
$ pg_repack -D -t "public.footable" "example_db"
INFO: repacking table "public.footable"
# e.g. run this as `postgres` system user to repack every table in the database
$ pg_repack -D "example_db"

4. Remove the extension again if it's only a one time thing (optional)

Copy$ psql
postgres=# \c example_db
example_db=# DROP EXTENSION pg_repack;

Copysudo apt-get remove postgresql-16-repack