Autovacuum does NOT support parallel index vacuuming

 

Overview

Autovacuum does NOT support parallel index vacuuming, unlike manual VACUUM operations. This is a fundamental architectural limitation that affects performance characteristics of background maintenance operations.

Key Limitation

Autovacuum workers always process indexes sequentially, regardless of:

• Number of indexes on the table
• Index size or complexity
• Available system resources
• max_parallel_workers or related settings

Source Code Evidence

Autovacuum Parameter Setup

In src/backend/postmaster/autovacuum.c, the autovacuum_do_vac_analyze() function explicitly sets parallel workers to zero:

static void
autovacuum_do_vac_analyze(autovac_table *tab, BufferAccessStrategy bstrategy)
{
    VacuumParams params;
    
    /* Initialize vacuum parameters */
    memset(&params, 0, sizeof(params));
    
    /* Set various vacuum options */
    params.options = VACOPT_SKIPTOAST | 
                    (dovacuum ? VACOPT_VACUUM : 0) |
                    (doanalyze ? VACOPT_ANALYZE : 0);
    
    /* CRITICAL: Autovacuum never uses parallel workers */
    params.nworkers = 0;  /* No parallel workers for autovacuum */
    
    /* Set other parameters... */
    params.freeze_min_age = freeze_min_age;
    params.freeze_table_age = freeze_table_age;
    params.multixact_freeze_min_age = multixact_freeze_min_age;
    params.multixact_freeze_table_age = multixact_freeze_table_age;
    
    /* Call vacuum with sequential-only parameters */
    vacuum(NIL, &params, bstrategy, vac_context, true);
}

Manual VACUUM vs Autovacuum Comparison

Manual VACUUM (Supports Parallel)

/* In ExecVacuum() - src/backend/commands/vacuum.c */
void
ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
{
    VacuumParams params;
    
    /* Parse PARALLEL option from user command */
    if (vacstmt->options & VACOPT_PARALLEL)
    {
        /* User can specify: VACUUM (PARALLEL 4) table_name; */
        params.nworkers = vacstmt->parallel_workers;
    }
    else
    {
        params.nworkers = 0;  /* Default: no parallel */
    }
    
    /* Manual vacuum can use parallel workers */
    vacuum(vacstmt->rels, &params, bstrategy, vac_context, isTopLevel);
}

Autovacuum (Always Sequential)

/* In autovacuum_do_vac_analyze() - src/backend/postmaster/autovacuum.c */
static void
autovacuum_do_vac_analyze(autovac_table *tab, BufferAccessStrategy bstrategy)
{
    VacuumParams params;
    
    /* Autovacuum NEVER supports parallel workers */
    params.nworkers = 0;  /* Hardcoded to 0 - no user control */
    
    /* No way to override this in autovacuum */
    vacuum(NIL, &params, bstrategy, vac_context, true);
}

Index Vacuuming Process

Sequential Index Processing in Autovacuum

When autovacuum processes indexes, it uses the sequential path in lazy_vacuum_all_indexes():

/* In src/backend/access/heap/vacuumlazy.c */
static void
lazy_vacuum_all_indexes(LVRelState *vacrel)
{
    int nindexes = vacrel->nindexes;
    Relation *indrels = vacrel->indrels;
    
    /* Check if parallel vacuum is possible */
    if (vacrel->params->nworkers > 0 && nindexes > 1)
    {
        /* PARALLEL PATH - Only for manual VACUUM */
        lazy_parallel_vacuum_indexes(vacrel);
    }
    else
    {
        /* SEQUENTIAL PATH - Always used by autovacuum */
        for (int i = 0; i < nindexes; i++)
        {
            lazy_vacuum_one_index(indrels[i], vacrel->stats,
                                 vacrel->dead_items, vacrel->old_live_tuples);
        }
    }
}

Since vacrel->params->nworkers is always 0 for autovacuum, it always takes the sequential path.

Individual Index Vacuum Function

