pg_search

Overview

bm25 am conflicts with pg_textsearch and vchord_bm25

Version

Build

You can build the RPM / DEB packages for pg_search using pig build:

pig build pkg pg_search         # build RPM / DEB packages

Install

You can install pg_search directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pg_search;          # Install for current active PG version

pig ext install -y pg_search -v 18  # PG 18
pig ext install -y pg_search -v 17  # PG 17
pig ext install -y pg_search -v 16  # PG 16
pig ext install -y pg_search -v 15  # PG 15

dnf install -y pg_search_18       # PG 18
dnf install -y pg_search_17       # PG 17
dnf install -y pg_search_16       # PG 16
dnf install -y pg_search_15       # PG 15

apt install -y postgresql-18-pg-search   # PG 18
apt install -y postgresql-17-pg-search   # PG 17
apt install -y postgresql-16-pg-search   # PG 16
apt install -y postgresql-15-pg-search   # PG 15

Preload:

shared_preload_libraries = 'pg_search';

Create Extension:

CREATE EXTENSION pg_search;

Usage

Sources: ParadeDB extension install docs, create-index docs, match docs, score docs, highlight docs, v0.24.0 release, pg_search README

pg_search is ParadeDB’s BM25-based search extension for PostgreSQL. The upstream README says support starts at PostgreSQL 15; Pigsty packages 0.24.0 for PostgreSQL 15-18 and builds it with cargo-pgrx 0.18.1.

Enable And Create The Extension

shared_preload_libraries = 'pg_search'

CREATE EXTENSION pg_search;

The self-hosted extension docs require shared_preload_libraries = 'pg_search' before CREATE EXTENSION.

Create A BM25 Index

Current examples use the bm25 access method with a unique key field:

CREATE INDEX search_idx ON mock_items
USING bm25 (id, description, category, rating)
WITH (key_field = 'id');

Only one BM25 index is supported per table. key_field is mandatory, must be unique, and must be the first indexed column; text key fields must be untokenized.

Query Operators And Helpers

The current docs use these query operators:

• |||: match disjunction, equivalent to term1 OR term2.
• &&&: match conjunction, equivalent to term1 AND term2.

Examples:

SELECT description, rating
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY rating
LIMIT 5;

SELECT description, pdb.score(id) AS score
FROM mock_items
WHERE description &&& 'running shoes'
ORDER BY score DESC
LIMIT 5;

SELECT description, pdb.snippet(description) AS snippet, pdb.score(id) AS score
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY score DESC
LIMIT 5;

Useful result helpers include pdb.score(id), pdb.snippet(field), pdb.snippets(field), and pdb.snippet_positions(field). Highlighting is relatively expensive and is not supported for fuzzy search queries.

Notes

• The old quickstart URL was removed; use the versioned docs pages above for current |||, &&&, scoring, and highlighting syntax.
• Release 0.24.0 requires preloading pg_search, upgrades pgrx to 0.18.1, and documents crash-recovery, ltree, and inline-tokenizer work without changing the basic BM25 query examples above.
• The Pigsty metadata notes that the bm25 access method conflicts with pg_textsearch and vchord_bm25; do not preload competing BM25 access-method extensions in the same cluster without testing the target combination.