Firstly, I would suggest using pgstattuple to obtain tuple-level statistics.
pgstattuple returns a relation's physical length, percentage of “dead”
tuples, and other info. This may help users to determine whether
vacuum is necessary or not.
For example:
create extension pgstattuple ;
create table my_table ( id int , name text);
insert into my_table select a, md5(a::text) from generate_series(1, 1e7)a;
-- size of my_table
Schema | Name | Type | Owner | Size | Description
--------+----------+-------+----------+--------+-------------
public | my_table | table | postgres | 651 MB |
-- dead_tuple_percent = 0, pgstattuple will not lock your table
SELECT tuple_percent, dead_tuple_count, dead_tuple_percent, free_space, free_percent FROM pgstattuple('my_table');
tuple_percent | dead_tuple_count | dead_tuple_percent | free_space | free_percent
---------------+------------------+--------------------+------------+--------------
89.35 | 0 | 0 | 338776 | 0.05
-- let update 50% rows
update my_table set name = name || id where id < 5000000;
-- now, dead_tuple_percent = 28.63%
tuple_percent | dead_tuple_count | dead_tuple_percent | free_space | free_percent
---------------+------------------+--------------------+------------+--------------
60.43 | 4999999 | 28.63 | 1834236 | 0.17
-- size of my_table has increased
Schema | Name | Type | Owner | Size | Description
--------+----------+-------+----------+---------+-------------
public | my_table | table | postgres | 1016 MB |
-- try to vacuum full
vacuum full my_table;
-- after that, dead_tuple_percent = 0 and size of my_table has reduced
tuple_percent | dead_tuple_count | dead_tuple_percent | free_space | free_percent
---------------+------------------+--------------------+------------+--------------
88.92 | 0 | 0 | 1664780 | 0.23
Schema | Name | Type | Owner | Size | Description
--------+----------+-------+----------+------------+-------------
public | my_table | table | postgres | 691 MB |
Secondly, if you are in production environment, I would suggest using pg_repack to reclaim disk without locking your table.
pg_repack is a PostgreSQL extension which lets you remove bloat from
tables and indexes, and optionally restore the physical order of
clustered indexes. Unlike CLUSTER and VACUUM FULL it works online,
without holding an exclusive lock on the processed tables during
processing. pg_repack is efficient to boot, with performance
comparable to using CLUSTER directly.
For instance:
/usr/pgsql-11/bin/pg_repack -d postgres -U postgres -n -t my_table &