/* Sequential index vacuum - used by autovacuum */
static void
lazy_vacuum_one_index(Relation indrel, LVRelStats *stats,
                     TidStore *dead_items, double old_live_tuples)
{
    IndexBulkDeleteResult *stats_res;
    
    /* Single-threaded index cleanup */
    stats_res = index_bulk_delete(indrel, lazy_tid_reaped,
                                 (void *) dead_items,
                                 stats->num_dead_tuples,
                                 old_live_tuples);
    
    /* Update statistics */
    if (stats_res)
    {
        stats->pages_removed += stats_res->pages_removed;
        stats->tuples_removed += stats_res->tuples_removed;
        pfree(stats_res);
    }
}

Performance Implications

Tables with Many Indexes

For tables with multiple large indexes, autovacuum performance is significantly impacted:

-- Example: Table with 8 indexes
CREATE TABLE large_table (
    id BIGINT PRIMARY KEY,
    col1 INTEGER,
    col2 TEXT,
    col3 TIMESTAMP,
    col4 JSONB,
    col5 NUMERIC,
    col6 UUID,
    col7 INET
);

CREATE INDEX idx1 ON large_table (col1);
CREATE INDEX idx2 ON large_table (col2);
CREATE INDEX idx3 ON large_table (col3);
CREATE INDEX idx4 ON large_table USING GIN (col4);
CREATE INDEX idx5 ON large_table (col5);
CREATE INDEX idx6 ON large_table (col6);
CREATE INDEX idx7 ON large_table (col7);

Autovacuum behavior:

• Processes all 8 indexes sequentially
• Total time = sum of individual index vacuum times
• Cannot utilize multiple CPU cores for index cleanup

Manual VACUUM behavior:

-- Can process indexes in parallel
VACUUM (PARALLEL 4) large_table;

• Can process up to 4 indexes simultaneously
• Total time ≈ max(individual index vacuum times)
• Utilizes multiple CPU cores

Resource Utilization Differences

Autovacuum Resource Usage

/* Autovacuum characteristics */
- Single worker process per table
- Sequential index processing
- Lower CPU utilization
- Longer vacuum duration
- Designed for minimal impact on workload

Manual Parallel VACUUM Resource Usage

/* Manual parallel vacuum characteristics */
- Leader process + multiple worker processes
- Parallel index processing
- Higher CPU utilization
- Shorter vacuum duration
- Can impact concurrent workload more significantly

Why Autovacuum Doesn't Support Parallel Processing

1. Background Process Design Philosophy

/*
 * Autovacuum is designed to be minimally intrusive:
 * - Runs in background with low priority
 * - Uses cost-based delay to throttle I/O
 * - Avoids competing for resources with user queries
 * - Parallel workers would increase resource contention
 */

2. Complexity Management

/*
 * Parallel worker management adds complexity:
 * - Dynamic shared memory allocation
 * - Inter-process communication
 * - Error handling across multiple processes
 * - Resource cleanup on worker failure
 */

3. Cost-Based Delay Coordination

In src/backend/postmaster/autovacuum.c:

/*
 * Cost-based delay balancing across workers:
 * - Autovacuum balances vacuum_cost_delay across all active workers
 * - Parallel workers within a single vacuum would complicate this
 * - Current design: one worker per table, simple cost accounting
 */
static void
AutoVacuumUpdateDelay(void)
{
    /* Rebalance cost delay across all autovacuum workers */
    int nworkers_for_balance = pg_atomic_read_u32(&AutoVacuumShmem->av_nworkersForBalance);
    
    if (nworkers_for_balance > 0)
    {
        /* Distribute delay across workers */
        VacuumCostDelay = VacuumCostDelayLimit / nworkers_for_balance;
    }
}

4. Historical Design

/*
 * Timeline of features:
 * - Autovacuum: PostgreSQL 8.1 (2005)
 * - Parallel vacuum: PostgreSQL 11 (2018)
 * 
 * Autovacuum predates parallel vacuum by 13 years
 * Retrofitting parallel support would require significant changes
 */

Workarounds and Alternatives

Manual Parallel VACUUM for Large Tables

