pg_search: Modern Full-Text Search Inside PostgreSQL: Part 2

By /

In Part 1, we explored the architectural design, motivation, performance characteristics, and advantages of pg_search. We examined how it enhances PostgreSQL full-text search by introducing a BM25-based index access method and integrating efficient ranking directly within the database engine.

In this part, we shift from conceptual understanding to practical implementation. The goal of Part 2 is to demonstrate:

  • How to install and enable pg_search
  • How to create a BM25 index
  • How to execute search queries using the @@@ operator
  • How ranking and filtering behave in real scenarios
  • How the extension performs under different usage patterns

This section focuses on hands-on implementation using native PostgreSQL. pg_search can be used either through ParadeDB’s distribution or as a native PostgreSQL extension, depending on your deployment environment.

Implementation

Using pg_search in Native PostgreSQL

postgres=# CREATE EXTENSION pg_search;

Create a table

postgres=#
postgres=# CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    category TEXT,
    tags TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE
postgres=#

Insert data into articles table

postgres=# INSERT INTO articles (title, content, category, tags) VALUES
('Introduction to PostgreSQL',
'PostgreSQL is an advanced open source relational database system.',
'database',
'postgres,sql,relational'),

('Understanding Logical Replication',
'Logical replication in PostgreSQL allows selective data replication between databases.',
'database',
'postgres,replication,logical'),

('Scaling PostgreSQL Performance',
'Performance tuning in PostgreSQL involves indexing, query optimization, and configuration tuning.',
'database',
'postgres,performance,scaling'),

INSERT 0 3
postgres=#

Create a BM25 index on the articles table

postgres=# CREATE INDEX articles_bm25_idx
ON articles
USING bm25 (id, title, content, category, tags)
WITH (key_field = 'id');
CREATE INDEX
postgres=#

Scenario 1 : Basic Keyword Search

Retrieve rows containing a single keyword.

postgres=# SELECT *
FROM articles
WHERE content @@@ 'PostgreSQL';
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------
id         | 1
title      | Introduction to PostgreSQL
content    | PostgreSQL is an advanced open source relational database system.
category   | database
tags       | postgres,sql,relational
created_at | 2026-02-24 07:08:08.958528
-[ RECORD 2 ]-------------------------------------------------------------------------------------------------
id         | 2
title      | Understanding Logical Replication
content    | Logical replication in PostgreSQL allows selective data replication between databases.
category   | database
tags       | postgres,replication,logical
created_at | 2026-02-24 07:08:08.958528
-[ RECORD 3 ]-------------------------------------------------------------------------------------------------
id         | 3
title      | Scaling PostgreSQL Performance
content    | Performance tuning in PostgreSQL involves indexing, query optimization, and configuration tuning.
category   | database
tags       | postgres,performance,scaling
created_at | 2026-02-24 07:08:08.958528

postgres=#

What Happens:

  • The BM25 index is used.
  • Matching documents are retrieved efficiently.
  • No full table scan occurs.

Scenario 2: Relevance-Based Ranking

Sort results by relevance score.

postgres=# SELECT id, title, paradedb.score(id) AS relevance
FROM articles
WHERE content @@@ 'PostgreSQL'
ORDER BY relevance DESC;
 id |               title               | relevance
----+-----------------------------------+------------
  1 | Introduction to PostgreSQL        |  0.5222342
  2 | Understanding Logical Replication | 0.49191087
  3 | Scaling PostgreSQL Performance    |  0.4649158
(3 rows)

postgres=#

What Happens:

  • BM25 scoring is computed.
  • Documents with higher contextual importance rank higher.

Scenario 3: Multi-Term Search

Search using multiple keywords.

postgres=# SELECT *
FROM articles
WHERE content @@@ 'postgres replication scaling';
-[ RECORD 1 ]--------------------------------------------------------------------------------------
id         | 2
title      | Understanding Logical Replication
content    | Logical replication in PostgreSQL allows selective data replication between databases.
category   | database
tags       | postgres,replication,logical
created_at | 2026-02-24 07:08:08.958528

