This section covers B-Tree index implementation details that may be
of use to advanced users. See
src/backend/access/nbtree/README
in the source
distribution for a much more detailed, internals-focused description
of the B-Tree implementation.
PostgreSQL B-Tree indexes are multi-level tree structures, where each level of the tree can be used as a doubly-linked list of pages. A single metapage is stored in a fixed position at the start of the first segment file of the index. All other pages are either leaf pages or internal pages. Leaf pages are the pages on the lowest level of the tree. All other levels consist of internal pages. Each leaf page contains tuples that point to table rows. Each internal page contains tuples that point to the next level down in the tree. Typically, over 99% of all pages are leaf pages. Both internal pages and leaf pages use the standard page format described in Section 68.6.
New leaf pages are added to a B-Tree index when an existing leaf page cannot fit an incoming tuple. A page split operation makes room for items that originally belonged on the overflowing page by moving a portion of the items to a new page. Page splits must also insert a new downlink to the new page in the parent page, which may cause the parent to split in turn. Page splits “cascade upwards” in a recursive fashion. When the root page finally cannot fit a new downlink, a root page split operation takes place. This adds a new level to the tree structure by creating a new root page that is one level above the original root page.
A duplicate is a leaf page tuple (a tuple that points to a table row) where all indexed key columns have values that match corresponding column values from at least one other leaf page tuple that's close by in the same index. Duplicate tuples are quite common in practice. B-Tree indexes can use a special, space-efficient representation for duplicates when an optional technique is enabled: deduplication.
Deduplication works by periodically merging groups of duplicate tuples together, forming a single posting list tuple for each group. The column key value(s) only appear once in this representation. This is followed by a sorted array of TIDs that point to rows in the table. This significantly reduces the storage size of indexes where each value (or each distinct combination of column values) appears several times on average. The latency of queries can be reduced significantly. Overall query throughput may increase significantly. The overhead of routine index vacuuming may also be reduced significantly.
While NULL is generally not considered to be equal to any other value, including NULL, NULL is nevertheless treated as just another value from the domain of indexed values by the B-Tree implementation (except when enforcing uniqueness in a unique index). B-Tree deduplication is therefore just as effective with “duplicates” that contain a NULL value.
The deduplication process occurs lazily, when a new item is inserted that cannot fit on an existing leaf page. This prevents (or at least delays) leaf page splits. Unlike GIN posting list tuples, B-Tree posting list tuples do not need to expand every time a new duplicate is inserted; they are merely an alternative physical representation of the original logical contents of the leaf page. This design prioritizes consistent performance with mixed read-write workloads. Most client applications will at least see a moderate performance benefit from using deduplication. Deduplication is enabled by default.
Write-heavy workloads that don't benefit from deduplication due to
having few or no duplicate values in indexes will incur a small,
fixed performance penalty (unless deduplication is explicitly
disabled). The deduplicate_items
storage
parameter can be used to disable deduplication within individual
indexes. There is never any performance penalty with read-only
workloads, since reading posting list tuples is at least as
efficient as reading the standard tuple representation. Disabling
deduplication isn't usually helpful.
B-Tree indexes are not directly aware that under MVCC, there might
be multiple extant versions of the same logical table row; to an
index, each tuple is an independent object that needs its own index
entry. Thus, an update of a row always creates all-new index
entries for the row, even if the key values did not change. Some
workloads suffer from index bloat caused by these
implementation-level version duplicates (this is typically a
problem for UPDATE
-heavy workloads that cannot
apply the HOT optimization due to modifying at
least one indexed column). B-Tree deduplication does not
distinguish between these implementation-level version duplicates
and conventional duplicates. Deduplication can nevertheless help
with controlling index bloat caused by implementation-level version
churn.
A special heuristic is applied to determine whether a
deduplication pass in a unique index should take place. It can
often skip straight to splitting a leaf page, avoiding a
performance penalty from wasting cycles on unhelpful deduplication
passes. If you're concerned about the overhead of deduplication,
consider setting deduplicate_items = off
selectively. Leaving deduplication enabled in unique indexes has
little downside.
Deduplication cannot be used in all cases due to
implementation-level restrictions. Deduplication safety is
determined when CREATE INDEX
or
REINDEX
is run.
Note that deduplication is deemed unsafe and cannot be used in the following cases involving semantically significant differences among equal datums:
text
, varchar
, and char
cannot use deduplication when a
nondeterministic collation is used. Case
and accent differences must be preserved among equal datums.
numeric
cannot use deduplication. Numeric display
scale must be preserved among equal datums.
jsonb
cannot use deduplication, since the
jsonb
B-Tree operator class uses
numeric
internally.
float4
and float8
cannot use
deduplication. These types have distinct representations for
-0
and 0
, which are
nevertheless considered equal. This difference must be
preserved.
There is one further implementation-level restriction that may be lifted in a future version of PostgreSQL:
Container types (such as composite types, arrays, or range types) cannot use deduplication.
There is one further implementation-level restriction that applies regardless of the operator class or collation used:
INCLUDE
indexes can never use deduplication.