Vacuumlo improvements

Shawn McCoy <shawn.the.mccoy@gmail.com>

From: Shawn McCoy <shawn.the.mccoy@gmail.com>
To: pgsql-hackers@postgresql.org
Date: 2026-05-12T17:34:10Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →
  1. Improve the -l (limit) option recently added to contrib/vacuumlo.

Attachments

I'd like to discuss a behavior in the vacuumlo utility that can lead
to silent data loss when large object references are stored in columns
whose type is a domain over `oid` or `lo`.  While fully stated in the
docs, we have observed users getting some surprises when they are
trying to do routine maintenance.  I'll attach a very simple repro
that displays the behavior using
a fairly routine use of a domain. [1]

DESCRIPTION
---------------------
The vacuumlo documentation [2] states:

  "Only types with these names are considered; in particular, domains
   over them are not considered."

While the behavior is documented, in the field, the consequence is
severe: if a user creates a domain over `oid` (e.g., for semantic
clarity or to add constraints) and uses that domain-typed column to
store large object OIDs, vacuumlo will treat those LOs as orphaned and
delete them. The referenced data is silently destroyed.

This is particularly dangerous because:
- Domains over base types are a popular PostgreSQL practice
- There is no warning or diagnostic output from vacuumlo when it skips
domain-typed columns
- The --dry-run / -n flag will show these LOs as "would be removed"
but nothing indicates *why* they appear orphaned
- The data loss is irreversible


SUGGESTION
---------------------
Ideally, vacuumlo could be improved to:
- Resolve domain types back to their base types when scanning columns
(using pg_type.typbasetype), or
- At least emit a WARNING when it encounters columns with domains over
oid/lo that it is skipping, so the user is aware.

I don't currently have a patch attached, but wanted to shine light on
the issue given the silent data-loss risk. I know LO's are a sensitive
topic with discussions that wander towards deprecation, but they seem
to be here to stay and are very commonly used in the field.

At minimum, I can submit a documentation improvement to make the
data-loss risk more prominent. The current parenthetical note is easy
to miss.

[1] attached vacuumlo_repro.txt
[2] https://www.postgresql.org/docs/current/vacuumlo.html

Thanks,
Shawn