-- Identify tables that would benefit from parallel vacuum
SELECT 
    schemaname,
    tablename,
    n_dead_tup,
    last_autovacuum,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_stat_user_tables 
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Run manual parallel vacuum during maintenance windows
VACUUM (PARALLEL 4, VERBOSE) large_table;

Vacuum and Autovacuum Implementation in PostgreSQL 18

 

Table of Contents

1. Overview
2. Core Concepts
3. Vacuum Implementation
4. Autovacuum Implementation
5. Key Data Structures
6. Vacuum Process Flow
7. Autovacuum Process Flow
8. Configuration Parameters
9. Performance Considerations
10. Troubleshooting

Overview

PostgreSQL's vacuum system is responsible for maintaining database health by:

• Reclaiming storage space from deleted tuples
• Preventing transaction ID wraparound
• Updating table statistics
• Maintaining visibility maps and free space maps
• Freezing old tuples to prevent XID wraparound

The system consists of two main components:

1. Manual VACUUM - User-initiated vacuum operations
2. Autovacuum - Automatic background vacuum processes

Core Concepts

Transaction ID (XID) Management

• PostgreSQL uses 32-bit transaction IDs that wrap around
• Old tuples must be "frozen" to prevent XID wraparound
• relfrozenxid tracks the oldest unfrozen XID in each table
• datfrozenxid tracks the oldest unfrozen XID in each database

Multi-Transaction ID (MXID) Management

• Used for row-level locking with multiple transactions
• Similar wraparound concerns as XIDs
• relminmxid and datminmxid track oldest MXIDs

Visibility and Free Space Maps

• Visibility Map (VM): Tracks which pages are all-visible and all-frozen
• Free Space Map (FSM): Tracks available free space on pages
• Used to optimize vacuum operations by skipping unnecessary pages

Vacuum Types

• Normal Vacuum: Reclaims space and updates statistics
• Aggressive Vacuum: Must advance relfrozenxid/relminmxid
• Full Vacuum: Rewrites entire table (like CLUSTER)

Vacuum Implementation

Main Entry Points

ExecVacuum() - Command Processing

Located in src/backend/commands/vacuum.c

void ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)

Responsibilities:

• Parse VACUUM command options
• Validate parameters
• Set up vacuum parameters structure
• Create memory context for vacuum operations
• Call main vacuum() function

vacuum() - Core Vacuum Logic

void vacuum(List *relations, const VacuumParams params, 
           BufferAccessStrategy bstrategy, MemoryContext vac_context, 
           bool isTopLevel)

Responsibilities:

• Expand relation list (handle inheritance)
• Determine transaction strategy
• Process each relation via vacuum_rel()
• Update database-wide statistics

Heap Vacuum Implementation

heap_vacuum_rel() - Table-Specific Vacuum

Located in src/backend/access/heap/vacuumlazy.c

Main phases:

1. Setup Phase: Initialize vacuum state, determine aggressiveness
2. Scan Phase: Scan heap pages, prune tuples, collect dead items
3. Index Vacuum Phase: Remove dead index entries
4. Heap Vacuum Phase: Mark dead items as unused
5. Cleanup Phase: Update statistics, truncate if possible

Key Functions

lazy_scan_heap() - Main Scanning Logic

• Scans relation pages using read streams
• Calls lazy_scan_prune() or lazy_scan_noprune() for each page
• Manages dead item collection and index vacuuming cycles
• Updates visibility and free space maps

lazy_scan_prune() - Page Pruning and Freezing

• Requires cleanup lock on buffer
• Prunes HOT chains and freezes tuples
• Updates visibility map bits
• Collects LP_DEAD items for index vacuuming

lazy_scan_noprune() - Lightweight Page Processing

• Only requires shared lock
• Counts tuples and collects existing LP_DEAD items
• Used when cleanup lock unavailable
• May return false to force full processing

Vacuum Phases

Phase I: Heap Scanning

• Scan relation pages sequentially
• Skip pages based on visibility map
• Prune dead tuples and freeze old tuples
• Collect TIDs of dead items in TidStore
• Update page-level visibility information

Phase II: Index Vacuuming

