vacuumdb

Vacuum a specific database

$ vacuumdb [database_name]

$ vacuumdb [[-a|--all]]

$ vacuumdb [[-t|--table]] [table_name] [database_name]

$ vacuumdb [[-z|--analyze]] [database_name]

$ vacuumdb [[-f|--full]] [database_name]

$ vacuumdb [[-v|--verbose]] [database_name]

$ vacuumdb --jobs [number_of_jobs] [database_name]

vacuumdb [option...] [dbname]
vacuumdb [option...] --all
vacuumdb [option...] --table=table [(column,...)]

-a, --all
    Vacuum or analyze all databases.

-d dbname, --dbname=dbname
    Specifies the name of the database to be vacuumed or analyzed. If not specified, defaults to the user's login name.

-e, --echo
    Echo the commands that vacuumdb generates and sends to the server.

-f, --full
    Performs a 'full' vacuum, which can reclaim more space but is much slower and requires an exclusive lock on the table.

--freeze
    Freezes transaction IDs in tuples. This is equivalent to running VACUUM FREEZE. This option is important for preventing transaction ID wraparound.

-j num, --jobs=num
    Executes commands concurrently using num jobs. Can speed up vacuuming and analyzing of multiple tables or databases.

-q, --quiet
    Do not display progress messages.

-t table [(column,...)], --table=table [(column,...)]
    Vacuum or analyze a specific table. Can optionally specify columns to analyze only those.

-v, --verbose
    Print detailed progress reports during operation.

-V, --version
    Print the vacuumdb version and exit.

-z, --analyze
    Updates statistics for the query planner after vacuuming.

-Z, --analyze-only
    Only updates statistics; does not perform a vacuum operation.

--disable-page-skipping
    Disables the page-skipping optimization during vacuuming. Useful in conjunction with --freeze to ensure all pages are processed.

--clean-catalog
    Clean system catalog tables. Equivalent to VACUUM (CLEAN_CATALOG ON).

--no-index-cleanup
    Skips index cleanup. Use with caution as this can lead to larger indexes. Equivalent to VACUUM (INDEX_CLEANUP OFF).

--no-truncate
    Do not truncate empty pages at the end of the table. Equivalent to VACUUM (TRUNCATE OFF).

--skip-locked
    Skip any relations (tables) that cannot be locked immediately. This allows vacuumdb to continue processing other relations.

--min-xid-age=age
    Vacuum tables where the transaction ID age is at least age, helping to prevent transaction ID wraparound.

--min-mxid-age=age
    Vacuum tables where the multixact ID age is at least age.

vacuumdb is a utility for performing maintenance on a PostgreSQL database, specifically to vacuum and analyze it.

The primary function of vacuuming is to reclaim storage occupied by 'dead' tuples (rows that have been deleted or updated). In PostgreSQL's MVCC (Multi-Version Concurrency Control) architecture, old versions of rows are not immediately removed from disk; they are marked as 'dead' and remain until vacuumed. Reclaiming this space is crucial for preventing disk bloat and maintaining database performance.

The analyze function gathers statistics about the contents of tables, which the PostgreSQL query planner uses to determine the most efficient execution plans for queries. Regularly updated statistics are vital for optimal query performance.

Beyond space reclamation and statistics updates, vacuumdb also plays a critical role in preventing transaction ID wraparound, a condition that can lead to database shutdown if not properly managed. It can operate on a single database, all databases, or specific tables, making it a flexible tool for database administrators. While PostgreSQL's auto-vacuum daemon handles much of the routine maintenance, vacuumdb provides a powerful command-line interface for manual, on-demand, or more aggressive maintenance tasks.

Using --full can be very resource-intensive, requiring an exclusive lock on the table and temporarily doubling the disk space usage. It should be used sparingly and typically during maintenance windows.

While vacuumdb is essential, frequent manual runs might indicate that PostgreSQL's auto-vacuum daemon is not optimally configured for your workload.

Inadequate vacuuming can lead to significant database bloat, decreased performance, and ultimately, a critical database shutdown due to transaction ID wraparound.

PostgreSQL's auto-vacuum daemon automatically performs vacuum and analyze operations in the background. For most routine maintenance, auto-vacuum is sufficient. vacuumdb provides a way to trigger these operations manually, which is useful for:

• Performing more aggressive vacuuming (e.g., with --full).
• Running vacuum/analyze on demand after large data changes.
• Addressing specific issues like impending transaction ID wraparound.
• Integrating with custom maintenance scripts.

Every transaction in PostgreSQL is assigned a unique Transaction ID (XID). These IDs are 32-bit integers, meaning they eventually 'wrap around'. If the oldest active transaction ID in the database is not 'frozen' (i.e., marked as permanently completed) before the system runs out of new XIDs, the database will shut down to prevent data corruption. vacuumdb (especially with options like --freeze or --min-xid-age) is a critical tool for preventing this by ensuring old transaction IDs are properly frozen and recycled.

vacuumdb has been a cornerstone utility of PostgreSQL since its early development, essential due to PostgreSQL's MVCC architecture. Its core functionality of reclaiming dead space and updating query planner statistics has remained consistent.

Over time, the command has evolved with PostgreSQL itself, gaining new options to offer finer control over the vacuuming process, improve performance (e.g., through parallel execution with --jobs), and address specific maintenance challenges like transaction ID wraparound with options like --freeze and --min-xid-age.

The introduction and continuous improvement of the auto-vacuum daemon in PostgreSQL have significantly reduced the necessity for frequent manual vacuumdb runs, but it remains an indispensable tool for targeted maintenance, troubleshooting, and administrative scripts.

psql(1), pg_dump(1), pg_restore(1), reindexdb(1), clusterdb(1), createdb(1), dropdb(1), pg_ctl(1)