postgres=#

Behavior:

  • Documents containing more matching terms rank higher.
  • BM25 weights term rarity and frequency.

Scenario 4: Phrase Search

Match exact phrases.

postgres=# SELECT *
FROM articles
WHERE content @@@ '"logical replication"';
-[ RECORD 1 ]--------------------------------------------------------------------------------------
id         | 2
title      | Understanding Logical Replication
content    | Logical replication in PostgreSQL allows selective data replication between databases.
category   | database
tags       | postgres,replication,logical
created_at | 2026-02-24 07:08:08.958528

postgres=#

Behavior:

  • Exact phrase matches receive higher ranking.
  • Partial matches rank lower.

Scenario 5: Search + Structured Filtering

Combine search with category filtering.

postgres=# SELECT *
FROM articles
WHERE content @@@ 'PostgreSQL'
AND category = 'database'
ORDER BY paradedb.score(id) DESC;
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------
id         | 1
title      | Introduction to PostgreSQL
content    | PostgreSQL is an advanced open source relational database system.
category   | database
tags       | postgres,sql,relational
created_at | 2026-02-24 07:08:08.958528
-[ RECORD 2 ]-------------------------------------------------------------------------------------------------
id         | 2
title      | Understanding Logical Replication
content    | Logical replication in PostgreSQL allows selective data replication between databases.
category   | database
tags       | postgres,replication,logical
created_at | 2026-02-24 07:08:08.958528
-[ RECORD 3 ]-------------------------------------------------------------------------------------------------
id         | 3
title      | Scaling PostgreSQL Performance
content    | Performance tuning in PostgreSQL involves indexing, query optimization, and configuration tuning.
category   | database
tags       | postgres,performance,scaling
created_at | 2026-02-24 07:08:08.958528

postgres=#

What Makes This Powerful:

  • Filtering and ranking are handled efficiently.
  • No separate ranking pass required.
  • Suitable for product catalogs and documentation portals

 Scenario 6: Highlighted Snippet Generation

Generate UI-friendly highlighted search previews.

postgres=#
postgres=# SELECT id,
      paradedb.snippet(content)
FROM articles
WHERE content @@@ 'postgresql';
 id |                                                 snippet
----+---------------------------------------------------------------------------------------------------------
  1 | <b>PostgreSQL</b> is an advanced open source relational database system
  2 | Logical replication in <b>PostgreSQL</b> allows selective data replication between databases
  3 | Performance tuning in <b>PostgreSQL</b> involves indexing, query optimization, and configuration tuning
(3 rows)
postgres=#

Behavior:

  • Returns content snippets with matching terms emphasized.
  • Useful for search result pages.

Scenario 7: Real-Time Index Update (Insert)

Verify immediate index reflection.

INSERT INTO articles (title, content, category)
VALUES ('Postgres Search Guide', 'This article explains pg_search in detail', 'database');

Immediately run:

SELECT * FROM articles
WHERE content @@@ 'pg_search';
postgres=# INSERT INTO articles (title, content, category)
VALUES ('Postgres Search Guide', 'This article explains pg_search in detail', 'database');
INSERT 0 1
postgres=# SELECT *
FROM articles
WHERE content @@@ 'pg_search';
 id |         title         |                  content                  | category | tags |         created_at
----+-----------------------+-------------------------------------------+----------+------+----------------------------
  4 | Postgres Search Guide | This article explains pg_search in detail | database |      | 2026-02-24 09:17:15.838677
(1 row)

postgres=#

Scenario 8: Update Behavior

Validate ranking changes after update.

UPDATE articles
SET content = content || ' Advanced indexing techniques'
WHERE id = 1;

Run search again:

SELECT * FROM articles
WHERE content @@@ 'indexing';
postgres=# UPDATE articles
SET content = content || ' Advanced indexing techniques'
WHERE id = 1;
UPDATE 1

