Manual VACUUM in Postgres

Introduction

As mentioned in the previous article, we explored Autovacuum and Autoanalyze, but they have limitations in that they do not automatically run after changing just a few rows of data, requiring a specific threshold to be met.
Causes
- Because operations to check for Dead Tuples and update statistics data require significant processing costs, handling it this way helps Postgres save CPU and Disk I/O.
- If data changes have not reached the threshold, meaning the volume of changes is insignificant compared to the entire table, the Query Planner can still provide an effective solution.

Why Manual VACUUM Is Needed

When operating in production, many factors can prevent Autovacuum from working effectively, including the following causes:

Long-Running Transactions
- This is the most common cause because the MVCC mechanism of Postgres operates on the principle that Vacuum cannot reclaim any Dead Tuple if it remains visible to an active Transaction.
- Specific scenario:
  - If you start a Transaction (Tx1) with an isolation level of Repeatable Read/Serializable, it will operate with the current data snapshot.
  - When another Transaction executes an UPDATE/DELETE on data and successfully commits, it generates Dead Tuples.
  - However, within Tx1, you can still SELECT row data from before that UPDATE/DELETE, even though those rows are now Dead Tuples.
  - Consequently, running VACUUM will not reclaim Dead Tuples snapshot by Tx1, as that data might still be used by Tx1.
Database Configuration Falling Behind the Write Rate
- The default configuration of Autovacuum in Postgres is designed safely to avoid overloading system CPU/Disk I/O.
- For High-throughput systems with high UPDATE/DELETE frequencies, this configuration is too slow.
- Rate-limited cleanup (Throttling): Postgres uses the parameter autovacuum_vacuum_cost_limit. When Autovacuum reaches this cost threshold, it is forced to pause for a duration defined by autovacuum_vacuum_cost_delay.
- As a result, the rate at which the application produces Dead Tuples (such as 10,000 rows/sec) is significantly faster than the rate at which Autovacuum cleans them (such as 1,000 rows/sec).
Default Scale Factor Formula Unsuited for Ultra-Large Tables
- The default threshold to trigger Autovacuum on a table is: {Threshold} = 50 + (0.2 x {Total Rows}).
- For a table with 1,000 rows, it requires 250 Dead Tuples for Autovacuum to run immediately, which is highly effective.
- For a table with 50 million rows, {Threshold} = 50 + (0.2 x 50,000,000) = 10,000,050 Dead Tuples.
  - The result is that the table must accumulate over 10 million Dead Tuples before Autovacuum triggers.
  - Throughout the accumulation of these 10 million rows, the system suffers severe performance degradation from consuming storage and reading useless Dead Tuples.

These causes lead to Dead Tuples accumulating over time to excessive levels without being reclaimed, resulting in severe Table Bloat.

Manual Execution Commands

When excessive Dead Tuples accumulate without cleanup, it leads to Table Bloat. You must proactively run commands to clean up and update statistics manually:

ANALYZE table_name: Updates statistics so the Planner has sufficient information to execute queries efficiently.
VACUUM table_name: Checks for Dead Tuples no longer used anywhere, then updates the Free Space Map to allow subsequent INSERT rows to override those Dead Tuples.
- Running this command does not reduce disk volume immediately, as the disk allocation remains unchanged.
- If you wonder why INSERT operations do not automatically overwrite Dead Tuples without running this command first:
  - During INSERT, Postgres does not inspect every row to verify if it is a Dead Tuple to overwrite, as that would severely impact performance.
  - This command processes Dead Tuples in the Heap and Index Entry, removing the TID pointing from the Index to the Heap so Index scans avoid retrieving Dead Tuples.
  - Dead Tuples in the Heap are logged in the Free Space Map for future INSERT overrides.
  - Unused Index entries can then be merged as nodes in the B-Tree.
VACUUM ANALYZE table_name: Combines both operations above.
VACUUM (INDEX_CLEANUP ON) table_name: The explicit form of VACUUM table_name, cleaning up Dead Tuples in both Heap and Index Entries.
VACUUM (INDEX_CLEANUP OFF) table_name: Skips index cleanup to run faster.
- Useful when dataset size is extremely large and you want to clean Dead Tuples first.
- Or when you plan to ReIndex later, making index cleanup redundant.
VACUUM FULL table_name: Reconstructs the table entirely.
- Creates a brand new table file on disk, copies all Live Tuples to the new file and completely deletes the old file.
- Benefit: Reclaims 100% of unused disk space back to the OS.
- Drawback: Takes an Exclusive Lock on the table, blocking all queries.
VACUUM FREEZE table_name: Freezes row data to prevent Transaction ID Wraparound.
- When modifying data, the xmin of a row records the XID of the active Transaction. However, XID is limited to a 32-bit integer (maximum 2^32), wrapping around to zero when exceeded.
- At this point, Postgres cannot determine whether a newly created XID (smaller number) comes before or after an older XID (larger number) to display data (since default visibility checks require xmin=XID to be smaller than the current XID).
- Freezing data sets the HEAPTUPLE_XMIN_FROZEN flag on old rows so Postgres treats them as older than any active XID during SELECT queries.
- Two types of Freeze operations:
  - Lazy Freeze: Runs alongside Autovacuum, freezing rows older than the vacuum_freeze_min_age setting.
  - Full Table Freeze: Executed manually via VACUUM FREEZE table_name, scanning the entire table to freeze all Live Tuples, which runs slower than Lazy Freeze.
