Create an Index

Before a table can be queried by ParadeDB, it must be indexed. ParadeDB uses a custom index type called the ParadeDB index. The following code block creates a ParadeDB index over several columns in the mock_items table.

The key_field index option is deprecated and ignored. It is still accepted for backward compatibility. New indexes do not require a designated key field or a unique first column.

CREATE INDEX search_idx ON mock_items
USING paradedb (id, description, category);

import { indexing } from "@paradedb/drizzle-paradedb";

indexing
  .paradedbIndex("search_idx")
  .on(mockItems.id, mockItems.description, mockItems.category);

from django.db import connection
from paradedb.indexes import ParadeDBIndex

with connection.schema_editor() as schema_editor:
    schema_editor.add_index(
        MockItem,
        ParadeDBIndex(
            fields={
                "id": {},
                "description": {},
                "category": {},
            },
            name="search_idx",
        ),
    )

from sqlalchemy import Index
from paradedb.sqlalchemy import indexing

idx = Index(
    "search_idx",
    indexing.ParadeDBField(MockItem.id),
    indexing.ParadeDBField(MockItem.description),
    indexing.ParadeDBField(MockItem.category),
    postgresql_using="paradedb",
)

with engine.begin() as conn:
    idx.create(conn)

ActiveRecord::Base.connection.add_paradedb_index(
  :mock_items,
  fields: {
    id: {},
    description: {},
    category: {}
  },
  name: :search_idx
)

modelBuilder.Entity<MockItem>()
    .HasParadeDbIndex("search_idx", e => e.Id)
    .HasField(e => e.Description)
    .HasField(e => e.Category);

In version 0.25.0, the BM25 index was renamed to the ParadeDB index, and paradedb was added as the index access method name. The rename reflects the fact that the index is now much more than BM25 scoring — it also powers vector search, aggregates, top K, and filtering. USING bm25 remains supported as a backwards-compatible alias for USING paradedb.

These examples require ParadeDB 0.26.0 or newer and the ORM client versions listed in the getting started guide.

You’ll need to drop the existing search_idx before you can create a new one:

DROP INDEX search_idx;

import { sql } from "drizzle-orm";

await db.execute(sql`DROP INDEX search_idx`);

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("DROP INDEX search_idx")

from sqlalchemy import text

with engine.begin() as conn:
    conn.execute(text("DROP INDEX search_idx"))

ActiveRecord::Base.connection.remove_paradedb_index(:mock_items, name: :search_idx)

await dbContext.Database.ExecuteSqlRawAsync("DROP INDEX search_idx");

By default, text columns are tokenized using the unicode tokenizer, which splits text according to the Unicode segmentation standard. Because index creation is a time-consuming operation, we recommend experimenting with the available tokenizers to find the most suitable one before running CREATE INDEX. For instance, if a column contains multiple languages, the ICU tokenizer may be more appropriate.

CREATE INDEX search_idx ON mock_items
USING paradedb (id, (description::pdb.icu), category);

import { indexing, tokenizer } from "@paradedb/drizzle-paradedb";

indexing
  .paradedbIndex("search_idx")
  .on(
    mockItems.id,
    indexing.paradedbField(mockItems.description, tokenizer.icu()),
    mockItems.category,
  );

from django.db import connection
from paradedb.indexes import ParadeDBIndex
from paradedb.search import Tokenizer

with connection.schema_editor() as schema_editor:
    schema_editor.add_index(
        MockItem,
        ParadeDBIndex(
            fields={
                "id": {},
                "description": {"tokenizer": Tokenizer.icu()},
                "category": {},
            },
            name="search_idx",
        ),
    )

from sqlalchemy import Index
from paradedb.sqlalchemy import indexing, tokenizer

idx = Index(
    "search_idx",
    indexing.ParadeDBField(MockItem.id),
    indexing.ParadeDBField(
        MockItem.description,
        tokenizer=tokenizer.icu(),
    ),
    indexing.ParadeDBField(MockItem.category),
    postgresql_using="paradedb",
)

with engine.begin() as conn:
    idx.create(conn)

ActiveRecord::Base.connection.add_paradedb_index(
  :mock_items,
  fields: {
    id: {},
    description: { tokenizer: ParadeDB::Tokenizer.icu() },
    category: {}
  },
  name: :search_idx
)

modelBuilder.Entity<MockItem>()
    .HasParadeDbIndex("search_idx", e => e.Id)
    .HasField(e => e.Description, Tokenizer.Icu())
    .HasField(e => e.Category);

Only one ParadeDB index can exist per table. We recommend indexing all columns in a table that may be present in a search query, including columns used for sorting, grouping, filtering, and aggregations.

