TOAST Storage, Compression, Out-of-Line Values, and Large Attribute Tradeoffs

Learning outcomes

A ServiceHub work order starts storing diagnostic payloads and attachment metadata. Most rows are small, but some payloads are hundreds of kilobytes. PostgreSQL still uses fixed-size pages and does not let one heap tuple span pages. TOAST—The Oversized-Attribute Storage Technique—lets large variable-length values be compressed and/or moved out of the main heap while preserving ordinary SQL semantics.

1. Why TOAST exists

PostgreSQL's heap pages are fixed-size and tuples do not cross page boundaries. TOAST-able data types use a variable-length representation. When a row becomes too wide, PostgreSQL can compress values and/or replace the inline value with a small pointer to chunks stored in a per-table TOAST relation. This is transparent to SQL: the application still selects one text, bytea, jsonb, array, or other TOAST-able value.

TOAST is triggered based on physical row width, not a simplistic rule like “every text longer than 2 KB goes to another table.” Compressibility, other columns, storage strategy, table-level toast_tuple_target, and the actual stored representation all matter.

2. Build one deliberately large deterministic payload

DROP TABLE IF EXISTS app.ch08_large_payload;CREATE TABLE app.ch08_large_payload (    payload_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    work_code text NOT NULL UNIQUE,    status text NOT NULL,    payload text NOT NULL,    created_at timestamptz NOT NULL DEFAULT now());INSERT INTO app.ch08_large_payload(work_code, status, payload)SELECT 'WO-TOAST-1', 'open', string_agg(md5(g::text || ':servicehub'), '')FROM generate_series(1, 6000) AS g;SELECT payload_id, work_code, status, length(payload) AS charactersFROM app.ch08_large_payload;

The payload is deterministic but intentionally wide. Its exact compressed/stored size can vary with build and compression settings, so observe instead of asserting a byte count.

3. Compare compressible and hard-to-compress values

TOAST decisions are about physical representation, not just logical length. Add a second row containing a highly repetitive payload. Its logical character count can be similar to the first row while its compressed representation differs dramatically.

INSERT INTO app.ch08_large_payload(work_code, status, payload)VALUES ('WO-TOAST-2', 'open', repeat('SERVICEHUB-REPEAT-', 12000));SELECT payload_id, work_code, length(payload) AS logical_chars,       pg_column_size(payload) AS stored_bytes_reported,       pg_column_compression(payload) AS compression_method,       pg_column_toast_chunk_id(payload) AS toast_chunk_idFROM app.ch08_large_payloadORDER BY payload_id;

Do not expect both rows to choose the same representation. A compressed value can become small enough to remain inline, while a less-compressible value may move out-of-line. Conversely, an out-of-line value may itself be compressed. This is why “length > 2 KB means TOAST table” is an unreliable rule.

4. Discover the TOAST relation without querying its internal rows

SELECT c.oid::regclass AS main_relation,       c.reltoastrelid,       CASE WHEN c.reltoastrelid = 0 THEN NULL            ELSE c.reltoastrelid::regclass::text END AS toast_relationFROM pg_class AS cWHERE c.oid = 'app.ch08_large_payload'::regclass;SELECT pg_size_pretty(pg_relation_size('app.ch08_large_payload'::regclass)) AS heap_main,       pg_size_pretty(pg_table_size('app.ch08_large_payload'::regclass)) AS table_plus_toast_forks,       pg_size_pretty(pg_total_relation_size('app.ch08_large_payload'::regclass)) AS including_indexes;

pg_class.reltoastrelid links the main relation to its secondary TOAST table when one exists. Treat the TOAST table as PostgreSQL-owned storage. Do not build application SQL against its chunk_id/chunk_seq/chunk_data rows.

5. PostgreSQL 18 can expose compression and out-of-line chunk identity safely

SELECT payload_id,       pg_column_size(payload) AS stored_bytes_reported,       pg_column_compression(payload) AS compression_method,       pg_column_toast_chunk_id(payload) AS toast_chunk_idFROM app.ch08_large_payload;

pg_column_compression() returns the compression method when the datum is compressed and NULL otherwise. pg_column_toast_chunk_id() returns an identifier for an on-disk out-of-line TOAST value and NULL when the value is not stored that way. These are diagnostic functions, not business identifiers for attachments.

6. Column storage strategy is a policy for future storage decisions

SELECT attname,       CASE attstorage         WHEN 'p' THEN 'PLAIN'         WHEN 'e' THEN 'EXTERNAL'         WHEN 'm' THEN 'MAIN'         WHEN 'x' THEN 'EXTENDED'       END AS storage_strategy,       attcompressionFROM pg_attributeWHERE attrelid = 'app.ch08_large_payload'::regclass  AND attnum > 0 AND NOT attisdroppedORDER BY attnum;SHOW default_toast_compression;

