PostgreSQL TOAST: Transparent Compression for Oversized Values

I once saw a fairly typical production incident. An orders table had been receiving hundreds of thousands of rows per day without issue, but one day the API's P99 latency suddenly jumped to two seconds. The DBA investigated everything: CPU usage was low, the connection count was normal, and the buffer hit rate was 99%. The eventual cause was almost absurdly simple: someone had added a description TEXT column to the orders table, and the application had started storing multi-megabyte contract text in it.

It was still the same table with the same indexes, but reading each row now required an additional random I/O to retrieve that large value from a “shadow table” nobody had heard of. That is PostgreSQL TOAST: a feature that works silently in the background but can hit hard when you do not understand it.

This article explains TOAST in depth: how it stores and compresses data, how to choose among its four strategies, when it slows queries, and how large fields should be designed. By the end, one point should be clear: TOAST is elegant PostgreSQL engineering, but that elegance costs I/O, and I/O is never something you should pretend not to see.

1. The 8KB Page: PostgreSQL's Storage Rule

To understand TOAST, you first need to understand PostgreSQL's storage foundation.

PostgreSQL stores all data in pages, also called blocks, with each page occupying 8KB, or 8,192 bytes, by default. This 8KB size is not a configuration option; it is fixed at compile time through BLCKSZ. Every table, index, and row lives inside these 8KB units. A page can hold multiple rows, and PostgreSQL locates each row through line pointers within the page.

That raises a hard question: how large can one row, or tuple, be?

The answer is not 8KB, but considerably less. A page needs room for its Page Header, which occupies 24 bytes, as well as its line-pointer array, and it must be able to hold multiple rows. A single row therefore cannot consume the entire page. PostgreSQL's design assumes that a row should ideally stay below roughly one-eighth of a page; otherwise, each page can hold only one or two rows and space utilization collapses.

Real data does not respect that constraint:

• TEXT / VARCHAR: a Markdown article can easily exceed ten kilobytes
• JSONB: an API response or product specification can easily occupy hundreds of kilobytes
• BYTEA: an image, audio clip, or PDF often starts at the megabyte scale

That leaves the obvious question: how can an 8KB page hold a 5MB JSONB document?

The answer is TOAST.

2. How TOAST Works: Compress, Move Out of Line, and Leave a Pointer

TOAST stands for The Oversized-Attribute Storage Technique. It lets you put several megabytes of text into a TEXT field without worrying, as you would in MySQL, about choosing among TEXT, MEDIUMTEXT, and LONGTEXT. PostgreSQL makes no such distinction: everything is text, with TOAST handling the storage behind the scenes.

Trigger Threshold: About 2KB

PostgreSQL uses a threshold named TOAST_TUPLE_THRESHOLD to decide whether a row should be processed by TOAST. It is approximately 2,032 bytes, or about 2KB, roughly one-quarter of the page size.

Note that this threshold applies to the entire row, not to an individual field. When a row being written, including all of its fields, exceeds roughly 2KB, TOAST activates. It selects variable-length fields such as text, jsonb, bytea, varchar, and arrays, then processes them one by one until the row falls below the threshold.

Two Operations: Compression and Out-of-Line Storage

TOAST has only two ways to process a large value, applied in order:

First operation: compression. PostgreSQL attempts lossless compression. Its historical built-in algorithm is pglz, a lightweight LZ77 variant implemented by PostgreSQL itself. Starting with PostgreSQL 14, lz4 became available as an optional compression algorithm controlled through default_toast_compression, offering better speed and compression ratios. If compression reduces the value to less than one-quarter of its original size, PostgreSQL uses the compressed version and stores a marker in the row indicating that the data is compressed.

Second operation: moving the value out of line. If the compressed row still exceeds the threshold, PostgreSQL moves the entire value into a separate TOAST table. The original row keeps only an approximately 18-byte pointer, represented by the varatt_external structure, containing information such as the OID and length of the data in the TOAST table.

These two operations usually work together: compress first, then move the value out of line if it still does not fit. That is why a 5MB JSONB value may occupy only a few bytes in the main table: the row contains only a pointer.

