A PostgreSQL B-tree leaf page can hold many index tuples with identical key values. When deduplication is applicable, PostgreSQL can represent a group of those tuples as one posting-list tuple: the indexed key appears once, followed by a sorted array of heap tuple identifiers.
This representation changes physical index density without changing the logical set of index entries. Each heap tuple remains individually addressable through its TID, while repeated key material occupies less leaf-page space.
Posting lists compact repeated key material
An ordinary B-tree leaf tuple contains indexed key data and a TID that identifies a heap tuple. A non-unique index over a low-cardinality column can therefore repeat the same key bytes many times.
For an index such as:
CREATE INDEX events_state_idx ON events (state);a large population of rows with state = 'queued' can produce many logically separate entries carrying the same key. Deduplication can merge suitable entries into a posting-list tuple with one copy of queued and multiple TIDs.
The optimization is physical. Equality semantics, ordering semantics, and row visibility remain governed by the normal B-tree and MVCC machinery.
Deduplication is triggered near leaf-page pressure
During ordinary insertion, PostgreSQL does not rebuild posting lists after every duplicate arrives. Deduplication is performed lazily when a new item cannot fit on a leaf page and index-tuple deletion has not released enough space.
That timing makes deduplication part of the page-split avoidance path. Compacting duplicate tuples may create enough free space for the pending insertion, delaying or avoiding a split.
CREATE INDEX and REINDEX use a different opportunity. Their sorted input already groups equal keys, so duplicate ordinary tuples can be merged into posting-list tuples while leaf pages are assembled.
Duplicate frequency controls the storage effect
The largest space reduction appears when many rows share the same indexed key or key combination. The key payload is stored once per posting-list tuple instead of once per TID.
High-cardinality indexes with almost no repeated keys offer little material to compact. In that case, deduplication has limited opportunity to alter index size.
The effect also depends on key width. Replacing repeated wide key values can save more leaf space than replacing repeated narrow values, subject to the data type and index being eligible for the optimization.
Fewer leaf pages can reduce I/O for scans and reduce the amount of index storage that routine maintenance must process. The exact effect remains workload-dependent rather than a fixed ratio.
Posting lists preserve individual heap references
Deduplication does not collapse several table rows into one logical index match. The posting list stores a separate TID for each represented heap tuple.
An index scan that reaches a posting-list tuple can therefore enumerate the TIDs associated with its key. Heap visibility checks still determine which row versions are visible to the current snapshot when heap access is required.
This distinction matters for MVCC version churn. Multiple physical row versions can produce index entries whose key values compare equal. Deduplication can sometimes compact such entries even though they originated from different tuple versions.
Unique indexes can contain physical duplicates
A unique B-tree enforces uniqueness according to PostgreSQL’s visibility and concurrency rules; it does not imply that every on-disk leaf key is represented by exactly one physical tuple at every instant.
Updates can create multiple index tuple versions associated with successive heap versions. PostgreSQL can selectively apply deduplication in unique indexes when version churn creates suitable duplicates.
In this case the optimization is not a relaxation of the uniqueness constraint. It is a storage representation used while normal uniqueness semantics remain intact.
Eligibility has implementation boundaries
B-tree deduplication is enabled by default, but it is not valid for every B-tree definition. PostgreSQL excludes cases where physically merging equal keys could discard representation details that remain significant.
Current restrictions include B-tree indexes using INCLUDE columns. Some data types and collation configurations are also excluded, including numeric, floating-point types, jsonb, container types, and text types using nondeterministic collations.
These restrictions are determined from index properties rather than from duplicate frequency. An index can contain many equal keys and still remain ineligible for posting-list deduplication.
INCLUDE columns change the leaf tuple contract
An INCLUDE index stores non-key column values in leaf tuples so an index-only scan can return those values without reading them from the heap when visibility permits.
For example:
CREATE INDEX events_state_cover_idx
ON events (state)
INCLUDE (created_at);Rows with the same state can carry different created_at payloads. B-tree deduplication is not used for indexes with non-key included columns.
This creates a concrete storage tradeoff. Adding payload columns can support covering access patterns, but it also removes the posting-list compaction available to an otherwise eligible B-tree.
The storage parameter controls future deduplication
The deduplicate_items B-tree storage parameter controls whether the optimization is used. Its default value is on.
An index can be created with the optimization disabled:
CREATE INDEX events_state_idx
ON events (state)
WITH (deduplicate_items = off);Changing the parameter to off later prevents future insertions from triggering deduplication. It does not immediately expand existing posting-list tuples back into ordinary tuples.
As a result, the parameter describes future maintenance behavior rather than guaranteeing one uniform physical representation for every existing leaf entry.
Deduplication and page splits are related but distinct
A posting-list pass can free leaf-page space, but it does not eliminate B-tree page splits. Pages containing mostly distinct keys may still split as they fill, and an eligible page can still require a split when compaction does not free enough room.
The mechanism is therefore a pressure-reduction step inside B-tree maintenance. It is most effective when repeated keys consume enough space that replacing duplicate key copies with TID arrays materially changes page occupancy.
At the storage boundary, PostgreSQL retains the same logical index contents while choosing a denser physical form for eligible duplicate entries. That separation between logical keys and leaf representation is the central property of B-tree deduplication.