VACUUM (VERBOSE) table_name: Outputs detailed execution logs (number of Dead Tuples removed, pages freed, etc.). Used for debugging and monitoring.

Detail

VACUUM config

To view VACUUM configuration details, run:

SELECT 
    name, 
    setting, 
    unit, 
    short_desc 
FROM pg_settings 
WHERE name IN (
  'autovacuum_vacuum_cost_limit',
  'autovacuum_vacuum_cost_delay',
  'vacuum_cost_page_hit', 
  'vacuum_cost_page_miss', 
  'vacuum_cost_page_dirty'
);

SHOW vacuum_cost_limit;

vacuum_cost_page_hit: Default = 1 point, finding a data page directly in RAM (Shared Buffers).
vacuum_cost_page_miss: Default = 2 points, reading a data page from disk.
vacuum_cost_page_dirty: Default = 20 points, modifying a data page and writing it back to disk.
autovacuum_vacuum_cost_delay: Whenever the Autovacuum process reaches the cost limit, it pauses for 2ms before continuing cleanup.
- This is the default PostgreSQL setting, where 2ms enables reasonably fast execution and reduces bloat accumulation.
autovacuum_vacuum_cost_limit: The maximum I/O cost limit (resource contention threshold) that VACUUM (or AUTOVACUUM) can consume in a single cycle before pausing.
- This acts as a throttle, preventing cleanup processes from exhausting Disk I/O and slowing down active application traffic.
- If the value shows -1, it inherits the Global configuration, which can be verified using the SHOW command.
- During execution, PostgreSQL accumulates cost points: {vacuum_cost_page_hit + vacuum_cost_page_miss + vacuum_cost_page_dirty = Total Accumulated Points} >= {autovacuum_vacuum_cost_limit}.
  - Once points reach the vacuum_cost_limit (such as 200 points):
  - The VACUUM process pauses for the duration specified by vacuum_cost_delay (such as 2 ms).
  - After the delay, accumulated points reset to 0 and VACUUM resumes.
- Configured impacts:
  - A LOW value (such as 200) cleans slowly and takes longer, but remains safe with minimal impact on application performance.
  - A HIGH value (such as 1000 - 2000) cleans quickly and reduces Table Bloat, but demands high I/O and may cause latency on slower disks.

Long-Running Transactions

-- Tx1
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM test

-- Statement 2
DELETE FROM test

This example illustrates why Autovacuum fails to proceed:

You execute Tx1, followed by Statement 2.
Statement 2 deletes all rows in the test table.
Because Tx1 uses TRANSACTION ISOLATION LEVEL REPEATABLE READ, it continues to read data as it existed before deletion. These rows become Dead Tuples and VACUUM cannot remove them.
Checking the table state shows Dead Tuples at 100% because Autovacuum cannot run:

You must identify the blocking Transaction and perform a COMMIT/ROLLBACK. Running manual Vacuum commands will not reclaim dead space while the transaction remains active.
Running VACUUM with an open transaction yields no reclaimed Dead Tuples:

Closing the Transaction allows Dead Tuples to be cleaned up successfully:

Manual VACUUM

-- Statement 1
DELETE FROM test WHERE col_smallint = 1

-- Query 2
SELECT count(*) FROM test
SELECT reltuples AS estimated_row_count FROM pg_class WHERE relname = 'test'

-- Query 3
SELECT 
    relname AS table_name,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples,
    ROUND(
        (n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0)) * 100, 2
    ) AS dead_pct_ratio,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'test';

-- Statement 4
ANALYZE test
VACUUM test
VACUUM ANALYZE test

VACUUM (INDEX_CLEANUP ON) test
VACUUM (INDEX_CLEANUP OFF) test
VACUUM FREEZE test
VACUUM FULL test
VACUUM (VERBOSE) test

Execute Statement 1 to DELETE row data, generating Dead Tuples.
Run Query 2 to check remaining rows, observing the discrepancy between actual row counts and the estimate in pg_class.
Because ANALYZE has not run, statistics in pg_class remain unupdated, differing from exact COUNT results.