• Process collected dead TIDs
• Call ambulkdelete() for each index
• Remove index entries pointing to dead tuples
• May run in parallel for multiple indexes

Phase III: Heap Vacuuming

• Mark LP_DEAD items as LP_UNUSED
• Truncate line pointer arrays where possible
• Update free space map
• Set visibility map bits for newly all-visible pages

Eager Scanning Algorithm

Normal (non-aggressive) vacuums use eager scanning to freeze pages proactively:

Success Limiting

• Cap successful eager freezes to MAX_EAGER_FREEZE_SUCCESS_RATE (20%) of all-visible but not all-frozen pages
• Prevents excessive work in single vacuum cycle
• Amortizes freezing cost across multiple vacuum operations

Failure Limiting

• Use regional failure caps based on EAGER_SCAN_REGION_SIZE (4096 blocks)
• Suspend eager scanning in region after too many failures
• Configurable via vacuum_max_eager_freeze_failure_rate

Implementation Details

static void heap_vacuum_eager_scan_setup(LVRelState *vacrel, const VacuumParams params)

• Initializes eager scan state
• Calculates success and failure limits
• Sets random starting region to avoid patterns

Autovacuum Implementation

Architecture

Autovacuum uses a launcher/worker model:

• Autovacuum Launcher: Schedules and manages workers
• Autovacuum Workers: Perform actual vacuum operations

Autovacuum Launcher

AutoVacLauncherMain() - Main Launcher Process

Located in src/backend/postmaster/autovacuum.c

Responsibilities:

• Maintain database list with scheduling information
• Determine when to launch workers
• Handle worker lifecycle management
• Rebalance cost limits across workers

Database Scheduling

static void rebuild_database_list(Oid newdb)

• Builds prioritized list of databases
• Distributes vacuum times across autovacuum_naptime interval
• Considers database age and last vacuum time
• Handles wraparound emergencies with priority

Worker Management

static Oid do_start_worker(void)

• Selects database needing vacuum
• Prioritizes wraparound prevention
• Considers recent vacuum activity
• Signals postmaster to fork worker

Autovacuum Workers

AutoVacWorkerMain() - Worker Process Entry

• Connects to assigned database
• Scans pg_class for tables needing vacuum/analyze
• Applies autovacuum thresholds and settings
• Performs vacuum/analyze operations

Table Selection Logic

static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts, ...)

Vacuum thresholds:

vacuum_threshold = base_threshold + scale_factor * reltuples
insert_vacuum_threshold = base_threshold + scale_factor * reltuples * unfrozen_ratio

Analyze thresholds:

analyze_threshold = base_threshold + scale_factor * reltuples

Wraparound prevention:

• Force vacuum when relfrozenxid age exceeds autovacuum_freeze_max_age
• Force vacuum when relminmxid age exceeds autovacuum_multixact_freeze_max_age

Key Data Structures

VacuumParams

typedef struct VacuumParams {
    bits32      options;                    // VACUUM options bitmask
    int         freeze_min_age;             // Minimum age for freezing
    int         freeze_table_age;           // Age for aggressive vacuum
    int         multixact_freeze_min_age;   // MXID freeze minimum age
    int         multixact_freeze_table_age; // MXID freeze table age
    bool        is_wraparound;              // Wraparound prevention vacuum
    int         log_min_duration;           // Logging threshold
    VacOptValue index_cleanup;              // Index cleanup setting
    VacOptValue truncate;                   // Table truncation setting
    int         nworkers;                   // Parallel workers
} VacuumParams;

VacuumCutoffs

struct VacuumCutoffs {
    TransactionId relfrozenxid;    // Current table frozen XID
    MultiXactId   relminmxid;      // Current table min MXID
    TransactionId OldestXmin;      // Oldest visible XID
    MultiXactId   OldestMxact;     // Oldest visible MXID
    TransactionId FreezeLimit;     // XID freeze threshold
    MultiXactId   MultiXactCutoff; // MXID freeze threshold
};

LVRelState

