Covering indexes: payload availability still needs visibility

Suppose a query finds an account by account_id and returns its display_name. An index whose key is account_id and whose included payload is display_name can provide both values without making the name part of the ordered search boundary. Included values do not contribute the same navigation or ordering capabilities as key columns. In PostgreSQL 18, a unique index on account_id with an included name enforces uniqueness on the key, not on the account-and-name pair. This distinction matters when preserving an application invariant.

Covering is relative to a query, not an index's universal property. A query needing another nonstored value may still require table access. Expressions, partial predicates, collations, and access-method support can affect eligibility. An included value is duplicated data whose changes must be maintained correctly; it is not a pointer that magically fetches the latest table value at no cost. Read the actual plan and confirm that projected values, filters, and required ordering fit the chosen access path.

In PostgreSQL's MVCC model, finding an index entry is not sufficient to establish that the referenced row version is visible to the current snapshot. An index-only scan can consult a compact visibility map: if the corresponding heap page is marked all-visible, it can avoid a heap visibility check for that candidate. Otherwise it must visit the heap tuple. Thus the plan-node name does not guarantee zero heap fetches during execution. Recent updates can clear useful visibility information even when every requested value is present in the index.

Separate tuple visibility visits from distinct page reads. Several candidates can reside on one cached heap page, so a count of 100 heap fetches is not necessarily 100 physical device operations. Visibility-map access also has a cost, usually on a smaller structure, while cache state and concurrency affect timing. A relatively stable table can make covering attractive; a rapidly changing table may offer less heap avoidance. Observe actual execution metrics under representative snapshot lifetimes rather than estimating the benefit from index eligibility alone.

Wider leaf entries increase page count, cache pressure, insertion work, and the chance that an entry exceeds the access method's size limit. PostgreSQL can omit included payload from upper routing levels, so the leaf penalty need not widen every internal separator. Included columns can still affect update behavior and implementation optimizations; for example, PostgreSQL 18 does not apply B-tree deduplication to INCLUDE indexes. Compare the actual workload's saved heap work with the extra maintenance and retained storage instead of adding every projected column by default.

The cache lab illustrates a larger address working set without representing included columns. Reset, set C=4 and s=4, predict twelve hits among the sixteen initial requests, then Play or Step. For a separate experiment, Reset, set C=4 and s=2, predict zero initial hits because eight distinct addresses cycle through four slots, then Play or Step. The stream change is explicit; the lab cannot widen an index entry or inspect MVCC. Use it to explain why a layout that saves table visits can still reduce cache reuse elsewhere.