Alright, let’s get our hands dirty with the two types of VACUUM. This is where most people get tripped up, because the difference isn’t just “one does more.” It’s a fundamental trade-off between reclaiming space and preserving performance. You need to know which tool to grab and when, or you’re going to make a mess.

The Standard VACUUM: Your Daily Workhorse

Think of the standard VACUUM (without the FULL) as routine maintenance. It’s the equivalent of taking out the kitchen trash. It doesn’t magically create more cupboard space, but it prevents your kitchen from becoming a biohazard.

What does it actually do? It marks dead rows (rows deleted by UPDATE or DELETE statements) as available for reuse by future transactions. This is crucial. It doesn’t give space back to the operating system; it gives it back to the table itself. This process also updates the visibility map, which is critical for performance—it helps VACUUM itself run faster next time and supercharges index-only scans.

You should be running this regularly, preferably via autovacuum (which we’ll grill in another section), on all your tables. Here’s the basic, no-frills way to run it:

VACUUM your_table_name;

Or, to vacuum every table in the database (this is what your autovacuum daemon is doing behind the scenes):

VACUUM;

The beauty of standard VACUUM is that it’s an online operation. It doesn’t take an exclusive lock on the table. Reads and writes can hum along while it works. It’s generally safe to run during peak hours, though it might add a bit of CPU/I/O load.

VACUUM FULL: The Sledgehammer

Now, VACUUM FULL is a different beast. It’s the equivalent of moving everything out of your house, burning the house down, and rebuilding it from the ground up just to get rid of a few dust bunnies. Dramatic? Yes. Sometimes necessary? Unfortunately, also yes.

VACUUM FULL doesn’t just mark space as reusable; it physically rewrites the entire table into a new, pristine database file. This actually returns the free space to the operating system. The problem? It requires an exclusive lock on the table. While it’s running, no one can read from or write to that table. For a large table, this can mean minutes or even hours of downtime. It’s a blocking operation.

You use this when a table has become so horrifically bloated with free space that standard VACUUM can’t keep up. You’ve got a 100GB table where 80GB is just empty, reusable space. Running VACUUM won’t shrink the on-disk size. Running VACUUM FULL will.

VACUUM FULL your_ridiculously_bloated_table;

The Critical Trade-off: Reuse vs. Reclaim

This is the core of the issue. You’re choosing between reusing space and reclaiming space.

  • VACUUM (Reuse): “I’ll keep this free space within the table. It’s a bit messy, but the next INSERT or UPDATE can use this space immediately without asking the OS for more.” This is fantastic for performance because allocating new disk space is expensive. Reusing existing space within the table is cheap.
  • VACUUM FULL (Reclaim): “I’m throwing all this free space back to the OS. The table is now lean and mean on disk.” This is good for storage but terrible for immediate performance. The next INSERT will have to go ask the OS for disk space all over again.

So, if you VACUUM FULL a table that’s about to get a massive data load, you’ve just shot yourself in the foot. You traded a pre-allocated, efficient space pool for a temporary reduction in disk usage, and now the database has to slowly and painfully re-allocate that space row by row.

Best Practices and Pitfalls

  1. Default to VACUUM: Make autovacuum your best friend. Let it handle the day-to-day. Only intervene manually with VACUUM if you know you’ve just had a massive delete operation and can’t wait for the autovacuum run.
  2. VACUUM FULL is a Last Resort: Never schedule VACUUM FULL as a routine job. Use it only when monitoring shows a specific table has a disastrously high bloat percentage. Tools like pgstattuple are your friends here.
  3. The Lock is a Killer: Never run VACUUM FULL on a production table during business hours without understanding the lock duration. Test on a staging server with similar data sizes first. I’m not kidding. Your users will revolt.
  4. Consider pg_repack Instead: The open-source tool pg_repack is a genius workaround. It does what VACUUM FULL does—rewrites the table to remove bloat—but it does it online, without a long-term exclusive lock. It’s magic. Use it. Your sanity will thank you. The designers of PostgreSQL gave us MVCC, and the price was VACUUM; the community gave us pg_repack to help pay that bill.