Vacuum and Analyse PostgreSQL Safely with vacuumdb

Run vacuumdb with no database name and it quietly picks one for you, which is how the wrong database gets maintained. This guide runs a controlled vacuum against one named database, optionally refreshes planner statistics, and checks the result without guessing which database took the load. It follows the locally installed PostgreSQL 16.15 client on Ubuntu.

Allow a few minutes for a small database and longer for a busy or large one. You need a running PostgreSQL server, a database account allowed to run the requested maintenance, and enough time for the work to add load. The examples use APP_DB as a placeholder: replace it with a real database name before running anything.

1. Check the installed command and connection target

Start with read-only checks. vacuumdb is a client wrapper around the SQL VACUUM command: it connects to the server rather than opening database files directly. Confirm the version and the connection defaults that could silently pick the wrong database.

$ vacuumdb --version
vacuumdb (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)
$ printf 'PGDATABASE=%s PGHOST=%s PGPORT=%s PGUSER=%s\n' "$PGDATABASE" "$PGHOST" "$PGPORT" "$PGUSER"

The local manpage says an omitted database name falls back to PGDATABASE, then the connection user name. That is convenient interactively and risky in automation. Prefer an explicit name, or --dbname= when a connection string is required. Do not add sudo just because this is administration: the permission that matters is normally a PostgreSQL role privilege, not root access to the host.

2. Run an ordinary vacuum

With a confirmed target, run the least surprising operation. It cleans dead row versions and related storage without the table rewrite and locking behaviour of a full vacuum.

$ vacuumdb --dbname=APP_DB
vacuumdb: vacuuming database "APP_DB"

Progress output varies with the client and server settings, so treat a zero exit status as the primary success signal. Add --verbose when you need relation-level detail:

$ vacuumdb --verbose --dbname=APP_DB
$ printf 'vacuumdb exit status: %s\n' "$?"

3. Refresh optimiser statistics when needed

Use --analyze (short option -z) when the planner needs fresh column statistics after a substantial load, restore, or data distribution change. It performs the vacuum and calculates statistics in the same run.

$ vacuumdb --analyze --verbose --dbname=APP_DB

• --analyze-only skips the vacuum. It still changes planner metadata and can affect query plans, but does not perform the vacuum itself.
• --analyze-in-stages is narrower still. It runs three analyse stages, meant for a newly restored or upgraded database with missing or wholly incorrect statistics. On a database with useful existing statistics, the early low-target stages can temporarily make plans worse, so do not use it as a faster spelling of ordinary analyse.

$ vacuumdb --analyze-only --dbname=APP_DB

4. Limit the scope before increasing concurrency

For a targeted change, select tables with repeated --table options or schemas with repeated --schema options; exclude schemas with --exclude-schema. These selectors reduce the work; they do not grant access to objects your database role cannot maintain.

$ vacuumdb --analyze --verbose \
    --table='public.orders' \
    --table='public.order_items' \
    --dbname=APP_DB
$ vacuumdb --schema='reporting' --dbname=APP_DB

Column selectors are allowed only with --analyze or --analyze-only. Quote the parentheses so the shell passes them to vacuumdb as one argument:

$ vacuumdb --analyze --table='public.orders(customer_id,status)' --dbname=APP_DB

--jobs=NUMBER opens that many database connections and runs commands concurrently. It can shorten a broad job while raising server load, so check the available connection headroom first. Do not combine jobs with --full casually: the installed documentation warns that parallel processing of some system catalogue work can deadlock.

5. Treat full vacuum as a planned operation

--full rewrites tables to reclaim more space. That is a different operational class from the ordinary command: it needs more disk workspace and can block concurrent activity. Use it only with a maintenance window, a capacity check, and a tested recovery plan; it is not a routine fix for every slow query.

For a less disruptive job that should not wait on relations already locked, --skip-locked skips those relations instead. Record that the run was incomplete and arrange a later pass: skipping work is only preferable to an unexpected wait when your monitoring and follow-up make the omission visible.

6. Check failures without leaking credentials

If the server requires authentication, vacuumdb can prompt automatically. --password prompts before the connection attempt; --no-password never prompts and fails if no other credential source is available. For unattended work, use an appropriately protected .pgpass file or an approved secret mechanism.

$ vacuumdb --no-password --verbose --dbname=APP_DB
vacuumdb: error: connection to server ... failed

The server must be running and the host, port, user, and database must be correct. Supply them explicitly when defaults are unclear:

$ vacuumdb --host=DB_HOST --port=5432 --username=DB_USER --dbname=APP_DB --verbose

For all databases, use --all. It first connects to a maintenance database, defaulting to postgres and then template1, to collect the database list; use --maintenance-db=MAINT_DB when that default is not appropriate. This is a broad state-changing operation, so confirm the account, connection budget, and maintenance window before starting.