There is unfortunately no best practice when you should execute “VACUUM FULL”. The extension pg_freespacemap however gives you suggestion.
The following query shows the average freespace ratio of the table you want to know.
testdb=# CREATE EXTENSION pg_freespacemap;
CREATE EXTENSION
testdb=# SELECT count(*) as "number of pages",
pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
FROM pg_freespace('pgbench_accounts');
number of pages | Av. freespace size | Av. freespace ratio
-----------------+--------------------+---------------------
1640 | 99 bytes | 1.21
(1 row)
As the result above, You can find that there are few free spaces.
If you delete almost tuples and execute VACUUM command, you can find that almost pages are spaces ones.
testdb=# DELETE FROM pgbench_accounts WHERE aid %10 != 0 OR aid < 100;
DELETE 90009
testdb=# VACUUM pgbench_accounts;
VACUUM
testdb=# SELECT count(*) as "number of pages",
pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
FROM pg_freespace('pgbench_accounts');
number of pages | Av. freespace size | Av. freespace ratio
-----------------+--------------------+---------------------
1640 | 7124 bytes | 86.97
(1 row)
The following query inspects the freespace ratio of each page of the specified table.
testdb=# SELECT *, round(100 * avail/8192 ,2) as "freespace ratio"
FROM pg_freespace('pgbench_accounts');
blkno | avail | freespace ratio
-------+-------+-----------------
0 | 7904 | 96.00
1 | 7520 | 91.00
2 | 7136 | 87.00
3 | 7136 | 87.00
4 | 7136 | 87.00
5 | 7136 | 87.00
....
After executing VACUUM FULL, you can find that the table file of pgbench_accounts has been compacted.
testdb=# VACUUM FULL pgbench_accounts;
VACUUM
testdb=# SELECT count(*) as "number of pages",
pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
FROM pg_freespace('pgbench_accounts');
number of pages | Av. freespace size | Av. freespace ratio
-----------------+--------------------+---------------------
164 | 0 bytes | 0.00
(1 row)