How do I manage large objects in an Amazon RDS for PostgreSQL table?

Short description

Large objects allow you to store up to several gigabytes of binary (BYTEA) and text (TEXT) data types in PostgreSQL. The primary types of large objects are binary large objects and character large objects. These types of large objects are used for multimedia data such as images, audio, and large amounts of text.

Resolution

To manage your large objects, complete the following steps:

Create a large object

To create a large object, confirm that the lo extension is installed. If the lo extension isn't installed, then install the lo extension. To create a new large object, run the lo_create function. For more information, see Creating a large object on the PostgreSQL website.

Example:
Note: Replace example-oid with the OID that you want to assign to the new large object.

SELECT lo_create (example-oid)

Example output:

postgres=> SELECT lo_create(43213);
lo_create
-------
43213
(1 row)

postgres=> SELECT oid,* FROM pg_catalog.pg_largeobject_metadata where oid=43213;
oid | lomowner | lomacl
-------+----------+--------
43213 | 16394 |
(1 row)

Remove a large object

Note: In Amazon RDS, full access to the pg_catalog.pg_largeobject table is granted only to the superuser role. By default, the Amazon RDS primary user that's created doesn't have direct access to the pg_catalog.pg_largeobject table.

To remove the pg_catalog.pg_largeobject, use lo_unlink to remove unused large objects. For more information, see Removing a large object on the PostgreSQL website. Also, you can use the pg_largeobject and pg_largeobject_metadata system catalog to identify and remove orphaned large objects.

Example:
Note: Replace example-oid with the OID of the large object that you want to remove.

SELECT lo_unlink (example-oid);

Example output:

postgres=> SELECT lo_unlink(43213);
lo_unlink
-------+----------+--------
1
(1 row)

postgres=> SELECT oid,* FROM pg_catalog.pg_largeobject_metadata where oid=43213;
oid | lomowner | lomacl
-----+----------+--------
(0 rows)

Prevent or remove orphaned large objects

To clean up and prevent orphaned large objects when the lo is updated or deleted, use the lo_manage trigger function. To identify and remove orphaned large objects, use the vacuumlo utility.

To remove an orphaned large object, complete the following steps:

1. To show the objects that can be removed, run vacuumlo with the -n option. For more information, see vacuumlo on the PostgreSQL website.
   Note: Replace example-cluster-instance with your cluster instance identifier, example-database with your database, and example-database-pw with your database password.

   $ vacuumlo -n example-cluster-instance -p 5433 -U postgres example-database Password:example-database-pw

   Example output:

   Connected to database "example-database"
   Test run: no large objects will be removed!
   Would remove example-0123456789 large objects from database "example-database".

2. Remove orphaned large objects based on your use case.

3. To confirm that the orphaned large objects are removed, run vacuumlo from your PostgreSQL client. Then, check the pg_largeobject_metadata.

Best practices for management of large objects

• To keep your database up to date, schedule regular maintenance tasks such as vacuuming.
• To periodically check for orphaned large objects and remove them, write custom scripts.
• To monitor database performance metrics and set up alerts for unexpected workload behavior, use Amazon CloudWatch.
• To include large objects in your logical backups, use the pg_dump and pg_restore tools.
• To manage large objects efficiently, optimize your database configuration. Adjust parameters such as work_mem and maintenance_work_mem.
• If performance decreases, then make sure that your large objects don't cause excessive memory or CPU usage. Use indexing and query optimization as needed.
• To avoid exceeding your allocated storage limits in Amazon RDS, monitor your storage usage.