1. What Is B-tree Index Deduplication?

B-tree Index Deduplication, introduced in PostgreSQL 13, is a storage optimization that reduces index size by combining duplicate index keys into a compact structure called a posting-list tuple.

Normally, each B-tree index entry contains an indexed value and a reference to its corresponding heap tuple. When millions of rows contain identical indexed values, storing those values repeatedly increases index storage requirements.

Deduplication allows PostgreSQL to store one copy of an indexed key together with multiple heap tuple identifiers (TIDs).

This is particularly useful for low-cardinality columns, such as transaction status, account type, or Boolean flags.

PostgreSQL B-tree Deduplication can be considered a partial conceptual alternative to Oracle Bitmap Indexes for optimizing storage associated with low-cardinality data. Both reduce the overhead of representing repeated values, but their internal mechanisms differ. Oracle uses persistent bitmap structures, while PostgreSQL consolidates duplicate B-tree entries into posting lists. Therefore, they offer some comparable storage benefits but are not direct functional equivalents.

2. How Deduplication Works

Consider a table containing:

Without deduplication:

ACTIVE  → TID1
ACTIVE  → TID2
ACTIVE  → TID3
PENDING → TID4
PENDING → TID5

With deduplication:

ACTIVE  → [TID1, TID2, TID3]
PENDING → [TID4, TID5]

PostgreSQL stores the indexed value once within each posting-list tuple, followed by the corresponding heap TIDs.

Deduplication operates within individual B-tree leaf pages. Duplicate keys located on different pages are not consolidated into one global posting list.

It is generally performed when PostgreSQL needs additional space on a leaf page, potentially avoiding or postponing a page split.

3. Deduplication and Column Cardinality

Low-cardinality columns contain relatively few distinct values compared with the total number of rows.

Indexes on columns with many duplicate values can benefit significantly from deduplication.

However, low cardinality does not necessarily mean high query performance.

For example, if a status value represents 20% of a table, PostgreSQL may prefer a sequential scan even when the B-tree index is deduplicated.

Deduplication reduces index storage; it does not improve the selectivity of indexed values.

4. Enabling and Disabling Deduplication

PostgreSQL provides the deduplicate_items index storage parameter.

Deduplication is enabled by default for eligible B-tree indexes.

-- Enable deduplication.

CREATE INDEX idx_status
ON transactions(status)
WITH (deduplicate_items = on);

To disable it:

CREATE INDEX idx_status_no_dedup
ON transactions(status)
WITH (deduplicate_items = off);

For an existing index:

ALTER INDEX idx_status
SET (deduplicate_items = on);

Changing this setting does not immediately reorganize existing index entries.

To rebuild the index:

REINDEX INDEX idx_status;

Enabling deduplication does not guarantee that every duplicate entry will be consolidated.

5. Multicolumn Index Deduplication

PostgreSQL supports deduplication for eligible multicolumn B-tree indexes.

However, the complete indexed key must be identical.

For example:

(100, ACTIVE)
(100, ACTIVE)
(100, ACTIVE)

These entries may be consolidated.

But:

(100, ACTIVE)
(101, ACTIVE)
(102, ACTIVE)

These entries cannot be combined because their complete keys differ.

Therefore, an index containing a low-cardinality leading column may still benefit very little from deduplication if subsequent columns contain mostly unique values.

6. Deduplication and INCLUDE Columns

PostgreSQL supports covering indexes using INCLUDE.

CREATE INDEX idx_status_amount
ON transactions(status)
INCLUDE (amount);

However, B-tree indexes containing INCLUDE columns cannot use deduplication.

This creates a tradeoff:

• A deduplicated B-tree index may consume less storage.

• A covering index may support index-only scans and reduce heap access.

The optimal design depends on query patterns and workload requirements.

7. Deduplication and MVCC

PostgreSQL uses Multi-Version Concurrency Control (MVCC).

When rows are updated, new physical tuple versions may be created. Depending on the update, additional index entries may also be required.

Even unique indexes can temporarily contain multiple physical entries associated with the same logical key.

Deduplication can reduce storage overhead associated with these repeated keys.

However, it does not replace VACUUM, which remains responsible for cleaning up dead tuples.

8. Advantages of B-tree Deduplication

Reduced Index Size: Duplicate keys share storage through posting-list tuples.

Improved Cache Efficiency: Smaller indexes may require fewer pages in shared buffers.

Fewer Page Splits: Deduplication can recover space on leaf pages before PostgreSQL splits them.

Reduced I/O: Compact indexes may require fewer page reads and writes.

Lower Maintenance Overhead: Reduced index growth can decrease some maintenance costs.

Transparent Operation: Existing SQL queries do not require modification.

These benefits depend on the data distribution, index structure, and workload.

9. Limitations

B-tree deduplication has several important limitations:

1. It applies only to eligible B-tree indexes.

2. It does not improve column selectivity.

3. It does not guarantee faster query execution.

4. It is unavailable for indexes containing INCLUDE columns.

5. It requires compatible operator classes.

6. It operates within individual leaf pages.

7. It introduces some CPU overhead during consolidation.

8. It does not replace VACUUM or REINDEX.

10. Monitoring Deduplication

PostgreSQL provides several functions for evaluating index storage.

The most practical approach is to compare indexes created on identical data with deduplication enabled and disabled.

11. Conclusion

PostgreSQL B-tree Index Deduplication improves storage efficiency by consolidating repeated index keys into posting-list tuples.

It is especially beneficial for large tables containing many duplicate indexed values.

The optimization can reduce index size, improve cache efficiency, and avoid some index page splits.

Its primary advantage is reducing the physical storage cost of duplicate keys, not increasing query selectivity.