Overview - ParadeDB

Legacy Docs: This page describes our legacy API. It will be deprecated in a future version. Please use the v2 API where possible.

Basic Usage

You can change how individual fields are tokenized by passing JSON strings to the WITH clause of CREATE INDEX. For instance, the following statement configures an ngram tokenizer for the description field.

CREATE INDEX search_idx ON mock_items
USING paradedb (id, description)
WITH (
    key_field = 'id',
    text_fields = '{
        "description": {
          "tokenizer": {"type": "ngram", "min_gram": 2, "max_gram": 3, "prefix_only": false}
        }
    }'
);

The key(s) of the JSON string correspond to field names, and the values are the configuration options. If a configuration option or field name is not specified, the default values are used (see all configuration options).

Configure Multiple Fields

To configure multiple fields, simply pass more keys to the JSON string. For instance, the following statement specifies tokenizers for both the description and category fields.

CREATE INDEX search_idx ON mock_items
USING paradedb (id, description, category)
WITH (
    key_field = 'id',
    text_fields = '{
        "description": {
          "tokenizer": {"type": "ngram", "min_gram": 2, "max_gram": 3, "prefix_only": false}
        },
        "category": {
            "tokenizer": {"type": "ngram", "min_gram": 2, "max_gram": 3, "prefix_only": false}
        }
    }'
);

All Configuration Options

Text Fields

Options for columns of type VARCHAR, TEXT, UUID, and their corresponding array types should be passed to text_fields.

CREATE INDEX search_idx ON mock_items
USING paradedb (id, description)
WITH (
    key_field = 'id',
    text_fields = '{
        "description": {
          "fast": true,
          "tokenizer": {"type": "ngram", "min_gram": 2, "max_gram": 3, "prefix_only": false}
        }
    }'
);

The nested configuration JSON for text_fields accepts the following keys.

default:false

See fast fields for when this option should be set to true.

See tokenizers for how to configure the tokenizer.

See normalizers for how to configure the normalizer.

Advanced Options

default:true

Whether the field is indexed. Must be true in order for the field to be tokenized and searchable.

default:true

Fieldnorms store information about the length of the text field. Must be true to calculate the BM25 score.

JSON Fields

Options for columns of type JSON and JSONB should be passed to json_fields.

CREATE INDEX search_idx ON mock_items
USING paradedb (id, metadata)
WITH (
  key_field = 'id',
  json_fields = '{
    "metadata": {
      "fast": true
    }
  }'
);

The nested configuration JSON for json_fields accepts the following keys.

default:false

See fast fields for when this option should be set to true.

See tokenizers for how to configure the tokenizer.

See normalizers for how to configure the normalizer.

default:true

If true, JSON keys containing a . will be expanded. For instance, if expand_dots is true, {"metadata.color": "red"} will be indexed as if it was {"metadata": {"color": "red"}}.

Advanced Options

default:true

Whether the field is indexed. Must be true in order for the field to be tokenized and searchable.

default:true

Fieldnorms store information about the length of the text field. Must be true to calculate the BM25 score.

Advanced Options

In addition to text and JSON, ParadeDB exposes options for numeric, boolean, range, and enum fields. For most use cases, it is not necessary to change these options.

Numeric Fields

Options for columns of type SMALLINT, INTEGER, BIGINT, OID, REAL, DOUBLE PRECISION, NUMERIC, and their corresponding array types should be passed to numeric_fields.

CREATE INDEX search_idx ON mock_items
USING paradedb (id, rating)
WITH (
    key_field = 'id',
    numeric_fields = '{
        "rating": {"fast": true}
    }'
);

Advanced Options

default:true

Whether the field is indexed. Must be true in order for the field to be tokenized and searchable.

default:true

Fast fields can be random-accessed rapidly. Fields used for aggregation must have fast set to true. Fast fields are also useful for accelerated scoring and filtering.

Datetime Fields

As of 0.24.1, datetime_fields is deprecated. The performance improvements it used to enable are now on by default, so the option should be removed from your CREATE INDEX statement.

Columns of type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, TIME WITH TIME ZONE, and their corresponding array types are indexed and made fast automatically — no configuration is required.

Boolean Fields

Options for columns of type BOOLEAN and BOOLEAN[] should be passed to boolean_fields.

CREATE INDEX search_idx ON mock_items
USING paradedb (id, in_stock)
WITH (
  key_field = 'id',
  boolean_fields = '{
      "in_stock": {"fast": true}
  }'
);

CREATE INDEX accepts several configuration options for boolean_fields:

Advanced Options

default:true

Whether the field is indexed. Must be true in order for the field to be tokenized and searchable.

default:true

Fast fields can be random-accessed rapidly. Fields used for aggregation must have fast set to true. Fast fields are also useful for accelerated scoring and filtering.

Range Fields

Options for columns of type int4range, int8range, numrange, daterange, tsrange, and tstzrange should be passed to range_fields. Range fields should be queried with range term queries.

CREATE INDEX search_idx ON mock_items
USING paradedb (id, weight_range)
WITH (
  key_field = 'id',
  range_fields = '{
      "weight_range": {"fast": true}
  }'
);

Advanced Options

default:true

Fast fields can be random-accessed rapidly. Fields used for aggregation must have fast set to true. Fast fields are also useful for accelerated scoring and filtering.

Enumerated Types

Options for custom Postgres enums should be passed to numeric_fields. Enums should be queried with term queries. If the ordering of the enum is changed with ADD VALUE ... [ BEFORE | AFTER ], the BM25 index should be dropped and recreated to account for the new enum ordinal values.

​Basic Usage

​Configure Multiple Fields

​All Configuration Options

​Text Fields

​JSON Fields

​Advanced Options

​Numeric Fields

​Datetime Fields

​Boolean Fields

​Range Fields

​Enumerated Types

Basic Usage

Configure Multiple Fields

All Configuration Options

Text Fields

JSON Fields

Advanced Options

Numeric Fields

Datetime Fields

Boolean Fields

Range Fields

Enumerated Types