CREATE INDEX search_idx ON mock_items
USING paradedb (id, description, category, rating, in_stock, created_at, metadata, weight_range);

import { indexing } from "@paradedb/drizzle-paradedb";

indexing
  .paradedbIndex("search_idx")
  .on(
    mockItems.id,
    mockItems.description,
    mockItems.category,
    mockItems.rating,
    mockItems.inStock,
    mockItems.createdAt,
    mockItems.metadata,
    mockItems.weightRange,
  );

from django.db import connection
from paradedb.indexes import ParadeDBIndex

with connection.schema_editor() as schema_editor:
    schema_editor.add_index(
        MockItem,
        ParadeDBIndex(
            fields={
                "id": {},
                "description": {},
                "category": {},
                "rating": {},
                "in_stock": {},
                "created_at": {},
                "metadata": {},
                "weight_range": {},
            },
            name="search_idx",
        ),
    )

from sqlalchemy import Index
from paradedb.sqlalchemy import indexing

idx = Index(
    "search_idx",
    indexing.ParadeDBField(MockItem.id),
    indexing.ParadeDBField(MockItem.description),
    indexing.ParadeDBField(MockItem.category),
    indexing.ParadeDBField(MockItem.rating),
    indexing.ParadeDBField(MockItem.in_stock),
    indexing.ParadeDBField(MockItem.created_at),
    indexing.ParadeDBField(MockItem.metadata_),
    indexing.ParadeDBField(MockItem.weight_range),
    postgresql_using="paradedb",
)

with engine.begin() as conn:
    idx.create(conn)

ActiveRecord::Base.connection.add_paradedb_index(
  :mock_items,
  fields: {
    id: {},
    description: {},
    category: {},
    rating: {},
    in_stock: {},
    created_at: {},
    metadata: {},
    weight_range: {}
  },
  name: :search_idx
)

modelBuilder.Entity<MockItem>()
    .HasParadeDbIndex("search_idx", e => e.Id)
    .HasField(e => e.Description)
    .HasField(e => e.Category)
    .HasField(e => e.Rating)
    .HasField(e => e.InStock)
    .HasField(e => e.CreatedAt)
    .HasField(e => e.Metadata)
    .HasField(e => e.WeightRange);

Most ordinary scalar columns can be added directly to a ParadeDB index. The separate indexing pages cover cases that need extra configuration, such as JSON, arrays, composite fields, expressions, vectors, and text fields that need columnar storage.

Tracking Create Index Progress

To monitor the progress of a long-running CREATE INDEX, open a separate Postgres connection and query pg_stat_progress_create_index:

SELECT pid, phase, blocks_done, blocks_total
FROM pg_stat_progress_create_index;

Comparing blocks_done to blocks_total will provide a good approximation of the progress so far. If blocks_done equals blocks_total, that means that all rows have been indexed and the index is being flushed to disk.

Index Partitioning (Beta)

To speed up filtered queries by skipping non-matching segments or accelerate parallel joins between tables, you can configure the partition_by option:

CREATE INDEX search_idx ON mock_items
USING paradedb (id, description, category, tenant_id)
WITH (
  partition_by = 'tenant_id',
  target_segment_count = 16
);

See the Partitioning Guide for column selection guidance, data type requirements, and join setup.

Token Filters

After tokens are created, token filters can be configured to apply further processing like lowercasing, stemming, or unaccenting. For example, the following code block adds English stemming to description:

CREATE INDEX search_idx ON mock_items
USING paradedb (id, (description::pdb.simple('stemmer=english')), category);

import { indexing, tokenizer } from "@paradedb/drizzle-paradedb";

indexing
  .paradedbIndex("search_idx")
  .on(
    mockItems.id,
    indexing.paradedbField(
      mockItems.description,
      tokenizer.simple({ stemmer: "english" }),
    ),
    mockItems.category,
  );

from django.db import connection
from paradedb.indexes import ParadeDBIndex
from paradedb.search import Tokenizer

with connection.schema_editor() as schema_editor:
    schema_editor.add_index(
        MockItem,
        ParadeDBIndex(
            fields={
                "id": {},
                "description": {
                    "tokenizer": Tokenizer.simple(
                        options={"stemmer": "english"}
                    ),
                },
                "category": {},
            },
            name="search_idx",
        ),
    )

from sqlalchemy import Index
from paradedb.sqlalchemy import indexing, tokenizer

idx = Index(
    "search_idx",
    indexing.ParadeDBField(MockItem.id),
    indexing.ParadeDBField(
        MockItem.description,
        tokenizer=tokenizer.simple(options={"stemmer": "english"}),
    ),
    indexing.ParadeDBField(MockItem.category),
    postgresql_using="paradedb",
)

