Safely Remove Orphaned Large Objects with vacuumlo

vacuumlo deletes things, which makes the dry run the only step worth rushing past. This guide previews orphaned large objects in a PostgreSQL database and, only once the result is understood, removes them. The installed command is PostgreSQL 16.15 from Ubuntu package postgresql-client-16.

Allow about fifteen minutes for a first run, plus whatever time you need to review the proposed removals.

• This is database cleanup, not ordinary table vacuuming.
• An orphaned large object is one whose OID is not found in a column of type oid or lo.
• The program can delete those objects, so keep a current database backup and confirm the application is not still relying on an untracked OID before you run the destructive form.

1. Confirm the installed command

Check which executable will run and record its version:

$ command -v vacuumlo
/usr/bin/vacuumlo
$ vacuumlo --version
vacuumlo (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

Your package revision may differ. The checkpoint that matters is that the command is the PostgreSQL client you intend to use, and that its option syntax matches this guide.

vacuumlo uses PostgreSQL's normal client connection settings. The database name is a positional argument. Supply connection details explicitly with --host, --port and --username, or use the PGHOST, PGPORT and PGUSER environment variables. Do not put a password in a shell command or a service definition.

2. Check connectivity without changing the database

Set obvious placeholders for the server and role, then run the dry-run form. This asks vacuumlo to report what it would do without removing anything:

$ vacuumlo \
    --host=DB_HOST \
    --port=5432 \
    --username=DB_USER \
    --dry-run \
    DB_NAME

Replace DB_HOST, DB_USER and DB_NAME with real values. If the server asks for a password, the client can prompt automatically: use --password to force the prompt before the connection attempt, or --no-password when a prompt would be unsafe in a batch job (the latter fails if no other supported credential is available).

Expected output depends on the database. With no candidates, the command should finish without proposing removals; with candidates, it should show the work it would do. Treat a connection, authentication or permission error as a stop condition. Do not switch to sudo as a guess: sudo changes the local process identity, not the PostgreSQL role used for the database operation.

Checkpoint: Run the dry run again with --verbose if the normal output is not detailed enough:

$ vacuumlo --dry-run --verbose \
    --host=DB_HOST --port=5432 --username=DB_USER DB_NAME
$ printf 'exit status: %s\n' "$?"
exit status: 0

The exact progress text and counts are data from your database, so do not treat an example count as evidence that your server is clean.

3. Review what counts as orphaned

Before deleting anything, check how the application stores large-object references. This utility scans columns whose types are named oid or lo; it does not treat domains over those types as references. A reference stored as text, an integer, JSON, or another application-specific format will not protect the corresponding large object from being considered orphaned.

That boundary is the main safety trap. If your application keeps its own large-object catalogue, reconcile it with the database schema before proceeding. Do not infer safety from the age or size of an object, and do not run the command against a production database merely because the dry run listed a large number of candidates.

4. Remove candidates in bounded transactions

Warning: The next command changes database state by removing large objects. There is no vacuumlo undo option. Confirm your backup, maintenance window and application review before running it.

Use the same connection arguments as the dry run and set a transaction limit:

$ vacuumlo \
    --host=DB_HOST \
    --port=5432 \
    --username=DB_USER \
    --limit=1000 \
    DB_NAME

The installed default is 1000 large objects per transaction. The server acquires a lock for each object removed, so a very large transaction can run into max_locks_per_transaction. A smaller limit commits more often and keeps each transaction smaller. Set --limit=0 only when you have a specific reason to remove all candidates in one transaction; it is not the cautious default.

For multiple databases, list each name as a separate final argument. The program processes every one supplied:

$ vacuumlo --dry-run --host=DB_HOST --username=DB_USER \
    APP_DB REPORTING_DB

Preview the complete list of databases first. A connection or permission failure for one target should be investigated before you assume the others were handled as intended.

5. Verify the result and recover carefully

Run the same dry-run command after cleanup:

$ vacuumlo --dry-run --verbose \
    --host=DB_HOST --port=5432 --username=DB_USER DB_NAME
$ printf 'exit status: %s\n' "$?"
exit status: 0

An empty candidate set is useful evidence that the second scan found nothing else to remove. It is not a substitute for checking application behaviour, because the scan still follows the type-based rules from step 3.

Recovery: If you removed a large object the application still needs, stop the workflow that uses it and restore the affected database from a verified backup, or restore the object through your established PostgreSQL large-object recovery process. Do not recreate an arbitrary OID by hand: application references and large-object contents must be restored consistently. If a run fails part-way through, rerun the dry run and review the remaining candidates before retrying.

Common traps

• Do not confuse vacuumlo with SQL VACUUM. SQL vacuuming reclaims dead row space; this utility removes unreferenced large objects.
• Do not omit the database name. The command syntax is vacuumlo [options] DBNAME...; it does not automatically mean every database on the server.
• Do not assume --verbose is a preview. It adds progress messages. Only --dry-run prevents removal.
• Do not pass an unreviewed password through the shell. Use the normal PostgreSQL credential mechanism or the interactive prompt.
• Do not run as root by habit. Use the least-privileged local account and PostgreSQL role that can perform the reviewed operation.

Done means

• Version confirmed. The executable is PostgreSQL 16.15, or you checked the equivalent on your host.
• Dry run reviewed. It completed against the exact database and connection target you intended.
• Reference type confirmed. Application references use oid or lo columns, not an unsupported representation.
• Backup and recovery ready. You have a verified backup and an explicit recovery path before deletion.
• Removal bounded and re-checked. The removal used a deliberate transaction limit, and a second dry run returned no unexpected candidates.