The worst index I ever owned was on a status column with four distinct values. The events table held about 300 million rows, and its plain btree on status weighed 11.8 GB — nearly a sixth of the table itself — because every single row paid for its own index tuple. I reindexed it after our PostgreSQL 13 upgrade for unrelated reasons and watched it come back at 1.9 GB. Same column, same queries, same plans, same cardinality estimates. Six times smaller, from a storage-format change that nobody enabled on purpose and most teams never notice in the release notes.
That gap between 11.8 GB and 1.9 GB is posting lists, and once you understand the mechanism, a lot of inherited btree intuition — about low-cardinality indexes being hopeless, about unique indexes being immune to bloat — quietly stops being true.
What did PostgreSQL 13 actually change inside the btree?
It started storing duplicates as posting lists: one index tuple per distinct key value that carries a sorted array of heap tuple identifiers, instead of one index tuple per row. Before 13, three hundred thousand rows with status = 'open' meant three hundred thousand index tuples, each repeating the same key bytes. After 13, they collapse into a chain of compact tuples that are mostly just TID arrays. A posting list item costs about six bytes per row instead of the sixteen-plus a standalone tuple costs once you count headers and alignment, and that per-row difference is the entire 6x in my incident.
Two properties of the design deserve respect. First, deduplication is lazy: it is applied when a page fills and would otherwise split, at which point nbtree runs a dedup pass and merges what it can before resorting to the split. You do not schedule it and you cannot force it; it is a split-avoidance strategy. Second, it is purely physical. The opclass, the scan semantics, the planner's cost model — none of that knows or cares. TIDs inside a posting list stay in heap order, so bitmap heap scans still get physically sorted input for free. Your queries cannot tell the difference, which is exactly why so many people enjoyed the win without ever learning it happened.
Which indexes qualify for deduplication, and which quietly do not?
Deduplication is on by default — deduplicate_items unless you switch it off per index — but three classes silently opt out. Indexes built on a nondeterministic collation cannot deduplicate, because dedup needs equal keys to be truly interchangeable and a nondeterministic collation breaks that guarantee. Indexes with INCLUDE columns never deduplicate at all: the payload columns are not part of the key, so there is no whole-tuple equality to merge on, and the covering-index hope dies right there. And one posting list is still bounded by the maximum index tuple size — about 2704 bytes on standard 8 KB pages — so a key with millions of duplicates becomes many posting lists rather than one magical tuple. The win stays enormous; it just stops being infinite.
The clean way to see the trade for yourself is an A/B build on a copy of the table:
CREATE INDEX events_status_dedup_idx ON events (status);
CREATE INDEX events_status_plain_idx ON events (status) WITH (deduplicate_items = off);
SELECT count(*) AS rows, count(DISTINCT status) AS distinct_keys FROM events;
SELECT pg_size_pretty(pg_relation_size('events_status_dedup_idx')) AS with_dedup,
pg_size_pretty(pg_relation_size('events_status_plain_idx')) AS without_dedup;Run that on any low-cardinality column — status flags, type enums, boolean-ish markers, tenant skew columns where one tenant owns most rows — and the size ratio will tell you how much of your index volume was repeated key bytes. If the two indexes come out the same size, your column was not actually duplicate-heavy, or an INCLUDE column is defeating the merge; either is useful information before you blame the storage format.
Why do unique indexes still benefit from deduplication?
Because MVCC makes unique indexes temporarily non-unique, and deduplication eats exactly that churn. An UPDATE creates a new row version, and the old version's index entry stays until vacuum removes it — so between vacuums, a hot row's key value appears in the unique index many times over. Those are duplicates in the only sense the storage layer cares about, and posting lists merge them. This is why the feature was not just a gift to low-cardinality columns: on our write-heavy unique indexes, dedup measurably slowed index growth between vacuum cycles, which matters most on exactly the tables where vacuum is already behind.
The story completes in PostgreSQL 14 with bottom-up index deletion. Before it, a page full of mostly-dead posting-list entries still had to split when a new tuple needed room, and splits are how churn becomes permanent bloat. Bottom-up deletion instead prunes individual dead TIDs out of posting lists in place — it spots likely-dead versions from the index and asks the heap to confirm before pruning — and postpones or avoids the split entirely. The two features compose: dedup packs duplicates tightly, bottom-up deletion prunes them without cracking the page open. Neither rescues updates that actually change indexed columns, since those are non-HOT and mint brand-new index entries every time; that is the fillfactor and HOT-update conversation, which the HOT updates and fillfactor field notes cover in detail. Deduplication shrinks what remains after you get that part right.
How do you verify the savings with pgstatindex?
Measure, do not trust the release notes. The pgstattuple extension ships pgstatindex, which reports the physical shape of a btree directly:
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT pg_size_pretty(index_size) AS index_size,
leaf_pages, deleted_pages,
round(avg_leaf_density::numeric, 1) AS avg_leaf_density_pct,
leaf_fragmentation
FROM pgstatindex('events_status_dedup_idx');On the status index from my incident, avg_leaf_density went from roughly 37 percent before the reindex to about 89 percent after — the old index was mostly free space and repeated keys, the new one is mostly useful bytes. My verification ritual for any dedup claim is three numbers: pg_relation_size before and after REINDEX CONCURRENTLY, avg_leaf_density from pgstatindex, and idx_scan from pg_stat_user_indexes to confirm the planner still uses the index exactly as before. That last check exists to kill a superstition: deduplication changes storage, not scan behavior, and if someone reports slower queries after a reindex the cause is elsewhere — usually freshly reset statistics or a changed fillfactor, not posting lists.
How do you watch index growth with MonPG?
Deduplication changes what "normal" index growth looks like, so your baselines from PG12 and earlier will mislead you. A low-cardinality btree that grows at heap rate on a dedup-capable version is telling you something real — nondeterministic collation, INCLUDE churn, or deduplicate_items switched off in a migration nobody documented. MonPG graphs per-index size over time alongside table size, dead tuples, and vacuum runs, so the step down after a dedup-aware REINDEX CONCURRENTLY shows up as a visible event rather than a rumor, and the regrowth rate afterward tells you whether bottom-up deletion is keeping pace with your churn. Pair that with the reindex workflow in the btree bloat and REINDEX CONCURRENTLY notes and you have the full loop: detect, reindex, verify, baseline. That is the loop MonPG's PostgreSQL monitoring is built to keep honest, with the size series retained long enough to compare this quarter's bloat against last quarter's.