Checking for bloat

The following SQL query examines each table in the XML schema and identifies dead rows (tuples) that waste disk space:

SELECT schemaname || '.' || relname as tuplename, 
    n_dead_tup, 
    (n_dead_tup::float / n_live_tup::float) * 100 as pfrag 
FROM pg_stat_user_tables 
WHERE schemaname = 'xml' and n_dead_tup > 0 and n_live_tup > 0 order by pfrag desc;

If this query returns a high percentage (pfrag) of dead tuples, you can use the VACUUM command to reclaim space.

To monitor data sizes before and after transactions, run the following query in the shell after connecting to a specific database:

SELECT pg_size_pretty(pg_relation_size('table_name'));