pg_search

pg_search : Full text search for PostgreSQL using BM25

Overview

Packages

Source

pig build pkg pg_search;		# build deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

pig install pg_search;		# install via package name, for the active PG version

pig install pg_search -v 18;   # install for PG 18
pig install pg_search -v 17;   # install for PG 17
pig install pg_search -v 16;   # install for PG 16
pig install pg_search -v 15;   # install for PG 15

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_search';

Create this extension with:

CREATE EXTENSION pg_search;

Usage

• Sources: ParadeDB extension install docs, quickstart, v0.23.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, and the v0.23.0 self-hosted install docs still require preloading the library before CREATE EXTENSION.

Enable And Create The Extension

shared_preload_libraries = 'pg_search'

CREATE EXTENSION pg_search;

The self-hosted extension docs for v0.23.0 describe prebuilt binaries for Postgres 15+.

Create A BM25 Index

Quickstart 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');

The v0.23.0 release also notes newly tunable BM25 k1 and b parameters per field.

Query Operators And Helpers

The current quickstart uses 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)
FROM mock_items
WHERE description &&& 'running shoes'
ORDER BY score DESC
LIMIT 5;

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

Notes

The development README points users to the docs site for installation and usage instead of documenting SQL details inline. The quickstart is therefore the authoritative usage surface for current pg_search syntax, and it reflects the post-0.20 API rather than the older @@@ examples still found in some secondary materials.