The ticket said "product listing page got slow." The query had not changed. The indexes were fine. EXPLAIN looked clean. And yet a list endpoint that used to return in 40ms was now bouncing around 600ms under no extra load.
The cause turned out to be a column nobody on the team thought about: a description_html field that the marketing team had started pasting full landing-page content into. The list query did not even select it directly, but a SELECT * three layers down in an ORM did. Every row was dragging a detoast off disk.
TOAST is one of those PostgreSQL features that works so well you forget it exists, right up until it is the reason for your latency. So let's actually look at what it does.
What TOAST is and why it exists
A PostgreSQL heap page is 8KB. A row has to fit on a single page, which means without help a single wide value could blow past the page size. TOAST (The Oversized-Attribute Storage Technique) is how PostgreSQL handles values too big to store inline.
When a row gets too large, PostgreSQL compresses variable-length columns and, if that is not enough, moves them out of line into a separate TOAST table. The main row keeps a small pointer. This all happens automatically for types like text, jsonb, bytea, and arrays.
The threshold is TOAST_TUPLE_THRESHOLD, normally 2KB. Once a row exceeds it, PostgreSQL starts compressing and pushing the widest columns out until the row fits.
The cost you don't see in EXPLAIN
Here is the trap: the TOAST table is a separate relation with its own pages. When you read a toasted value, PostgreSQL has to fetch and decompress it. That cost frequently does not show up clearly in a plan, because the detoast happens during output, not during the scan you are reading.
So a query can look cheap, scan few rows, hit an index perfectly, and still be slow because each returned row triggers an out-of-line read and a decompression pass.
-- See how much of a table lives in TOAST
SELECT
c.relname AS table_name,
pg_size_pretty(pg_relation_size(c.oid)) AS main_size,
pg_size_pretty(pg_relation_size(t.oid)) AS toast_size,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind = 'r'
ORDER BY pg_relation_size(t.oid) DESC
LIMIT 20;Stop selecting columns you don't render
The single highest-leverage fix is almost never a database setting. It is selecting only the columns you actually use. A list view that shows a title and a price has no business pulling a 40KB HTML body for every row.
If you use an ORM, audit what it generates for list endpoints. SELECT * on a table with a fat jsonb or text column is the most common self-inflicted version of this problem I see.
Storage strategies are a real lever
Each column has a storage strategy you can change with ALTER TABLE. EXTENDED (the default for most varlena types) compresses then moves out of line. EXTERNAL stores out of line but skips compression, which makes substring reads on large values faster. MAIN prefers keeping the value inline and compressed. PLAIN disables both.
If you frequently read a prefix of a large value with substr() or LIKE 'prefix%', EXTERNAL can be a meaningful win because PostgreSQL can fetch only the needed slices instead of decompressing the whole thing.
-- Favor slice reads on a large, rarely-compressible blob column
ALTER TABLE documents
ALTER COLUMN raw_payload SET STORAGE EXTERNAL;
-- Check current storage strategies
SELECT attname, attstorage
FROM pg_attribute
WHERE attrelid = 'documents'::regclass
AND attnum > 0
AND NOT attisdropped;Compression: pglz vs lz4
Since PostgreSQL 14 you can choose LZ4 for TOAST compression instead of the historical pglz. LZ4 compresses and, more importantly, decompresses much faster, usually at a small cost in ratio. For workloads dominated by reading large compressible values, switching can drop CPU noticeably.
Set default_toast_compression = 'lz4' for new values, or set it per column. Existing rows keep their old compression until they are rewritten, so a column rewrite or a fresh load is what actually migrates the data.
-- Per-column compression for new/updated values
ALTER TABLE documents
ALTER COLUMN body SET COMPRESSION lz4;
-- Verify which algorithm a stored value used
SELECT pg_column_compression(body) AS algo, count(*)
FROM documents
GROUP BY 1;Watch out for the update amplification
TOAST interacts badly with churn. If you update a row but do not touch its large toasted column, PostgreSQL is smart enough to keep the existing TOAST pointer rather than rewriting the blob. But if you do touch that column, even to write the same value, you rewrite the whole out-of-line value and create dead tuples in the TOAST table that VACUUM later has to clean.
Tables with a wide column and a hot update pattern can grow TOAST bloat faster than the main table. If your TOAST size dwarfs the main relation and keeps climbing, check whether you are rewriting big columns unnecessarily.
A short checklist when a query is mysteriously slow
- Does the query select a wide text/jsonb/bytea column it does not need?
- Is the TOAST table large relative to the main table?
- Are you reading prefixes of large values that EXTERNAL storage would speed up?
- Would LZ4 cut decompression CPU for this read pattern?
- Is a hot update path rewriting a large column and bloating the TOAST table?
The practical standard
The best PostgreSQL performance work is boring in the right way. Name the failure mode, capture the before plan or metric, make one change, and compare the exact same signal afterward. Anything else is just a more confident guess.