Large object
A large object
is a special data type which provides stream-style access to data which is too large to manipulate as a whole.
A large object
can contain up to 4TB of data, compared to the maximum 1GB available with conventional data types via the TOAST mechanism.
Usage
Large objects
differ from normal database objects in that they are not manipulated via SQL commands (with the exception of ownership and permissions metadata). Instead, they can be uploaded, downloaded, modified and removed via a libpq
-based API, which is also exposed via system functions.
WAL and replication
Large objects
are written to WAL and are therefore also replicated via streaming replication.
SQL commands
System functions
psql commands
\lo_export
LOBOID FILE\lo_import
FILE [COMMENT]\lo_list
(also implemented as\dl
)\lo_unlink
LOBOID
Contrib modules
System catalog relations
Configuration parameters
Change history
- PostgreSQL 9.4
- PostgreSQL 9.0
ALTER LARGE OBJECT
command added (commit f1325ce2)pg_largeobject_metadata
system catalog table added (commit f1325ce2)lo_compat_privileges
added (commit f1325ce2)
- PostgreSQL 8.4
- PostgreSQL 8.0
COMMENT ON LARGE OBJECT
support added (commit 42ce74bf)
- PostgreSQL 6.5
- PostgreSQL 6.4
Large object support has always been present in PostgreSQL.
Examples
Basic creation and manipulation of a large object using psql's large object slash commands:
postgres=# \lo_import /tmp/random-meme.jpg 'Some meme' lo_import 16458
postgres=# \dl Large objects ID | Owner | Description -------+----------+------------- 16458 | postgres | Some meme (1 row) postgres=# ALTER LARGE OBJECT 16458 OWNER TO lo_usr; ALTER LARGE OBJECT postgres=# \dl Large objects ID | Owner | Description -------+--------+------------- 16458 | lo_usr | Some meme (1 row) postgres=# \lo_unlink 16458 lo_unlink 16458 postgres=# \dl Large objects ID | Owner | Description ----+-------+------------- (0 rows)
References
- PostgreSQL documentation: Large Objects
Useful links
- BLOB cleanup in PostgreSQL - December 2020 blog article by CyberTec
- PostgreSQL Toast and Working with BLOBs/CLOBs Explained - November 2019 tutorial by EnterpriseDB
- Playing with large objects in Postgres - September 2013 blog article by Michael Paquier