This is a brief use case of pgstattuple extension to check a table bloat.
The PostgreSQL contrib package provides the pgstattuple extension which allows you to perform tables and indexes analysis, including computing the amount of free space and dead tuples.
As every other extension, it can be activated executing:
test=# CREATE EXTENSION pgstattuple;
CREATE EXTENSION
pgstattuple returns details like table length, number of live tuples in a table, total length of live tuples, number of dead tuples, free space in a table and much more.
For test purpose a 10.000 rows table is created:
test=# CREATE TABLE t_test AS SELECT * FROM generate_series(1, 10000);
SELECT 10000
In this example pgstattuple returns the following data:
test=# SELECT * FROM pgstattuple('t_test');
table_len | 368640 # physical table length in bytes
tuple_count | 10000 # number of live tuples
tuple_len | 280000 # total tuples length in bytes
tuple_percent | 75.95 # % of live tuples
dead_tuple_count | 0 # number of dead tuples
dead_tuple_len | 0 # total dead tuples length in bytes
dead_tuple_percent | 0 # % of dead tuples
free_space | 7380 # free space in bytes
free_percent | 2 # % of free space
Let's add some bloat, deleting one third of the table's rows:
test=# DELETE FROM t_test WHERE generate_series % 3 = 0;
DELETE 3333
If you run it promptly, before autovacuum kicks in and cleans up bloat, pgstattuple will show:
test=# SELECT * FROM pgstattuple('t_test');
table_len | 368640
tuple_count | 6667
tuple_len | 186676
tuple_percent | 50.64
dead_tuple_count | 3333
dead_tuple_len | 93324
dead_tuple_percent | 25.32
free_space | 7380
free_percent | 2
Note: while the same value of deleted rows has been moved from tuple_count to dead_tuple_count, the table size on disk is still the same. This because DELETE does not shrink a table on disk, it merely marks tuples as dead.
During a VACUUM the dead rows will be marked as "free space" that can be reused to store new rows inside the table, assuming the database and global xmin permit removal. Sometimes PostgreSQL has to keep dead rows around for a while.
VACUUM VERBOSE will report counts of rows that could be marked free, are dead but cannot be marked free yet, or are still in use.
This is what pgstattuple show after a VACUUM, assuming it could remove the rows:
test=# SELECT * FROM pgstattuple('t_test');
table_len | 368640
tuple_count | 6667
tuple_len | 186676
tuple_percent | 50.64
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 114036
free_percent | 30.93
NOTE: in this example, an autovacuum process started a moment after executing the DELETE action, because 3333 dead tuples exceed the default limit value ruled by the autovacuum formula:
50 + 6667 * 0.2 = 1343