ParadeDB pg_search

What it is

ParadeDB pg_search adds BM25-ranked full-text search and faceted analytics directly to PostgreSQL. The index stays beside the source table, so application writes do not need a separate search synchronization pipeline.

What it provides

BM25 relevance ranking across multiple fields
Tokenizers, fuzzy matching, phrase queries, highlighting, and facets
Search indexes maintained with the PostgreSQL data they cover

Use it for product catalogs, documentation search, support tickets, log exploration, faceted navigation, or hybrid lexical-and-vector retrieval.

Use it with Rent

extension vector {
  name = "vector"
}

extension pg_search {
  name = "pg_search"
}

The RSL declaration makes installation and migration intent part of the reviewed application schema. Register the typed pack at runtime to call its APIs:

let search = extensions.register_pack::<PgSearch>()?;
let index = Bm25Index::new(
    &search,
    "posts_search",
    ["id", "title", "body"],
    "id",
)?
.fast_text_field("title")?;

let migration_operation = index.operation("public", "posts")?;

let query = search.search(
    post::body(),
    "rust database toolkit",
);

let posts = client
    .post()
    .filter_raw(query.predicate())
    .order_by_expression_desc(query.rank(post::id()))
    .limit(20)
    .all()
    .await?;

let highlighted_body = query.highlight();
let title_facets = query.facet(
    serde_json::json!({"terms": {"field": "title"}}),
);

Text fields used for facets or sorting must be stored as fast fields. fast_text_field validates that the field is included in the index and emits ParadeDB's text_fields configuration for the migration.

Install on a PostgreSQL server that provides pg_search. Stock PostgreSQL does not include the extension. This example includes vector, a dependency of the ParadeDB installation used here. The complete project below includes a first migration that installs that dependency before pg_search, so a fresh database can reproduce the setup.

Run the application

The complete project below includes an authored SQL migration. It installs the extensions in dependency order and creates the post table. Review it, seal its checksum, and apply it before running the example. The application then creates a BM25 index, stores a post, and searches its body through the generated client.

Complete example

Download this project, or copy the files below into an empty directory.

Replace RENT_REPOSITORY_URL in Cargo.toml with the URL supplied with your preview access. Use the matching Rent CLI release.

Use an empty PostgreSQL database with the extension binaries available on its server. Set DATABASE_URL to that database and follow any server setup requirements described above.

rent extension check --provider self-hosted
rent generate
rent migrate seal
rent migrate apply
cargo run

If your host preinstalls other extensions, declare those in RSL as well. For a database with existing application tables, baseline it first.

The included first migration installs vector before pg_search, which depends on it. That reviewed migration also creates the posts table described by the schema.

Cargo.toml

[package]
name = "rent-extension-pg-search"
version = "0.1.0"
edition = "2024"
[workspace]
[[bin]]
name = "rent-extension-pg-search"
path = "main.rs"
[dependencies]
rent = { git = "RENT_REPOSITORY_URL", tag = "v0.8.6", default-features = false, features = ["postgres", "extensions", "migrate"] }
rent-ext-pg-search = { git = "RENT_REPOSITORY_URL", tag = "v0.8.6" }
anyhow = "1.0.104"
tokio = { version = "1.53.1", features = ["full"] }

rent.toml

schema = "schema.rsl"
generated = "generated"
migrations = "migrations"

[database]
dialect = "postgres"
backends = ["postgres"]

schema.rsl

datasource db {
  provider = "postgresql"
}

extension vector {
  name = "vector"
}

extension pg_search {
  name = "pg_search"
}

model Post {
  id    BigInt  @id
  title String
  body  String  @default("")
}

main.rs

use anyhow::Result;
use rent::sql::ast::column;
use rent::{driver::DatabasePool, extension::ExtensionRegistry};
#[allow(dead_code, unused_imports)]
#[rustfmt::skip]
#[path = "generated/mod.rs"]
mod generated;
#[tokio::main]
async fn main() -> Result<()> {
    let pool = DatabasePool::connect_url(&std::env::var("DATABASE_URL")?).await?;
    let client = generated::client::Client::new(pool.clone());
    let mut extensions = ExtensionRegistry::default();
    use rent_ext_pg_search::{PgSearch, PgSearchExt};
    let pack = extensions.register_pack::<PgSearch>()?;
    pool.execute(
        "CREATE INDEX IF NOT EXISTS posts_search ON posts USING bm25 (id, title, body) WITH (key_field = 'id')",
    )
    .await?;
    client
        .upsert_post(generated::post::unique::id)
        .id(1)
        .title("Rust database toolkit")
        .body("Typed queries for the writing app")
        .save()
        .await?;
    let search = pack.search(column(generated::post::Post::body.name()), "typed queries");
    let posts = client
        .post()
        .filter_raw(search.predicate())
        .order_by_expression_desc(search.rank(column(generated::post::Post::id.name())))
        .limit(20)
        .all()
        .await?;
    println!("Search results: {}", posts.len());
    Ok(())
}

migrations/001_extensions.sql

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_search;
CREATE TABLE public.posts (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  body text NOT NULL DEFAULT ''
);