Deferred Columns and Wide Row Fetches #

A list endpoint renders 50 articles with their titles and authors. The ORM selects every column of articles, including a body column averaging 40 kB, and a metadata jsonb column. The query reads 50 rows and 3,100 buffers, most of them from the TOAST table, and serialises 2 MB the application discards. Three characters of ORM configuration — naming the columns — cut it to 12 buffers.

Lazy loading of relations is covered in lazy loading plan artifacts. This page is about lazy loading of columns, which has its own plan signature.

The Condition #

PostgreSQL stores values larger than about 2 kB out of line in a TOAST table, leaving a pointer in the main row. Reading such a column means following the pointer and reading (and decompressing) the TOAST chunks. Crucially:

ORMs select all mapped columns by default. The ways to narrow them:

Each changes the SQL’s select list. The trap is symmetrical with relation lazy loading: touching a deferred column later issues another query per object, so deferral helps only when the column is genuinely unused on that code path.

Two select lists, same 50 rows Left, selecting every column: the heap rows are read, the body column is detoasted for each row, 3,100 buffers are touched and two megabytes are serialised. Right, selecting id and title: an index-only scan reads twelve buffers, no TOAST access happens, and eight kilobytes are serialised. SELECT * (all mapped columns) heap rows + TOAST chunks 3,100 buffers ≈ 2 MB serialised no index-only scan possible SELECT id, title index-only scan 12 buffers ≈ 8 kB serialised no TOAST access deferring a column that is later touched costs one query per row

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS, SERIALIZE)
SELECT id, title, body, metadata, author_id, created_at, updated_at, slug, status
FROM articles WHERE status = 'published' ORDER BY created_at DESC LIMIT 50;
Limit  (actual time=0.09..38.4 rows=50 loops=1)
  ->  Index Scan Backward using articles_status_created_idx on articles  (actual rows=50 loops=1)
        Index Cond: (status = 'published'::text)
        Buffers: shared hit=3104 read=18
Serialization: time=41.2 ms  output=2014kB  format=text
Execution Time: 80.1 ms
-- 3,122 buffers for 50 rows: the body column's TOAST chunks
-- serialization dominates: 2 MB converted for a list that shows titles

With a narrowed select list:

EXPLAIN (ANALYZE, BUFFERS, SERIALIZE)
SELECT id, title FROM articles WHERE status = 'published' ORDER BY created_at DESC LIMIT 50;
Limit  (actual time=0.03..0.04 rows=50 loops=1)
  ->  Index Only Scan Backward using articles_status_created_title_idx on articles  (actual rows=50 loops=1)
        Index Cond: (status = 'published'::text)
        Heap Fetches: 0
        Buffers: shared hit=12
Serialization: time=0.4 ms  output=8kB  format=text
Execution Time: 0.09 ms

And the deferral trap, when the template later touches body:

-- one query per rendered row, from the ORM's deferred-column loader
SELECT body FROM articles WHERE id = $1;    ×50
Three signals of an over-wide select list Three items are annotated. Buffers far above the row count show TOAST chunk reads. A large serialization time and output size show wide values being converted. An index scan instead of an index-only scan shows a covering index defeated by an extra column. Buffers: shared hit=3104 for 50 rows TOAST chunks per row Serialization: time=41.2 ms output=2014kB wide values converted Index Scan (not Index Only Scan) extra column defeats the covering index SERIALIZE is the only way to see detoasting cost in EXPLAIN

Step-by-Step Resolution #

  1. Find the wide columns:

    SELECT attname, avg_width FROM pg_stats WHERE tablename = 'articles' ORDER BY avg_width DESC LIMIT 5;
