# VACUUM

***

In PostgreSQL, **VACUUM** is the essential process for managing storage and maintaining performance. Because of how Postgres handles data updates (using a system called MVCC), "deleted" data isn't actually removed from the disk immediately.

***

### Why VACUUM is Necessary: MVCC & Bloat

Postgres uses **Multi-Version Concurrency Control (MVCC)**. When you **DELETE** a row, the database simply marks it as "invisible" to future transactions. When you **UPDATE** a row, Postgres marks the old version as invisible and inserts a brand-new version (**a new tuple**).

* **Dead Tuples**: These invisible, old versions of rows are called "dead tuples."
* **Bloat**: If dead tuples aren't cleaned up, your table files grow larger and larger, even if the actual amount of "live" data stays the same. This makes queries slower because the engine has to scan through wasted space.

***

### Types of VACUUM

There are three main "levels" of vacuuming, ranging from standard maintenance to a total rebuild.

#### Standard `VACUUM`

This is the most common form. It scans the table and marks the space occupied by dead tuples as **available for new data**.

* **Locking**: It does **not** lock the table. You can continue to read and write data while it runs.
* **Disk Space**: It usually does **not** return space to the Operating System. It just keeps the space inside the database file for future use.

#### `VACUUM ANALYZE`

This performs a standard vacuum and then immediately runs **ANALYZE**.

* **Purpose**: It updates the **statistics** about the table (how many rows, common values, etc.).
* **Importance**: The Query Planner uses these statistics to decide whether to use an Index Scan or a Table Scan. Without regular `ANALYZE`, the planner makes bad, slow decisions.

#### `VACUUM FULL`

This is the "nuclear option." It creates a brand-new, compacted version of the table on disk and deletes the old, bloated version.

* **Locking**: It requires an **Access Exclusive Lock**. The table is completely unusable (no reads or writes) while this runs.
* **Disk Space**: It **does** return all reclaimed space to the Operating System.
* **Warning:** Only use this during maintenance windows when the table is extremely bloated (e.g., 50% or more dead space).

***

### The Visibility Map

To make standard vacuuming faster, Postgres uses a **Visibility Map**. This is a small file that tracks which "pages" of the table only contain live tuples. `VACUUM` will skip those pages entirely, significantly reducing the amount of work the database has to do.

***

### Practical Commands

| **Command**                  | **Action**                                | **Impact on Performance**                  |
| ---------------------------- | ----------------------------------------- | ------------------------------------------ |
| `VACUUM;`                    | Reclaims space for internal reuse.        | Minimal (can run during the day).          |
| `VACUUM ANALYZE my_table;`   | Reclaims space + updates optimizer stats. | **Recommended** after large data loads.    |
| `VACUUM FULL my_table;`      | Rebuilds the entire table file.           | **High** (Table is locked until finished). |
| `VACUUM (VERBOSE, ANALYZE);` | Shows detailed logs of what was cleaned.  | Useful for debugging bloat issues.         |

***

### Summary: The Lifecycle of a Row

1. **Insert:** A live tuple is created.
2. **Update/Delete**: The tuple is marked "dead" but stays on the disk.
3. **Vacuum**: The engine sees the dead tuple and marks that spot on the disk as "Available."
4. **New Insert:** The engine places a new live tuple in that "Available" spot instead of growing the file.

***