In PostgreSQL, what does B-tree deduplication do, and why can adding INCLUDE (price) to an index on books(category_id) make it much larger?
answer
- one key, many row pointers
- posting list tuples
- merged lazily before a page split
- safety decided when the index is built
- non-key columns switch it off
basics
~20 sDeduplication stores a repeated key once with a sorted list of heap TIDs, shrinking low-cardinality B-trees. It is on by default but never used in INCLUDE indexes, so adding INCLUDE (price) turns every row back into a full index tuple.
solid answer
~50 sA B-tree normally holds one tuple per row: the key plus a heap TID. With deduplication, on by default, PostgreSQL merges duplicate keys on a leaf page into a **posting list tuple**, the key once followed by a sorted array of TIDs. It does this lazily, when an insert would otherwise split the page and deleting dead entries did not free room, and directly during `CREATE INDEX` and `REINDEX`. For `books(category_id)`, a few dozen categories over millions of rows, that makes the index much smaller and cheaper to scan and vacuum. Whether an index can deduplicate is decided at build time: `INCLUDE` indexes never can, and neither can keys of type `numeric`, `jsonb`, `float4`/`float8`, containers, or text under a nondeterministic collation. So `(category_id) INCLUDE (price)` stores one full tuple per row; weigh that growth against the index-only scans it buys.
code
sql · 13 lines-- deduplicated: posting lists per category on each leaf page
CREATE INDEX books_category_idx ON books (category_id);
-- covering, but INCLUDE disables deduplication: one tuple per row
CREATE INDEX books_category_cover_idx ON books (category_id) INCLUDE (price);
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname IN ('books_category_idx', 'books_category_cover_idx');
-- allequalimage = f means this index can never deduplicate
CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT allequalimage FROM bt_metap('books_category_cover_idx');go deeper
Recall that a PostgreSQL B-tree can store a repeated key once with many row pointers, and that this happens by default.
Explain posting list tuples, when the lazy merge runs relative to a page split, and which key types and index shapes cannot use it.
Weigh an INCLUDE column's index-only scans against losing deduplication, measure index size before and after, and explain why unique indexes still benefit.
Treat index size as a cache budget: covering columns, numeric keys and duplicate-heavy indexes compete for memory, so decide which queries truly deserve a covering index.
## One tuple per row, or one per key Every entry in a PostgreSQL B-tree leaf page points to a row in the table's **heap** through a **TID** (tuple identifier: block number plus item offset). Without deduplication, an index on `books(category_id)` holds one leaf tuple per book, so a category with 200,000 books repeats the same integer key 200,000 times, each copy carrying its own tuple header and line pointer. **Deduplication** replaces groups of equal keys with a **posting list tuple**: the key stored once, followed by a sorted array of TIDs. The logical contents of the page are unchanged, so scans return exactly the same rows; only the physical representation is smaller. The manual's list of benefits: - much smaller indexes where each value appears several times on average; - lower query latency and higher throughput, since fewer pages are read; - cheaper routine index vacuuming. As a rough illustration, a plain entry for an `integer` key costs about 20 bytes per row once alignment and the line pointer are counted, while each extra TID in a posting list costs 6 bytes. For a low-cardinality column that is the difference between a large index and a compact one. ## When deduplication runs Deduplication is **on by default** and works lazily: 1. An insert arrives for a leaf page that has no free space. 2. PostgreSQL first considers deleting index tuples that point to dead row versions. 3. If that cannot free enough space, a deduplication pass merges duplicates on the page into posting lists. 4. Only if the page is still full does it split. `CREATE INDEX` and `REINDEX` take a different path: they build posting lists directly from the sorted input, packing each with as many TIDs as fit, with no separate pass. Read-only workloads never pay a penalty, because reading a posting list is at least as efficient as reading the same entries one by one. Write-heavy indexes with almost no duplicates pay a small, fixed cost for passes that find nothing to merge. ## What switches it off Whether an index may deduplicate is decided **once, when it is built**, and a single unsafe key column disables it for the whole index: | Case | Why deduplication is unsafe | |---|---| | Any `INCLUDE` column | Implementation restriction, whatever the types | | `numeric` key | Equal values can differ in display scale (`5.0` vs `5.00`), which must be kept | | `jsonb` key | Its B-tree operator class uses `numeric` internally | | `float4` / `float8` key | `-0` and `0` are equal but stored differently | | `text`, `varchar`, `char` under a nondeterministic collation | Case and accent differences must survive | | Arrays, composite and range types | Container types are not supported yet | The `pageinspect` function `bt_metap()` reports this as the `allequalimage` column: `f` means the index will never deduplicate. ## The INCLUDE trade-off in the bookstore Suppose the catalogue page runs `SELECT category_id, price FROM books WHERE category_id = $1` constantly, and someone proposes `CREATE INDEX ... ON books (category_id) INCLUDE (price)` to get index-only scans. The index-only scan is real, but the cost is larger than "one extra column": - the index loses deduplication completely, so every book gets a full leaf tuple again; - each tuple also carries a `numeric` price; - the index-only scan only skips the heap on pages the visibility map marks all-visible, so a heavily updated table gains less than expected. Alternatives worth comparing: keep the deduplicated `(category_id)` index and accept heap visits; or, if price must be in the index, note that making it a key column, `(category_id, price)`, does not restore deduplication either, because `numeric` keys are unsafe. Storing prices as integer cents would make that key deduplication-safe. Measure with `pg_relation_size()` on each candidate and with the query's buffers before deciding. ## Unique indexes and deduplicate_items Deduplication even helps **unique** indexes. Each update that cannot reuse the existing index entry adds a new entry with the same key for the new row version, so a unique index temporarily holds duplicates. Deduplicating them lets a leaf page absorb that version churn instead of splitting, buying time for bottom-up index deletion to remove the dead entries, for example once a long-running transaction ends. The B-tree storage parameter **`deduplicate_items`** (default `on`) turns the feature off per index. `ALTER INDEX ... SET (deduplicate_items = off)` only stops future insertions from deduplicating; existing posting lists stay until the index is rebuilt. The manual's advice is that disabling it is not usually helpful.
- After ALTER INDEX ... SET (deduplicate_items = off), is the index the same as one built without deduplication?No. The setting only stops future insertions from triggering deduplication passes; posting list tuples already on disk stay as they are. Rebuilding the index with `REINDEX` produces the plain representation throughout. It is rarely worth it, since reading posting lists is at least as efficient as reading plain tuples.
- Why does a unique index on books(book_id) benefit from deduplication at all?An update that cannot reuse the existing index entry inserts a new entry with the same key for the new row version, so the unique index briefly holds duplicates. Deduplicating them lets a leaf page absorb that churn instead of splitting, buying time for bottom-up deletion to remove dead entries once no snapshot needs them, for example after a long transaction ends.
- Why is an index on books(price) never deduplicated when price is numeric?`numeric` equality ignores display scale, so `5.0` equals `5.00`, but the stored values differ and that difference must survive. Merging them into one posting list would lose it, so the operator class reports deduplication unsafe and the whole index is built without it. An `integer` price-in-cents key would deduplicate.
- Deduplication is off by default and must be enabled per index.
- INCLUDE columns are deduplicated just like key columns.
- Setting deduplicate_items = off immediately rewrites existing posting lists.
- Deduplication slows read-only queries because posting lists must be expanded.
- Unique indexes never hold duplicate keys, so deduplication is pointless for them.