When a PostgreSQL Row Outgrows Its Page: Inside TOAST

Published: 2026-06-21

PostgreSQL stores tuples inside pages, and the default page size is 8 KB. A tuple cannot span pages. When one column value grows too large, PostgreSQL moves it out of the main heap into a separate TOAST table. Large text values, for example, may be compressed, split into chunks, and stored elsewhere. Insertion triggers this path when a tuple would exceed roughly 2 KB, about one quarter of a page. Before storing, PostgreSQL compresses the value; if compression brings it back under the limit, it stays inline. If not, it is split into chunks in the corresponding TOAST table.

Setting up a test case

On macOS, install PostgreSQL with Homebrew:

$ brew update
$ brew tap homebrew/core
$ brew install postgresql
$ brew services start postgresql

Create a login role and allow it to create databases:

CREATE ROLE dong WITH LOGIN PASSWORD 'password123';
ALTER ROLE dong CREATEDB;

Create a simple table:

CREATE TABLE public.t
(
     A INTEGER,
     B INTEGER,
     C VARCHAR
);

Not every table gets a TOAST table. A table needs at least one variable-length column before PostgreSQL creates a companion TOAST table for oversized values.

For an initial test, I inserted a long repeated string into C. This turned out to be a poor test value. The repeated pattern compresses very well, so the value stays in the main page and never appears in the TOAST table. If you want to observe TOAST directly, use something closer to 100 KB of Base64-encoded image data as the variable-length value. The repeated string can still be useful for seeing compression on the raw page, but it is not a reliable way to force TOAST storage.

BEGIN;
INSERT INTO t (A, B, C) VALUES (1, 1, 'eW6vtvRwaDq9nRveYLPNQ2jePsZWDmmm7t28BP8QRQMLFT5S3KVwGjsaHPxLDxjMkaR9GHn8mNXCY6ZKDkVRg7Fb2QivMeJrQJgDypLiWVKvibv8nXhy8f4XbwrvaZckeW6vtvRwaDq9nRveYLPNQ2jePsZWDmmm7t28BP8QRQMLFT5S3KVwGjsaHPxLDxjMkaR9GHn8mNXCY6ZKDkVRg7Fb2QivMeJrQJgDypLiWVKvibv8nXhy8f4XbwrvaZckeW6vtvRwaDq9nRveYLPNQ2jePsZWDmmm7t28BP8QRQMLFT5S3KVwGjsaHPxLDxjMkaR9GHn8mNXCY6ZKDkVRg7Fb2QivMeJrQJgDypLiWVKvibv8nXhy8f4XbwrvaZckeW6vtvRwaDq9nRveYLPNQ2jePsZWDmmm7t28BP8QRQMLFT5S3KVwGjsaHPxLDxjMkaR9GHn8mNXCY6ZKDkVRg7Fb2QivMeJrQJgDypLiWVKvibv8nXhy8f4XbwrvaZckeW6vtvRwaDq9nRveYLPNQ2jePsZWDmmm7t28BP8QRQMLFT5S3KVwGjsaHPxLDxjMkaR9GHn8mNXCY6ZKDkVRg7Fb2QivMeJrQJgDypLiWVKvibv8nXhy8f4XbwrvaZckeW6vtvRwaDq9nRveYLPNQ2jePsZWDmmm7t28BP8QRQMLFT5S3KVwGjsaHPxLDxjMkaR9GHn8mNXCY6ZKDkVRg7Fb2QivMeJrQJgDypLiWVKvibv8nXhy8f4XbwrvaZckeW6vtvRwaDq9nRveYLPNQ2jePsZWDmmm7t28BP8QRQMLFT5S3KVwGjsaHPxLDxjMkaR9GHn8mNXCY6ZKDkVRg7Fb2QivMeJrQJgDypLiWVKvibv8nXhy8f4XbwrvaZckeW6vtvRwaDq9nRveYLPNQ2jePsZWDmmm7t28BP8QRQMLFT5S3KVwGjsaHPxLDxjMkaR9GHn8mNXCY6ZKDkVRg7Fb2QivMeJrQJgDypLiWVKvibv8nXhy8f4XbwrvaZckeW6vtvRwaDq9nRveYLPNQ2jePsZWDmmm7t28BP8QRQMLFT5S3KVwGjsaHPxLDxjMkaR9GHn8mNXCY6ZKDkVRg7Fb2QivMeJrQJgDypLiWVKvibv8nXhy8f4XbwrvaZckeW6vtvRwaDq9nRveYLPNQ2jePsZWDmmm7t28BP8QRQMLFT5S3KVwGjsaHPxLDxjMkaR9GHn8mNXCY6ZKDkVRg7Fb2QivMeJrQJgDypLiWVKvibv8nXhy8f4XbwrvaZckeW6vtvRwaDq9nRveYLPNQ2jePsZWDmmm7t28BP8QRQMLFT5S3KVwGjsaHPxLDxjMkaR9GHn8mNXCY6ZKDkVRg7Fb2QivMeJrQJgDypLiWVKvibv8nXhy8f4XbwrvaZckeW6vtvRwaDq9nRveYLPNQ2jePsZWDmmm7t28BP8QRQMLFT5S3KVwGjsaHPxLDxjMkaR9GHn8mNXCY6ZKDkVRg7Fb2QivMeJrQJgDypLiWVKvibv8nXhy8f4XbwrvaZckeW6vtvRwaDq9nRveYLPNQ2jePsZWDmmm7t28BP8QRQMLFT5S3KVwGjsaHPxLDxjMkaR9GHn8mNXCY6ZKDkVRg7Fb2QivMeJrQJgDypLiWVKvibv8nXhy8f4XbwrvaZckeW6vtvRwaDq9nRveYLPNQ2jePsZWDmmm7t28BP8QRQMLFT5S3KVwGjsaHPxLDxjMkaR9GHn8mNXCY6ZKDkVRg7Fb2QivMeJrQJgDypLiWVKvibv8nXhy8f4XbwrvaZck');
COMMIT;

