Inverted index
An inverted index maps extracted keys, such as text terms or array elements, to postings identifying the records that contain them.
Mapping: A record can contribute several keys and appear in several posting lists. PostgreSQL GIN uses operator classes to define extraction and matching; GIN is an implementation, not another name for every inverted index. [1]
Detail: Postings need not retain positions. SQLite FTS5 detail=full stores row, column and token-position information; reduced detail omits information and limits phrase and NEAR queries. Tokenization and detail settings therefore belong to the search contract. [2]
Rechecking: An index can retain too little information to decide a query alone. PostgreSQL text-search GIN omits weight labels and rechecks weight-sensitive matches against table rows. This is a documented query requirement, not evidence of damaged postings. [3]
Representation: Inversion describes the key-to-record mapping, not one physical encoding. PostgreSQL GIN uses a B-tree over keys, with posting lists or posting trees for row identities. A B-tree can therefore be part of an inverted index. [1]
Maintenance: FTS5 external content separates indexed tokens from retrieved document values. The application must keep the two aligned; internal index consistency does not establish agreement with external rows or preservation of original document bytes. [2] [4]
Relations
Citations
- PostgreSQL 18: GIN indexes, sections 65.4.1, 65.4.3 and 65.4.4
- SQLite FTS5: external-content tables, detail option and FTS index structure (sections 4.4, 4.6 and 9.2)
- PostgreSQL 18: preferred text-search indexes, weight labels and rechecking (section 12.9)
- Hesela: index postings and external-content consistency
Further reading
Sources & caveats
- Source synthesis of PostgreSQL 18 GIN and SQLite FTS5 documentation accessed on 2026-11-18; implementations differ. No performance benchmark or general recovery guarantee.
- The linked analysis includes a separately recorded, four-document SQLite 3.45.1 detail-mode probe. Twelve query cases distinguish conjunction, phrase, NEAR and column-filter behavior; this is not a replication of the text-indexing survey.