skip to content

Several engines let you attach non-key payload columns to an index, spelled INCLUDE (...) in PostgreSQL and SQL Server. How do payload columns differ from putting those same columns at the end of the index key, and when would you choose each?

level: middleimportance: should knowfreq 42%

answer

  1. Key columns: branch pages + leaves; payload: leaves only
  2. Payload = projectable, not seekable/sortable
  3. Keeps fanout high, tree shallow
  4. UNIQUE(email) INCLUDE(name) preserves the constraint
  5. Write cost is the same either way

basics

~20 s

Payload columns are stored only in leaf entries: they can be returned but never used for seeking, ordering or uniqueness. Key columns are stored in internal nodes too and can do all three. Use payload for columns you only need to output, key columns when you must filter or sort by them.

solid answer

~60 s

Both make an index cover more queries; the difference is *where* the column lives and what it can do. **Key columns** participate in the sort order and appear in internal (branch) pages as well as leaves. They can drive a seek, extend the leftmost prefix, satisfy `ORDER BY`, and participate in uniqueness. **Payload / INCLUDE columns** exist only in leaf entries. They can be projected to satisfy covering, and nothing else — no seeking, no ordering, no uniqueness. Why prefer payload when you can: internal nodes stay narrow, so fanout stays high and the tree stays shallow; you avoid pretending a column has ordering value it does not have; and you can attach columns whose types are not sortable by that index type. Crucially, payload lets you cover a query on a **unique** index without weakening the uniqueness constraint — adding a column to a unique key changes what is unique, adding it as payload does not. Choose key columns when a query filters on the column, sorts by it, or needs it as part of the enforced uniqueness.

code

sql · 5 lines
sql
-- changes the constraint: email is no longer unique on its own
CREATE UNIQUE INDEX u_bad ON users (email, display_name);

-- keeps the constraint, still covers SELECT display_name WHERE email = ?
CREATE UNIQUE INDEX u_good ON users (email) INCLUDE (display_name);

go deeper

for a junior

Know the one-line distinction: INCLUDE columns can be returned but not searched or sorted by; key columns can do both.

for a middle

Explain where each is stored, why leaf-only storage preserves fanout, and give the usage rule — search or sort means key, project only means payload.

for a senior

Lead with the unique-index case where INCLUDE preserves the constraint, note residual filtering at the leaf, and state plainly that write cost and cache footprint are unchanged.

for a principal

Position it inside index-set strategy: covering is targeted denormalization, INCLUDE lets you buy it without distorting keys or constraints, and the budget is write amplification per table.

## Two places a column can live in a B+Tree index A B+Tree has internal (branch) pages that route a search, and leaf pages that hold the actual entries. **Key columns** appear in both: routing requires comparing on them. **Payload (non-key, INCLUDE) columns** appear only in leaf entries — they are cargo, carried along so the engine can return them without visiting the table. ## What each can do | Capability | Key column | Payload column | |---|---|---| | Seek / narrow the scan | yes | no | | Extend the leftmost prefix | yes | no | | Satisfy ORDER BY / GROUP BY | yes | no | | Participate in uniqueness | yes | no | | Satisfy covering (be returned) | yes | yes | | Widens internal pages | yes | no | ## Why payload is often the better choice for covering **Fanout.** Fanout is how many child pointers fit on an internal page, and it sets the height of the tree — a shallower tree means fewer page reads per lookup. Wide keys shrink fanout. A payload column adds bytes only to leaves, so the routing structure stays lean. **Honest intent.** If no query ever filters or sorts on `total_amount`, making it key column #4 implies an ordering capability nobody uses, and invites future readers to think the ordering matters. **Uniqueness preservation.** This is the decisive case. Suppose you have `UNIQUE (email)` and want a query returning `display_name` to be covered. Making it `UNIQUE (email, display_name)` changes the constraint — now two rows may share an email as long as the names differ, which is a correctness regression. `UNIQUE (email) INCLUDE (display_name)` keeps the constraint exactly as it was and still covers. **Type flexibility.** Some column types have no useful ordering under the index's operator class but can still be carried as payload. ## When key columns are right - **Any filtering.** `WHERE status = ?` requires `status` to be a key column in a usable prefix position. Payload columns can still eliminate rows as a residual filter at the leaf level — which saves table fetches, not index entries scanned — but they cannot bound the scan. - **Ordering.** Satisfying `ORDER BY created_at` from the index requires `created_at` in the key, in the right position and direction. - **Uniqueness scope.** If the constraint genuinely is on the pair, both columns belong in the key. A useful rule of thumb: put the column in the key if any query *searches or sorts* by it; otherwise attach it as payload. ## What does not change Write cost is not avoided. A payload column is still written into every leaf entry on insert, and updating that column still requires maintaining the index even though no query filters on it. Wide leaf entries still consume cache and still make index scans read more pages. Choosing INCLUDE over a longer key trims the internal levels, not the bulk of the storage. ## Engine reality check Support is not universal and spelling differs; PostgreSQL and SQL Server both use `INCLUDE`, while some engines have no separate concept and you achieve covering only by extending the key — and some clustered-index engines automatically append the primary key to every secondary index, which supplies a bit of covering for free. Say which mechanism you are assuming rather than asserting a single universal behaviour. ## Answering well Lead with the structural difference (leaf-only versus routing-and-leaves), map it to capabilities (seek/sort/unique versus project-only), give the unique-index example as the case where payload is not merely nicer but required for correctness, and close by noting the write cost is the same either way.

  • Can a payload column be used to filter rows at all?
    It can be applied as a residual filter once an entry has been read from a leaf page, so it can eliminate rows before any table fetch happens — a real saving. What it cannot do is bound the scan: the engine still reads every index entry the key columns select, because payload columns take no part in the sort order and therefore define no contiguous range.
  • If payload columns keep the tree shallower, why not always use INCLUDE instead of longer keys?
    Because anything you might filter, join or sort on must be a key column — payload cannot seek or supply ordering, so an INCLUDE-only design silently gives up access paths. The decision follows usage: search or sort by it means key column, project only means payload. Neither choice reduces the write cost of carrying the column.

saying these in an interview costs you the question

  • Believing INCLUDE columns can be used to seek or to satisfy ORDER BY
  • Adding a column to a UNIQUE index key just to cover a query, weakening the constraint
  • Assuming payload columns are free because they are not part of the key
  • Thinking INCLUDE reduces index size — it only keeps internal pages narrow
  • Assuming every engine supports non-key index columns

context

resolving links…