pg_search

pg_search: Full text search for PostgreSQL using BM25

Module:

PGEXT

Overview

PIGSTY 3rd Party Extension: pg_search : pg_search: Full text search for PostgreSQL using BM25

Information

Extension ID: 2100
Extension Name: pg_search
Package Name: pg_search
Category: FTS
License: AGPLv3
Website: https://github.com/paradedb/paradedb/tree/dev/pg_search
Language: Rust
Extra Tags: pgrx
Comment:

Metadata

Latest Version: 0.15.20
Postgres Support: 17,16,15,14
Need Load: Shared library do not need explicit loading
Need DDL: Need CREATE EXTENSION DDL
Relocatable: Can be installed into other schemas
Trusted: Trusted, Can be created by user with CREATE Privilege
Schemas: paradedb
Requires: N/A

RPM / DEB

RPM Repo: PIGSTY
RPM Name: pg_search_$v
RPM Ver : 0.15.18
RPM Deps: N/A
DEB Repo: PIGSTY
DEB Name: postgresql-$v-pg-search
DEB Ver : 0.15.20
DEB Deps: N/A

Availability

OS	Arch	PG17	PG16	PG15	PG14
`el8`	`x86_64`	`pg_search_17` PIGSTY 0.15.18	`pg_search_16` PIGSTY 0.15.18	`pg_search_15` PIGSTY 0.15.18	`pg_search_14` PIGSTY 0.15.18
`el8`	`aarch64`	`pg_search_17` PIGSTY 0.15.18	`pg_search_16` PIGSTY 0.15.18	`pg_search_15` PIGSTY 0.15.18	`pg_search_14` PIGSTY 0.15.18
`el9`	`x86_64`	`pg_search_17` PIGSTY 0.15.18	`pg_search_16` PIGSTY 0.15.18	`pg_search_15` PIGSTY 0.15.18	`pg_search_14` PIGSTY 0.15.18
`el9`	`aarch64`	`pg_search_17` PIGSTY 0.15.18	`pg_search_16` PIGSTY 0.15.18	`pg_search_15` PIGSTY 0.15.18	`pg_search_14` PIGSTY 0.15.18
`d12`	`x86_64`	`postgresql-17-pg-search` PIGSTY 0.15.20	`postgresql-16-pg-search` PIGSTY 0.15.20	`postgresql-15-pg-search` PIGSTY 0.15.20	`postgresql-14-pg-search` PIGSTY 0.15.20
`d12`	`aarch64`	`postgresql-17-pg-search` PIGSTY 0.15.20	`postgresql-16-pg-search` PIGSTY 0.15.20	`postgresql-15-pg-search` PIGSTY 0.15.20	`postgresql-14-pg-search` PIGSTY 0.15.20
`u22`	`x86_64`	`postgresql-17-pg-search` PIGSTY 0.15.20	`postgresql-16-pg-search` PIGSTY 0.15.20	`postgresql-15-pg-search` PIGSTY 0.15.20	`postgresql-14-pg-search` PIGSTY 0.15.20
`u22`	`aarch64`	`postgresql-17-pg-search` PIGSTY 0.15.20	`postgresql-16-pg-search` PIGSTY 0.15.20	`postgresql-15-pg-search` PIGSTY 0.15.20	`postgresql-14-pg-search` PIGSTY 0.15.20
`u24`	`x86_64`	`postgresql-17-pg-search` PIGSTY 0.15.20	`postgresql-16-pg-search` PIGSTY 0.15.20	`postgresql-15-pg-search` PIGSTY 0.15.20	`postgresql-14-pg-search` PIGSTY 0.15.20
`u24`	`aarch64`	`postgresql-17-pg-search` PIGSTY 0.15.20	`postgresql-16-pg-search` PIGSTY 0.15.20	`postgresql-15-pg-search` PIGSTY 0.15.20	`postgresql-14-pg-search` PIGSTY 0.15.20

Installation

Install pg_search via the pig CLI tool:

pig ext install pg_search

Install pg_search via Pigsty playbook:

./pgsql.yml -t pg_extension -e '{"pg_extensions": ["pg_search"]}' # -l <cls>

Install pg_search RPM from YUM repo directly:

dnf install pg_search_17;
dnf install pg_search_16;
dnf install pg_search_15;
dnf install pg_search_14;

Install pg_search DEB from APT repo directly:

apt install postgresql-17-pg-search;
apt install postgresql-16-pg-search;
apt install postgresql-15-pg-search;
apt install postgresql-14-pg-search;

Create pg_search extension on PostgreSQL cluster:

CREATE EXTENSION pg_search;

Usage

https://docs.paradedb.com/documentation/getting-started/quickstart

CREATE EXTENSION pg_search;

ALTER SYSTEM SET paradedb.pg_search_telemetry TO 'off';

CALL paradedb.create_bm25_test_table(
  schema_name => 'public',
  table_name => 'mock_items'
);
    
SELECT description, rating, category FROM mock_items LIMIT 3;

CALL paradedb.create_bm25(
        index_name => 'search_idx',
        schema_name => 'public',
        table_name => 'mock_items',
        key_field => 'id',
        text_fields => paradedb.field('description', tokenizer => paradedb.tokenizer('en_stem')) ||
                       paradedb.field('category'),
        numeric_fields => paradedb.field('rating')
     );

SELECT description, rating, category
FROM search_idx.search('(description:keyboard OR category:electronics) AND rating:>2',limit_rows => 5);

CALL paradedb.create_bm25(
        index_name => 'ngrams_idx',
        schema_name => 'public',
        table_name => 'mock_items',
        key_field => 'id',
        text_fields => paradedb.field('description', tokenizer => paradedb.tokenizer('ngram', min_gram => 4, max_gram => 4, prefix_only => false)) ||
                       paradedb.field('category')
     );

SELECT description, rating, category
FROM ngrams_idx.search('description:blue');

Feedback

Was this page helpful?

Glad to hear it! Please tell us how we can improve.

Sorry to hear that. Please tell us how we can improve.

Last modified 2025-05-27: bump 421 extensions (e56fb2c)