SELECT pg_size_pretty(pg_total_relation_size('articles')) AS total,
       pg_size_pretty(pg_relation_size('articles')) AS heap;

    A large gap between total and heap size is TOAST.

  2. Identify code paths that do not need them. List endpoints, search results, export headers and count queries rarely need document bodies.

  3. Narrow the select list in the ORM for those paths, using only/select/load_only.

  4. Verify no deferred column is touched afterwards. Django raises no error — it silently issues another query; SQLAlchemy can be configured to raise. Add a test asserting the query count for the endpoint.

  5. Build a covering index for the narrow list so the plan becomes an index-only scan, following covering indexes for ORDER BY and LIMIT.

  6. Measure with SERIALIZE, since output conversion is invisible otherwise.

  7. Consider a schema split when a wide column is rarely read: moving body to a side table with the same primary key makes every query on the main table narrow by construction.

Cost of the same 50-row list page Selecting all columns reads 3,122 buffers and serialises 2,014 kilobytes. Selecting id and title reads 12 buffers and serialises 8 kilobytes. Deferring the body but touching it in the template reads 3,140 buffers across 51 queries and serialises the same 2 megabytes. all columns 3,122 buffers, 2 MB out id, title only 12 buffers, 8 kB out deferred but touched 3,140 buffers, 51 queries deferral helps only when the column is truly unused

Before and After #

-- BEFORE: all mapped columns, including a 40 kB body
Index Scan  Buffers: shared hit=3104  Serialization: output=2014kB        Execution Time: 80.1 ms

-- AFTER: .only('id', 'title') + covering index
Index Only Scan  Heap Fetches: 0  Buffers: shared hit=12  output=8kB      Execution Time: 0.09 ms

Why the cost hides from ordinary EXPLAIN ANALYZE #

Without the SERIALIZE option, EXPLAIN ANALYZE discards result rows before they are converted to their output form, and detoasting only happens during that conversion. A wide-column query can therefore report an execution time of a few milliseconds while the application observes hundreds. That gap is one of the most common sources of “the database says it is fast” disagreements, and it is entirely explained by columns the query did not need to return.

The same effect makes column width a hidden factor in sort and hash memory. A Sort node’s Memory: figure includes the full width of every row it holds, so a query that sorts wide rows can spill while the same sort over ids fits comfortably. Where a wide column must eventually be returned but the query also sorts or aggregates, fetching ids first and joining back for the wide columns — a two-step pattern many ORMs support directly — keeps the expensive part narrow.

A quick audit for over-wide queries #

Two catalog queries find most candidates without touching application code. The first ranks tables by the share of their size that lives in TOAST, which identifies where wide columns exist at all. The second, over pg_stat_statements, finds statements whose rows count is small but whose shared_blks_hit per row is large — the signature of reading wide values for few rows. Together they point at the handful of endpoints worth narrowing, which is usually a much shorter list than the number of queries in an application.

Once those are fixed, the remaining wide-row reads are typically detail pages fetching one row, where reading the document is the point of the query and costs nothing to worry about.

Common Pitfalls #

Deferring a column the view uses. One extra query per row follows. Diagnostic signal: repeated single-column lookups by id. Fix: include the column, or stop using it on that path.

Assuming SELECT * is free because rows are few. Width, not count, drives TOAST reads. Diagnostic signal: high buffers per row. Fix: narrow the select list.

Wide columns in sorts. Memory grows with row width. Diagnostic signal: sorts spilling on small row counts. Fix: sort ids, then join.

Measuring without SERIALIZE. Detoasting stays invisible. Diagnostic signal: server time far below application time. Fix: capture with SERIALIZE.

Frequently Asked Questions #

Does selecting unused columns actually cost anything? #

Yes. Wide values stored out of line must be read and decompressed when the row is returned, and extra columns can prevent index-only scans and inflate sort memory. The cost is invisible unless you use the SERIALIZE option in EXPLAIN.

How do I select fewer columns in my ORM? #

Django uses only() or defer(), ActiveRecord uses select(), SQLAlchemy uses load_only() or deferred column mappings. Each changes the generated select list.

Why did deferring a column make things slower? #

Because something later touched the deferred column, so the ORM issued an extra query for every object. Deferral only helps on code paths that never access the column.

Up: Lazy Loading Plan Artifacts