> ## Documentation Index
> Fetch the complete documentation index at: https://docs.paradedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Create an Index

<Danger>
  **Legacy Docs:** This page describes our legacy API. It will be deprecated in
  a future version. Please use the [v2 API](/) where possible.
</Danger>

## Basic Usage

<Note>
  For large indexes, tuning Postgres' default memory and parallel worker
  settings prior to running `CREATE INDEX` is strongly recommended. Please see
  [index tuning](/documentation/performance-tuning/overview) for details.
</Note>

The following code block creates a BM25 index called `search_idx` over multiple columns of the `mock_items` table.
All columns that are relevant to the search query, including columns used for sorting and filtering, should be indexed for optimal performance.

```sql theme={null}
CREATE INDEX search_idx ON mock_items
USING bm25 (id, description, category, rating, in_stock, created_at, metadata, weight_range)
WITH (key_field='id');
```

Note that the index's column list contains fields of type text, JSON, numeric, timestamp, and boolean. See the [Field Configuration](/legacy/indexing/field-options) section for how to configure these fields.

## Syntax

```sql theme={null}
CREATE INDEX <index_name> ON <schema_name>.<table_name>
USING bm25 (<columns_or_expressions>)
WITH (key_field='<key_field>');
```

<ParamField body="index_name">
  The name of the index being created. If unspecified, Postgres will
  automatically choose a name.
</ParamField>

<ParamField body="schema_name">
  The name of the schema that the table belongs to. If unspecified, `CURRENT
      SCHEMA` is used.
</ParamField>

<ParamField body="table_name" required>
  The name of the table being indexed.
</ParamField>

<ParamField body="columns_or_expressions" required>
  A comma-separated list of columns or [expressions](#indexes-on-expressions) to
  index, starting with the `key_field`. Text, numeric, datetime, boolean, range,
  enum, and JSON types can be indexed.
</ParamField>

<ParamField body="key_field" required>
  The name of a column in the table that represents a unique identifier for each
  record. Usually, this is the same column that is the primary key of the table.
</ParamField>

## Choosing a Key Field

The `key_field` must have a `UNIQUE` constraint. A non-unique key field is likely to lead to undefined behavior
or incorrect search results.

While the key field can be any unique text, numeric, or datetime value, an integer key field will be the most
performant. The Postgres `SERIAL` type is an easy way to create a unique integer column.

Finally, the key field must be the first column in the target list.

```sql theme={null}
-- Recommended: key_field is the first column in the list
CREATE INDEX search_idx ON mock_items
USING bm25 (id, description)
WITH (key_field = 'id');

-- NOT recommended: key_field is not the first column
CREATE INDEX search_idx ON mock_items
USING bm25 (description, id)
WITH (key_field = 'id');
```

## Partitioned Index

In Postgres, a partitioned index is an index created over a [partitioned table](https://www.postgresql.org/docs/current/ddl-partitioning.html).
A BM25 index can be created over a partitioned table in the same way as a normal table.

<Note>
  If the partition key of your partitioned table is only indexed as a fast field
  by the BM25 index (and not additionally indexed via a B-Tree index), note that
  the `@@@` operator [must be
  used](/documentation/aggregates/limitations#filtering) in order for filtering
  to be optimized by the index.
</Note>

## Partial Index

The following code block demonstrates how to pass predicates to `CREATE INDEX`
to construct a [partial index](https://www.postgresql.org/docs/current/indexes-partial.html). Partial
indexes are useful for reducing index size on disk and improving update speeds over non-indexed rows.

```sql theme={null}
CREATE INDEX search_idx ON mock_items
USING bm25 (id, description)
WITH (key_field='id')
WHERE category = 'Electronics' AND rating > 2;
```

## Indexes on Expressions

Postgres supports creating [indexes on expressions](https://www.postgresql.org/docs/current/indexes-expressional.html),
which can be useful for optimizing queries that involve computed values.
A BM25 index can also be created over an expression, as shown below:

```sql theme={null}
CREATE INDEX search_idx ON mock_items
-- index a single field converting a text field to lowercase
USING bm25 (id, description, lower(description))
WITH (key_field='id');
```

In order to modify how the BM25 engine will index the expression, you can specify
the newly computed field as `_pg_search_{i}` where `{i}` is the 0-based ordinal position
of the expression in the index definition. In the example above, the lowercase version
of the `description` field will be referred to as `_pg_search_2`.

```sql theme={null}
CREATE INDEX search_idx ON mock_items
USING bm25 (id, description, lower(description))
WITH (
  key_field='id',
  text_fields='{
    "_pg_search_2": {
      "fast":true,
      "tokenizer": {"type": "keyword"},
      "record": "basic"
    }
  }'
);
```

This rule holds for querying the field as well:

```sql theme={null}
SELECT * FROM mock_items
WHERE mock_items @@@ paradedb.term('_pg_search_2', 'television');
```

## Concurrent Indexing

To create a new index without blocking writes to your table, use the `CONCURRENTLY` keyword:

```sql theme={null}
CREATE INDEX CONCURRENTLY search_idx_v2 ON mock_items
USING bm25 (id, description, category, rating, in_stock)
WITH (key_field='id');
```

This is particularly useful when you need to:

* Reindex with different settings
* Update an existing index without downtime
* Change the indexed columns

`pg_search` can only use a single BM25 index per table - the most recently created one will automatically be used for queries. After creating a new index concurrently and verifying it works as expected, you can safely drop the old index:

```sql theme={null}
DROP INDEX search_idx;
```

## Delete Index

The following command deletes a BM25 index.

```sql theme={null}
DROP INDEX search_idx;
```