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:

• Release 0.26.1

• Control

• 0.26.0 migration notes

• 0.26.0 SQL upgrade

• pg_search v0.25.11 README

• pg_search v0.25.11 release

• PGXN 0.25.11 metadata

• 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.26.0 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.11 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.
• Versions 0.25.4 through 0.25.6 add paradedb.vector_clusters, partition-aware index builds, aggregate score joins, unified planner-warning controls, and bitmap-scan intersection. They also fix dropped bitmap-intersection children and sortable encoding for negative high-precision numeric values. Validate plans and ordering after the upgrade even when no explicit new index migration is documented.
• Versions 0.25.7–0.25.10 extend partition-aware concurrent builds, lateral array unnest and join aggregates, and parallel aggregation. Version 0.25.10 restores parallel top-K planning and fixes HOT-redirect row loss, missing JSON-path values, and aggregate defaults whose types cannot preserve the requested value. Review result correctness and query plans after upgrading.
• Version 0.25.11 fixes a backend crash when a search scan is cancelled or terminated and treats a null STRING_AGG delimiter as an empty separator.
• 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.

0.26.0 Migration

Version 0.26.0 changes vector storage to format 4. After updating the library and SQL extension, rebuild every index containing a vector field, including indexes with quantization disabled. Until rebuilt, vector queries fail with a rebuild-required error; writes continue and REINDEX CONCURRENTLY is supported. Quantization defaults on for vectors with at least 64 dimensions. Its settings take effect only during index creation or rebuilding. Indexes storing centroid_ratio or training_samples_per_centroid must be recreated using training_sample_ratio and max_leaf_size. Rebuild indexes with multiple regex-tokenized fields to correct pattern selection as well.

The release adds beta partition_by index partitioning, window-aggregate pushdown over joins, and opt-in paradedb.spill_to_disk. Aggregate visibility defaults to transaction; raw skips MVCC checks and must not be mistaken for transaction-visible results. Recheck ranking, recall, plans and storage requirements before application rollout.

Version 0.26.1

Version 0.26.1 fixes aggregate/filter correctness, RLS behaviour in generic plans and cache handling. It does not remove the 0.26.0 migration requirement: when crossing that release, rebuild every index containing a vector field and recreate indexes using removed vector options. Keep the required vector dependency and server preload/restart configuration.