The raw-page route also shows why this test is misleading. Repeated data compresses down to short repeated markers, so a raw page may show only a compact pattern rather than a TOAST row. A Base64 image payload will compress less effectively, exceed the size limit, and eventually be written into the TOAST table.

Finding the TOAST table

Get the TOAST table name and OIDs:

SELECT reltoastrelid::regclass FROM pg_class WHERE relname = 't'; -- pg_toast.pg_toast_16386
SELECT relname FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE relname='t'); -- pg_toast_16386

SELECT oid FROM pg_class WHERE relname = 't'; -- 16386
SELECT oid FROM pg_class WHERE relname = 'pg_toast_16386'; -- 16389

Query the TOAST table and its index:

SELECT
    relname,
    relpages,
    oid
FROM
    pg_class,
    ( SELECT reltoastrelid FROM pg_class WHERE relname = 't' ) AS ss
WHERE
    oid = ss.reltoastrelid
OR oid = ( SELECT indexrelid FROM pg_index WHERE indrelid = ss.reltoastrelid )

Output:

pg_toast_16386    0    16389
pg_toast_16386_index    1    16390

Looking at raw pages

SELECT * FROM get_raw_page('t', 0);

If the function is missing, PostgreSQL reports an error:

ERROR:  function get_raw_page(unknown, integer) does not exist
LINE 1: SELECT * FROM get_raw_page('t', 0)
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Install the pageinspect extension:

-- Check whether pageinspect is installed
SELECT * FROM pg_extension;

-- Install pageinspect; otherwise get_raw_page() is unavailable
create EXTENSION pageinspect;

If permissions are insufficient, grant superuser access:

-- Existing user
ALTER ROLE dong WITH SUPERUSER;

-- New user
CREATE ROLE super WITH SUPERUSER;

Then run the raw-page query again:

SELECT * FROM get_raw_page('t', 0);

In Navicat, the default display may show “(BLOB) 8KB”. Switch the query editor to hexadecimal and click the BLOB column to inspect it.

