PostgreSQL TOAST Storage Models

The main problem TOAST solves is fundamental : postgres pages are of 8kb, and tuple must fit within that page. so what happens if you try to insert 1MB of text field ?

Without toast you would get an error: "Row too big". With toast PostgreSQL handles the large value by compressing it and if it is still large, it is broken down into chunks and stored in separate TOAST table.

Here is how it works. every table has potentially at least one large column gets assosiated with TOAST table. you don't see these tables in your normal schema, rather they live in special pg_toast schema and have auto generated names like pg_toast_16223. each TOAST table has the same structure.

CREATE TABLE pg_toast.pg_toast_16385 (
    chunk_id OID,
    chunk_seq INT,
    chunk_data BYTEA
);

When you insert a row with a large column, PostgreSQL first tries to fit the entire row in the page. If it doesn't fit, it tries compressing the large columns using the pglz algorithm (or lz4 in newer versions). If a 10KB text field compresses down to 1KB, great—it now fits inline, and no TOAST table is needed.

But if after compression it's still too large, PostgreSQL takes the compressed data and chunks it into ~2KB pieces, inserts those pieces into the TOAST table, and replaces the large column in the main tuple with an 18-byte TOAST pointer. That pointer contains the chunk_id and enough metadata to reassemble and decompress the original value.

This happens completely transparently. When you query the column, PostgreSQL sees the TOAST pointer, reads the chunks from the TOAST table, reassembles them, decompresses, and returns the value. You don't have to do anything special.

But there are performance implications. Fetching a TOASTed column is much more expensive than fetching an inline column. If the main table is cached in memory but the TOAST table isn't, you'll incur I/O. If the value is split into 100 chunks, you're doing 100 additional tuple fetches.

This is why the golden rule of PostgreSQL performance is: only SELECT the columns you need. If you do SELECT * on a table with a large TEXT column, you'll fetch and decompress that column even if you don't use it. If you do SELECT id, name instead, the TOAST column is never touched.

You can control TOAST behavior per column with the SET STORAGE option:

ALTER TABLE mytable ALTER COLUMN mycolumn SET STORAGE EXTERNAL;

TOAST Storage Models

---

PLAIN means no TOAST at all. This is for fixed-sized types that can't be TOASTed. eg INT

EXTENDED is the default option. it tries to compress, and if still too large move out of the line to the TOAST table.

EXTERNAL means skip compression but do move out of line if needed. This is useful in those scenarios where you have already compressed data from the client side like JPEG image, compressing those again on database level could waste CPU cycle.

MAIN means prefer to keep the value inline. Try compression, and only move out-of-line as a last resort. This is useful for frequently accessed columns where you want to avoid TOAST overhead.

TOAST in Action

---

CREATE TABLE toast_test(id INT, small_data TEXT, large_data TEXT);
-- small value stored inline
INSERT INTO TOAST_TEST VALUES (1, 'hello', 'hello');
SELECT oid, reltoastelid FROM pg_class where relname = 'toast_test'

You'll see something like toast_demo and pg_toast.pg_toast_16385. Now:

-- Check TOAST table (should be empty)
SELECT COUNT(*) FROM pg_toast.pg_toast_16385;

Zero rows, because we haven't inserted anything large. Now:

-- Large value: triggers TOAST
INSERT INTO toast_demo VALUES (2, 'small', repeat('x', 10000));

-- Check TOAST table again
SELECT chunk_id, chunk_seq, length(chunk_data) 
FROM pg_toast.pg_toast_16385 
ORDER BY chunk_id, chunk_seq;

Now you'll see several rows, each with about 2000 bytes of data, representing the chunks of your 10KB value.

TOAST has an interesting interaction with VACUUM. When you update or delete a row that has TOASTed columns, the old version's TOAST chunks remain in the TOAST table until VACUUM runs. If you do a lot of updates, your TOAST table can become bloated with unreferenced chunks. This is why monitoring TOAST table size is important for tables with large columns.