typedef struct LVRelState {
    Relation    rel;                    // Target relation
    Relation   *indrels;                // Index relations
    int         nindexes;               // Number of indexes
    
    bool        aggressive;             // Aggressive vacuum?
    bool        skipwithvm;             // Use visibility map?
    
    struct VacuumCutoffs cutoffs;       // Freeze/prune cutoffs
    TidStore   *dead_items;             // Dead tuple TIDs
    
    // Statistics and counters
    BlockNumber rel_pages;              // Total pages
    BlockNumber scanned_pages;          // Pages examined
    double      new_rel_tuples;         // Estimated tuple count
    int64       tuples_deleted;         // Deleted tuples
    int64       tuples_frozen;          // Frozen tuples
    
    // Eager scanning state
    BlockNumber eager_scan_remaining_successes;
    BlockNumber eager_scan_remaining_fails;
    BlockNumber next_eager_scan_region_start;
} LVRelState;

AutoVacuumShmemStruct

typedef struct {
    sig_atomic_t av_signal[AutoVacNumSignals];  // IPC signals
    pid_t        av_launcherpid;                // Launcher PID
    dclist_head  av_freeWorkers;               // Available workers
    dlist_head   av_runningWorkers;            // Active workers
    WorkerInfo   av_startingWorker;            // Worker being started
    AutoVacuumWorkItem av_workItems[NUM_WORKITEMS]; // Work queue
    pg_atomic_uint32 av_nworkersForBalance;    // Workers for cost balancing
} AutoVacuumShmemStruct;

Vacuum Process Flow

Manual VACUUM Flow

1. Command Parsing (ExecVacuum)

   • Parse options and parameters
   • Validate settings
   • Create memory context

2. Relation Processing (vacuum)

   • Expand relation list
   • Start transactions as needed
   • Process each relation

3. Table Vacuum (vacuum_rel)

   • Open and lock relation
   • Check permissions
   • Call table AM vacuum function

4. Heap Vacuum (heap_vacuum_rel)

   • Determine aggressiveness
   • Set up parallel workers if enabled
   • Scan heap pages
   • Vacuum indexes and heap
   • Update statistics

Heap Scanning Flow

1. Page Selection (heap_vac_scan_next_block)

   • Use visibility map to skip pages
   • Apply eager scanning logic
   • Return next block to process

2. Page Processing

   • Try to get cleanup lock
   • Call lazy_scan_prune or lazy_scan_noprune
   • Update visibility and free space maps

3. Dead Item Management

   • Collect dead TIDs in TidStore
   • Trigger index vacuum when full
   • Reset dead items after processing

Autovacuum Process Flow

Launcher Flow

1. Initialization

   • Set up signal handlers
   • Create database list
   • Enter main loop

2. Scheduling Loop

   • Determine sleep time
   • Wait for events or timeout
   • Check for worker completion
   • Launch new workers as needed

3. Worker Launch

   • Select database needing vacuum
   • Find available worker slot
   • Signal postmaster to start worker

Worker Flow

1. Startup

   • Connect to assigned database
   • Set up vacuum parameters
   • Scan system catalogs

2. Table Selection

   • Check each table against thresholds
   • Prioritize wraparound prevention
   • Apply per-table settings

3. Vacuum Execution

   • Call standard vacuum functions
   • Use autovacuum-specific parameters
   • Report progress and statistics

Configuration Parameters

Core Vacuum Parameters

• vacuum_freeze_min_age (50M): Minimum age for tuple freezing
• vacuum_freeze_table_age (150M): Age for aggressive vacuum
• vacuum_multixact_freeze_min_age (5M): MXID freeze minimum
• vacuum_multixact_freeze_table_age (150M): MXID aggressive threshold
• vacuum_failsafe_age (1.6B): Emergency failsafe trigger
• vacuum_cost_delay (0): Delay between operations
• vacuum_cost_limit (200): Cost accumulation limit

Autovacuum Parameters