with engine.begin() as conn:
    idx.create(conn)

ActiveRecord::Base.connection.add_paradedb_index(
  :mock_items,
  fields: {
    id: {},
    description: {
      tokenizer: ParadeDB::Tokenizer.simple(options: { stemmer: "english" })
    },
    category: {}
  },
  name: :search_idx
)

modelBuilder.Entity<MockItem>()
    .HasParadeDbIndex("search_idx", e => e.Id)
    .HasField(e => e.Description, Tokenizer.Simple(new() { ["stemmer"] = "english" }))
    .HasField(e => e.Category);

BM25 Parameters

Tuning BM25 parameters is not necessary for most use cases.

BM25 uses two parameters, k1 and b, to control how term frequency and field length affect relevance scores. You can configure them independently for each field when creating an index.

Parameter	Default	Accepted Range	Effect
`k1`	`1.2`	`0` to `100`, inclusive	Controls term frequency saturation. Lower values reduce the benefit of repeated query terms; higher values give them more weight. At `0`, repeated occurrences add no weight, and `b` has no effect.
`b`	`0.75`	`0` to `1`, inclusive	Controls field length normalization relative to the field’s average length. Higher values penalize longer fields more strongly. At `0`, normalization is disabled; at `1`, it is fully applied.

Pass k1 and b as options to a field’s tokenizer. In this example, the description field uses lower values to reduce the influence of repeated terms and field length. The category field gives repeated terms more weight while keeping the default length normalization.

CREATE INDEX search_idx ON mock_items
USING paradedb (
  id,
  (description::pdb.simple('k1=0.9', 'b=0.3')),
  (category::pdb.simple('k1=1.5', 'b=0.75'))
);

import { indexing, tokenizer } from "@paradedb/drizzle-paradedb";

indexing
  .paradedbIndex("search_idx")
  .on(
    mockItems.id,
    indexing.paradedbField(
      mockItems.description,
      tokenizer.simple({ k1: 0.9, b: 0.3 }),
    ),
    indexing.paradedbField(
      mockItems.category,
      tokenizer.simple({ k1: 1.5, b: 0.75 }),
    ),
  );

from django.db import connection
from paradedb.indexes import ParadeDBIndex
from paradedb.search import Tokenizer

with connection.schema_editor() as schema_editor:
    schema_editor.add_index(
        MockItem,
        ParadeDBIndex(
            fields={
                "id": {},
                "description": {"tokenizer": Tokenizer.simple(options={"k1": 0.9, "b": 0.3})},
                "category": {"tokenizer": Tokenizer.simple(options={"k1": 1.5, "b": 0.75})},
            },
            name="search_idx",
        ),
    )

from sqlalchemy import Index
from paradedb.sqlalchemy import indexing, tokenizer

idx = Index(
    "search_idx",
    indexing.ParadeDBField(MockItem.id),
    indexing.ParadeDBField(
        MockItem.description,
        tokenizer=tokenizer.simple(options={"k1": 0.9, "b": 0.3}),
    ),
    indexing.ParadeDBField(
        MockItem.category,
        tokenizer=tokenizer.simple(options={"k1": 1.5, "b": 0.75}),
    ),
    postgresql_using="paradedb",
)

with engine.begin() as conn:
    idx.create(conn)

ActiveRecord::Base.connection.add_paradedb_index(
  :mock_items,
  fields: {
    id: {},
    description: { tokenizer: ParadeDB::Tokenizer.simple(options: { k1: 0.9, b: 0.3 }) },
    category: { tokenizer: ParadeDB::Tokenizer.simple(options: { k1: 1.5, b: 0.75 }) }
  },
  name: :search_idx
)

modelBuilder.Entity<MockItem>()
    .HasParadeDbIndex("search_idx", e => e.Id)
    .HasField(e => e.Description, Tokenizer.Simple(new() { ["k1"] = 0.9f, ["b"] = 0.3f }))
    .HasField(e => e.Category, Tokenizer.Simple(new() { ["k1"] = 1.5f, ["b"] = 0.75f }));

You can set either parameter on its own or both together. Omitted parameters keep their defaults, and queries automatically use the values configured in the index.

Indexing

Full-Text / Lexical Search

Vector / Semantic Search

Hybrid Search

Aggregations

JOINs

Sharding

Cheatsheets

Tracking Create Index Progress

Index Partitioning (Beta)

Token Filters

BM25 Parameters

​Tracking Create Index Progress

​Index Partitioning (Beta)

​Token Filters

​BM25 Parameters

Tracking Create Index Progress

Index Partitioning (Beta)

Token Filters

BM25 Parameters