What a TOAST Table Looks Like

For every table that might contain large values, PostgreSQL quietly creates a shadow table named according to the pattern pg_toast.pg_toast_<oid>. You can see it with \d+ table_name, or query it directly:

-- Show the TOAST table for the orders table
SELECT reltoastrelid::regclass AS toast_table
FROM pg_class
WHERE relname = 'orders';

-- Example result: pg_toast.pg_toast_16384

A TOAST table always has the same internal structure, consisting of three columns:

column     |  type   |  description
-----------+---------+--------------------------------
chunk_id   | oid     |  identifies the main-table value
chunk_seq  | int4    |  chunk number, starting from 0
chunk_data | bytea   |  actual data chunk

Its primary key is (chunk_id, chunk_seq). The third column is bytea, and the TOAST table could itself be subject to further TOASTing. PostgreSQL prevents recursion by limiting each chunk_data value to approximately 1,996 bytes, ensuring that a chunk never triggers TOAST again.

A 5MB value is therefore divided into more than 2,500 chunks of roughly 2KB, with each chunk stored as a row in the TOAST table. When reading the value, PostgreSQL retrieves those chunks through a primary-key range scan, joins them in order, decompresses them, and reconstructs the original 5MB value.

All of this is transparent. When you run SELECT description FROM orders, PostgreSQL handles chunk reconstruction and lz4 or pglz decompression in the background, and you receive the original text. That transparency is what makes TOAST elegant—and dangerous, because its cost is invisible.

3. The Four TOAST Strategies: PLAIN / EXTERNAL / EXTENDED / MAIN

Not every field follows the same processing rules. PostgreSQL assigns each field a storage strategy, represented by pg_attribute.attstorage, that determines whether it can be compressed or moved out of line. There are four strategies:

Here is how each one works:

PLAIN, the default for fixed-length types. Values are neither compressed nor moved out of line. Fixed-length types such as int4, bigint, boolean, and timestamp always use PLAIN because they cannot exceed the threshold and do not need TOAST. You also cannot change an int column to EXTENDED because it is not a variable-length type in the first place.

EXTENDED, the default for variable-length types. PostgreSQL first attempts compression, then moves the value into the TOAST table if it still exceeds the threshold. This is the strategy used by 99% of fields and PostgreSQL's default choice. A description TEXT column automatically uses EXTENDED when you create it.

EXTERNAL, which moves values out without compressing them. A value can be moved into the TOAST table, but PostgreSQL does not compress it. Reads therefore avoid decompression and save CPU. This is useful in two situations: the data is already compressed, such as PNG, JPEG, or WebP images, zip, gzip, or zstd archives, and encrypted ciphertext, where another compression pass wastes CPU without reducing size; or the application frequently accesses random substrings, such as substring(big_text, 100000, 200), because compressed values must be fully decompressed for every access.

MAIN, which tries to keep values inline. PostgreSQL compresses the value and tries to retain the compressed result in the main-table row. It moves the value to the TOAST table only when the row still exceeds the threshold after compression, and it may try to move it back into the main table if space later becomes available. This strategy is uncommon and mainly serves fields that are usually small but occasionally exceed the threshold, allowing ordinary values to remain with their rows for faster reads.

To inspect each field's strategy in a table:

SELECT attname, attstorage
FROM pg_attribute
WHERE attrelid = 'orders'::regclass
  AND attnum > 0;

-- attstorage: x=EXTENDED  e=EXTERNAL  m=MAIN  p=PLAIN

Alternatively, run \d+ orders. Its Storage column shows extended, external, main, or plain.

4. TOAST's Effect on Query Performance: It Is Not Free

TOAST is transparent to developers, but transparent does not mean free. This is the most important section of the article: TOAST costs I/O and CPU, not merely storage.

Cost 1: Every Large-Value Read Adds Random I/O

The main-table row contains only a pointer. When you run SELECT description FROM orders WHERE id = 100, PostgreSQL does the following:

1. Reads the row from the orders table and obtains the pointer stored in the description field
2. Uses that pointer to query the pg_toast_16384 table and retrieve every chunk
3. Reassembles the complete data in order and decompresses it

Step 2 adds another random I/O. A 5MB value can require reading more than 2,500 chunks, which means 2,500 small I/O operations, or fewer physical reads when buffers and read-ahead help. The cost may be unnoticeable when hot data is already buffered, but latency immediately climbs when the data resides on disk or concurrency increases.

Cost 2: SELECT * Is the Biggest Performance Killer in the TOAST Era

This is the easiest mistake to make. Many ORMs default to SELECT *, and developers often use it for convenience:

-- Disaster: you only need the title, but retrieve a 5MB description from every row
SELECT * FROM orders WHERE status = 'pending' LIMIT 100;

For each of those 100 rows, the description triggers a TOAST-table query and decompression. A request that should have read only titles through an index becomes 100 large-object reconstruction operations. Memory is exhausted, network capacity is saturated, and CPU time is spent decompressing data.

Hard rule: for every table containing large fields, explicitly list the required columns and never use SELECT *. Query the large value separately only when it is needed, through a second query or a separate endpoint.

-- List endpoint: do not touch the large field
SELECT id, title, status, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 100;

-- Detail endpoint: read it only when the user opens the item
SELECT id, description
FROM orders
WHERE id = $1;

This habit prevents 90% of the mysterious production slowdowns associated with TOAST.

Cost 3: WHERE and ORDER BY Can Also Trigger TOAST

This problem is subtler. Even if a query does not select the large field, using it in WHERE or ORDER BY still requires decompression:

-- Even without selecting description, filtering must decompress every value for comparison
SELECT id FROM docs WHERE body LIKE '%breach clause%';

This type of full-text matching is disastrous for large text. PostgreSQL must completely detoast every row's body before applying LIKE. That is why full-text search should use tsvector with a GIN index, rather than forcing LIKE to scan large fields.

Cost 4: CPU Overhead from Compression and Decompression

Every compressed TOAST value must be decompressed when read and compressed when written. pglz is reasonably fast and lz4 is faster, but this CPU cost becomes visible in high-concurrency workloads that write large values, such as a log table receiving thousands of rows per second with several kilobytes of JSONB in each row. If PostgreSQL processes show high CPU usage but low I/O wait, especially on tables with large fields, TOAST compression is worth investigating.

5. When to Tune a TOAST Strategy Manually

The default EXTENDED strategy is correct in most cases, so do not change it casually. A few common situations do justify manual tuning.

Scenario 1: the field contains already compressed binary data. Typical examples include images, PDFs, and gzip archives stored in BYTEA. This data is already compressed, so running pglz again cannot reduce it, may even make it slightly larger, and wastes CPU. Change the field to EXTERNAL:

ALTER TABLE user_files
  ALTER COLUMN file_data
  SET STORAGE external;

After the change, note that it affects only new writes and later updates. Existing rows are not rewritten automatically. To apply the strategy to old data, rewrite the table:

-- Caution: locks the table and performs a complete rewrite; schedule a maintenance window for large tables
VACUUM FULL user_files;
-- Or, more thoroughly
CLUSTER user_files USING user_files_pkey;

Scenario 2: large text requires frequent random substring reads. Consider a log tens of megabytes long where the application frequently uses substring() to read a section from the middle. With compression, retrieving one section still requires decompressing the entire value, so EXTENDED imposes the full decompression cost on every random access. After switching to EXTERNAL, PostgreSQL can locate the relevant chunks and read only a few 2KB fragments, saving substantial I/O and CPU.

Scenario 3: a field is almost never read but occupies too much space inline in the main table. EXTENDED already moves it out of line by default, but if you want a more compact main table, allowing more rows per page and better cache hit rates, consider moving the field explicitly into a separate child table, as described in the next section.

For the compression algorithm itself, PostgreSQL 14+ users should set default_toast_compression to lz4. lz4 clearly outperforms pglz in both compression ratio and speed. It can substantially reduce CPU consumption on tables with write-intensive large values:

