pg_trgm

What it is

pg_trgm is a PostgreSQL-supplied extension that compares text by shared three-character groups. It supports similarity scoring and indexed fuzzy matching without introducing a separate search system.

What it provides

Text similarity scores and threshold-based matching
Fast indexed LIKE, ILIKE, regular-expression, and similarity searches
GiST and GIN operator classes for text columns

Use it for typo-tolerant name lookup, autocomplete, deduplication, fuzzy product search, or suggesting a likely match after a misspelling.

Use it with Rent

extension pg_trgm {
  name = "pg_trgm"
}

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

If the author directory stores Ken Brooks, a search for the misspelled Ken Broks can still find him:

let trgm = extensions.register_pack::<PgTrgm>()?;
let matches = trgm.similar_to(
    user::name(),
    "Ken Broks",
);

let score = trgm.similarity_to(
    user::name(),
    "Ken Broks",
);

let users = client
    .user()
    .filter_raw(matches)
    .order_by_expression_desc(score)
    .all()
    .await?;

Add the extension declaration above to your application's schema. Confirm that your PostgreSQL provider offers the extension and that your migration role can install it, then apply the migration and run your application:

rent migrate dev --name enable_pg_trgm
cargo run

similar_to uses the PostgreSQL session similarity threshold. Add an appropriate GIN or GiST trigram index and tune the threshold for your workload.

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 dev --name initial
cargo run

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

Cargo.toml

[package]
name = "rent-extension-pg-trgm"
version = "0.1.0"
edition = "2024"
[workspace]
[[bin]]
name = "rent-extension-pg-trgm"
path = "main.rs"
[dependencies]
rent = { git = "RENT_REPOSITORY_URL", tag = "v0.8.6", default-features = false, features = ["postgres", "extensions", "migrate"] }
rent-ext-pg-trgm = { 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 pg_trgm {
  name = "pg_trgm"
}

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

model User {
  id   BigInt  @id
  name String
}

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_trgm::{PgTrgm, PgTrgmExt};
    let pack = extensions.register_pack::<PgTrgm>()?;
    client
        .upsert_user(generated::user::unique::id)
        .id(1)
        .name("Ken Brooks")
        .save()
        .await?;
    let users = client
        .user()
        .filter_raw(pack.similar_to(column(generated::user::User::name.name()), "Ken Broks"))
        .order_by_expression_desc(pack.similarity_to(column(generated::user::User::name.name()), "Ken Broks"))
        .all()
        .await?;
    println!("Matching authors: {}", users.len());
    Ok(())
}

pg_trgm

What it is

What it provides

Use it with Rent

Run the application

Complete example

On this page