vacuumdb

Garbage-collect and analyze a PostgreSQL database

TLDR

Vacuum a database

$ vacuumdb [database]

Vacuum all databases

$ vacuumdb --all

Vacuum and update optimizer statistics

$ vacuumdb --analyze [database]

Full vacuum (rewrites tables and reclaims disk space)

$ vacuumdb --full [database]

Vacuum a specific table

$ vacuumdb -t [table_name] [database]

Run vacuum in parallel with multiple jobs

$ vacuumdb -j [4] [database]

Freeze tuples aggressively to prevent transaction ID wraparound

$ vacuumdb --freeze [database]

Only update statistics without vacuuming

$ vacuumdb --analyze-only [database]

Vacuum on a remote server with authentication

$ vacuumdb -h [hostname] -p [5432] -U [username] [database]

SYNOPSIS

vacuumdb [-a] [-z] [-f] [-F] [-t table] [-j jobs] [options] [dbname]

vacuumdb cleans and analyzes PostgreSQL databases. It is a wrapper around the SQL VACUUM and ANALYZE commands, providing convenient options for scripting and cron jobs.Regular vacuum marks dead tuples for reuse. It doesn't reclaim disk space but prevents table bloat from growing indefinitely.Full vacuum rewrites tables, reclaiming disk space. It requires an exclusive lock on the table and more time than regular vacuum.Analyze updates statistics used by the query planner. Accurate statistics lead to better query plans and performance.Parallel vacuuming with -j processes multiple tables simultaneously. The -P option controls the number of parallel workers within each vacuum operation.

PARAMETERS

-a, --all

Vacuum all databases.

-d DBNAME, --dbname DBNAME

Database to clean or analyze.

-z, --analyze

Also calculate statistics for use by the optimizer.

-Z, --analyze-only

Only calculate statistics, do not vacuum.

--analyze-in-stages

Analyze in three stages with increasing statistics targets. Useful for databases with no or wholly incorrect statistics.

-f, --full

Perform full vacuuming (rewrites tables, reclaims disk space).

-F, --freeze

Aggressively freeze tuples to prevent transaction ID wraparound.

-t TABLE, --table TABLE

Vacuum specific table only. Can be specified multiple times.

-n SCHEMA, --schema SCHEMA

Clean or analyze all tables in the specified schema only. Can be specified multiple times.

-N SCHEMA, --exclude-schema SCHEMA

Exclude tables in the specified schema.

-j N, --jobs N

Run vacuum or analyze commands in parallel by running N jobs simultaneously.

-P N, --parallel N

Specify the number of parallel workers for each vacuum operation.

--skip-locked

Skip relations that cannot be immediately locked for processing.

--no-index-cleanup

Do not remove index entries pointing to dead tuples.

--no-truncate

Do not truncate empty pages at the end of the table.

--disable-page-skipping

Disable skipping pages based on the visibility map.

--min-xid-age AGE

Only process tables with a transaction ID age of at least AGE.

--min-mxid-age AGE

Only process tables with a multixact ID age of at least AGE.

-e, --echo

Echo the commands sent to the server.

-q, --quiet

Do not display progress messages.

-v, --verbose

Print detailed information during processing.

-h HOST, --host HOST

Database server host.

-p PORT, --port PORT

Server port.

-U USER, --username USER

Username to connect as.

-w, --no-password

Never prompt for password.

-W, --password

Force password prompt.

--maintenance-db DBNAME

Database to connect to when using --all.

CAVEATS

Full vacuum locks tables exclusively and requires extra disk space for the rewrite. Regular vacuum should run frequently. The --freeze option is important for preventing transaction ID wraparound on busy databases.

HISTORY

vacuumdb has been part of PostgreSQL since early versions. It provides command-line access to vacuum operations essential for database maintenance.