Schedule icon
Query icon
Get icon
OutputValues icon
Set icon
Log icon
If icon
SlackIncomingWebhook icon
Pause icon
Loop icon
Fail icon

PostgreSQL Orphaned Large Object Cleanup Guard

Detect orphaned PostgreSQL large objects, unlink the aged ones behind an age window and approval gate, then re-read to confirm.

Categories
DataInfrastructure

A PostgreSQL large object created with lo_from_bytea or lo_import lives in pg_largeobject and pg_largeobject_metadata. The owning row stores its id in an oid or lo column, but there is no foreign key between the two. Delete the row and the large object stays behind forever. No delete reclaims it and autovacuum never touches it, so the bytes leak until someone runs the unlink by hand. This guard finds those orphans, unlinks the aged ones under a gate and proves the unlink landed. It is the vacuumlo job done as a scheduled, gated, verified flow.

This blueprint was created by zkasuran.

Safety

  • Read-only by default. dry_run is true and auto_remediate is false, so a plain run reports and never unlinks.
  • An unlink happens only when auto_remediate is true and dry_run is false. It then waits for a human to resume the Pause with approved set true. An unanswered gate reads as not approved.
  • A first-seen minimum age protects a fresh large object. An orphan is unlinked only once this guard has seen it with no referencing row for at least min_age_minutes, so a large object created moments before its owner row is inserted is reported but never unlinked on the same run.
  • The referenced set is the union of every oid and lo column value, so a value a non-reference oid column happens to hold still protects a matching id. The guard errs toward keeping, the same safe direction vacuumlo takes.
  • The re-read of pg_largeobject_metadata is the real gate, so an unlink the user is not allowed to run surfaces as a failed run rather than a false success.

How it works

  1. now_probe (io.kestra.plugin.jdbc.postgresql.Query) reads the server clock as epoch seconds, so the first-seen age is measured against the PostgreSQL clock.
  2. detect (io.kestra.plugin.jdbc.postgresql.Query) finds every user column of type oid or lo from pg_attribute, unions the large object ids they hold through a generated query and returns the ids in pg_largeobject_metadata that no column points at.
  3. read_cursor (io.kestra.plugin.core.kv.Get) loads the first-seen cursor, a KV map from large object id to the epoch second this guard first saw it orphaned. A missing key is the first run.
  4. compose and enrich (io.kestra.plugin.core.output.OutputValues) merge the cursor with the detected orphans, set a first-seen time of now for an orphan seen for the first time, compute each orphan's age and flag a target once its age is at or past min_age_minutes.
  5. plan (io.kestra.plugin.core.output.OutputValues) splits the orphans into unlink targets and orphans still inside the minimum age, then builds the pruned cursor.
  6. write_cursor (io.kestra.plugin.core.kv.Set) writes the pruned cursor back with no ttl, so a long-lived orphan keeps its original first-seen time and an id that is no longer orphaned drops out.
  7. guard (io.kestra.plugin.core.flow.If) branches on the orphan count. With none found, clean logs and the run ends. Otherwise findings_alert posts the finding on every detection.
  8. mode (io.kestra.plugin.core.flow.If) enters the remediation branch only when auto_remediate is true, dry_run is false and at least one orphan is past the minimum age. Otherwise report_only lists what would be unlinked and stops.
  9. approval_gate (io.kestra.plugin.core.flow.Pause) holds the run until a human resumes it. unlink_orphans (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) then runs lo_unlink once per target that is still unreferenced.
  10. reverify re-runs the orphan diff and verify_result fails the run through io.kestra.plugin.core.execution.Fail if any targeted id is still present. A fresh orphan left in place by design does not count.

What you get

  • A check that reads the real orphan set from the catalog, so it catches the large objects that leak disk forever after their owner rows are gone.
  • A generic scan. It reads the referencing columns from pg_attribute at run time, so it covers every oid and lo column in every user schema without a hand-written list.
  • A minimum age that holds back an unlink until the guard has seen an orphan for a set time, so a large object created just before its row is reported but kept.
  • A real verification step. The run passes only when the second read of pg_largeobject_metadata shows every target gone.
  • Slack alerts on the findings and on any failure.

Who it's for

  • DBAs and platform engineers who store large objects and watch disk grow from rows that were deleted without an unlink.
  • Teams that use lo_from_bytea or lo_import without the lo_manage trigger, where a delete leaves the large object behind.
  • Teams evaluating the PostgreSQL plugin who want an example that reads the catalog, keeps a KV cursor and runs gated, verified SQL.

Why orchestrate this with Kestra

PostgreSQL has no scheduler, no age window, no approval step and no way to prove an unlink took effect. The vacuumlo utility unlinks in one shot with none of those guards. Kestra adds the missing pieces. It schedules the sweep, keeps a first-seen cursor in KV so a fresh orphan is held back, gates the destructive statement behind dry-run and approval, re-reads pg_largeobject_metadata to turn a hopeful unlink into a verified one and posts a Slack alert the moment a step fails.

Tested end to end

Run on Kestra 2.0.5 against a PostgreSQL 16 server. A table with an oid column and a table with a lo column were seeded with eight large objects through lo_from_bytea, all referenced. Five owner rows were then deleted to orphan five large objects. Counts were confirmed with psql.

  • Detector can fail: with all eight referenced the detector returned 0 orphans, after deleting five owner rows it returned exactly 5, confirmed with psql against pg_largeobject_metadata.
  • Dry run detected the five orphans, alerted and left pg_largeobject_metadata at eight rows.
  • Remediate with approval and an aged cursor unlinked the four aged orphans and the re-read reported 0 of them remaining. The three referenced large objects were never touched.
  • Minimum age: a large object orphaned moments earlier stayed inside the window and was reported but not unlinked.
  • A read-only user that could read the catalog but could not own the large objects turned every unlink into a no-op, the re-read found the targets still present and the run failed and alerted.
  • An unreachable host failed the first query and fired the Slack alert.

Prerequisites

A PostgreSQL server reachable over JDBC. The connecting user must be able to read pg_largeobject_metadata and the referencing tables and must own the large objects it unlinks.

Secrets

  • POSTGRES_USERNAME: PostgreSQL user, read when use_secret_auth is true.
  • POSTGRES_PASSWORD: password for that user.
  • SLACK_WEBHOOK_URL: used by findings_alert and the errors block.
  • In Kestra OSS, provide each secret as a base64-encoded environment variable prefixed SECRET_.

Inputs

Name Type Default Description
postgres_url STRING jdbc:postgresql://localhost:5432/postgres PostgreSQL JDBC URL for the target database.
use_secret_auth BOOL true Read credentials from secrets. Set false only for inline test credentials.
min_age_minutes INT 60 Minimum age. An orphan is unlinked only after it has been seen this long.
dry_run BOOL true Report without unlinking.
auto_remediate BOOL false Unlink behind the approval gate when dry_run is false.

Quick start

  1. Set POSTGRES_USERNAME, POSTGRES_PASSWORD and SLACK_WEBHOOK_URL as namespace secrets.
  2. Import this flow.
  3. Run it with postgres_url pointed at a database that uses large objects. The default dry run reports the orphans.
  4. To unlink, run again with dry_run false and auto_remediate true, then resume the paused execution with approved true.

How to extend

  • Raise or lower min_age_minutes to widen or shorten the window before an orphan is eligible to unlink.
  • Add a size read from pg_largeobject per target for a report that shows the bytes reclaimed, where the connecting user may read pg_largeobject.
  • Fail the run as soon as detection finds anything, for a read-only drift check with no remediation.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.