Understanding TOAST in PostgreSQL

Introduction:

Handling large data efficiently is crucial in PostgreSQL, especially for long text, binary files, and JSON types. PostgreSQL uses a unique mechanism called TOAST (The Oversized-Attribute Storage Technique) to store large values efficiently. When data exceeds the standard page size (typically 8KB), PostgreSQL automatically compresses and moves it to a separate TOAST table, ensuring better performance and space efficiency.

What makes TOAST powerful is its transparency, users don’t need to take any action. PostgreSQL automatically manages TOAST storage, optimizing queries and reducing disk space usage. 

What is Toast?

TOAST is a PostgreSQL mechanism for handling large data types such as TEXT, BYTEA, and JSON when they exceed the database page size (8KB). Instead of storing the entire oversized value in the main table, PostgreSQL:

• Compresses large values using LZ compression.
• If still too large, store the data in a TOAST table, keeping only a reference in the main table.

This approach helps PostgreSQL efficiently manage large objects without increasing table bloat or memory consumption.

How TOAST Works

PostgreSQL follows a heap-based storage model where each row is stored in 8KB pages. If a column exceeds this limit, TOAST automatically kicks in with the following steps:

1. Compression of Large Values

Before storing data in a TOAST table, PostgreSQL first attempts to compress the value using the LZ compression algorithm. This reduces storage space and improves query performance.

2. Storing in TOAST Tables

If the compressed value is still larger than 8KB, PostgreSQL moves it to an automatically created TOAST table. The main table stores only a reference (pointer) to this data.

3. Efficient Retrieval

TOAST ensures that only necessary data is retrieved, improving performance. If only part of a large text or binary value is needed, PostgreSQL fetches just that portion instead of loading the entire object into memory.

4. TOAST Storage Strategies

PostgreSQL uses two storage strategies for TOAST:

Extended Storage: A mix of compression and partial storage is used to keep values within the main table while offloading excess data.

External Storage: The full value is stored in the TOAST table, with only a pointer in the main table.

Creating a Table with an Oversized Data Column

To demonstrate TOAST in action, let’s create a table with a large TEXT column:

postgres=# CREATE TABLE company (com_id BIGINT, com_name TEXT, com_state TIMESTAMP, com_emp INT);
CREATE TABLE

Insert records:

postgres=# INSERT INTO company (com_id, com_name, com_state, com_emp)
VALUES
(1, 'Apple Inc.', '1976-04-01 00:00:00', 147000),
(2, 'Microsoft', '1975-04-04 00:00:00', 181000);
INSERT 0 2

Checking TOAST Data for a Table

To check if a table uses TOAST storage, find its OID (Object Identifier):

postgres=#  SELECT oid, relname FROM pg_class WHERE relname = 'company';
  oid  | relname 
-------+---------
 35159 | company
(1 row)

Now, check the TOAST table associated with this OID:

postgres=# SELECT * FROM pg_toast.pg_toast_35159;
 chunk_id | chunk_seq | chunk_data 
----------+-----------+------------
(0 rows)

After inserting 2 records, no TOAST data is present because the inserted column size is below 8KB.

Insert Operation:

postgres=# INSERT INTO company (com_id, com_name, com_state, com_emp)
VALUES
(3, 'OpenSourceDB: A DataBase company ...', '2021-04-01 00:00:00', 187000);
INSERT 0 1

An attempt to insert a large text record into the company table.

After this insertion, check the TOAST table again:

postgres=# SELECT * FROM pg_toast.pg_toast_35159;
-[ RECORD 1 ]---
chunk_id   | 35164
chunk_seq  | 0
chunk_data | <toasted data>

The chunk_data column now contains multiple entries, indicating that PostgreSQL has used TOAST storage for the large text column.

Benefits of TOAST:

1. Efficient Storage: Compresses and offloads large data, preventing table bloat.
2. Performance Optimization: Fetches only required portions of large objects, reducing memory usage.
3. Automatic Management: PostgreSQL handles TOAST transparently without user intervention.
4. Data Integrity: Ensures relationships between main tables and TOAST tables remain intact.

Conclusion:

TOAST is a crucial PostgreSQL feature that ensures efficient storage and retrieval of large data types like text, binary files, and JSON. By leveraging compression and external storage, TOAST minimizes table bloat and improves query performance—all handled automatically by PostgreSQL. Understanding how TOAST works can help database administrators optimize database storage and enhance application performance when dealing with large datasets.