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 — answer phrase / proximity queries from the index alone; without them a phrase needs the column's text in the index (phrases without positions)
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. A column listed both in USING inverted (...) and in INCLUDE (...) is searchable and stored, which lets phrase queries on it work without position; an indexed expression gets the same when it is listed a second time with included() to store its value, as in USING inverted (id, (lower(body)) words, (lower(body)) included()). Each may set a storage compression codec: auto, the default, or one of uncompressed, rle, bitpacking, zstd, alp, alprd, roaring or dict_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 and for norm columns; there is no separate norm setting. Must be a multiple of the vector size (2048). A scan claims one row group per worker, so a smaller value spreads one segment over more threads at the cost of more per-unit setup
optimize_top_k	—	Scorer expression enabling top-K (WAND) pruning, e.g. 'bm25(1.2, 0.75)'

An index on a search table (CREATE TABLE … WITH (storage = 'search')) takes none of these options. It has no storage or background tasks of its own: the table's options run refresh and compaction for every index on it, and the table keeps the score bounds that optimize_top_k prunes with. So CREATE INDEX rejects all five on a search table, with an error that names the option, such as row_group_size is a table option on a search-backed table. Set them on the table instead; see Background intervals.

Partial indexes

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

sql

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.

A constant predicate, one that reads no column and calls no function whose result can change (such as now()), is evaluated once by CREATE INDEX, the way a query's WHERE evaluates it. WHERE 'true' or WHERE 1 + 1 = 2 builds an ordinary index, which pg_indexes shows without a WHERE; WHERE 'f' is stored as WHERE false and the index stays empty. A string that is not a boolean fails as in a query: WHERE 'abc' raises Could not convert string 'abc' to BOOL. This holds for plain indexes too.

Partial indexes are an inverted-index feature. A plain (ART) CREATE INDEX … WHERE … is accepted, but the predicate filters only the rows present when the index is built; see PostgreSQL compatibility.

An index on a search table can't be partial: there, CREATE INDEX … WHERE … fails with partial indexes are not supported on a search-backed table.

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). The index derives each row's identity automatically from the view's source; there is no pk option, and only attached external databases accept an explicit key through key_columns (see Row identity). The postings are a snapshot of the source, refreshed by REINDEX INDEX or the reindex_interval option (see Refreshing the index). See also Indexing a table vs. a view.

Examples

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

SQL

CREATE TEXT SEARCH DICTIONARY english_dict AS    split_text(case := 'lower') | normalize_tokens('en_US.UTF-8', accent := false)    WITH (frequency, position);
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;

 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:

SQL

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;

 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

Syntax

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

Syntax

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

Syntax