A database stores rows in fixed-size pages. A row must fit in a page, which puts a hard ceiling on how large a single row can be — and a column holding a long document or a large JSON structure would exceed it easily.
The mechanism that resolves this moves oversized values out of the main table into separate storage, leaving a pointer behind. It is transparent, and it produces performance behaviour that looks arbitrary until you know it exists.
What changed in 2026
- JSON columns made it common. Storing documents in relational tables put large values into far more schemas — see JSON columns.
- Compression options expanded. Choice of compression algorithm per column became more widely available.
- The
SELECT * cost became better known. The relationship between column selection and out-of-line fetches entered general awareness.
- Storage strategy tuning stayed niche. Per-column strategy settings remained available and rarely adjusted.
What happens to a large value
When a row would exceed the page limit, the database works through options in order:
Compress the value. Frequently enough to bring the row under the limit, in which case nothing is moved. Text and JSON compress well.
Move it out of line. If still too large, the value is split into chunks and stored in a separate associated table, with the main row keeping a reference.
Both. Compress and then move, for very large values.
The result is that a table with a large text column has two physical structures: the main table holding ordinary columns and pointers, and a secondary store holding the large values.
Why column selection matters so much
This is the practical consequence and the reason to care.
A query that does not reference the large column reads only the main table. Those pages are small, so many rows fit per page, and scans are fast — the large values are simply not touched.
A query that does reference it must additionally fetch the value from the secondary store, which is an extra read per row.
| Query |
Reads |
Relative cost |
SELECT id, name |
Main table only |
Low |
SELECT id, document |
Main table plus out-of-line fetch per row |
High |
SELECT * |
Everything, always |
Highest |
WHERE document @> ... |
Must fetch to evaluate |
High |
So SELECT * on a table with large columns is not slightly more expensive than selecting what you need — it can be an order of magnitude worse, because every row triggers an additional fetch of data you are discarding.
This is the single most actionable thing about the mechanism: name your columns. It is advice people follow for style reasons and the performance reason is larger than the style one here.
It also explains an observation that otherwise looks strange: adding a large column to a table does not slow down existing queries that ignore it. The data is not in the pages those queries read.
Updates rewrite the whole value
Out-of-line values are not updated in place. Changing one character in a large document writes an entirely new copy, with the old one remaining until cleanup reclaims it — see MVCC explained.
On a table where large values are updated frequently, that produces substantial write volume and bloat. A JSON document updated on every request, with one field changing, rewrites the whole document every time.
Where that pattern appears, splitting the frequently-updated field into its own small column — or a separate table — avoids rewriting the large value repeatedly. Generated columns do not help here, since they derive from the document rather than replacing it.
Common mistakes
SELECT * on tables with large columns. The dominant avoidable cost.- Frequent small updates to large values. Rewrites everything each time.
- Filtering on a large column's contents without an index. Fetches every value to evaluate.
- Assuming column count affects scan speed uniformly. Untouched large columns are free.
- Not measuring the secondary storage size. It can dwarf the main table.
- Storing large values that are never queried. Consider whether they belong in the database at all.
FAQ
Does this apply to all databases?
The specific mechanism and naming vary by engine; the general behaviour — large values stored separately with a pointer — is common. Check the details for yours.
Can I control it?
Most engines expose a per-column storage strategy, letting you prefer or avoid compression and out-of-line storage. Rarely worth adjusting unless you have measured a specific problem.
How do I see how much space it uses?
Engines expose the size of the associated storage separately from the main table. Worth checking on tables with large columns; the ratio is frequently surprising.
Should large files go in the database at all?
Frequently not. Object storage with a reference in the database is usually better for genuinely large binary content, and keeps your database smaller and backups faster.
Where to go next
For the column type this most affects, read JSON columns. For the version churn updates produce, MVCC explained, and for indexing inside stored documents, GIN indexes.