• autovacuum (on): Enable autovacuum
• autovacuum_naptime (1min): Time between launcher runs
• autovacuum_max_workers (3): Maximum worker processes
• autovacuum_work_mem (-1): Memory per worker
• autovacuum_vacuum_threshold (50): Base vacuum threshold
• autovacuum_vacuum_scale_factor (0.2): Vacuum scale factor
• autovacuum_analyze_threshold (50): Base analyze threshold
• autovacuum_analyze_scale_factor (0.1): Analyze scale factor
• autovacuum_freeze_max_age (200M): XID wraparound threshold
• autovacuum_multixact_freeze_max_age (400M): MXID wraparound threshold

Per-Table Settings

Tables can override autovacuum settings via storage parameters:

• autovacuum_enabled
• autovacuum_vacuum_threshold
• autovacuum_vacuum_scale_factor
• autovacuum_analyze_threshold
• autovacuum_analyze_scale_factor
• autovacuum_vacuum_cost_delay
• autovacuum_vacuum_cost_limit
• autovacuum_freeze_min_age
• autovacuum_freeze_max_age
• autovacuum_freeze_table_age

Performance Considerations

Cost-Based Delay

• Prevents vacuum from overwhelming I/O system
• Balances cost across multiple workers
• Configurable delay and limit parameters
• Disabled during failsafe mode

Parallel Vacuum

• Supports parallel index vacuuming
• Requires multiple indexes
• Shares dead item storage in DSM
• Coordinates via shared memory

Buffer Access Strategy

• Uses ring buffer to limit cache pollution
• Configurable via vacuum_buffer_usage_limit
• Bypassed during failsafe mode

Visibility Map Optimization

• Skips all-visible pages during normal vacuum
• Tracks all-frozen pages for aggressive vacuum
• Reduces I/O for large, stable tables

Troubleshooting

Common Issues

Transaction ID Wraparound

• Monitor age(relfrozenxid) and age(datfrozenxid)
• Increase autovacuum_freeze_max_age if needed
• Check for long-running transactions blocking vacuum

Autovacuum Not Running

• Verify autovacuum = on
• Check track_counts = on
• Monitor pg_stat_user_tables for last vacuum times
• Review autovacuum thresholds

Poor Vacuum Performance

• Increase maintenance_work_mem or autovacuum_work_mem
• Adjust cost-based delay parameters
• Consider parallel vacuum for large tables
• Monitor I/O and lock contention

Monitoring Queries

Check Table Vacuum Status

SELECT schemaname, tablename, 
       last_vacuum, last_autovacuum,
       n_dead_tup, n_live_tup,
       age(relfrozenxid) as xid_age
FROM pg_stat_user_tables 
ORDER BY xid_age DESC;

Monitor Autovacuum Activity

SELECT pid, state, query_start, query
FROM pg_stat_activity 
WHERE query LIKE '%autovacuum%';

Check Wraparound Status

SELECT datname, age(datfrozenxid), 
       2^31 - age(datfrozenxid) as xids_remaining
FROM pg_database 
ORDER BY age(datfrozenxid) DESC;

Debug Settings

• log_autovacuum_min_duration: Log slow autovacuum operations
• autovacuum_verbose: Verbose autovacuum logging
• vacuum_verbose: Detailed vacuum output
• track_cost_delay_timing: Monitor cost-based delays

Implementation Files

Core Files

• src/backend/commands/vacuum.c - Main vacuum command processing
• src/backend/access/heap/vacuumlazy.c - Heap vacuum implementation
• src/backend/postmaster/autovacuum.c - Autovacuum launcher and workers
• src/backend/commands/vacuumparallel.c - Parallel vacuum coordination

Header Files

• src/include/commands/vacuum.h - Vacuum function declarations
• src/include/postmaster/autovacuum.h - Autovacuum declarations

Related Files

• src/backend/access/heap/pruneheap.c - Heap pruning and freezing
• src/backend/storage/freespace/freespace.c - Free space map
• src/backend/access/common/reloptions.c - Storage parameters
• src/backend/utils/adt/pgstatfuncs.c - Statistics functions

This documentation provides a comprehensive overview of PostgreSQL's vacuum and autovacuum implementation, covering both the high-level concepts and detailed implementation specifics.