A PostgreSQL B-tree can represent several equal index keys with one physical key value followed by multiple heap tuple identifiers. This representation, called a posting-list tuple, reduces repeated key storage on leaf pages without changing the logical contents of the index.
The mechanism matters most when an indexed value occurs many times. An index on a low-cardinality status column, for example, may contain thousands of entries whose key is pending. Logically those entries still identify separate table tuples. Physically, B-tree deduplication can pack groups of equal keys so the key datum is stored once for a group of TIDs.
Deduplication changes leaf-page representation
A conventional B-tree leaf tuple contains an indexed key and a reference to a heap tuple. With repeated values, many adjacent leaf tuples can carry identical key data:
pending -> TID (42,1)
pending -> TID (42,4)
pending -> TID (43,2)
pending -> TID (44,7)A posting-list tuple represents the same logical entries more compactly:
pending -> [(42,1), (42,4), (43,2), (44,7)]The TIDs remain sorted. Index scans can still identify each referenced heap tuple, so deduplication does not collapse rows or alter SQL equality semantics. It is an on-disk representation choice inside the B-tree access method.
This distinction also separates B-tree deduplication from a unique constraint. A non-unique B-tree may legitimately contain many rows with the same key. Deduplication stores those entries compactly; it does not impose uniqueness.
Packing is triggered around leaf-page pressure
PostgreSQL does not need to rebuild a B-tree each time duplicate keys appear. During ordinary insertion, deduplication is considered when a new item cannot fit on a leaf page and index tuple deletion has not freed enough space. Groups of duplicate tuples can then be merged into posting-list tuples before PostgreSQL proceeds to a page split.
That timing makes deduplication a page-local space-management technique. It can delay splits when duplicate keys occupy enough of a leaf page to make packing useful. The result depends on the key distribution and the tuples present on a page, not merely on the table-wide count of distinct values.
CREATE INDEX and REINDEX can also produce posting-list tuples while building the index from sorted input. Equal keys are already adjacent in that input, so groups can be packed as leaf pages are formed.
B-tree deduplication is enabled by default where the index definition and data types permit it. The index storage parameter can disable future deduplication:
CREATE INDEX events_state_idx
ON events (state)
WITH (deduplicate_items = off);Turning the setting off is not a conversion operation. Existing posting-list tuples can remain in the index; the setting controls future use of the optimization.
Duplicate keys and MVCC version churn can meet
Repeated application-level values are not the only source of duplicate physical keys. PostgreSQL’s multiversion storage can leave several index entries associated with successive versions of a logical row. From the B-tree’s perspective, index tuples are physical objects that reference heap tuples.
This can make equal index keys appear even in cases where the logical data model suggests little duplication. PostgreSQL can selectively apply deduplication in unique indexes to absorb some version churn. Uniqueness checking still follows the index’s uniqueness rules; the compact physical representation does not permit two simultaneously valid rows that violate a unique constraint.
The interaction is useful because delaying a leaf-page split can give later index cleanup a chance to remove obsolete versions. Deduplication and cleanup address different parts of the same physical pressure: one packs equal keys, while the other removes entries that are no longer needed.
INCLUDE columns prevent B-tree deduplication
A B-tree index with non-key columns declared through INCLUDE cannot use deduplication. Included values live in leaf tuples but are not part of the search key used for tree navigation. Packing several heap references under one key would also need to preserve the corresponding included payload for each row, so PostgreSQL excludes this index shape from the deduplication mechanism.
Consider these two indexes:
CREATE INDEX orders_state_idx
ON orders (state);
CREATE INDEX orders_state_cover_idx
ON orders (state)
INCLUDE (updated_at);The first index may deduplicate repeated state keys. The second cannot use B-tree deduplication because it carries updated_at as a non-key column.
That boundary can matter when evaluating index size. Adding an INCLUDE column does more than append payload bytes: it also removes the opportunity to pack duplicate keys into posting lists. The actual size effect depends on key repetition, included-column width, tuple churn, and page occupancy.
Data type and collation details set further limits
Deduplication is only used when PostgreSQL can treat equal key datums as safely interchangeable in the physical representation. Some data types and index configurations retain representation details that make packing unsafe.
For example, B-tree deduplication is not used for text, varchar, or char under a nondeterministic collation. Values can compare as equal while retaining distinctions that the index implementation must preserve. PostgreSQL also excludes several other type families where equal values may have semantically relevant representation differences.
These restrictions are determined from the index definition and operator-class behavior. They are a reminder that SQL equality alone is not the complete criterion for safe physical merging.
Posting lists favor repeated keys, not every workload
An index with mostly distinct keys has little material to deduplicate. The feature does not turn a high-cardinality B-tree into a compressed structure in the general sense. Its target is repeated equal keys that would otherwise occupy separate leaf tuples.
For suitable distributions, storing a key once with several TIDs can reduce leaf-page consumption and postpone some page splits. Smaller leaf storage can also reduce later index maintenance work. Those effects are consequences of packing duplicate entries; they should not be assumed for an index whose keys are nearly all distinct.
B-tree deduplication is therefore best understood as a physical representation rule rather than a query feature. SQL statements keep the same index semantics, while leaf pages gain a compact form for repeated keys. When index size or page-split behavior looks surprising, key repetition, INCLUDE columns, collation rules, and MVCC version churn are all relevant parts of the index’s physical shape.