Make heap TID a tiebreaker nbtree index column

Enterprise / PostgreSQL - Peter Geoghegan [bowt.ie] - 20 March 2019 17:04 EDT

Make nbtree treat all index tuples as having a heap TID attribute. Index searches can distinguish duplicates by heap TID, since heap TID is always guaranteed to be unique. This general approach has numerous benefits for performance, and is prerequisite to teaching VACUUM to perform "retail index tuple deletion".

Naively adding a new attribute to every pivot tuple has unacceptable overhead (it bloats internal pages), so suffix truncation of pivot tuples is added. This will usually truncate away the "extra" heap TID attribute from pivot tuples during a leaf page split, and may also truncate away additional user attributes. This can increase fan-out, especially in a multi-column index. Truncation can only occur at the attribute granularity, which isn't particularly effective, but works well enough for now. A future patch may add support for truncating "within" text attributes by generating truncated key values using new opclass infrastructure.

Only new indexes (BTREE_VERSION 4 indexes) will have insertions that treat heap TID as a tiebreaker attribute, or will have pivot tuples undergo suffix truncation during a leaf page split (on-disk compatibility with versions 2 and 3 is preserved). Upgrades to version 4 cannot be performed on-the-fly, unlike upgrades from version 2 to
version 3. contrib/amcheck continues to work with version 2 and 3 indexes, while also enforcing stricter invariants when verifying version 4 indexes. These stricter invariants are the same invariants described by "3.1.12 Sequencing" from the Lehman and Yao paper.

A later patch will enhance the logic used by nbtree to pick a split point. This patch is likely to negatively impact performance without smarter choices around the precise point to split leaf pages at. Making these two mostly-distinct sets of enhancements into distinct commits seems like it might clarify their design, even though neither commit is particularly useful on its own.

The maximum allowed size of new tuples is reduced by an amount equal to the space required to store an extra MAXALIGN()'d TID in a new high key during leaf page splits. The user-facing definition of the "1/3 of a page" restriction is already imprecise, and so does not need to be revised. However, there should be a compatibility note in the v12 release notes.

Author: Peter Geoghegan

dd299df818 Make heap TID a tiebreaker nbtree index column.
contrib/amcheck/expected/check_btree.out | 5 +-
contrib/amcheck/sql/check_btree.sql | 5 +-
contrib/amcheck/verify_nbtree.c | 341 +++++++++++++++++++---
contrib/pageinspect/btreefuncs.c | 2 +-
contrib/pageinspect/expected/btree.out | 2 +-
contrib/pgstattuple/expected/pgstattuple.out | 10 +-
doc/src/sgml/indices.sgml | 24 +-
src/backend/access/common/indextuple.c | 6 +-
src/backend/access/nbtree/README | 124 ++++----
src/backend/access/nbtree/nbtinsert.c | 407 ++++++++++++++++----------
src/backend/access/nbtree/nbtpage.c | 206 +++++++++-----
src/backend/access/nbtree/nbtree.c | 2 +-
src/backend/access/nbtree/nbtsearch.c | 106 ++++++-
src/backend/access/nbtree/nbtsort.c | 91 +++---
src/backend/access/nbtree/nbtutils.c | 410 ++++++++++++++++++++++++---
src/backend/access/nbtree/nbtxlog.c | 47 +--
src/backend/access/rmgrdesc/nbtdesc.c | 8 -
src/backend/utils/sort/tuplesort.c | 13 +-
src/include/access/nbtree.h | 213 +++++++++++---
src/include/access/nbtxlog.h | 35 +--
src/test/regress/expected/btree_index.out | 34 +--
src/test/regress/expected/create_index.out | 13 +-
src/test/regress/expected/dependency.out | 4 +-
src/test/regress/expected/event_trigger.out | 4 +-
src/test/regress/expected/foreign_data.out | 9 +-
src/test/regress/expected/rowsecurity.out | 4 +-
src/test/regress/sql/btree_index.sql | 37 +--
src/test/regress/sql/create_index.sql | 14 +-
src/test/regress/sql/foreign_data.sql | 2 +-
29 files changed, 1619 insertions(+), 559 deletions(-)

Upstream: git.postgresql.org


  • Share