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 →
-
Improve the -l (limit) option recently added to contrib/vacuumlo.
- 64c604898e81 9.2.0 cited
Attachments
- vacuumlo_repro.txt (text/plain)
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