-- Global default in postgresql.conf
-- default_toast_compression = 'lz4'

-- Or for a single column in PostgreSQL 14+
ALTER TABLE events
  ALTER COLUMN payload
  SET COMPRESSION lz4;

Again, SET COMPRESSION affects only new data. Existing data must be rewritten before the change applies.

The rule for choosing a strategy is simple: do not recompress data that is already compressed, do not compress values requiring random access, and use the default EXTENDED strategy for ordinary text that will be read as a whole.

6. Best Practices for Large Fields: Vertical Partitioning, Narrower SELECTs, and Separate JSONB Tables

Strategy tuning is tactical; table design is strategic. These recommendations come from hard-earned experience.

Best practice 1: vertically partition large fields.

Do not place large fields in the same table as hot data. In an orders table, keep only the small fields that are frequently queried and filtered, such as id, customer_id, status, amount, and created_at. Move a large field such as description into an order_details child table:

-- Hot table: almost every query scans it, so keep it narrow and cache-friendly
CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  customer_id bigint NOT NULL,
  status      text   NOT NULL,
  amount      numeric(12,2),
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- Cold table: joined only when viewing details
CREATE TABLE order_details (
  order_id    bigint PRIMARY KEY REFERENCES orders(id),
  description text
);

-- List query: touches only the narrow table for a high cache hit rate
SELECT id, customer_id, status, amount
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC LIMIT 100;

-- Detail query: retrieves the large field only when needed
SELECT o.*, d.description
FROM orders o
JOIN order_details d ON d.order_id = o.id
WHERE o.id = 123;

This is more controllable than relying on TOAST's automatic out-of-line storage. The main table remains narrow, more rows fit on each page, and buffer-pool hit rates improve significantly. TOAST solves “how can this value fit in one row?” Vertical partitioning solves “how can this table be read quickly?” The two approaches are compatible, but partitioning is an intentional design decision.

Best practice 2: never use SELECT *; specify the columns explicitly.

This was covered earlier, but it deserves repeating because it is the most common basic performance problem involving TOAST. Disable the ORM's default SELECT * behavior and make any SELECT * against a table with large fields a mandatory code-review blocker.

Best practice 3: place large JSONB documents in a separate table, or consider whether a document database is more appropriate.

JSONB is one of PostgreSQL's strongest features, but putting a 5MB JSONB document in one field is an anti-pattern. GIN indexes are extremely expensive for oversized documents because index entries balloon, while every read and write must detoast the entire value. If the application genuinely needs to store deeply nested, oversized documents, ask:

• Can the document be flattened into relational tables? This often performs better.
• Can the metadata, meaning fields that are frequently queried or filtered, remain in the main table while the JSONB body moves to a separate table?
• Should you evaluate MongoDB or object storage, with PostgreSQL storing only the metadata?

The decision rule is straightforward: if you almost never read the entire JSONB field and query only a particular path, it should not be JSONB; flatten it. If it is large and frequently read in full, place it in a separate table.

Best practice 4: do not use large values as index keys.

A single btree index entry is limited to roughly 2,712 bytes. Exceeding that limit produces index row size ... exceeds maximum. Even below the limit, placing large text in an index is disastrous: the index expands and writes slow down. To search large text, use tsvector full-text search or pg_trgm fuzzy matching so the index stores small tokens or trigrams rather than the original large value.

Best practice 5: account for WAL amplification when bulk-writing large values.

Every TOAST chunk write is recorded in WAL. Writing a 5MB value puts 5MB of chunk data plus the main-table update into WAL, creating substantial write amplification. For bulk imports of large values, such as an ETL job loading a collection of documents, process data in batches and consider disabling selected indexes before rebuilding them afterward.

7. Diagnostics: TOAST Table Size and Usage

Do not optimize without measuring. The following queries are a standard toolkit for investigating TOAST issues.

1. Measure a table's total size, including TOAST.

pg_total_relation_size() includes the main table, every index, and the TOAST table, while pg_relation_size() measures only the main table itself. Comparing the two shows how much space TOAST and indexes consume:

