pg_search

pg_search : Full text search for PostgreSQL using BM25

Overview

Packages

Source

pig build pkg pg_search;		# build rpm/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 CASCADE; -- requires vector

Usage

Sources:

• pg_search v0.25.2 README
• pg_search v0.25.2 release
• pg_search v0.25.2 changelog
• pg_search v0.25.1 migration notes
• Create a ParadeDB index
• Full-text match operators
• BM25 scoring
• Highlighting and snippets
• Index vectors
• Query vectors
• Hybrid-search overview

pg_search 0.25.2 adds ParadeDB’s full-text, structured, vector, and hybrid search index to PostgreSQL. Version 0.25 uses the paradedb index access method; the older bm25 access-method name remains a compatibility alias. The extension requires vector, supports PostgreSQL 15-18 upstream, and must be loaded through shared_preload_libraries.

Install and Build an Index

shared_preload_libraries = 'pg_search'

Restart PostgreSQL, then create the extension and a table with a stable unique key:

CREATE EXTENSION pg_search CASCADE;

CREATE TABLE documents (
  id          bigint PRIMARY KEY,
  title       text,
  body        text,
  category    text,
  embedding   vector(768)
);

CREATE INDEX documents_search_idx ON documents
USING paradedb (
  id,
  title,
  body,
  category,
  embedding vector_cosine_ops
)
WITH (key_field = 'id');

The key_field must be the first indexed column and uniquely identify every row. A text key must be indexed without tokenization. A table can have only one ParadeDB index, so include every searchable field in that index.

Full-Text Search

Use ||| to match any token and &&& to require all tokens:

SELECT id, title, pdb.score(id) AS score
FROM documents
WHERE body ||| 'postgresql search'
ORDER BY score DESC, id;

SELECT id, pdb.snippet(body) AS excerpt
FROM documents
WHERE body &&& 'postgresql indexing';

pdb.score(key_field) exposes the relevance score for the current row. pdb.snippet(indexed_text_column) returns a highlighted excerpt. These helpers are meaningful only in a query driven by a ParadeDB search predicate.

Vector Search

Vector indexing is beta in the 0.25 line and uses the vector type from pgvector. Choose the operator class when the index is created; changing the metric requires rebuilding the index.

SELECT id, title, embedding <=> $1::vector AS distance
FROM documents
WHERE id @@@ pdb.all()
ORDER BY embedding <=> $1::vector, id
LIMIT 20;

Supported index operator classes are vector_l2_ops, vector_ip_ops, and vector_cosine_ops. The 0.25 vector index does not index halfvec, sparsevec, or bit columns.

Hybrid Search

A single ParadeDB index can combine lexical predicates, structured filters, and vector ordering. For more elaborate fusion, use the documented RRF and weighted hybrid-search functions instead of adding scores from unrelated scales directly.

SELECT id, title, pdb.score(id) AS lexical_score
FROM documents
WHERE body ||| 'postgresql extension'
  AND category === 'database'
ORDER BY embedding <=> $1::vector, id
LIMIT 20;

Version 0.25.2 and Caveats

• Version 0.25 renamed the primary index access method from bm25 to paradedb. Existing USING bm25 definitions remain supported, but new examples should use USING paradedb.
• Version 0.25.1 supports deterministic vector tie breakers and pushes the vector arm of reciprocal-rank-fusion queries into the index. It also adds paradedb.vector_clustering_threshold, whose default is 500, and caps vector-index build parallelism at four workers.
• Version 0.25.1 removes paradedb.vector_cluster_probe_epsilon and changes the vector-index bounds gate. After upgrading a database from 0.25.0, REINDEX every ParadeDB index that contains a vector field; installing the new shared library and running ALTER EXTENSION alone is not sufficient for those indexes.
• Version 0.25.2 is a stability and correctness release. It fixes fieldless more_like_this with vector columns, pdb.fuzzy under generic prepared plans, orphaned dynamic filters, several parallel subplan and MPP plan-shape errors, and tightens access controls for typemod definitions. It adds no further index migration beyond the inherited 0.25.0 vector-index rebuild.
• CREATE EXTENSION pg_search CASCADE can install the required vector extension, but every server process still needs the preload configuration and restart first. Loading it only with LOAD or session_preload_libraries is insufficient.
• Query plans, tokenization, and ranking can change when an index is rebuilt with different field options. Test relevance and vector recall with production-shaped data before rollout.