Inverted Index

An inverted index maps each token back to the rows that contain it. Instead of scanning every row, the engine looks up the query's tokens and jumps straight to the matching rows — which is what makes full-text search fast. SereneDB's inverted index is built on IResearch and, beyond text, the same index also powers vector / approximate-nearest-neighbor search and geospatial search.

The guiding principle is that the same analysis is applied at index time and at query time (see Text Analysis). The text search dictionary attached to a column decides how its text is split into tokens and normalized; that identical pipeline runs on the query, so the search terms always match the stored tokens even when the surface forms differ.

Choosing a search type

One inverted index can serve several kinds of search, each documented on its own page:

You want to…	Use	Page
Match natural-language text, rank by relevance	Full-text search (@@, BM25)	Full-Text Search · Ranking
Find nearest vectors / semantic similarity	Vector / ANN search (IVF)	Vector Search
Combine a lexical signal with a vector signal	Hybrid search	Hybrid Search
Query points, shapes and distances	Geospatial search (ST_*)	Geospatial Search
Match exact values, ids, tags, enums	Verbatim columns + range predicates	Full-Text Search

Creating an inverted index

An inverted index is created by adding USING inverted to CREATE INDEX. The example below indexes two text columns with a dictionary, then queries the index by name:

Query

CREATE TEXT SEARCH DICTIONARY english_dict (    template = 'text',    locale = 'en_US.UTF-8',    case = 'lower',    stemming = false,    accent = false,    frequency = true,    position = true);
CREATE INDEX articles_idx ON articles    USING inverted (id, title english_dict, body english_dict);
VACUUM (REFRESH_TABLE) articles;
SELECT id, title FROM articles_idx WHERE body @@ 'search' ORDER BY id;

Result

 id | title----+------------------  1 | Inverted indexes  2 | Vector search

This page covers the concepts. For the complete CREATE INDEX … USING inverted grammar — every operator-class option, INCLUDE codec, index WITH option and supported column type — see the statement reference.

A trailing WHERE <predicate> builds a partial index that contains only the matching rows; DML keeps membership current as rows cross the predicate boundary.

Tip

After loading data, run VACUUM (REFRESH_TABLE) table_name to make newly inserted rows visible to the index immediately. A background thread otherwise refreshes the index on an interval — see Maintenance.

Operator classes and fields

Each indexed column carries its own operator class — the column [dictionary] [WITH (...)] form in the column list tells the index how to analyze and store that column. Different columns in the same index can use different operator classes:

• a column with a dictionary is analyzed into tokens (full-text);
• a column with no dictionary is indexed verbatim — one token per value — giving exact, case-sensitive matching, ideal for ids, tags and enum-like categories;
• a numeric or temporal column is indexed for exact and range queries;
• a FLOAT[N] column with ivf (...) is indexed for vector search, and a JSON/GEOMETRY column with a geo dictionary for geospatial search.

The example below puts an analyzed column (name) and a verbatim column (category) in one index:

Query

CREATE INDEX products_idx ON products    USING inverted (id, name english_dict, category);
VACUUM (REFRESH_TABLE) products;
SELECT id, name FROM products_idx WHERE category @@ 'footwear' ORDER BY id;

Result

 id | name----+---------------  1 | Running shoes  3 | Sandals

Because operator classes are per-column, a single index can mix full-text, verbatim and numeric (and vector and geo) columns, and a query can constrain several of them at once:

Query

CREATE INDEX catalog_idx ON catalog    USING inverted (id, name english_dict, category, price);
VACUUM (REFRESH_TABLE) catalog;
SELECT id, name, priceFROM catalog_idxWHERE name @@ 'shoes'  AND category @@ 'footwear'  AND price @@ ts_le(100)ORDER BY id;

Result

 id | name          | price----+---------------+-------  1 | Running shoes |    80

Tip

Field order does not matter. This is a key difference from a regular composite (ART) index, where column order is significant and only a query constraining a leading prefix of the columns can use the index. An inverted index indexes each field independently: the order of fields in USING inverted (...) carries no meaning, and a query may constrain any subset of the indexed fields, in any combination, and still be served entirely by the index.

Tip

Analyzed vs. verbatim. A column with a dictionary is tokenized and normalized, so 'Running Shoes' matches shoes. A column without a dictionary stores the value verbatim, so it only matches the exact string. Choose verbatim for identifiers, codes and categories; choose a dictionary for natural-language text. See Text Analysis.

Querying an inverted index

An inverted index behaves as a queryable relation: full-text, verbatim, range and geospatial predicates are issued by selecting from the index by name, with the @@ match operator on the indexed column.

SELECT id, title
FROM articles_idx          -- the index, by name
WHERE body @@ 'search';    -- @@ match on an indexed column

A TSQUERY predicate (@@, ST_*, range functions) only resolves against an inverted-indexed column inside the index relation — issuing it against the base table raises an error. The exception is vector ANN: an ORDER BY emb <-> $q LIMIT k is routed through the IVF index automatically whether you select from the index or the base table.

Tip

Filtering vs. ranking. WHERE col @@ query is a yes/no filter (does the row match?). Relevance is separate: order matches with a scorer such as ORDER BY BM25(idx.tableoid) DESC — see Ranking.

Indexed vs. INCLUDEd columns

Columns in the USING inverted (...) list are indexed — searchable with @@. Columns in the INCLUDE (...) list are stored but not indexed: they cannot be searched, but they can be returned by a query against the index, avoiding a separate lookup against the base table.

Query

CREATE INDEX pages_idx ON pages    USING inverted (id, body english_dict)    INCLUDE (url);
VACUUM (REFRESH_TABLE) pages;
SELECT id, url FROM pages_idx WHERE body @@ 'tuning' ORDER BY id;

Result

 id | url----+----------------------------  2 | https://example.com/tuning

See What to Index for choosing indexed vs. INCLUDEd columns, indexing expressions and JSON, and sizing trade-offs.

Indexing a table or a view

An inverted index can be built over a base table or a view:

• Base tables use the table's PRIMARY KEY as row identity, and the background refresh tracks inserts, updates and deletes.
• Views let you index data the database does not own a primary copy of — including external Parquet/CSV/JSON files on disk or S3. A view-backed index is a static snapshot.

Query

CREATE INDEX recent_articles_idx ON recent_articles    USING inverted (id, body english_dict);
SELECT id, title FROM recent_articles_idx WHERE body @@ 'search' ORDER BY id;

Result

 id | title----+---------------  2 | Vector search

See Indexing Views and Indexing External Data.

Lifecycle

An inverted index is eventually consistent: after a write, the new rows become searchable once the index is refreshed — immediately with VACUUM (REFRESH_TABLE), or automatically by the background refresh thread. Compaction merges segments in the background. See Maintenance & Introspection for the full refresh / compaction / statistics model and how to inspect an index.

Limitations

• Indexed expressions cannot contain aggregates, subqueries or volatile functions (e.g. random()).
• HUGEINT, DECIMAL, UUID and INTERVAL columns cannot be indexed.
• Composite ROW(...) columns are not supported.
• The same indexed expression cannot be listed twice with different dictionaries.

See also

• Text Analysis · What to Index
• Full-Text Search · Ranking · Vector Search · Hybrid Search · Geospatial Search
• Indexing Views · Indexing External Data · Maintenance & Introspection
• CREATE INDEX … USING inverted — full syntax reference
• Migrating from Elasticsearch · Search cookbook