Understanding PostgreSQL TOAST (The Oversized-Attribute Storage Technique)

TOAST (The Oversized-Attribute Storage Technique) is a PostgreSQL mechanism for storing very large field values. PostgreSQL stores data in fixed‑size pages (default 8 KB) and does not allow a single row to span multiple pages. When a row becomes too large, TOAST automatically compresses the data and, if necessary, moves it to an external TOAST table.

Each table column in PostgreSQL can have one of four TOAST storage strategies:

PLAIN : No compression, no external storage (e.g., integer).

EXTENDED : Allows compression first; if still too large, external storage is used.

EXTERNAL : Allows external storage but disables compression, which can be beneficial for data that is frequently accessed in parts.

MAIN : Allows compression but tries to avoid external storage; external storage is used only as a last resort.

Example: create a simple blog table.

postgres=# create table blog(id int, title text, content text);
\d+ blog;
   Table "public.blog"
 Column | Type  | Modifiers | Storage  | Stats target | Description
--------+-------+-----------+----------+--------------+------------
 id     | integer |           | plain    |              |
 title  | text    |           | extended |              |
 content| text    |           | extended |              |

Inspecting the table shows that integer uses the plain strategy while text uses extended . PostgreSQL automatically creates a TOAST table for columns that need it. The TOAST table can be found via:

postgres=# select relname,relfilenode,reltoastrelid from pg_class where relname='blog';
 relname | relfilenode | reltoastrelid 
---------+-------------+---------------
 blog    | 16441       | 16444

The corresponding TOAST table is pg_toast.pg_toast_16441 and its definition is:

\d+ pg_toast.pg_toast_16441;
TOAST table "pg_toast.pg_toast_16441"
   Column   | Type | Storage
------------+------+----------
 chunk_id   | oid  | plain
 chunk_seq  | integer | plain
 chunk_data | bytea | plain

The TOAST table contains three columns: chunk_id (identifies the original row), chunk_seq (order of chunks), and chunk_data (the actual stored bytes).

Insert a short content value (10 characters). No TOAST rows appear because the data fits within a page.

postgres=# insert into blog values(1, 'title', '0123456789');
postgres=# select * from blog;
 id | title | content
----+-------+----------
 1  | title | 0123456789

Repeatedly double the content length with UPDATE blog SET content = content || content WHERE id=1; . When the length exceeds roughly 2 KB, PostgreSQL starts storing chunks in the TOAST table. At a length of 327 680 bytes, two rows appear in the TOAST table, each just under 2 KB, showing that the extended strategy first compresses then externalizes the data.

postgres=# select chunk_id,chunk_seq,length(chunk_data) from pg_toast.pg_toast_16441;
 chunk_id | chunk_seq | length
----------+-----------+--------
 16439    | 0         | 1996
 16439    | 1         | 1773

Change the content column's storage strategy to EXTERNAL (disable compression):

postgres=# alter table blog alter content set storage external;
\d+ blog;
   Table "public.blog"
 Column | Type | Modifiers | Storage   | Stats target | Description
--------+------+-----------+-----------+--------------+------------
 id     | integer |           | plain    |              |
 title  | text    |           | extended |              |
 content| text    |           | external |              |

Insert a new row with short content, then double its size until it exceeds ~2 KB. At a length of 2 560 bytes, four rows appear in the TOAST table (two new chunk_id values), confirming that external storage is triggered without compression.

postgres=# select chunk_id,chunk_seq,length(chunk_data) from pg_toast.pg_toast_16441;
 chunk_id | chunk_seq | length
----------+-----------+--------
 16447    | 0         | 1996
 16447    | 1         | 1773
 16448    | 0         | 1996
 16448    | 1         | 564

Conclusions:

If the storage strategy permits compression, TOAST prefers to compress first.

Regardless of compression, data larger than roughly 2 KB triggers external storage.

Changing a column's TOAST strategy does not affect how existing data is stored.