PostgreSQL large objects: Best practices for Amazon Aurora and RDS

PostgreSQL large objects (LOs) store binary data such as files, documents, and images. PostgreSQL also supports a storage system called TOAST (The Oversized-Attribute Storage Technique), which automatically stores values larger than a single database page in a secondary per-table storage area. TOAST has largely superseded the large object for most use cases. But the large object feature retains two advantages:
- It allows values up to 4 TB in size, whereas TOASTed fields can be at most 1 GB.
- It can read and update portions of a value efficiently, whereas most operations on a TOASTed field read or write the whole value as a unit.
These advantages come with operational trade-offs that most teams discover only after something breaks. Because large objects live outside your tables and are referenced only by an Object Identifier (OID), autovacuum does not clean up orphaned and unused objects, logical replication does not copy them, and each large object draws from a single, shared OID space. Left unmanaged, orphaned objects accumulate without warning, OID allocation slows under contention, and major version upgrades can fail.
This post covers how to monitor the accumulation of orphaned large objects on Amazon Aurora PostgreSQL-Compatible Edition and Amazon Relational Database Service (Amazon RDS) for PostgreSQL. It also shows you how to use vacuumlo safely, including its domain-type blind spot, set up lo_manage triggers for automatic cleanup, and prepare for major version upgrades when large object counts are high.
What are large objects?
With large objects, you can store binary data beyond the 1 GB limit that bytea columns impose. Unlike regular table data stored inline through TOAST, large objects are stored in dedicated system catalog tables and referenced by an Object Identifier (OID).
pg_largeobject_metadataholds one row per large object, recording the object’s identity (its OID) along with its owner and access privileges.pg_largeobjectholds the actual binary content. Each object is split into 2 KB pages, and each page is stored as a separate row tagged with the object’s OID and its page number.
The OID links the two: it identifies the object in pg_largeobject_metadata and tags every data page belonging to that object in pg_largeobject. This storage is entirely separate from your application tables. Your row holds only the OID, the object’s data lives independently in these catalog tables, and PostgreSQL enforces no link between them.
What are orphaned large objects?
In PostgreSQL, orphaned large objects refer to large object entries in the pg_largeobject system table that are no longer referenced by any table in the database. These large objects persist in the database but serve no useful purpose, consuming storage unnecessarily. Large objects become orphaned in the following situations:
- Deleting rows without deleting large objects: A large object has a lifecycle of Create, Put, Unlink. Applications use the
lo_creat()function to create a new, empty large object and generate an OID for it. Applications put data into the large object usinglo_put()orlo_from_bytea(), using the OID to identify which large object to populate. The OID is normally stored for later use (to retrieve the large object) in some tables according to the application schema. When the data row that stores the OID is deleted, you must call thelo_unlink()function to delete the large object. Otherwise, the large object is left as an orphan in the database with no pointer (OID) referring to it. - Dropping tables that further refer to large objects: If a table storing large object OID is dropped, PostgreSQL doesn’t remove or drop the corresponding LOs.
- Updating rows without cleaning up old LOs: If an update performed on the database is changing an LO reference, the old LOs might become orphaned unless explicitly deleted.
Orphaned large objects are not removed by autovacuum. They require explicit cleanup with vacuumlo. Because of this operational challenge, when designing your database schema, avoid using large objects when possible and consider using alternative data types like bytea instead.
Best practices for managing large objects
In this section, you learn how to monitor large objects, understand how vacuumlo works, safely clean up orphaned large objects, prevent OID exhaustion, and prepare for major version upgrades.
Prerequisites
Before you start, verify that you have the following:
- PostgreSQL knowledge: Familiarity with PostgreSQL system catalogs, basic SQL operations, and database maintenance concepts.
- Database access: Connection credentials with sufficient privileges to create extensions, triggers, and query system catalogs (typically
rds_superuserrole for Amazon RDS and Aurora). - Client tools: PostgreSQL client tools installed, including psql and vacuumlo utility (available in the
postgresql-contribpackage). - Extensions: If you plan to use the
lo_managetrigger for automatic cleanup (shown later), install the lo extension:CREATE EXTENSION IF NOT EXISTS lo;. - Versions: Tested with Amazon Aurora PostgreSQL 14, 15, 16 and Amazon RDS for PostgreSQL 14, 15, 16. Behavior of
lo_compat_privilegesand the ownership check is consistent across these versions.
Important: Test all steps in a non-production environment before applying them to production databases. The cleanup operations in this post permanently delete large objects and cannot be undone.
Monitor large objects
Unmonitored, orphaned large objects accumulate undetected, wasting storage, pressuring OID allocation, and complicating major version upgrades.
Run a basic health check
Start by getting a high-level picture of your large objects. This query returns the total number of large objects, their combined size, and your overall database size. This shows you how much of your database large objects account for.
Example output:
This query uses the pg_largeobject table directly to calculate large object sizes, which gives you an accurate total. Note: This query might perform a full table scan, so run it during off-peak hours if the database is large.
Review a detailed large object inventory
The health check shows your total large object size, but not which objects make up that total. To find the biggest consumers, list each large object with its size.
Identify which columns reference large objects
Before you clean up orphaned large objects, find every column in your database that references one. The pre-flight schema check in the “Clean up orphaned large objects safely with vacuumlo” section shows you how, using the exact query vacuumlo runs. Run it before any cleanup.
To identify which large objects are orphaned, use vacuumlo in dry-run mode rather than a hand-written query. vacuumlo automatically scans every oid and lo column in the database. Writing your own orphan-detection query can be risky: if it omits even one column that references large objects, those objects appear orphaned and could be permanently deleted if acted upon.
Monitor growth over time
Example output:
Set up Amazon CloudWatch monitoring
Set Amazon CloudWatch alarms on FreeStorageSpace (RDS) or VolumeBytesUsed (Aurora) to catch unexpected storage growth from orphaned large objects. In Amazon RDS Performance Insights, LWLock:OidGenLock wait events signal OID allocation pressure and large object creation might be slowing down.
Understand vacuumlo and its limitations
The vacuumlo utility is the standard PostgreSQL tool for removing orphaned large objects. Understanding how it works internally is critical to using it safely.
Learn how vacuumlo works
Step 1: Build the suspect list
vacuumlo creates a temporary table and inserts every OID from pg_largeobject_metadata. Initially, every large object is assumed to be orphaned.
Step 2: Scan all tables for references
For every table in the database, vacuumlo examines every column of type oid or lo. If a column contains a matching OID, that OID is removed from the suspect list (it’s referenced, therefore not orphaned).
Step 3: Delete remaining orphans
Everything still in the temporary table after scanning is considered orphaned. vacuumlo calls lo_unlink() on each remaining OID, deleting the large object data from pg_largeobject.
Review important limitations
As stated in the PostgreSQL documentation: “Only types with these names are considered. In particular, domains over them are not considered.” Names are the important thing to note here. vacuumlo finds candidate columns with a catalog query that filters on pg_type.typname IN ('oid', 'lo'). It matches the type’s name and never resolves a domain to its base type. This is why lo is still handled even though lo is itself a domain over oid. It qualifies because its name happens to be ‘lo’, not because vacuumlo sees through the domain.
Any other domain over oid is invisible to vacuumlo, however it is defined. That means it doesn’t detect large object references stored in:
- User-defined domain types (for example,
CREATE DOMAIN large_object_id AS OID). integerorbigintcolumns.textcolumns.- Custom type.
If your application stores OIDs in any of these column types, vacuumlo treats those referenced objects as orphaned and permanently deletes them. This deletion cannot be undone from within the database.
Clean up orphaned large objects safely with vacuumlo
Run vacuumlo regularly to help minimize issues with orphaned LOs. Follow these practices when using vacuumlo for safe and effective cleanup. However, it is difficult to reliably identify columns that hold large object references but use a data type other than oid or lo. PostgreSQL records no enforced link between a row and the large object it points to — the row stores the OID as a plain value, so a bigint, text, or custom-domain column can hold real references with nothing in the catalog marking them as such. You can infer likely references by matching a column’s values against live OIDs in pg_largeobject_metadata, but that’s evidence, not proof, so final confirmation must come from the application team.
Run the pre-flight schema check first
Before running vacuumlo, confirm that every large object reference in your database lives in a column typed oid or lo. The following query is the exact catalog query vacuumlo itself runs to decide which columns to scan, taken from contrib/vacuumlo/vacuumlo.c (PostgreSQL 17):
Important: This list is what vacuumlo protects. A column holding large object OIDs under another type, such as a domain over oid, integer, bigint, text, or a composite type, is absent from it, and the objects it references are deleted with no undo.
Compare the list against where your application stores large object OIDs. If the application names a column that is not here, or you cannot account for a column that is, do not proceed until the schema is resolved.
Run in dry-run mode first
Example output:
Review the output carefully. If the number of objects to be removed seems unexpectedly high, investigate before proceeding.
Fix schema mismatches before running vacuumlo
If your application stores large object OIDs in a domain type, integer, bigint, or text column, vacuumlo doesn’t recognize those references. Before running vacuumlo, align your schema so that all large object references use the oid or lo type. Alternatively, implement a custom trigger that calls lo_unlink() directly for those columns. Because converting an existing column to oid or lo requires a type-specific cast and can affect live data, plan and test the change on a non-production copy first. Take a snapshot before applying it in production. Following is the SQL to change column pdf_oid of table bad_refs from bigint to oid.
Schedule regular vacuumlo runs
Run vacuumlo during maintenance windows. We recommend completing all preceding actions before each production run. vacuumlo permanently deletes large objects it considers orphaned, and a missed reference column cannot be recovered after deletion.
Tune temp_buffers and batch size to improve vacuumlo performance
You can potentially improve the performance of vacuumlo by increasing the batch size using the -l option. This allows vacuumlo to process more LOs at once. This is helpful when the database has lots of orphaned large objects to remove.
If your system has sufficient memory and you can accommodate the temporary table completely in memory, increasing the temp_buffers setting at the database level might improve performance. This allows the table to reside entirely in memory, which can enhance the overall performance. The following query estimates the size of the temporary table:
Run VACUUM on pg_largeobject after cleanup
After vacuumlo removes orphaned objects, autovacuum eventually reclaims the space. To reclaim it immediately, run:
Use lo_manage triggers for prevention
Rather than relying solely on periodic vacuumlo runs, set up triggers that automatically unlink large objects when references change:
Important trigger limitations: The lo_manage trigger function works only in the context of updating or deleting column data, depending on how you define the trigger. It has no effect when you perform a DROP or TRUNCATE operation on a database. To avoid creating orphaned objects, delete object columns from tables before you drop them.
Monitor and avoid OID exhaustion
Every large object is identified by an OID, allocated when the object is created and recorded in pg_largeobject_metadata. The application stores that OID (usually in an oid or lo column) and uses it to read the data back later. Each object holds its OID until the object is removed. That OID comes from a single, cluster-wide counter that wraps at 2³² (~4 billion), shared by TOAST tables, large objects, and catalog objects alike. TOAST tables are usually the largest consumer. Large objects are next, and consumption builds up fast in systems that frequently create and delete them (document stores, media pipelines, file attachments).
Because OIDs must be unique, once the space is densely populated PostgreSQL must skip in-use values and retry to find an available one. This retry process is serialized through OidGenLock. Creating a large object then slows down or fails, surfacing in Performance Insights as the LWLock:OidGenLock wait event. Because applications often create large objects in latency-sensitive code paths, this wait shows up as slow responses or timeouts — even when the rest of the database looks fine.
Monitor OID usage before you hit this ceiling. For identifying and resolving OID exhaustion in TOAST tables, check Identifying and resolving performance issues caused by TOAST OID contention in Amazon Aurora PostgreSQL Compatible Edition and Amazon RDS for PostgreSQL.
Prepare for major version upgrades
During a major version upgrade, PostgreSQL runs pg_upgrade internally and it involves two key phases: dumping the schema using pg_dump and restoring it to the new cluster using pg_restore. During the database upgrade process, pg_dump and pg_restore operations can consume substantial memory when handling a large volume of database objects. This high memory usage can result in outcomes such as an extended upgrade duration or an unexpected termination of the upgrade process because of an out-of-memory (OOM) condition.
Complete the pre-upgrade checklist
- Check large object counts in each database:
- If counts have grown large (tens to hundreds of millions):
- Run
vacuumloto remove orphaned objects (after the pre-flight schema check). - Run
VACUUM FREEZEon each database. - If there are lots of large objects remaining after
vacuumlo, consider scaling up your instance class for the duration of the upgrade to provide additional memory forpg_dumpandpg_restore, then scale back down afterward. - Allocate a long maintenance window (upgrades with high large object counts can take many hours).
- Test the upgrade on a clone or restored snapshot before attempting it on production.
- Run
Clean up
If you created test resources while following the guidance in this post, remove them to avoid unnecessary charges:
Note: CASCADE also drops any lo-typed columns and their data, along with the lo_manage triggers attached to them. That is appropriate when tearing down a test database, but do not copy this into a production cleanup script without dropping those columns deliberately first.
If you created manual snapshots or restored test clusters during major version upgrade testing, delete them through the Amazon RDS console or using the AWS Command Line Interface (AWS CLI):
Aurora PostgreSQL:
Amazon RDS for PostgreSQL:
Considerations and limitations
vacuumlo detection:
vacuumloidentifies candidate columns by type name, filtering onpg_type.typname IN ('oid', 'lo'). It doesn’t resolve a domain to its base type, so a domain overoidwith a different name isn’t included. Columns typedinteger,bigint,text, or a composite type aren’t included.- If a large object is referenced only by a column that
vacuumlodoesn’t scan,vacuumlotreats that object as orphaned and removes it. You can’t recover a removed large object from within the database. - PostgreSQL doesn’t record an enforced relationship between a row and the large object its OID refers to. Matching column values against
pg_largeobject_metadatacan indicate likely references, but the result is evidence rather than confirmation. Verify the schema with your application team. vacuumloscans ordinary tables and materialized views (relkind in (‘r’,‘m’)). It doesn’t scan views, foreign tables, or partitioned parent tables. For a partitioned table, the individual partitions appear in the scan instead of the parent.
lo_manage triggers:
lo_manageruns on UPDATE and DELETE of the column data, depending on how you define the trigger.DROP TABLEandTRUNCATEbypass row-level triggers, solo_managedoesn’t run for either operation. Delete the rows before you drop or truncate a table that references large objects.
Privileges:
- Reading
pg_largeobjectdirectly requires elevated privileges. On Amazon RDS and Amazon Aurora, run the health check and inventory queries as a member of therds_superuserrole.
Conclusion
Orphaned large objects are one of the few PostgreSQL maintenance problems autovacuum won’t catch. The monitoring queries in this post show you how many orphaned large objects exist and how quickly they are building up. Running vacuumlo with the pre-flight check and dry-run steps removes orphans without risking live data, provided you have verified that no domain-typed columns reference large objects outside vacuumlo’s visibility. If a major version upgrade is on your roadmap, reducing large object counts beforehand and scaling up your instance can help prevent the out-of-memory failures that pg_upgrade encounters when processing millions of objects.
To learn more, see the lo extension documentation and the related blog post Identifying and resolving performance issues caused by TOAST OID contention in Amazon Aurora PostgreSQL Compatible Edition and Amazon RDS for PostgreSQL.