Full-Text Search in Drizzle ORM
with ParadeDB — BM25 Without a Second System

Why BM25 Matters (and ts_vector Doesn't Cut It)

PostgreSQL's built-in full-text search uses tsvector / tsquery with the ts_rank function. It works for basic keyword matching, but its scoring only accounts for term frequency (how often a word appears in a document) and document length. It ignores inverse document frequency (IDF) — how rare a term is across the entire corpus.

BM25 (Best Matching 25, often called Okapi BM25) considers all three factors. A word that appears in every document — like "the" or "product" — contributes less to the score than a rare word that only shows up in a few documents. This produces dramatically better relevance ranking, which is why BM25 is the foundation of Elasticsearch, Lucene, and most modern search engines.

ParadeDB brings BM25 into PostgreSQL as a native index access method (IAM) via the pg_search extension. Instead of running a second system for search, your BM25 index lives inside PostgreSQL, is transactionally consistent with your data, and is queryable through your ORM — Drizzle ORM in this case.

Setup: Drizzle ORM + ParadeDB + PostgreSQL

1. Install Dependencies

You need a ParadeDB-compatible PostgreSQL instance (either ParadeDB's distribution or stock PostgreSQL with the pg_search extension installed). Then add the Node packages:

npm install drizzle-orm @paradedb/drizzle-paradedb postgres
npm install -D drizzle-kit @types/node

2. Define Your Schema

Start with a standard Drizzle schema for an e-commerce products table — this will be our search target throughout the guide:

import { pgTable, serial, text, integer, boolean, jsonb } from "drizzle-orm/pg-core";

export const mockItems = pgTable("mock_items", {
  id: serial("id").primaryKey(),
  description: text("description").notNull(),
  category: text("category").notNull(),
  rating: integer("rating"),
  inStock: boolean("in_stock").default(false),
  metadata: jsonb("metadata"),
});

3. Create the BM25 Index

Instead of creating the index with raw SQL, use the indexing API from @paradedb/drizzle-paradedb:

import { indexing } from "@paradedb/drizzle-paradedb";
import { sql } from "drizzle-orm";
import { db } from "./db";
import { mockItems } from "./schema";

// Create BM25 index across text, numeric, and JSON columns
await indexing
  .bm25Index("search_idx")
  .on(
    mockItems.id,
    mockItems.description,
    mockItems.category,
    mockItems.rating,
    mockItems.inStock,
    mockItems.metadata,
  );

// Equivalent SQL:
// CREATE INDEX search_idx ON mock_items
// USING bm25 (id, description, category, rating, in_stock, metadata)
// WITH (key_field='id');

The key_field must be a UNIQUE column — typically the primary key. Only one BM25 index can exist per table, so index all columns you'll search or filter on.

4. Configure Tokenizer for Your Data

By default, text columns are tokenized using the Unicode segmentation standard. For English search with stemming, configure the simple tokenizer with a stemmer:

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

await indexing
  .bm25Index("search_idx")
  .on(
    mockItems.id,
    indexing.bm25Field(
      mockItems.description,
      tokenizer.simple({ stemmer: "english" }),
    ),
    mockItems.category,
    mockItems.rating,
    mockItems.inStock,
    mockItems.metadata,
  );

The stemmer reduces words to their root form — "running", "runs", and "ran" all match the token "run". This significantly improves recall for user searches.

Basic Keyword Search

ParadeDB exposes BM25 search through SQL operators. In Drizzle, you pass the query as a raw SQL expression through db.execute or via the sql template tag.

The ||| operator performs match disjunction — find documents that match one or more of the query terms:

import { sql } from "drizzle-orm";

const results = await db.execute(
  sql`
    SELECT id, description, category, rating,
           paradedb.score(id) as bm25_score
    FROM mock_items
    WHERE description ||| 'running shoes'
    ORDER BY paradedb.score(id) DESC
    LIMIT 20
  `
);

The paradedb.score(id) function returns the BM25 relevance score for each row. Documents with higher scores appear first. This is the same scoring algorithm Elasticsearch uses internally — you get production-quality ranking without running a second database.

For phrase search (documents where words appear in order), use the @@@ operator:

// Phrase match — "trail" must appear before "running"
const results = await db.execute(
  sql`
    SELECT id, description, paradedb.score(id) as score
    FROM mock_items
    WHERE description @@@ 'trail running'
    ORDER BY paradedb.score(id) DESC
    LIMIT 10
  `
);

Faceted Search: Filter by Category, Stock, and Price

One of BM25's advantages over tsvector is that the index stores non-text columns too, enabling fast filtering and aggregation alongside text search:

// Full-text search + facet filters in a single query
const results = await db.execute(
  sql`
    SELECT
      id, description, category, rating, in_stock,
      paradedb.score(id) as score
    FROM mock_items
    WHERE
      description ||| 'waterproof hiking'
      AND category = 'Outdoor'
      AND in_stock = true
      AND rating >= 4
    ORDER BY paradedb.score(id) DESC
    LIMIT 20
  `
);

Because all these columns are in the same BM25 index, the filter pushdown is efficient — no separate database round-trips for filtering. You can also aggregate within the index without hitting the base table:

// Facet counts — how many matches per category
const facets = await db.execute(
  sql`
    SELECT category, COUNT(*) as count
    FROM mock_items
    WHERE description ||| 'camping gear'
    GROUP BY category
    ORDER BY count DESC
  `
);

For date-range faceting, include the datetime column in the BM25 index and filter with standard PostgreSQL operators:

const results = await db.execute(
  sql`
    SELECT id, description, created_at, paradedb.score(id)
    FROM mock_items
    WHERE description ||| 'tent'
      AND created_at >= '2026-01-01'
      AND created_at < '2026-07-01'
    ORDER BY paradedb.score(id) DESC
    LIMIT 20
  `
);

Autocomplete with Prefix Search

Autocomplete requires prefix matching — finding documents where a field starts with the user's typed characters. ParadeDB supports this with the ^ operator:

// Prefix search for autocomplete
const results = await db.execute(
  sql`
    SELECT id, description, category
    FROM mock_items
    WHERE description ^ 'hik'
    LIMIT 5
  `
);
// Returns: "Hiking Boots", "Hiking Poles", "Hiking Backpack 40L"

For a production autocomplete endpoint, combine prefix search with facet filters to narrow suggestions by category or department:

// Autocomplete with category filter
async function autocomplete(query: string, category?: string) {
  const sql_query = category
    ? sql`
        SELECT id, description, category
        FROM mock_items
        WHERE description ^ ${query}
          AND category = ${category}
        LIMIT 8
      `
    : sql`
        SELECT id, description, category
        FROM mock_items
        WHERE description ^ ${query}
        LIMIT 8
      `;
  return await db.execute(sql_query);
}

const suggestions = await autocomplete("hik", "Outdoor");

Hybrid Search: BM25 + pgvector with RRF Scoring

For modern search applications, you often want both keyword precision (exact term matches) and semantic similarity (vector embeddings). ParadeDB supports hybrid search using Reciprocal Rank Fusion (RRF), which combines BM25 scores with pgvector similarity scores into a single ranked result set.

First, add a vector column to your table and generate embeddings:

import { vector } from "drizzle-orm/pg-core";

// Extend the schema with a vector column
export const mockItems = pgTable("mock_items", {
  id: serial("id").primaryKey(),
  description: text("description").notNull(),
  category: text("category").notNull(),
  rating: integer("rating"),
  inStock: boolean("in_stock").default(false),
  metadata: jsonb("metadata"),
  embedding: vector("embedding", { dimensions: 384 }),
});

Then run a hybrid query that searches by both text BM25 and vector similarity simultaneously:

// Hybrid search: BM25 + pgvector with RRF
const results = await db.execute(
  sql`
    SELECT
      id, description, category,
      paradedb.score(id) as bm25_score,
      embedding <=> ${queryEmbedding}::vector as vector_score
    FROM mock_items
    WHERE description ||| 'camping gear'
       OR embedding IS NOT NULL
    ORDER BY
      paradedb.score(id) * 0.3 + (1 - (embedding <=> ${queryEmbedding}::vector)) * 0.7
    DESC
    LIMIT 20
  `
);

The weight factors (0.3 for BM25, 0.7 for vector) let you tune the balance between keyword precision and semantic relevance. For e-commerce search, you typically weight BM25 higher (0.6-0.7) because users search for specific product names. For content discovery, the vector side gets more weight.

ParadeDB also supports native paradedb.hybrid() for RRF scoring, but the weighted combination above gives you more control over the blend ratio.

RAG Pattern: BM25 as the Retriever

Retrieval-Augmented Generation (RAG) typically uses vector search to find relevant documents, but BM25 is equally effective — especially for fact-oriented queries where exact keyword matches matter (product specs, documentation, legal text).

// RAG retriever using BM25
async function retrieveContext(
  query: string,
  topK: number = 5
): Promise<string> {
  const results = await db.execute(
    sql`
      SELECT description, category,
             paradedb.score(id) as score
      FROM mock_items
      WHERE description ||| ${query}
      ORDER BY paradedb.score(id) DESC
      LIMIT ${topK}
    `
  );

  return results.rows
    .map((r: any) => `[${r.category}] ${r.description}`)
    .join("\n\n");
}

// Use in an LLM call
const context = await retrieveContext("waterproof hiking tent four season");
const llmResponse = await callLLM(
  `Answer based on this context:\n\n${context}\n\nQuestion: What four-season tents do you have?`
);

The advantage over pure vector retrieval: BM25 returns exact matches for product names and model numbers, which vector embeddings often miss. A combined BM25 + vector hybrid retriever (as shown above) gives the best of both worlds for production RAG systems.

Comparison: ParadeDB vs Alternatives

Here's how ParadeDB + Drizzle stacks up against the main alternatives for adding search to a PostgreSQL-backed application:

The bottom line: if your data lives in PostgreSQL and your search volume fits a single database, ParadeDB eliminates the most painful part of adding search — the ETL pipeline and second infrastructure. For massive search clusters at Google-scale, Elasticsearch still wins on raw throughput and sharding.

Performance Considerations

ParadeDB's BM25 index is laid out on disk as an LSM tree, where each segment combines an inverted index (for text search) and a columnar index (for faceted filters and aggregations). This architecture optimizes for high-frequency writes (common in OLTP workloads) while maintaining fast reads.

• Write throughput: BM25 indexes handle concurrent INSERT/UPDATE/DELETE without blocking — writes go to the memtable first, then flush to disk in segments.
• Query latency: Most searches return in milliseconds even across millions of documents, comparable to Elasticsearch single-node performance.
• Index size: Depends on tokenizer and column count, but roughly 1.5-3x the indexed text size. Monitor with pg_stat_progress_create_index during initial build.
• Concurrent queries: BM25 indexes participate in PostgreSQL's standard planning and execution — they benefit from connection pooling, query parallelism, and shared buffers.

Dropping and Rebuilding Indexes

During development, you'll need to drop and recreate indexes as your schema evolves. ParadeDB provides a Drizzle-compatible way:

import { sql } from "drizzle-orm";

// Drop existing BM25 index
await db.execute(sql`DROP INDEX search_idx`);

// Rebuild with updated columns
await indexing
  .bm25Index("search_idx")
  .on(
    mockItems.id,
    indexing.bm25Field(mockItems.description, tokenizer.simple({ stemmer: "english" })),
    mockItems.category,
    mockItems.rating,
    mockItems.inStock,
    mockItems.metadata,
  );

Production note: rebuilding a BM25 index on a large table is I/O-intensive. Monitor progress with pg_stat_progress_create_index and consider building in a maintenance window.

FAQ

Need Search in Your Application?

Adding production-quality search to a web application involves more than just installing a package — it requires thoughtful data modeling, index tuning, query optimization, and ongoing maintenance. If you're planning a search feature for your project and want an experienced perspective on architecture and implementation, reach out to me. I provide free initial consultations.

I'm a full-stack developer with 20+ years of experience building data-heavy web applications with PostgreSQL, Drizzle ORM, and modern TypeScript. Based in Minsk and working worldwide, let's discuss your project.