Those familiar with PostgreSQL know how it internally manages blocks for storing data and how when you delete some entry it is only marked dead (dead tuples). In order to reclaim space VACUUM needs to be run.

Vacuuming database won’t give that space back to the operating system, instead, it will just reclaim it for further use. If you specify VACUUM FULL in that case PostgreSQL will return free space back to the operating system, but running such action requires locking the tables which depending on the database size and the time it takes might not be optimal solution to run anytime.

There is an in-between solution called pg_repack which is basically running full vacuum without doing exclusive lock on on your tables.

In order for it to work you need to first install it. On my Ubuntu server which has PGDG repository enabled it was a matter of:

apt-get update
apt-get install postgresql-11-repack

After that you also need to enable extension on specific database you want to repack:

sudo -u postgres psql DBNAME
CREATE EXTENSION pg_repack;
\q

And now you’re ready to run repack:

sudo -u postgres pg_repack DBNAME