pg_search

Overview

bm25 am conflicts with pg_textsearch; PG15-16 require shared_preload_libraries while PG17-18 do not.

Version

Build

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

pig build pkg pg_search         # build 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, 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.