Indexing¶

AkasicDB text search indexes are defined with PostgreSQL's CREATE INDEX ... USING texton statement. Run the SQL on this page in a database where AkasicDB text search is available.

Note

A USING texton index requires the extension to be enabled in the target database. To use background worker features such as automatic batch commit, the server must be started with the extension registered in shared_preload_libraries. See Operations for details.

Create a shared example table.

CREATE TABLE docs (
  id    bigserial PRIMARY KEY,
  title text,
  body  text
);

Whole-Row Index¶

To search across an entire row, create a whole-row expression index. The @@ search operator uses a whole-row left operand, so docs @@ ... queries require this index.

CREATE INDEX docs_texton_idx
  ON docs
  USING texton ((docs.*))
  WITH (mapping='{"fields":{"title":{"type":"text"},"body":{"type":"text"}}}');

A field mapping is required. CREATE INDEX is rejected if WITH (mapping='...') is omitted or fields is empty. Mapping syntax is covered in Field Mapping.

Rows already present at CREATE INDEX time are indexed immediately.

Field Mapping¶

Per-field type, analyzer, tokenizer, and dictionary, along with whether a field is available for sorting and aggregation, are declared in the JSON of the index option WITH (mapping='{...}'). A text field with no analyzer is indexed with the language-neutral standard analyzer. Tokenizer and dictionary selection is covered in Tokenization.

The examples on this page reuse a single index name, docs_texton_idx. From the second example on, each one is preceded by DROP INDEX IF EXISTS so that the page can be run top to bottom.

DROP INDEX IF EXISTS docs_texton_idx;

CREATE INDEX docs_texton_idx
  ON docs
  USING texton ((docs.*))
  WITH (mapping='{"fields":{
    "title":{"type":"text","analyzer":{"tokenizer":{"lindera":{"dictionary":"ko-dic"}}}},
    "body":{"type":"text","analyzer":{"tokenizer":{"lindera":{"dictionary":"ko-dic"}}}}
  }}');

Mapping key	Description
`type`	The field type. Specifies the type of the indexed value, such as text, integer/float, or timestamptz.
`analyzer`	The tokenizer and dictionary for a text field. When omitted, the field is indexed with `standard`.
`fast`	Declares the field as available for sorting and aggregation. Sort keys for `akasicdb.text_search()` and aggregation fields for `akasicdb.text_aggregate()` must be `fast`. Sort keys carry one further type restriction (see Fast Fields).
`stored`	Retains the original field value so highlighting can build snippets. A field used with `akasicdb.text_highlight()` must be `stored`.

Warning

A field mapping cannot be changed in place after the index is created. To change the mapping, recreate the index (use REINDEX to apply dictionary updates). See the reindex section in Tokenization.

Fast Fields¶

Declare fields used for sorting or aggregation as fast in the mapping. A field that is not fast cannot be used as a sort key for akasicdb.text_search() or as a target field for akasicdb.text_aggregate().

Sort keys carry one further restriction. Sorting supports only integer (i64 / u64), float (f64), bool, and date types, so text and keyword fields cannot be sort keys even when declared fast. Aggregation has no such restriction.

An analyzed text field is indexed as its individual tokens, so it does not work as a terms-aggregation key on its own — aggregating title would count morphemes, not titles. Declare an unanalyzed keyword sub-field alongside it and mark that fast. The sub-field is referenced with dot notation (title.raw).

DROP INDEX IF EXISTS docs_texton_idx;

CREATE INDEX docs_texton_idx
  ON docs
  USING texton ((docs.*))
  WITH (mapping='{"fields":{
    "title":{"type":"text",
             "analyzer":{"tokenizer":{"lindera":{"dictionary":"ko-dic"}}},
             "fields":{"raw":{"type":"keyword","fast":true}}},
    "body":{"type":"text",
             "analyzer":{"tokenizer":{"lindera":{"dictionary":"ko-dic"}}},
             "stored":true}
  }}');

With this mapping, title is searched with morphological analysis while title.raw holds the whole title as a single unanalyzed term for aggregation. body is declared stored so that highlighting can build snippets from it.

Multi-Column Index¶

To index specific columns instead of a whole-row expression, list the columns. Per-column mappings are declared with mapping, the same as for a whole-row index.

CREATE INDEX docs_title_body_texton_idx
  ON docs
  USING texton (title, body)
  WITH (mapping='{"fields":{
    "title":{"type":"text","analyzer":{"tokenizer":{"lindera":{"dictionary":"ko-dic"}}}},
    "body":{"type":"text","analyzer":{"tokenizer":{"lindera":{"dictionary":"ko-dic"}}}}
  }}');

A single column is listed the same way when only that column needs indexing.

CREATE INDEX docs_body_texton_idx
  ON docs
  USING texton (body)
  WITH (mapping='{"fields":{
    "body":{"type":"text","analyzer":{"tokenizer":{"lindera":{"dictionary":"ko-dic"}}}}
  }}');

A column-list index does not provide the whole-row left operand that @@ requires, so docs @@ ... still needs the whole-row index above.

Partitioned Tables¶

Only a column-list index can be defined on a partitioned parent. The parent index fans out to each leaf partition, creating one index per partition.

The example table for this section:

CREATE TABLE docs_part (
  id    bigint,
  title text,
  body  text
) PARTITION BY RANGE (id);

CREATE TABLE docs_part_p1 PARTITION OF docs_part FOR VALUES FROM (1) TO (1000);
CREATE TABLE docs_part_p2 PARTITION OF docs_part FOR VALUES FROM (1000) TO (2000);

CREATE INDEX docs_part_texton_idx
  ON docs_part
  USING texton (body)
  WITH (mapping='{"fields":{
    "body":{"type":"text","analyzer":{"tokenizer":{"lindera":{"dictionary":"ko-dic"}}}}
  }}');

A whole-row expression cannot be used on the parent: USING texton ((docs_part.*)) on a partitioned parent is rejected with cannot convert whole-row table reference. To run @@ searches against a partitioned table, create a whole-row index on each leaf partition and search the leaf as the left operand rather than the parent.