Inspecting a raw page as hexadecimal in Navicat

The right side shows the repeated Base64-looking content, with repeated . ... markers below it. Those markers are the compressed representation of repeated data, and they explain why the earlier test did not write anything into the TOAST table. Once an image’s Base64 data is large enough that it remains over the limit after compression, it will be written into the TOAST table.

Reading TOAST contents

SELECT ctid, * FROM pg_toast.pg_toast_16386

Contents of a TOAST table

The output has several fields. chunk_id identifies a chunk record, chunk_seq is its sequence number, and chunk_data holds the data. In the first chunk you can see the stored value starting with data:image/jpeg;base64,/9j/4AAQSkZJRgABAQEASABIA, confirming that the payload was split and stored in pieces.

Storage strategies

PostgreSQL supports four strategies for variable-length columns:

PLAIN: the value cannot be compressed and cannot be stored in a TOAST table.
EXTENDED: the default; the value can be compressed and stored in a TOAST table.
EXTERNAL: the value cannot be compressed, but it can be stored in a TOAST table.
MAIN: the value can be compressed and stored in a TOAST table, but with lower priority than EXTENDED.

This design has tradeoffs. PostgreSQL does not use cross-page storage. Large values live elsewhere, which helps when filtering rows: the large column does not need to be read unless it is selected. That reduces disk I/O and can improve performance. The downside is writes. Large values must be chunked, inserted into the TOAST table, and indexed, which is slower than a sequential write path. Reading a full row also requires locating the TOAST table index and then reading the TOAST data, so it cannot take advantage of purely sequential reads in the same way.

For large text, VARCHAR without a length and TEXT behave the same in terms of performance. For images, audio, video, documents, and other binary data, bytea is the appropriate type. When bytea data crosses the threshold and moves to TOAST, PostgreSQL does not parse it; it stores the raw binary data.

Testing binary storage

CREATE TABLE public.t2
(
     A BYTEA
);

A small Python example:

import psycopg2

# Connect to the database
conn = psycopg2.connect("dbname=tmp01 user=dong password=password123")

# Get a cursor
cur = conn.cursor()

# Open the image file and read its contents
with open('1.jpg', 'rb') as f:
    binary_data = f.read()

# Insert statement
insert_query = "INSERT INTO t2(a) VALUES(%s)"

# Execute the insert, passing bytes
cur.execute(insert_query, (binary_data,))

# Commit the transaction
conn.commit()

# Close the connection
cur.close()
conn.close()

After inserting, locate its TOAST table in the same way. The data will be split into chunks of about 1.95 KB, because the TOAST table, like the main table, has a size limit of about 2 KB per record.

Bytea data split into TOAST chunks

Changing the TOAST threshold

By default, PostgreSQL moves a column value to TOAST when it exceeds 2 KB. The value can be changed between 128 and 8160; 8160 is the maximum size of a PostgreSQL row.

ALTER TABLE t2 SET (toast_tuple_target=128);

Most tables do not need this adjustment. Lowering the threshold can be useful for a few reasons:

It frees space in the main table, which is a valuable resource.
It can improve query performance because large values are no longer in the main heap, making the main table smaller and more compact for single-row scans.
It supports incremental maintenance: every row modification in the main table requires a full-row update, while the TOAST table can handle non-full-row updates more efficiently.

Besides TOAST_TUPLE_TARGET, there is also TOAST_TUPLE_THRESHOLD; both default to 2 KB. When a column value exceeds TOAST_TUPLE_THRESHOLD, PostgreSQL tries compression. If the compressed value falls below TOAST_TUPLE_TARGET, it can remain in the main table instead of moving to TOAST.

Checking space usage

SELECT
  pg_size_pretty(pg_relation_size(oid)) table_size,
  pg_size_pretty(pg_relation_size(reltoastrelid)) toast_size
FROM pg_class
WHERE relname = 't';

Main table and TOAST table sizes

That covers the basic mechanics of TOAST. The next step would be to examine how these storage choices behave under a real workload.