How to Remove Orphan Large Objects in PostgreSQL

Binary Large Objects (BLOBS) in PostgreSQL should be removed using the lo_unlink() function.

Simply deleting the related row without calling lo_unlink() will leave orphaned records behind.

In such cases, vacuumlo can come to the rescue as the “DEUS EX MACHINA” to clean up these orphaned large objects.

Here’s how you can manage large objects.

test=# -- Create table to store large object references
test=# CREATE TABLE files (id SERIAL PRIMARY KEY,filename TEXT, file_oid OID );

test=# -- Insert a large object and get its OID
test=# INSERT INTO files (filename, file_oid) VALUES ('insanedba.txt',lo_import('/Users/osmandinc/Downloads/insanedba.txt'));

test=# -- Retrieve the OID for deletion
test=# SELECT file_oid FROM files WHERE filename = 'insanedba.txt';

-- The correct method for unlinking is shown, but we will not do it correctly, thus leaving orphaned large objects behind.
-- SELECT lo_unlink(19023);

-- Delete the record from the table
DELETE FROM files WHERE filename = 'insanedba.txt';

osmandinc@192 Downloads % vacuumlo test -v

Here is a short demo in action.

Hope it helps.