CREATE INDEX … USING inverted

Adding USING inverted to CREATE INDEX builds an inverted index, which powers full-text, vector and geospatial search. This page is the syntax reference; for a conceptual guide with query examples, see Inverted Index.

Column specification

Each entry in the USING inverted (...) list is an indexed column.

• Text and scalar columns may name a text search dictionary (the analyzer). With no dictionary the column is indexed verbatim — one token per value — giving exact, case-sensitive matching suitable for identifiers, tags and categories. Numeric and temporal columns are indexed for exact and range queries.
• Vector columns use ivf (...) to build an IVF index. metric is required; nlist / nlist_factor size the coarse clustering and quant enables optional compression (sq8, sq4, pq, rabitq).
• Expressions — an entry may be a parenthesized expression over one or more columns, e.g. (lower(name)) my_dict or (price * 110 / 100). The query must use the identical expression. Aggregates, subqueries and volatile functions (e.g. random()) are rejected.
• JSON — index a JSON extraction expression such as (doc ->> 'host') my_dict, optionally with a dictionary; this indexes exactly the sub-field you search. (See also the VARIANT type for storing typed payloads.)
• Generated columns — index a generated column the same as any other column (index the column itself, not its defining expression).
• Array columns — a TEXT[] / VARCHAR[] column is indexed element-by-element; a row matches if any element matches.

HUGEINT, DECIMAL, UUID and INTERVAL columns cannot be indexed. Composite ROW(...) columns are not supported. For a guided treatment of these choices, see What to Index.

Operator-class options (feature flags)

A column's trailing WITH (...) sets per-column feature flags controlling what the index records. They are off by default; enable only what your queries need.

Flag	Default	Enables
frequency	false	Term frequency — required for relevance scoring
position	false	Term positions — required for phrase / proximity queries
offset	false	Character offsets — required for highlighting
norm	false	The length-normalization factor used by some scorers

The same flags can be set on the dictionary itself, in which case every column using it inherits them. See the feature flags reference.

INCLUDE columns

Columns in INCLUDE (...) are stored but not indexed: they cannot be searched, but a query that selects from the index can return them without a separate base-table lookup. Each may set a storage compression codec — one of uncompressed, bitpacking, alp, rle or fsst — for example INCLUDE (payload included (compression = 'alp')).

Index options

The trailing WITH (...) clause sets index-level options.

Option	Default	Description
refresh_interval	1000	Background refresh interval in milliseconds; 0 disables it
compaction_interval	1000	Background compaction interval in milliseconds; 0 disables it
cleanup_interval_step	1	Commit ticks between cleanup passes; 0 disables it
row_group_size	122880	Row-group size for stored (INCLUDEd) columns
norm_row_group_size	122880	Row-group size for norm columns when norm is enabled
optimize_top_k	—	Scorer expression enabling top-K (WAND) pruning, e.g. 'bm25(1.2, 0.75)'
pk	auto	Primary-key column to use as row identity when indexing a view

Partial indexes

A trailing WHERE <predicate> builds a partial index containing only the rows that satisfy the predicate:

CREATE INDEX recent_errors ON logs USING inverted(message log_dict)
  WHERE level = 'error';

The predicate is a boolean expression over the table's columns; a row whose predicate evaluates to NULL is treated as non-matching, following PostgreSQL semantics. DML keeps membership current — rows enter and leave the index as updates move them across the predicate boundary — and queries against the index only ever see matching rows.

Partial indexes are an inverted-index feature: a plain (ART) CREATE INDEX … WHERE … is rejected.

Indexing tables and views

An inverted index can be built over a base table or a view (including a view over read_parquet/read_csv on local disk or S3). A view has no primary key, so the index materializes its columns at build time and resolves a row identity — automatically for base-table and fast-path-reader views, or via WITH (pk = '...') for generic views. See Indexing a table vs. a view.

Examples

Create a dictionary, index two text columns with it, then query the index:

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

Store an extra column alongside the index with INCLUDE so it can be returned without touching 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 also

• Inverted Index — conceptual guide
• CREATE INDEX — the general statement (ART)
• CREATE TEXT SEARCH DICTIONARY — analyzer reference
• Full-Text Search Functions

Syntax

A column_spec is an indexed column with an optional analyzer (dictionary) or, for vectors, an ivf configuration:

An include_spec is a stored-only column with an optional compression codec: