VACUUM in PostgreSQL is a maintenance operation used to reclaim storage by removing dead tuples from tables and indexes. It helps to optimize data retrieval by ensuring that the database runs efficiently, especially after many updates or deletes. By cleaning up the space occupied by obsolete data, VACUUM improves the performance of future queries and prevents excessive disk space usage. It’s essential to regularly perform VACUUM operations to maintain database health, although it may temporarily lock tables, impacting concurrent operations.

1. Check the tables to identify which table need to release space

SELECT 
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) AS indexes_size
FROM pg_tables 
WHERE schemaname = 'public' 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

2. Choose the top table and check its size again. In my example i choose orders table

SELECT pg_size_pretty(pg_total_relation_size('orders')) as before_vacuum;

3. Now perform the VACUUM operation on the table orders

VACUUM FULL rebuilds the whole table, removes all unused space, and returns disk space to the operating system.

\timing on
VACUUM FULL VERBOSE orders;

4. Check the size again after VACUUM operation.

SELECT pg_size_pretty(pg_total_relation_size('orders')) as after_vacuum;