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.

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.

$ sudo apt-get install postgresql-16-repack

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

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

3. Run pg_repack on your desired table(s)

# e.g. run this as `postgres` system user
$ pg_repack -t "public.footable" "example_db"
INFO: repacking table "public.footable"

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

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

sudo apt-get remove postgresql-16-repack