May 8, 2026
Summary: in this tutorial, you will learn how to check database bloat in PostgreSQL.
Table of Contents
Introduction
You can use PostgreSQL Multiversion Concurrency Control (MVCC) to help preserve data integrity. PostgreSQL MVCC works by saving an internal copy of updated or deleted rows (also called tuples) until a transaction is either committed or rolled back. This saved internal copy is invisible to users. However, table bloat can occur when those invisible copies aren’t cleaned up regularly by the VACUUM or AUTOVACUUM utilities. Unchecked, table bloat can incur increased storage costs and slow your processing speed.
Query table bloat
PostgreSQL may have an unreasonable number of dead tuples accumulate without autovacuum cleaning them up. However, even if there are way too many dead rows, VACUUM may clean them up eventually. But that would leave an undue amount of free space (“bloat”) inside a table. PostgreSQL doesn’t track the empty space in a table, which makes it difficult to monitor that free space. There are some monitoring queries out there that try to guess the amount of free space in a table, but I find that they get it wrong too often. The only reliable way to monitor bloat is to use the pgstattuple extension:
SELECT t.table_name,
s.free_percent,
s.free_space
FROM (SELECT c.oid::regclass AS table_name,
CASE WHEN s.size > 163840
THEN FALSE
ELSE TRUE
END AS tiny
FROM pg_class AS c
CROSS JOIN LATERAL pg_relation_size(c.oid) AS s(size)
WHERE c.relkind IN ('r','t')
/* prevent subquery flattening */
OFFSET 0) AS t
CROSS JOIN LATERAL pgstattuple(t.table_name) AS s
WHERE NOT t.tiny
ORDER BY free_percent DESC
LIMIT 20;
This query lists the 20 tables with the highest percentage of free space. It ignores tiny tables (less than 20 blocks), because they would distort the statistics.
This query will perform a sequential scan of all your tables, so you should run it only occasionally during a quiet time. You can consider using the function pgstattuple_approx() instead of pgstattuple(), as that function will only scan a part of the table to obtain an approximate result.
The amount of bloat you have is relative to the size of your database. If you’re running a smaller database with only 20 GB of data total this amount may be noticeable and useful to clean up up, but you also may be able to skate by for a while. If you are at several hundred GB of data stored within have bloat that is 100 GB or more and a significant percentage of your database in that case it can be really worth cleaning up.