SELECT
  relname,
  pg_size_pretty(pg_relation_size(c.oid))        AS main_size,
  pg_size_pretty(pg_total_relation_size(c.oid))   AS total_size,
  pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS toast_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND c.reltoastrelid <> 0
  AND n.nspname = 'public'
ORDER BY pg_relation_size(c.reltoastrelid) DESC
LIMIT 10;

If a table's toast_size is much larger than its main_size, you have found a table whose main relation is effectively a display case for TOAST pointers and a candidate for vertical partitioning.

2. Measure the actual storage used by a field in a particular row.

pg_column_size() returns the field's actual on-disk footprint. This includes the TOAST pointer; before detoasting, it reflects the stored representation:

-- Show description's inline storage size; after TOASTing, this is usually only the pointer's few bytes
SELECT
  id,
  pg_column_size(description) AS stored_size,
  octet_length(description)   AS logical_size
FROM orders
WHERE id = 100;

If stored_size is only a few bytes while logical_size is 5MB, the value has been moved into the TOAST table. If both values are similar and small, the value remains inline.

3. Determine whether a value is compressed.

You can infer compression by comparing pg_column_size before and after decompression, or by using an operation such as substring to trigger detoasting and then checking octet_length. PostgreSQL 14+ also lets you inspect the column's compression algorithm directly:

SELECT attname, attcompression
FROM pg_attribute
WHERE attrelid = 'orders'::regclass AND attnum > 0;
-- attcompression: p=pglz  l=lz4  (empty means uncompressed or fixed-length)

4. Monitor TOAST-table I/O.

Use pg_stat_all_tables to inspect TOAST tables as well:

SELECT
  relname,
  seq_scan,
  seq_tup_read,    -- Rows read sequentially from the TOAST table; many large values make this high
  idx_scan,
  idx_tup_fetch
FROM pg_stat_all_tables
WHERE schemaname = 'pg_toast'
ORDER BY seq_tup_read + idx_tup_fetch DESC
LIMIT 10;

Frequent reads of tables in the pg_toast schema indicate that corresponding fields in the main tables are repeatedly being detoasted. Combine this information with field-size measurements to identify hotspots.

5. Determine whether TOAST is slowing a query.

Run EXPLAIN (ANALYZE, BUFFERS). If a query is slow even though the row counts and costs reported for its sequential or index scans are small, while actual execution time is far higher than expected, inspect the table's TOAST relation. Detoasting a large field is probably responsible. Combine this with \d+ table_name to identify columns using extended or external storage, and the culprit is usually easy to find.

Summary

• TOAST is PostgreSQL's automatic mechanism for storing large values that do not fit on an 8KB page. Its threshold is approximately 2KB. Values above it are compressed first and then moved into a separate pg_toast table, leaving only an 18-byte pointer in the main-table row. This is transparent, but not free.
• Remember the four strategies: PLAIN for fixed-length values with no processing, EXTENDED for compression followed by out-of-line storage and the default behavior, EXTERNAL for out-of-line storage without compression when data is already compressed or requires random access, and MAIN for keeping values inline whenever possible. On PostgreSQL 14+, switch the compression algorithm to lz4.
• TOAST costs I/O and CPU, not merely storage. Every large-value read adds a random read from the TOAST table plus decompression. SELECT * is the biggest performance killer, so always specify columns explicitly for tables containing large fields.
• Vertically partition large fields. Store hot fields separately from cold large values, keep the main table narrow, and preserve the cache hit rate that underpins performance. Flatten oversized JSONB or move it into a separate table rather than making a GIN index swallow an enormous document.
• Use three core diagnostics: pg_total_relation_size for total size, pg_column_size versus octet_length to determine whether a field has been TOASTed, and pg_stat_all_tables to inspect read amplification in the pg_toast schema.

One final point: TOAST lets you store a value, but never forget that reading it has a cost. Treat large fields as second-class citizens: leave them alone during ordinary operations and retrieve them explicitly only when needed. That is the simplest and most effective engineering discipline for handling oversized values.