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:
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
pg_repack
$ psql
postgres=# \c example_db
example_db=# CREATE EXTENSION pg_repack;
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"
$ psql
postgres=# \c example_db
example_db=# DROP EXTENSION pg_repack;
sudo apt-get remove postgresql-16-repack