Large Object Storage in Postgres

PostgreSQL provides multiple mechanisms for handling large binary objects (LOBs), optimizing storage and retrieval for data exceeding the typical row size limit. The two primary methods for storing large objects in PostgreSQL are BYTEA and the Large Object API (LOBs using OID). Additionally, PostgreSQL uses TOAST (The Oversized-Attribute Storage Technique) to efficiently manage large field values.

BYTEA: In-Row Binary Storage

The BYTEA data type allows storing binary data directly within a table row. It is useful for relatively binary objects up to 1GB per column but is constrained by PostgreSQL’s per-row size limitations, since it is stored directly in a table row and can be accessed via standard INSERT/SELECT/UPDATE Example:

CREATE TABLE images (
    id SERIAL PRIMARY KEY,
    data BYTEA
);
 
INSERT INTO images (data) VALUES (pg_read_binary_file('/path/to/file.jpg'));
SELECT id, encode(data, 'base64') FROM images; -- Fetch binary data in base64 format.

Large Object Storage (LOBs using OID)

PostgreSQL provides a Large Object API that allows storing binary objects exceeding BYTEA limits. These objects are stored outside the table and referenced via OID (Object Identifier), supporting up to 4TB. The data is stored in a separate system catalog (pg_largeobject) and requires special API functions (lo_import, lo_export, lo_open, lo_write, etc.) for interaction. Example:

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    doc_oid OID -- References the large object
);
 
-- Import a large object from a file
INSERT INTO documents (doc_oid) VALUES (lo_import('/path/to/large_file.pdf'));
 
-- Retrieve the OID and export the file
SELECT lo_export(doc_oid, '/path/to/exported_file.pdf') FROM documents;

PostgreSQL provides several functions for handling large objects:

• lo_import('/file/path') – Imports a file as a large object.
• lo_export(oid, '/file/path') – Exports a large object to a file.
• lo_unlink(oid) – Deletes a large object.
• lo_open(oid, mode) – Opens a large object for reading or writing.
• lo_read(lo_fd, length) – Reads from a large object.
• lo_write(lo_fd, data) – Writes to a large object.

SELECT lo_unlink(doc_oid) FROM documents WHERE id = 1; -- Delete a large object.

Important

Using Object Identifiers storage provides streaming access not available via BYTEA

TOAST (The Oversized-Attribute Storage Technique)

TOAST is PostgreSQL’s automatic mechanism for managing large field values that exceed the database’s page size (typically 8KB). Instead of storing large values directly in table rows, TOAST compresses the values and tries to store separately, using Out-of-line storage. PostgreSQL fetches the full value only when needed, reducing I/O overhead for queries that do not access large fields.

Consider a table storing large text fields:

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT  -- This field can be TOASTed if very large
);

If content exceeds 8KB, PostgreSQL will automatically TOAST it, storing it in a separate table (pg_toast_<table_oid>), optimizing performance.

TOAST Storage Strategies

Each column in a table can have one of the following TOAST storage strategies:

• PLAIN: No TOAST, data is stored in-row.
• EXTENDED (default for large fields): Uses compression and out-of-line storage.
• EXTERNAL: Stores large values out-of-line but without compression.
• MAIN: Stores values in the main table unless too large, in which case TOAST is used.

To check TOAST behavior:

SELECT attname, attstorage FROM pg_attribute WHERE attrelid = 'articles'::regclass;

Feature	BYTEA	Large Objects (OID)	TOAST (TEXT, JSONB, etc.)
Max Size	1GB	4TB	Several GB (TOAST-managed)
Stored In-Row	Yes	No	Partially (depending on TOAST strategy)
Supports Indexing	Yes	No	No
Requires Special API	No	Yes (lo_* functions)	No (automatic)
Compression	No	No	Yes (default)