When Database Rows Survive but Files Don't: The Orphaned Blob Problem

When Database Rows Survive but Files Don't: The Orphaned Blob Problem

Roughly 400 media files vanished from object storage on a hotel operations system I worked on, even though the database was in perfect health. The rows that referenced them survived; the bytes they pointed at didn't. Only 45% ever came back.

Every backup green. Every migration applied. Every row intact. But photos attached to maintenance reports, incident evidence, and housekeeping documentation were simply gone. Nobody noticed until a user opened an old report and got a broken image.

This is the orphaned blob problem. Some version of it exists in most systems that store rows in a database and files on disk or in a bucket.

How files vanish while rows survive

The database is transactional. The file store isn't part of that transaction. That single architectural fact generates every variant of the failure:

• A storage migration moves files between servers or buckets and drops some on the way, with no error surfacing anywhere the application looks.
• A cleanup script scoped slightly wrong deletes files that active rows still reference.
• A bucket lifecycle rule expires objects someone assumed were permanent.
• An upload fails after the row was already committed, leaving a reference to a file that never existed at all.

None of these produce a database error. Picture the database and the file store as two clerks who never talk to each other: one keeps the guest list, the other keeps the room keys. Referential integrity inside PostgreSQL is enforced by the engine. Referential integrity between PostgreSQL and the file store is enforced by nobody — unless you build the job that enforces it.

The reconciliation manifest

Recovery starts with knowing the exact shape of the damage. The tool for that is a three-way manifest, built from the only sources of truth available:

List every file path the rows reference. List every object storage actually holds. List everything the backup mirrors contain.

The joins classify each file into one of four buckets, and a vague "some images are broken" turns into a precise work order: restore these, write off those, review the orphans.

What recovery actually looked like

About 45% of the missing files came back from backup mirrors — older copies of the storage volume that happened to predate the loss. The remaining 55% were permanently gone, for one unambiguous reason: the backup regime covered only the database.

The rows describing each file — filename, upload date, which report it belonged to — were preserved immaculately. The files themselves had never been inside any backup scope. The system had been diligently protecting the pointers while ignoring what they pointed at.

That partial recovery is the typical outcome, not the worst case. Without the manifest, even the recoverable fraction would have been lost, because nobody would have known which files to look for in which mirror.

The lessons that generalize

Object storage belongs inside the backup scope. Files referenced by database rows are business data with different storage economics — nothing more. Back them up on the same schedule and retention as the database, and verify both sides in the same restore test. The discipline for that is the subject of your pg_dump is not a backup until you've restored it.

Write files first, commit rows second. The upload-then-commit pattern makes the failure modes benign: if the upload fails, no row ever references the missing file; if the commit fails, you get an orphaned blob — wasted space, not a broken reference.

Deletion runs the same logic in reverse: remove the row first, let a cleanup job collect the unreferenced file later. Between the two failure modes, orphans are far cheaper than dangling references, so arrange the writes so orphans are the only kind you can produce.

Run the reconciliation continuously. The manifest that powered the recovery should never have been a one-off. As a weekly job it costs a listing and a set-diff, and it turns silent drift into an alert while the losses are still small and still inside backup retention. A missing-file count that ticks up from zero is an early-warning system for every failure class listed above.

Media is business data. The instinct that lost these files is the belief that "the real data is in the database" and files are decoration. On a hotel management system, a maintenance photo is the evidence a dispute is settled with. An incident attachment is what the insurance claim rests on.

Rows and files are one dataset that happens to live in two places. Every part of the system, from backups to integrity checks, has to treat them that way.