In this lesson, you will learn how "bloat" happens in your database tables and how to reclaim that wasted storage space.
The Problem: The "Ghost" Data
When you update or delete a row in a relational database, the system doesn't always physically remove that data from the disk immediately. Instead, it often marks the old row version as "dead" or invisible to future queries. This is part of how databases handle concurrency and isolation levels—ensuring that a query currently reading a row doesn't break just because another user is updating it.
Over time, frequent updates and deletions create "holes" in your table files. This accumulation of dead space is called Table Bloat. While the table appears to have a certain number of active rows, the file size on your hard drive remains artificially large, which slows down sequential scans and wastes storage.
The Solution: Vacuuming
To fix bloat, databases provide a maintenance operation commonly known as VACUUM. When you run a vacuum command, the database engine scans the table, identifies those "dead" rows, and clears them out. This makes the space available again for new data, effectively "shrinking" the working size of your table without you needing to drop or recreate it.
In many systems, you can trigger this manually or configure an automated background process. A simple SQL command often looks like this:
-- Reclaims space for the specific table
VACUUM ANALYZE my_large_table;
Note: The ANALYZE part is important because it updates the database statistics we discussed earlier, ensuring the query optimizer knows the table size has changed.
When to Perform Maintenance
You shouldn't just run VACUUM constantly, as it requires system resources. Instead, monitor your table bloat by checking your schema metadata. If you notice a high ratio of dead rows compared to live rows, it is time to perform maintenance. By keeping your tables "lean," you ensure that the database engine spends less time skipping over dead data and more time retrieving the information your users actually need.
Key Takeaway: Table bloat occurs when updated or deleted rows leave behind empty gaps; running vacuum operations identifies and clears this dead space to restore storage efficiency.