Those darn Large Objects

Intro

PostgreSQL gives developers the chance of choosing between two possible storage facilities for large binary data: Bytea and LargeObjects.

Large Objects have been around for a long time, and PostgreSQL has a smart way of storing large binary data. It does so by splitting it into chunks of LOBLKSIZE (a forth of BLCKSZ). That way the tuples from pg_largeobject don’t spill on the toast table.

On the other hand bytea stores the binary data directly in the tuple, which may lead to poor performance depending on how your schema looks.

This sounds great if you have an intelligent interface for dealing with the manipulation of these binary files, specially if update modify just a small portion of the whole binary file.

But normally we don’t bother writing code that takes advantage of this, and instead we write again of the whole binary data.

One of the things that I believe make people adopt large objects are the functions available for importing and exporting files directly from the database server to it’s filesystem. There’s a con to this: if the application is on a different server, you’ll need more code to move the file to the location where it’s needed.

A problem you might face

The past days I had to examine a database used to store information of user sessions from a Java CAS system. I found there were almost 100 million large objects in the database, not very big ones.

I went over the user tables checking the fields that had an oid field, and then I cross-referencing the values in those fields with the pg_largeobject_metadata table. I found that 96% of those large objects where orphan ones. Those are large objects which weren’t referenced by any tuple from the user tables.

Further investigation concluded that Hibernate didn’t take care of purging the largeobjects it created when deleting or updating tuples with oid fields. So it was generating a great amount of bloat which could not be clean up by vacuuming, but had to be purged from pg_largeobjects table manually.

In the particular case of the CAS database, this query served to identify the largeobjects still in use:

SELECT unnest(array[expiration_policy,
                    authentication,
                    services_granted_access_to])
       FROM public.ticketgrantingticket
UNION
SELECT unnest(array[expiration_policy, 
                    service])
       FROM public.serviceticket

The query can be used to exclude from the list of large objects which ones to remove. Something like this:

SELECT lo_unlink(pg_largeobject_metadata.oid)
       FROM pg_largeobject_metadata
       WHERE pg_largeobject_metadata.oid NOT IN (
             SELECT unnest(array[expiration_policy,
                                 authentication,
                                 services_granted_access_to])
             FROM public.ticketgrantingticket
             UNION
             SELECT unnest(array[expiration_policy, 
                                 service])
             FROM public.serviceticket
)

Conclusion

Large objects have their issues, just like other types of data (especially when using types to store large binary data). It’s up to the developers and database administrators to take advantage of the pros and mitigate the cons.

We gave a possible query to perform the clean-up, but there’s also a nice extension which cleans up the orphaned large objects with triggers: Large Object Manager

Some people might prefer running a purge query during quiet hours instead of executing a trigger on every UPDATE and DELETE. On systems with very, very low UPDATE and/or DELETE rate, a trigger over each table that has an oid field, seems a more elegant solution. And any performance loss for having to execute the trigger function would be superfluous.

In any case, large objects still have great fans, most likely because of the internal functions provided to import and export the binary data directly to the local filesystem. With bytea you’d normally use more memory at the application tier. It’s a very common procedure to read the binary field completely into a variable an then process it.

I might write something about using bytea which I used in one of my past developments in a future blog post.