Vacuum and Analyse PostgreSQL Safely with vacuumdb

By the end, you will have run a controlled vacuum against one PostgreSQL database, optionally refreshed its planner statistics, and checked what happened without guessing which database received the work. This guide 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 a command.

1. Check the installed command and connection target

Start with read-only checks. The command 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 may silently choose 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 that 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 use --dbname= when a connection string is required. Do not add sudo just because this is administration: the relevant permission is normally a PostgreSQL role privilege, not root access to the host.

Checkpoint: identify the exact database

Before changing anything, make the target visible in the command you are about to run. If you need to discover databases first, use an authorised PostgreSQL client or your normal inventory process. Do not infer the target from the current shell directory.

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 associated with 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' "$?"

A normal vacuum changes database state. There is no general undo command: if this is a production system, schedule it with the expected workload and take the recovery measures required by your change process first. Vacuum is maintenance, not a substitute for a backup.

3. Refresh optimiser statistics when needed

Use --analyze when the planner needs fresh column statistics after a substantial load, restore, or data distribution change. It performs the vacuum and also calculates statistics. The short option is -z.

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

Use --analyze-only when you want statistics without vacuuming. This still changes planner metadata and can affect query plans, but it does not perform the vacuum operation.

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

--analyze-in-stages is narrower still: it runs three analyse stages and is intended 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. Do not use it as a faster spelling of ordinary analyse.

4. Limit the scope before increasing concurrency

For a targeted change, select tables with repeated --table options or schemas with repeated --schema options. You can 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 increasing server load. 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, capacity check, and 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 preferable to an unexpected wait only 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. Never put a password in a shell command, pasted transcript, or process-visible connection string.

$ 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 or 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.

Done means

• The installed version and connection target were checked.
• The command named the intended database explicitly.
• The vacuum or analyse completed with a successful exit status.
• Any selected tables or schemas, skipped relations, and added load were recorded.
• Full vacuum and all-database runs were treated as planned maintenance.
• No password was exposed in a command, log, or terminal transcript.