Database Reference
In-Depth Information
The preceding discussion was a small comparison and analysis to show you the
available choices in PostgreSQL to store binary data using
BYTEA
or large object
storage. A requirement is the best judge to opt any of these.
Implementing large objects
Things are well remembered when they are listed and this is how we will remember
PostgreSQL large objects implementation in our memory:
• Large objects, unlike
BYTEA
, are not a data type but an entry in a system table.
• All large objects are stored in the
pg_largeobject
system table.
• Each large object also has a corresponding entry in the
pg_largeobject_
metadata
system table.
• Large objects are broken up into chunks of default size and further stored as
rows in the database.
• These chunks in rows are B-tree indexed; hence, this ensures fast searches
during read/write operations.
• From PostgreSQL 9.3 onwards, the maximum size of a large object in a table
can be 4 TB.
• Large objects are not stored in user tables; rather, a value of the
Object
Identiier
(
OID
) type is stored. You will use this OID value to access the
large object. So, when you have to access a large object, you will reference
the OID value that points to a large object present on the
pg_largeobject
system table.
• PostgreSQL provides the read/write
Application Program Interface
(
API
)
that offers client- and server-side functions. Using this API, you can perform
operations such as create, modify, and delete on large objects. OIDs are used
in this function as a reference to access large objects, for example, to transfer
the contents of any ile to the database or to extract an object from the
database into a ile.
• From PostgreSQL 9.0 onwards, large objects now have an associated owner
and a set of access permissions. Retrieving data using these functions gives
you the same binary data you added. Examples of the functions are
lo_
create()
,
lo_unlink()
,
lo_import()
, and
lo_export()
.
• PostgreSQL provides the
ALTER LARGE TABLE
feature to change the deinition
of a large object. Remember that its only functionality is to assign a new owner.
