What is the difference between VACUUM and VACUUM FULL?
VACUUM marks dead space reusable and runs alongside normal reads and writes, but usually does not shrink the file. VACUUM FULL rewrites the table into a new compact file, returning space to the OS, but takes an ACCESS EXCLUSIVE lock for the whole run.
Explain it simply
Plain VACUUM is tidying a classroom between periods: students can stay, and free desks are noted for reuse. VACUUM FULL is emptying the classroom, rebuilding it with only the needed desks, and shutting the door until it is done.
After plain VACUUM, free space inside the table is reused by later inserts. The file only shrinks if the empty pages are at the very end. VACUUM FULL copies live rows into a brand-new file, so the table becomes as small as possible, but nobody can read or write it meanwhile, and it needs extra disk for the copy.
Example
VACUUM (VERBOSE, ANALYZE) orders; -- routine, non-blocking
VACUUM FULL orders; -- exclusive lock, rewrites table
SELECT pg_size_pretty(pg_total_relation_size('orders'));For big live tables people usually use the pg_repack extension, which rebuilds the table with only a brief lock.
What to say in the interview
"VACUUM reclaims space for reuse without blocking queries. VACUUM FULL rewrites the table and returns space to the OS but locks it exclusively, so I avoid it on a live system and use pg_repack, or run it in a maintenance window."
Common mistakes
- Running VACUUM FULL on a busy production table at midday.