postgres=# SELECT *
FROM articles
WHERE content @@@ 'indexing';
 id |             title              |                                              content
                 | category |             tags             |         created_at
----+--------------------------------+----------------------------------------------------------------------------------
-----------------+----------+------------------------------+----------------------------
  3 | Scaling PostgreSQL Performance | Performance tuning in PostgreSQL involves indexing, query optimization, and confi
guration tuning. | database | postgres,performance,scaling | 2026-02-24 07:08:08.958528
  1 | Introduction to PostgreSQL     | PostgreSQL is an advanced open source relational database system. Advanced indexi
ng techniques    | database | postgres,sql,relational      | 2026-02-24 07:08:08.958528
(2 rows)

postgres=#

Scenario 9:  Delete Behavior

Ensure deleted records disappear from search.

DELETE FROM articles WHERE id = 1;

Search again:

postgres=# SELECT *
FROM articles
WHERE content @@@ 'postgresql';
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------
id         | 2
title      | Understanding Logical Replication
content    | Logical replication in PostgreSQL allows selective data replication between databases.
category   | database
tags       | postgres,replication,logical
created_at | 2026-02-24 07:08:08.958528
-[ RECORD 2 ]-------------------------------------------------------------------------------------------------
id         | 3
title      | Scaling PostgreSQL Performance
content    | Performance tuning in PostgreSQL involves indexing, query optimization, and configuration tuning.
category   | database
tags       | postgres,performance,scaling
created_at | 2026-02-24 07:08:08.958528

postgres=#

Scenario 10 — Performance Analysis

Evaluate execution plan.

postgres=# EXPLAIN ANALYZE
SELECT *
FROM articles
WHERE content @@@ 'postgresql'
ORDER BY paradedb.score(id) DESC;
                                                                         QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------
-
 Sort  (cost=10.02..10.02 rows=1 width=148) (actual time=0.641..0.642 rows=2 loops=1)
   Sort Key: (paradedb.score(id)) DESC
   Sort Method: quicksort  Memory: 25kB
   ->  Custom Scan (ParadeDB Scan) on articles  (cost=10.00..10.01 rows=1 width=148) (actual time=0.624..0.630 rows=2 loops=1)
         Table: articles
         Index: articles_bm25_idx
         Segment Count: 1
         Heap Fetches: 2
         Exec Method: NormalScanExecState
         Scores: true
         Tantivy Query: {"with_index":{"query":{"parse_with_field":{"field":"content","query_string":"postgresql","lenient":null,"conjunction_mode":null}}}}
 Planning Time: 0.508 ms
 Execution Time: 0.746 ms
(13 rows)

postgres=#
Observe:
  • Index scan usage
  • Execution time
  • Cost estimates
  • Absence of sequential scan

This implementation can be further extended with additional real-world scenarios to deepen evaluation and benchmarking.
You can perform large dataset testing to measure index build time, query latency, and ranking stability at scale.
JSON-based and multi-column search can be explored to validate flexibility across structured and semi-structured data models in PostgreSQL.
Weighted searches across multiple fields can also be tested to fine-tune relevance behavior.
Additionally, advanced capabilities such as fuzzy search can be evaluated, which are not natively available in traditional PostgreSQL full-text search configurations.

Conclusion

In Part 1, we explored the architecture, motivation, and performance advantages of pg_search in PostgreSQL.
In Part2, we demonstrated practical implementation, including installation, BM25 indexing, and real-world search scenarios.
Together, both parts show how pg_search integrates modern relevance ranking directly inside PostgreSQL.
It enables fast, accurate, and transactionally consistent search without external search engines.
Overall, pg_search provides a powerful and scalable approach to full-text search within PostgreSQL.

See this in action at PGConf India 2026 – pg_search: Bringing Elasticsearch-Grade Search to PostgreSQL presented by Mithun Chicklore Yogendra.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top