ALTER TABLE app.ch08_large_payload  ALTER COLUMN payload SET STORAGE EXTERNAL;UPDATE app.ch08_large_payloadSET payload = payload || md5(clock_timestamp()::text)WHERE payload_id = 1;SELECT pg_column_compression(payload),       pg_column_toast_chunk_id(payload)FROM app.ch08_large_payloadWHERE payload_id = 1;

Changing SET STORAGE does not magically rewrite every existing datum. A newly stored/updated value is evaluated under the new strategy. For production schema changes, document whether a rewrite/update is intended and what WAL/I/O/storage consequences it creates.

7. Narrow projection can avoid work on large values

PostgreSQL's TOAST design allows the main heap tuple to carry a small pointer to the oversized value. If a query needs only payload_id, work_code, and status, it does not need to reconstruct the payload just to return those columns. This is a concrete reason to avoid habitual SELECT * on wide tables.

-- Narrow list view: no payload value requested.SELECT payload_id, work_code, statusFROM app.ch08_large_payloadORDER BY payload_id;-- Detail request: the large value is required.SELECT payload_id, length(payload) AS charactersFROM app.ch08_large_payloadWHERE payload_id = 1;

Do not invent performance numbers from this tiny table. On a real workload, validate with representative row widths, buffer/cache state, query plans, network transfer, and application behavior.

8. Updating a narrow column does not necessarily rewrite a large out-of-line value

PostgreSQL's TOAST machinery normally preserves unchanged field representations during UPDATE. That matters for a table where a large diagnostic payload is immutable but status changes frequently: changing status does not imply that PostgreSQL must fetch, recompress and rewrite the payload simply because the logical row received a new MVCC version.

SELECT payload_id, pg_column_toast_chunk_id(payload) AS before_chunkFROM app.ch08_large_payloadORDER BY payload_id;UPDATE app.ch08_large_payloadSET status = 'reviewed'WHERE payload_id = 1;SELECT payload_id, status, pg_column_toast_chunk_id(payload) AS after_chunkFROM app.ch08_large_payloadORDER BY payload_id;

For an unchanged out-of-line attribute, the stored representation is normally preserved. The exact chunk identifier is still an internal diagnostic value, but comparing it inside this one lab can illustrate that a narrow metadata update is not equivalent to rewriting the large payload.

9. toast_tuple_target is a table-level physical tradeoff, not a tuning slogan

The table storage parameter toast_tuple_target influences when PostgreSQL starts trying to compress/move long values and the width it tries to reach. Changing it affects newly stored tuples; it does not retroactively rewrite the table. Raising the target can keep more data inline, potentially improving some access patterns while making the heap wider; lowering it can move/compress more data, potentially making the main heap denser while increasing TOAST activity.

SELECT reloptionsFROM pg_classWHERE oid = 'app.ch08_large_payload'::regclass;-- Disposable demonstration only; future writes are evaluated under this policy.ALTER TABLE app.ch08_large_payload SET (toast_tuple_target = 3072);SELECT reloptionsFROM pg_classWHERE oid = 'app.ch08_large_payload'::regclass;

There is no universal “best” target. Measure representative row widths, projection patterns, cache behavior, compression availability and update frequency before changing it.

10. Wrong approach: “TOAST is PostgreSQL's blob service”

TOAST solves tuple/page representation inside PostgreSQL. It does not automatically provide content-addressed object naming, CDN delivery, streaming APIs, lifecycle policies, cheap archival tiers, malware scanning, or external-object integrity. Storing bytea in PostgreSQL can be correct when transactional coupling and object size/workload justify it; using external object storage can be correct when delivery/lifecycle economics dominate.

11. Lab verification and cleanup

SELECT payload_id, length(payload),       pg_column_compression(payload),       pg_column_toast_chunk_id(payload)FROM app.ch08_large_payload;SELECT pg_size_pretty(pg_relation_size('app.ch08_large_payload'::regclass)) AS heap_main,       pg_size_pretty(pg_table_size('app.ch08_large_payload'::regclass)) AS table_with_toast;

DROP TABLE IF EXISTS app.ch08_large_payload;

12. Production judgment and bridge

Use TOAST as transparent PostgreSQL storage machinery, not an API. Monitor main-table, TOAST and index sizes separately when wide attributes dominate. Lesson 3 moves from large values to frequent updates: when PostgreSQL can create a new row version without creating new entries in ordinary indexes, and how fillfactor affects that opportunity.

Authoritative references

• PostgreSQL 18 — TOAST
• PostgreSQL 18 — ALTER TABLE / SET STORAGE
• PostgreSQL 18 — CREATE TABLE storage parameters
• PostgreSQL 18 — pg_class reltoastrelid
• PostgreSQL 18 — System Administration Functions